MSDB Transaction Log filling very fast..

Question

MSDB Transaction Log filling very fast..

ALICIA SABAT

SSC Veteran

Points: 261
More actions
February 17, 2014 at 5:23 pm

#304599

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?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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[/url]
Learn Extended Events

ALICIA SABAT SSC Veteran Points: 261 More actions · Answer 2

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 SSC Veteran Points: 261 More actions · Answer 3

ALICIA SABAT

SSC Veteran

Points: 261

February 18, 2014 at 3:13 am

#1689788

service broker was not enabled.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

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 SSC Guru Points: 1003851 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ALICIA SABAT SSC Veteran Points: 261 More actions · Answer 9

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!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

ALICIA SABAT (2/19/2014)
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!

That is a good find. Hope it continues to remain stable for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events