SQL – How To Shrink Un-shrinkable Database Log / Database Log Won’t Shrink SQL Server 2008

To shrink the transactional logs in SQL Server 2005 follow this link:
https://robbamforth.wordpress.com/2009/11/16/sql-how-to-shrink-un-shrinkable-database-log-database-log-wont-shrink/

In SQL Server 2008 it’s a matter of changing the recovery model to SIMPLE and re-running the DBCC SHRINKFILE  command – assuming the current recovery model is FULL.

Firstly, here is a quick script to backup the database and also the transactional log (if required to back up separately) to disk:

— QUICK SCRIPT TO BACKUP THE DATABASE TO DISK
BACKUP DATABASE [DATABASE]
TO DISK = ‘c:\backup\DATABASE.BAK’
WITH NOFORMAT, NOINIT, NAME = ‘DATABASE-Full Database Backup’,
SKIP,NOREWIND,NOUNLOAD,STATS = 10— QUICK SCRIPT TO BACKUP THE LOG FILE TO DISK
BACKUP LOG [DATABASE]
TO DISK = ‘c:\backup\DATABASE_LOG.BAK’
WITH NOFORMAT, NOINIT,  NAME = ‘DATABASE-Transaction Log  Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10

 

 

Step 1: View the current recovery model configuration.

— SHOW AVAILABLE DATABASES AND THEIR CURRENT RECOVERY MODEL CONFIGURATION
select name,recovery_model_desc from sys.databases

 

Step 2: Change the recovery model to SIMPLE.

–CHANGE THE RECOVERY MODEL CONFIGURATION TO SIMPLE
Alter database [DATABASE] set Recovery simple

 

Step 3: Use DBCC SHRINKFILE to truncate the transactional logs of the database.

— USE BDCC CHRINK FILE TO TRUNCATE THE TRANSACTIONAL LOGS
USE [DATABASE]
GO
DBCC SHRINKFILE ([DATABASE_LOG], 0, TRUNCATEONLY)
GO

 

Step 4: Return the recovery model back to FULL if required.

— CHANGE TEH RECPVERY MODEL BACK TO FULL IF REQUIRED
Alter database [DATABASE] set Recovery full

 

  

Advertisements

3 thoughts on “SQL – How To Shrink Un-shrinkable Database Log / Database Log Won’t Shrink SQL Server 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s