SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Overview VI - Monitoring Process Locks


SQL Overview VI - Monitoring Process Locks

Author
Message
David Bird
David Bird
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 1230
Comments posted to this topic are about the item SQL Overview VI - Monitoring Process Locks

David Bird
b_seattle
b_seattle
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 550
Great article!

Just one thing to add-- there is a FREE tool out there that does this and more:

SQL Deadlock Detector

We used to have a job/package set up before we found SQL Deadlock Detector. What's nice about Deadlock Detector is that it can send emails when long running locks or deadlocks are detected. Can't beat that.
Luke C
Luke C
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 1203
David, I just want to say thanks for creating this package. I have found it to be paramount in my ability to manage multiple instances and saves several hours every day by no longer having the need to visit each and every instance as part of my morning routine.

Luke C
MCSE: Data Platform, MCP, MCTS, MCITP - Database Administrator & Database Developer
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48473 Visits: 18569
Thanks for yet another nice article in this series.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Olson-435047
Jeff Olson-435047
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 158
This is great!
David Bird
David Bird
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 1230
Some components such as fn_ServerInstanceName is included in the file attachment for the previous article: SQL Overview Part V

David Bird
David Bird
David Bird
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 1230
I recently discovered the space in the new table SQL_Sysprocesses was not being freed even though the delete old rows statement was successful for each package execution. To fix this issue, I altered the table's index to be clustered. The database went from from 40 GB to 220 mb, a very big difference in space. I would recommend everyone making this change.

Afterwards I found the Microsoft KB article 913399 which confirmed this issue in SQL Server 2005, 2000, and 7. It provides several work arounds including the cluster index.

Sorry for the mistake

David Bird
cengland0
cengland0
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2018 Visits: 1300
Can't wait to try this out. It's something I've always wanted to do but didn't have the time to develop it myself.
jswong05
jswong05
SSC-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 476
You should always have a cluster index on a table when you can. You should always use row-versioning based isolation level - in SQL Server snapshot isolation. In Oracle, it is done at "Read Committed" isolation.

Jason
http://dbace.us
:-P
shurdul
shurdul
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Dosh Express is a Payday lender, and money can be transferred from our bank account to yours within minutes Payday Express
business lawyers melbourne
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search