Open transactions – transactions that run too long or are hung – can cause havoc on the ConfigMgr database. Notice a backlog of files (DDRs or Mifs)? Slow processing in general? Collections having a problem updating? You might want to give this a quick check.
First, lets find out what the oldest transaction on the ConfigMgr database is. Open SQL Management Studio, and start a new query. Change the focus to your SCCM database,and run this command first:
Did any transactions come back? If so, check their start time and make sure they aren’t too long in the past. Anything past a couple of minutes, except for the largest of queries, would be unacceptable. Note the Process ID – we will use that next.
So now we know what the oldest transaction is, but what do we do with this info? Let’s see what that transaction is doing. Run this command next:
You will see a snippet of the code that the process is running. Does this help track down what the open transaction is? Perhaps a long-running query rule for a collection, or a site maintenance task that is hung. Typically you can get a decent idea what it is by examine the output of Inputbuffer.
Now that you know what is causing the problem, how do you deal with it? Well, if you are sure that you want to stop this transaction, you do it easily with one more command. Use it with caution!
Note that if is sometimes helpful to do these same steps on the tempdb of the SQL server the ConfigMgr database sits on…especially for long running transactions.