Transaction log for the database is growing and system SPID is holding open transaction
Posted by Karthick P.K on May 18, 2012
Transaction log for the database is growing and system SPID is holding open transaction
Select log_reuse_wait_desc From Sys.databases where name=’%’ will return
‘ACTIVE_TRANSACTION’
DBCC Opentran says there is a Opentran held by system thread (Similar to SPID (server process ID): 7s and 6s in below example)
Oldest active transaction:
SPID (server process ID): 7s
UID (user ID) : -1
Name : user_transaction
LSN : (543263:28204:1)
Start time : Dec 8 2011 11:02:19:483PM
SID : 0x01
Replicated Transaction Information:
Oldest distributed LSN : (544101:227459:27)
Oldest non-distributed LSN : (543263:28204:1)
Oldest active transaction:
SPID (server process ID): 6s
UID (user ID) : -1
Name : tran_sp_MScreate_peer_tables
LSN : (958510:111529:1)
Start time : May 6 2012 5:33:37:240AM
SID : 0x01
Replicated Transaction Information:
Oldest distributed LSN : (962272:93878:5)
Oldest non-distributed LSN : (0:0:0)
If we see Name: tran_sp_MScreate_peer_tables in DBCC opentran
Follow http://support.microsoft.com/kb/2509302 ie. Restart SQL-Server and wait for Script upgrade to complete and then start the SQL Server Agent.
If you see Name : user_transaction then script upgrade for database has failed leaving an open transaction. Verify the SQL Server error log to identify why the script upgrade has failed.
We can enable Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log while doing script upgrade . Identify the batch which is failing and troubleshoot the batch.
For more information Follow : https://mssqlwiki.com/2010/11/17/sqlserver2008-script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100_msdb_upgrade-sql-encountered-error-574-state-0-severity-16/
Thanks
Karthick P.K
MSSQLWIKI 
SEM said
Howdy! I simply would like to give an enormous thumbs up for the nice information you’ve gotten here on this post.
I can be coming back to your blog for more soon.
Jim Johnston said
I am having this problem, but it is not due to any upgrade to SQL or to Replication. It appears to be associated with installing compression on a 2008 db that is running replication. We have has this happen twice in the past 24 hours. I am attempting to compress the tables in a database that has transactional replication on several tables (about 40 or so). The entire database has about 4000 tables. Here is a summary of what I’m seeing:
Today we have the same issue with the system spid holding an open transaction, and the transaction log filling. After the reboot, the runaway transaction cleared, and the pecentage of the log filling went from 96 percent to .05 percent – you can see this by running “dbcc sqlperf (logspace)”. The log is back up to 92 percent full, and the transaction dumps are successful. What this means is that the “active” part of the log is filling up with a transaction that is not clearing. I can issue a checkpoint, but that will not affect an active transaction. I cant kill the transaction because it is a “system” spid – not user spid.
According to what I see online, this issue crops up after an incomplete service pack, or cumulative update has been run. and their solution is to restart SQL server, and let the update complete. Well, that’s kind of what we did last night, and the log did shrink after a long reboot. But its back again today. I’m wondering if the issue doesn’t have something to do with my compressing the data – There is no mention of that in the KB articles that I have seen.
Details: Run “dbcc sqlperf (logspace)” and I see that the log is 90+ percent full even after a recent transaction log backup has completed successfully.
I run a “dbcc opentran” and I see that the oldest transaction is held by spid 9. That is a system spid. I also see that the name of the transaction is “tran_sp_MSCreate_peer_tables”, its start time is 11:36 pm last night (right after the reboot completed) . This is the transaction that is causing the log files to fill.
When I google “tran_sp_MSCreate_peer_tables”, I see the following article: http://support.microsoft.com/kb/2509302?wa=wsignin1.0 (and other blogs as well). They suggest we do the same thing as we did last night, that’s is, restart SQL server and let the upgrade complete. The problem is, we have not installed any updates. There is no mention in the solution notes about this being an issue, so I’m a little baffled about how to fix this now that it has resurfaced. In the logs for last nights startup (after the reboot) it did indicate that the server was completing some kind of upgrade, as the message shows up: “SQL server is in upgrade mode and does not accept connections” and shows the percentage of completion of the upgrade. The upgrade did complete, and the server became available after about 20 minutes. And the log file showed .05 percent filled, which is where it should be after a log backup.
I’m looking for a real solution to this, but in the meantime we may need to bring SQL down and allow it to go through the “upgrade” process again (about 20 minutes) and then the system should be faster. I’m thinking the reason for the sluggishness is that every change has to be written (appended) to the end of the nearly full 203gb file. We need to clear that transaction and let the log file return to a smaller size. Since I started this email, the log is now at 97 percent full. We can add space so it doesn’t crash, but it will only become slower.
(we wound up rebooting the server and sure enough, its running through a recovery and is in script upgrade mode. We have applied no upgrades to this server in two years. It ran through the same process last night. There have been no upgrades between 11 last night and this morning. SQL simply thinks it is upgrading something – but what? – I’m not even totally convinced that it is the compression scripts I’m running that is causing this.
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] Transaction log for the database is growing and system SPID is holding open transaction […]
jual baju batik modern said
I visited many web sites but the audio quality for audio songs existing at this site is actually fabulous.
streaming movies said
I go to see everyday some web pages and sites to read articles or reviews,
however this weblog presents feature based content.
walking dead coda said
Excellent post. I was checking continuously this blog and I’m impressed!
Very useful info specially the final phase 🙂 I take care of such information much.
I used to be looking for this certain information for a long time.
Thanks and good luck.