Home » SQL Server » MSDB log file grown large – SLEEP_MSDBSTARTUP waittype

MSDB log file grown large – SLEEP_MSDBSTARTUP waittype

Issue: MSDB database log file is getting full very rapidly and became very huge. Currently, T-Log file is of 5GB whereas Data file is only 1GB for MSDB on server.

Findings: We found system owned open transaction in MSDB which is causing issue.

Transaction information for database ‘msdb’.

Oldest active transaction:

SPID (server process ID): 19s

UID (user ID) : -1

Name : CTraceEvDataQueue

LSN : (12968:15110:1)

Start time : May 7 2016 12:33:14:507PM

SID : 0x0

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When we try to find SPID 19 details from sys.sysprocesses, we found that session is causing SLEEP_MSDBSTARTUP waittype.

SLEEP_MSDBSTARTUP waittype Occurs when SQL Trace waits for the msdb database to complete startup. This is very rare watitype which we come across in our day to day production support.

Resolution: As we all know, we cannot kill SYSTEM session IDs, We need to restart SQL Services to get rid out of this problematic session.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Leave a Reply

%d bloggers like this: