Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MSDB Transaction Log filling very fast.. Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 5:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:06 AM
Points: 5, Visits: 146
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?
Post #1542314
Posted Monday, February 17, 2014 6:46 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #1542328
Posted Tuesday, February 18, 2014 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:06 AM
Points: 5, Visits: 146
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!
Post #1542436
Posted Tuesday, February 18, 2014 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:06 AM
Points: 5, Visits: 146
service broker was not enabled.
Post #1542438
Posted Tuesday, February 18, 2014 8:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 2:41 PM
Points: 169, Visits: 469
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!
Post #1542614
Posted Tuesday, February 18, 2014 9:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542630
Posted Tuesday, February 18, 2014 9:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #1542632
Posted Tuesday, February 18, 2014 9:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
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
Post #1542633
Posted Tuesday, February 18, 2014 10:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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?


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542859
Posted Wednesday, February 19, 2014 1:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:06 AM
Points: 5, Visits: 146
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!
Post #1542887
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse