How do I find unique indexes in SQL-server 2005.

I am trying to get to grips with a database.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugman
select sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>

How do I find unique indexes in SQL-server 2005.

I am trying to get to grips with a database.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugmanselect sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>

How do I find unique indexes in SQL-server 2005.

I am trying to get to grips with a database.
At the moment I am locking at contraints in general.
Most I can find in the information_schema views.
(Is there a webpage or -site which does describe this ?).
But how do I find indexes which are not implemented as constraints,
especially 'unique - indexes' ?
Thanks for your time and attention,
ben brugmanselect sysobjects.name from sysobjects join
sysindexes on sysobjects.id=sysindexes.id
where sysindexes.status=2 and type='u'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:OaBHeCg5GHA.1256@.TK2MSFTNGP04.phx.gbl...
>I am trying to get to grips with a database.
> At the moment I am locking at contraints in general.
> Most I can find in the information_schema views.
> (Is there a webpage or -site which does describe this ?).
> But how do I find indexes which are not implemented as constraints,
> especially 'unique - indexes' ?
> Thanks for your time and attention,
> ben brugman
>

How do I find out whos locking a table? and than do something about it?

So randomly every 1 to 6 days queries start timing out and I'm almost positive it's from an improperly terminated transaction

Is there a way to snoop this out the next time it happens? Like when a table's locked I can look and see yea this is the transaction it's in the middle of?

You can either do this pro-actively or reactively.

Proactive : Set up a job that runs perhaps every minute, queries sysprocesses and spits the result into a table. So when a timeout happens you can query this table and see who/what/when.

Reactive: As soon as you see a timeout, query sysprocesses, get the spid, do dbcc inputbuffer(@.spid) get the T-SQL and fix it.

|||

If you already know the proc, fix it.

|||

You can use the Activity Monitor in SQl Server 2005 Management studio. Located under Management -> Activity Monitor.

|||

jperry4:

You can use the Activity Monitor in SQl Server 2005 Management studio. Located under Management -> Activity Monitor.

SWEET that's exactly what I've always been looking for :)

one more questions
There's like 100 processes in there that are "Sleeping" "Awaiting COmmand"

Are these like connections that haven't been closed?
So in CODE if conn.Close() or conn.Dispose()

NEver got called it's just left dangling in a "Sleeping" "Awaiting Command" state?

Shane

|||

No, those are likely connections that are in the connection pool.

Don't get confused by a database physical connection and a logical connection. Conn.Close closes the logical connection, which (probably) is tied to a physical one. The physical connection is then returned to the connection pool waiting for someone to request a connection again.

|||

Cool thanks for the info :)

Yea I ran a bunch of tests with basic programs just opening and closing connections and then read a bunch of stuff on pooling...

The Activity Monitor tool worked out great though for sniffing around the application and finding all the leaks though

Finding those places where the logical was never shut down so it kept opening 100s of new ones