Sql-server – How to shrink the MASTER log file in SQL Server 2008

sql serversql-server-2008transaction-log

This is for a non-system database:

USE AdventureWorks
GO
ALTER DATABASE AdventureWorks 
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks_Log)
GO
ALTER DATABASE AdventureWorks 
SET RECOVERY FULL
GO

But should I do something different for the MASTER database?

Best Answer

The first question that is going to come to mind for many readers is, "why is your master database log so big that you are thinking of shrinking it?" If user tables were accidentally placed in master that caused log file growth, they need to be removed.

If the master log file has become overly large for some reason and there is now free space in it, you can shrink it like any other database log file. Master should already be in simple recovery mode, so your shouldn't need to change it.

USE master
GO
DBCC SHRINKFILE (N'mastlog', 0, TRUNCATEONLY)
GO

You may want to resize it after this if it shrunk down to less than a couple MB. Not sure what the default is, but I'm looking at a later version of SQL Server and it is 3 MB, so anything in that range should be a sufficient.

Related Solutions

Sql-server – Manually set log file size after shrink SQL Server 2008 R2

Just shrink to what you think is the optimal size. Don't use the UI, just do this - let's say 200 MB is your optimal size:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);

If you're only interested in taking a log backup once a day, and aren't interested in point-in-time recovery, then you should switch to simple recovery model. This will mean log backups are unnecessary (in fact impossible) but the contents of the log will self-manage.

If you want log backups to be meaningful, don't plan on taking a full backup at night and then a single log backup right after. This keeps you in full recovery model, makes the log work really hard, and doesn't buy you anything. So if you want point-in-time recovery, run log backups more frequently at a rate that satisfies your data loss tolerance. If you don't ever want to lose more than 15 minutes of data, run a log backup every 15 minutes.

SQL Server – How to Shrink a Log File Multiple Times

Next time, run two checkpoints before trying to shrink the file (assuming simple recovery) or take two log backups (assuming full or bulk-logged). This will force the log to wrap around to the beginning of the file, allowing the shrinkfile operation to eradicate most of the file. I think the fact that the 4th operation was successful was just by coincidence (perhaps multiple checkpoints happened in the meantime).

That said, why on earth would you want a 1 MB log file? If it grew once, it will grow again. Freeing up the space temporarily just so that it can autogrow during real activity (which blocks, because the growth of the log has to be zero-initialized, even with instant file initialization) seems like an exercise in futility. Why not just size the log appropriately, so that you don't have to deal with growths (ideally) or shrinks (wasteful).

Please have a good thorough read of this question and its answers:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Further reading that might be more useful than trying to summarize:

Best Answer

Related Solutions

Sql-server – Manually set log file size after shrink SQL Server 2008 R2

SQL Server – How to Shrink a Log File Multiple Times

Related Question