Search This Blog

Sunday, November 13, 2011

SQL Server–Shrink Transaction Log without taking database offline

We have a database that captures a large amount of data every day. Whilst the data is important, it is not critical so it is backed up on a daily basis but we do not perform transaction log backups on it, as this would be expensive and is just not warranted (to lose one days worth of data is acceptable).

With this setup this means that the transaction log is not given a checkpoint, and so never shrinks. So to shrink it we need to issue 2 commands.

BACKUP LOG <db name> WITH TRUNCATE_ONLY

basically truncates the entire log file all data lost.

DBCC SHRINKFILE(<logical transaction log name>,1000)
Where 1000 is the size in MB to shrink the log file to, in this case 1GB.
 

Share/Bookmark

No comments:

Post a Comment