MSSQLWIKI

Karthick P.K on SQL Server

«
»

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 : 0×01

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 : 0×01

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 : http://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

 

About these ads

This entry was posted on May 18, 2012 at 1:19 PM and is filed under Space management, SQL Server Engine, Startup failures. Tagged: , , , , , . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Transaction log for the database is growing and system SPID is holding open transaction”

  1. SEM said

    April 13, 2013 at 5:58 PM

    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.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

«
»
 
Follow

Get every new post delivered to your Inbox.

Join 1,700 other followers

%d bloggers like this: