Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MSDB Transaction Log filling very fast..


MSDB Transaction Log filling very fast..

Author
Message
ALICIA SABAT
ALICIA SABAT
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 151
I had a problem last Wednesday one of my SQL 2012 MSDB logs was growing at about 100mb every 6 to 7 mins.
.. my MSDB was in simple recovery mode so I was a bit shocked to say the least!
I put it in full recovery backed up and did a log back up – this only gave me more breathing space it still kept growing…
I stopped the service and restarted … and it kept on growing. I could not get control of the MSDB to limit the growth..

So on searching the web it was suggested that one of the 3rd party tools was probably the cause.
I removed the tool completely and used some clean up scripts as it left some hooks in SQL.. still kept growing…
Ok so MSDB? What uses it?… Ah yes Agent so turned agent off….. still kept growing…
Jobs right? So I disabled all jobs and maintenance plans .. still kept growing…
The only thing left was replication this machine had 11 publishers so dropped all replication still it kept growing…
Frustrated and head hurt by now.. What was casing it?
Ok we are down to the fix it or re-install options now I need this servers up it’s a fundamental part of my transitional environment for 24/7 operations.
I installed the same SQL version on another box and attempted to restore the last good back up…not so good… So I took the radical action of replacing the problematic MSDB files (.mdf .ldf) with the ones from my brand new install….Yipee it no longer grew… Then I applied sp1 while I had the chance.

Is there a cause for this problem I don’t know.. Can anyone enlighten me why this happened?
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
Did you have service broker enabled?

It also sounds like maybe somebody had created a table and kept inserting tons of records into it. Find what table is the largest and investigate from there. If msdb transaction log was growing, then something was using that database and you should be able to find that there was a table growing rather large too.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

ALICIA SABAT
ALICIA SABAT
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 151
Hi

I don't have any user tables in the MSDB database.
I also used sp_who2 and killed any spid that looked suspicious or was running ..

Its a weird one!
ALICIA SABAT
ALICIA SABAT
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 151
service broker was not enabled.
Jacob Wilkins
Jacob Wilkins
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2423 Visits: 7604
When the log is growing, check the log_reuse_wait_desc for the msdb database in sys.databases. That should help narrow things down.

If it shows there's an active transaction, you should be able to figure out what the open transaction(s) is.

You can also check sys.dm_tran_database_transactions for the log_bytes_reserved and log_bytes_used columns to see what is actively writing to the logs.

For historical information, you can get some information by querying the default trace and looking at the autogrowth events for the msdb log. Something like the below should do the trick:


DECLARE @filename NVARCHAR(4000);

-- Current default trace
SELECT @filename = CAST(value AS NVARCHAR(4000))
FROM ::
FN_TRACE_GETINFO(DEFAULT)
WHERE traceid = 1
AND property = 2


-- Preserve the path and replace the current default trace with log.trc
SET @filename = LEFT(@filename,
LEN(@filename) - CHARINDEX('\',REVERSE(@filename)))
+ '\log.trc'




-- Auto shrink/growth events in the current trace file
SELECT TE.name AS [EventName],T.DatabaseName, t.DatabaseID, t.NTDomainName, t.ApplicationName, t.LoginName, t.SPID, t.Duration, t.StartTime, t.EndTime, t.textdata
FROM sys.fn_trace_gettable ( @filename, DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE TE.name like '%grow%'
ORDER BY t.StartTime;




Cheers!
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44993 Visits: 39879
IIRC correctly, there was a bug that cause this behaviour depending on the INITIAL SIZE and GrOWTH SETTINGS of a database in SQL Server 2005. I believe that SP3 fixed it. What service pack is your 2005 instance sitting at?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
Jeff Moden (2/18/2014)
IIRC correctly, there was a bug that cause this behaviour depending on the INITIAL SIZE and GrOWTH SETTINGS of a database in SQL Server 2005. I believe that SP3 fixed it. What service pack is your 2005 instance sitting at?


According to the OP, this is a 2012 instance. I wonder if it was re-introduced.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
Jacob Wilkins (2/18/2014)
When the log is growing, check the log_reuse_wait_desc for the msdb database in sys.databases. That should help narrow things down.

If it shows there's an active transaction, you should be able to figure out what the open transaction(s) is.

You can also check sys.dm_tran_database_transactions for the log_bytes_reserved and log_bytes_used columns to see what is actively writing to the logs.

For historical information, you can get some information by querying the default trace and looking at the autogrowth events for the msdb log. Something like the below should do the trick:


DECLARE @filename NVARCHAR(4000);

-- Current default trace
SELECT @filename = CAST(value AS NVARCHAR(4000))
FROM ::
FN_TRACE_GETINFO(DEFAULT)
WHERE traceid = 1
AND property = 2


-- Preserve the path and replace the current default trace with log.trc
SET @filename = LEFT(@filename,
LEN(@filename) - CHARINDEX('\',REVERSE(@filename)))
+ '\log.trc'




-- Auto shrink/growth events in the current trace file
SELECT TE.name AS [EventName],T.DatabaseName, t.DatabaseID, t.NTDomainName, t.ApplicationName, t.LoginName, t.SPID, t.Duration, t.StartTime, t.EndTime, t.textdata
FROM sys.fn_trace_gettable ( @filename, DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE TE.name like '%grow%'
ORDER BY t.StartTime;




Cheers!


Good stuff



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44993 Visits: 39879
SQLRNNR (2/18/2014)
Jeff Moden (2/18/2014)
IIRC correctly, there was a bug that cause this behaviour depending on the INITIAL SIZE and GrOWTH SETTINGS of a database in SQL Server 2005. I believe that SP3 fixed it. What service pack is your 2005 instance sitting at?


According to the OP, this is a 2012 instance. I wonder if it was re-introduced.


Could be. They've supposedly fixed things like the connection leaks reportedly cause by sp_OA* usage, what, 17 times now? :-D

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ALICIA SABAT
ALICIA SABAT
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 151
Thanks Guys.. I have been reading CU5 for SQL 2012 and it says :

A database transaction log continues to grow after you upgrade to
SQL Server 2008 SP2 or SQL Server 2008 SP3

Yes I know it says 2008 and I am 2012 and I have not upgraded.

Microsoft page.....
http://support.Microsoft.com/kb/2777772

Looks like this may be reoccurring bug they have had for a while 2005 , 2008 and still in 2012!
I have applied sp1 as it includes CU5 and all is still stable. Fingers crossed it says that way!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search