model database transaction log

Question

model database transaction log

sqlfriend

SSC Guru

Points: 52769
More actions
January 22, 2013 at 10:25 am

#269570

Is there a reason that the transaction logs for database modle are almost full?
we only do full backup of model, but we don't do the transaction log backup for we think there is no transaction going on in the model database.
Why it gets almost full?
Thanks

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

how are you determining that it is "full"?

my model database is 3.5 Megabytes in size, with three meg for data, and one meg for the log. that's right...one meg.

we just don't add default objects to that database, so it probably has never, ever been modified.

so if you are measuring it against he way you do real databases, ie % of use, or # megs free, it's probably a false positive issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

I did a search find another article:

http://shaunjstuart.com/archive/2011/02/the-mystery-of-the-model-database-transaction-log-growth/

So the solution may be either change the recovery mode to simple or do transaction log backup or exclude it from full backup.

for the 3 rd one, I usually include them in the backup plan backup system databases.

So not sure which option is the best.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sqlfriends (1/22/2013)
So the solution may be either change the recovery mode to simple or do transaction log backup or exclude it from full backup.
for the 3 rd one, I usually include them in the backup plan backup system databases.
So not sure which option is the best.

How often do you change model? That's how often it needs to be backed 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

It sounds to exclude it from full backup is a good idea, and do a full backup whenever there is a change.

One thing I need to change though, when I use maintenance plan to delete older backups, for example remove older than 4 weeks, I have to customize for model database, otherwise it will be deleted.

Thanks

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 5

sqlfriends (1/22/2013)
It sounds to exclude it from full backup is a good idea, and do a full backup whenever there is a change.
One thing I need to change though, when I use maintenance plan to delete older backups, for example remove older than 4 weeks, I have to customize for model database, otherwise it will be deleted.
Thanks

Not a good idea to exclude from full backup.

What if the dive holding the backups failed ? or the backup server crashed ? Think about Disasters do happen ... natural or human error :w00t:

Best practice is to take Full backup of system databases everyday or 1 a week.. depending on your company policy.

Good info about importance of system databases: http://www.mssqltips.com/sqlservertip/1420/sql-server-system-databases/

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

scogeb SSCrazy Points: 2059 More actions · Answer 6

SQLQuest29 (1/23/2013)
sqlfriends (1/22/2013)
It sounds to exclude it from full backup is a good idea, and do a full backup whenever there is a change.
One thing I need to change though, when I use maintenance plan to delete older backups, for example remove older than 4 weeks, I have to customize for model database, otherwise it will be deleted.
Thanks
Not a good idea to exclude from full backup.
What if the dive holding the backups failed ? or the backup server crashed ? Think about Disasters do happen ... natural or human error :w00t:
Best practice is to take Full backup of system databases everyday or 1 a week.. depending on your company policy.
Good info about importance of system databases: http://www.mssqltips.com/sqlservertip/1420/sql-server-system-databases/

In my environment, the system databases are "tiny". I run fulls on all of them daily. It takes < 1 min. so why not?

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

thanks, I do them, but here we are talking about model database only and the transaction log issue.

I guess I will just do the fullbackup, and monitor it, if transaction log backup is full for model, either do a transaction log backup or shrink it.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

sqlfriends (1/23/2013)
thanks, I do them, but here we are talking about model database only and the transaction log issue.
I guess I will just do the fullbackup, and monitor it, if transaction log backup is full for model, either do a transaction log backup or shrink it.

Why not just add a once a week tlog backup for model? It certainly isn't going to hurt anything. And since it is model and no transactions are being done to this database, it should be very quick and will prevent it from growing.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

sqlfriends (1/23/2013)
if transaction log backup is full for model, either do a transaction log backup or shrink it.

Why do people want to shrink a full log?

If I have a 5 litre bottle that is full of water, would there be any logic in trying considering reducing the bottle to 2 litres? No, because 5 litres of water does not fit into 2 litres.

A full transaction log means that there is no empty space in the file. Shrink reduces the file size by releasing free space back to the OS. A full log, by definition, has no free space and hence needs to either grow or have some of the contents removed.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52769 More actions · Answer 10

We don't shrink transaction log . But just for this case model database, thought its transaction log shouldn't grow.

If the full db really makes it full, then I guess we will just go as usual to do transaction log backup.

Thanks,