SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

MSSQLFUN

I, Rohit Garg, am working as Consultant in IT Company. I am having an around 5 years of experience in MSSQL server & other Microsoft technologies. I am working as DBA in Microsoft SQL Server from last 5 years in e-Commerce, Telecom, Finance domain. In this tenure, I got a chance of working as Database administrator, Developer and trainer on SQL server 2000 to SQL Server 2012. I am holding Master’s degree in Computer Science along with certification in SQL Server & .Net. I like to learn new things by hand-on experience on regular basis. This journey is so far is delightful & valuable with the addition of wonderful friends.

Comments

Leave a comment on the original post [mssqlfun.com, opens in a new window]

Loading comments...