SQL Overview VI - Monitoring Process Locks

  • David Bird

    SSCarpal Tunnel

    Points: 4669

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

    David Bird

  • b_seattle

    SSC Rookie

    Points: 35

    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

    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: 281210

    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: 78

    This is great!

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    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

    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

    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: 3503

    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

    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: 2675

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

    MCITP: Database Administrator 2005
    MCTS DBA 2008
    MCSA DBA 2014

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    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 12 (of 12 total)

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