|
|
|
SSC-Addicted
 
Group: General Forum Members
Last Login: Friday, February 05, 2010 5:23 PM
Points: 481,
Visits: 1,519
|
|
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
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 8,670,
Visits: 4,946
|
|
Do you have the database in Full Recovery mode, and do you do backups?
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Friday, February 05, 2010 5:23 PM
Points: 481,
Visits: 1,519
|
|
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?
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 1:55 AM
Points: 4,975,
Visits: 3,921
|
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Friday, February 05, 2010 5:23 PM
Points: 481,
Visits: 1,519
|
|
| is it safe to keep the system databases in simple recovery model?
|
|
|
|
|
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 8,670,
Visits: 4,946
|
|
madhu.arda (1/12/2009)
is it safe to keep the system databases in simple recovery model?
Except for tempdb, no.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Friday, February 05, 2010 5:23 PM
Points: 481,
Visits: 1,519
|
|
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
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 2:25 PM
Points: 3,208,
Visits: 971
|
|
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.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 17,108,
Visits: 12,210
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 782,
Visits: 772
|
|
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."
|
|
|
|
