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

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

To shrink the transactional logs in SQL Server 2008 follow this link: https://robbamforth.wordpress.com/2010/06/24/sql-%e2%80%93-how-to-shrink-un-shrinkable-database-log-database-log-won%e2%80%99t-shrink-sql-server-2008/

The following should only be considered as a last resort to force the log file to shrink. I suggest you take a full database back-up first.

The DBCC LOGINFO can be run against the database. This will give you information about your virtual logs inside your transaction log.
In particular look at the Status column. Status = 2 indicates the portions of the log that are in use, status = 0 are not in use.

DBCC LOGINFO (DATABASE_NAME)

Step 1:
Attemp to shrink the problematic log file and back up the database with TRUNCATE_ONLY.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME_LOG’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

Step 2:
Create a temporary table and insert a record.

CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO

Step 3:
Run the following loop to update the table.

SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
   UPDATE MyTable SET MyField = MyField WHERE PK = 1
   SELECT @Index = @Index + 1
END
SET NOCOUNT OFF

The log rows with status 2 should now be available with statis = 0.

Step 4:
Re-run the shrink command.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME_LOG’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

— Drop the temporary table as no longer required
drop table MyTable

The log file should now have shrunk successfully.

Source: http://www.broad-lea.com/sql_server/sql_reduce_log_size.html