Thursday, April 7, 2011

Log file of 'Model' database is growing huge.

Hi,

As many of us know the reason why the log file of a Model DB grows even though it won’t store any data in it I’m just sharing this as a recall...

Few times we notice ‘Model’ DB log file grown more than 90%. Why a database which doesn’t holds any data in it causing the log file grow so much. After referring few articles found many reasons that causes this issue and the below reason best suits my condition.

Basically a model database is just a template database for the entire server databases. If we make some changes to model database suppose like if create new user to model database the next user defined databases that are going to be created will have the new user by default, but we won’t find that user in already existing databases.

So if it’s just a template what’s causing the log file grow this much. If we are taking Full backup of Model database by keeping it in Full recovery model it definitely causes the log file to grow every time because full backup is a logged operation. So in order to resolve this issue we can change the recovery model of model database to ‘simple’ and so log will get truncated automatically and also there is no need of taking full backup of model DB daily unless we make some changes to it daily.

No comments:

Post a Comment