February 17, 2014 at 5:23 pm
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?
February 17, 2014 at 6:46 pm
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
February 18, 2014 at 3:06 am
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!
February 18, 2014 at 3:13 am
service broker was not enabled.
February 18, 2014 at 8:39 am
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!
February 18, 2014 at 9:04 am
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
Change is inevitable... Change for the better is not.
February 18, 2014 at 9:06 am
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
February 18, 2014 at 9:07 am
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
February 18, 2014 at 10:11 pm
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
Change is inevitable... Change for the better is not.
February 19, 2014 at 1:56 am
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!
February 19, 2014 at 7:35 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply