SQL Overview VI - Monitoring Process Locks

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

    David Bird

  • 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.

  • 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

  • Thanks for yet another nice article in this series.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • This is great!

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

    David Bird

  • 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

  • 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.

  • 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.

  • 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

  • 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

  • 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

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

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