SQL Overview VI - Monitoring Process Locks

Question

Post reply

SQL Overview VI - Monitoring Process Locks

David Bird

SSCarpal Tunnel

Points: 4669
More actions
March 30, 2010 at 10:43 pm

#225709

Comments posted to this topic are about the item SQL Overview VI - Monitoring Process Locks
David Bird

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

b_seattle SSC Rookie Points: 35 More actions · Answer 1

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 Right there with Babe Points: 790 More actions · Answer 2

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 SSC Guru Points: 281334 More actions · Answer 3

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[/url]
Learn Extended Events

Jeff Olson-435047 SSC Journeyman Points: 88 More actions · Answer 4

Jeff Olson-435047

SSC Journeyman

Points: 88

April 1, 2010 at 11:35 am

#1144315

This is great!

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 5

Some components such as fn_ServerInstanceName is included in the file attachment for the previous article: SQL Overview Part V[/url]

David Bird

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 6

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 SSCertifiable Points: 6102 More actions · Answer 7

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 Hall of Fame Points: 3553 More actions · Answer 8

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
😛

shurdul SSC Rookie Points: 31 More actions · Answer 9

Dosh Express is a Payday lender, and money can be transferred from our bank account to yours within minutes Payday Express[/url]

business lawyers melbourne

Feeg SSCrazy Points: 2766 More actions · Answer 10

Hi David thanks for this awesome tool,do you have any updates for this tool or planning any perhaps?

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 11

Hi I release SQL Overview VII - SSRS Reports and Subscriptions in November 2014. It also contains a change History. The history section article was not published I guess it lacked something. The 7 article did have the package with the history details.

Since then I have making modifications to correct problems. Adding reports for other needs. Adding some collections. Mostly to keep track of what the outsourced DBA's do and don't do correctly.

If I find a place, I might put it up so you can get latest version. When I do that I need to remove any corporate details/customization.

I have been working on another package for collecting Login/Security information for auditing purposes. Ideally I would be able view and recreated any permission granted to a login and the id itself. Reports would include logins added each month. Logins granted SYSADMIN. Password weakness. Other stuff.

Security is tricky.

I am glad you find this tool useful. It has help me prevent many potential problems and identify ones that are easily missed.

David Bird