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
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
