January 12, 2009 at 12:18 pm
Hi,
In one of our sql server production instances, the MSDB log file size is 17GB.
I have no idea why MSDB log size grown 17 GB??
What are the things I need to consider plz advice me
January 12, 2009 at 12:19 pm
Do you have the database in Full Recovery mode, and do you do backups?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 12:25 pm
Thanks,
MSDB is in full recovery mode and we are taking only full back up of system databases every night.
and yesturday night the full backup size of msdb is 10MB only.Now all of the sudden Iam seeing the 17gb log filr size and 11MB datafile size?
plz advice what me are the immediate steps to take?
January 12, 2009 at 12:31 pm
did you log grow over night to 17 Gb ?
or is it due to the fact you do not make log-backups ?
In full recovery mode, you need to make log-backups to clear the log file and stop it from growing. (so as well full backup as log backup(s))
Check out Gails nice article http://www.sqlservercentral.com/articles/64582/
(default trace mentions db-file growth !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 12, 2009 at 12:39 pm
is it safe to keep the system databases in simple recovery model?
January 12, 2009 at 12:41 pm
madhu.arda (1/12/2009)
is it safe to keep the system databases in simple recovery model?
Except for tempdb, no.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 12:44 pm
My concern is why MSDB log is grown to 17 GB overnight? what are things do I need to check to know what is the reason to increase log size over night?
Is it normal behaviour or its a critical issue to consider
Thanks for your help
January 12, 2009 at 1:27 pm
madhu.arda (1/12/2009)
My concern is why MSDB log is grown to 17 GB overnight? what are things do I need to check to know what is the reason to increase log size over night?Is it normal behaviour or its a critical issue to consider
Thanks for your help
No. Definitely not a normal behavior. Never seen msdb size grow to 17 GB. Only time I had seen my msdb database size grow abruptly is when I had to migrate more than 1000 dts packages when consolidating many servers on one. But even then it didn't grow that big.
Instead you can put msdb, model and master database in simple recovery mode and take full backup every night. I never got the satisfactory answer even from MS for - Why do I need to put system databases except tempdb in full recovery mode? Even If its in simple recovery and server crashed in mid-day how much generally it impacts the project. My answer is no loss or very minimal loss. But who cares, If I lose some job history for a day.
SQL DBA.
January 12, 2009 at 1:32 pm
madhu.arda (1/12/2009)
My concern is why MSDB log is grown to 17 GB overnight? what are things do I need to check to know what is the reason to increase log size over night?
Was there perhaps a reindex job run against it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2009 at 1:33 pm
This could be on account of a large DML operation overnight?
If that were to be the case, do you have transaction log backups running in the night too...my assumption is you dont have log backups in the night..this could be a potential reason for such a humongous growth pf msdb...investigate this and let us know what you find out...
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 12, 2009 at 1:36 pm
USE msdb
SELECT TOP(30) OBJECT_NAME(object_id), rows
FROM sys.partitions
ORDER BY rows DESC
Run this and see if you can find any hints..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 12, 2009 at 1:36 pm
SanjayAttray (1/12/2009)
Instead you can put msdb, model and master database in simple recovery mode and take full backup every night.
Just bear in mind that if you put Model into simple, any new database that you create on that server will be in simple recovery. Great if that's what you want, a bit of a problem if you assume new databases are in full by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2009 at 1:39 pm
The_SQL_DBA (1/12/2009)
USE msdbSELECT TOP(30) OBJECT_NAME(object_id), rows
FROM sys.partitions
ORDER BY rows DESC
How's that going to find what's filled the transaction log up?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2009 at 1:39 pm
No matter if reindex job happens in night. It would not grow that big. I don't think any one would do any dml transaction on " msdb " database.
SQL DBA.
January 12, 2009 at 1:41 pm
SanjayAttray (1/12/2009)
No matter if reindex job happens in night. It would not grow that big. I don't think any one would do any dml transaction on " msdb " database.
Wouldn't count on that. The DBA I replaced at my current job added tables and procs to msdb so he could call them from other databases.
I do a similar thing, but I put it in a database I call "Common", instead of in a system database.
But someone could have done something similar in this case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply