SQL SERVER – master Database Log File Grew Too Big
- by pinaldave
Couple of the days ago, I received following email and I find this email very interesting and I feel like sharing with all of you.
Note: Please read the whole email before providing your suggestions.
“Hi Pinal,
If you can share these details on your blog, it will help many.
We understand the value of the master database and we take its regular back up (everyday midnight). Yesterday we noticed that our master database log file has grown very large. This is very first time that we have encountered such an issue. The master database is in simple recovery mode; so we assumed that it will never grow big; however, we now have a big log file.
We ran the following command
USE [master]
GO
DBCC SHRINKFILE (N'mastlog' , 0, TRUNCATEONLY)
GO
We know this command will break the chains of LSN but as per our understanding; it should not matter as we are in simple recovery model.
After running this, the log file becomes very small.
Just to be cautious, we took full backup of the master database right away.
We totally understand that this is not the normal practice; so if you are going to tell us the same, we are aware of it.
However, here is the question for you? What operation in master database would have caused our log file to grow too large?
Thanks,
[name and company name removed as per request]“
Here was my response to them:
“Hi [name removed],
It is great that you are aware of all the right steps and method. Taking full backup when you are not sure is always a good practice.
Regarding your question what could have caused your master database log to grow larger, let me try to guess what could have happened.
Do you have any user table in the master database? If yes, this is not recommended and also NOT a good practice. If have user tables in master database and you are doing any long operation (may be lots of insert, update, delete or rebuilding them), then it can cause this situation.
You have made me curious about your scenario; do revert back.
Kind Regards,
Pinal”
Within few minutes I received reply:
“That was it Pinal. We had one of the maintenance task log tables created in the master table, which had many long transactions during the night. We moved it to newly created database named ‘maintenance’, and we will keep you updated.”
I was very glad to receive the email. I do not suggest that any user table should be created in the master database. It should be left alone from user objects. Now here is the question for you – can you think of any other reason for master log file growth?
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
