Transaction log for the database is growing and system SPID is holding open transaction
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 https://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/
Other Startup failure common issues:
https://mssqlwiki.com/2010/10/29/sqlserver-2008-fails-to-come-online-on-cluster-after-upgrade/
Thanks
Karthick P.K