April 1, 2013 at 4:55 am
Why am i not able to shrink the transaction log file of my database even though there's 91% free space in it?
I'm using the following script for testing purposes:
/* FULL Recovery and Log File Growth */
USE [master]
GO
-- Create Database SimpleTran
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SimpleTran')
BEGIN
ALTER DATABASE [SimpleTran] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SimpleTran]
END
GO
CREATE DATABASE [SimpleTran]
GO
-- Set Database backup model to FULL
ALTER DATABASE [SimpleTran] SET RECOVERY FULL
GO
BACKUP DATABASE [SimpleTran] TO DISK = N'D:\Backup\SimpleTran\SimpleTran.bak' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Check Database Log File Size
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- Create Table in Database with Transaction
USE SimpleTran
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[RealTempTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[RealTempTable]
GO
CREATE TABLE RealTempTable (ID INT)
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- Take Full Backup
BACKUP DATABASE [SimpleTran] TO DISK = N'D:\Backup\SimpleTran\SimpleTran.bak' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Run following transaction multiple times and check the size of T-Log
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
/*
Now run following code multiple times.
You will notice that it will not increase the size of .ldf file but will for sure
increasethe size of the log backup.
*/
-- Second Time
-- START
BACKUP LOG [SimpleTran] TO DISK = N'D:\Backup\SimpleTran\SimpleTran.trn' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Run following transaction and check the size of T-Log
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- END
April 1, 2013 at 5:19 am
I didn't go through all your script however, you can shrink your log file using
Use database name
DBCC SHRINKFILE (logical name, 1024)
If it didn't work first time, try couple of times.
If it still does not work, then take log backup, then shrink, it should work then.
SueTons.
Regards,
SQLisAwe5oMe.
April 1, 2013 at 6:34 am
Probably because the active portion of the log is at the end of the file. TRy again later, additonal operations and log backups will have moved the active portion of the log, once it wraps around you can shrink.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2013 at 6:54 am
If you get desperate, and I've been there, you can issue a CHECKPOINT command, backup the log, issue another CHECKPOINT, and backup the log again. That frequently clears the tail end of the log quicker. But it doesn't always work. Sometimes patience is the only solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2013 at 11:28 am
....and to know where the last active vlf is, run command
dbcc loginfo(yourdbname)
the last row with a '2' in the status column is the last active vlf, the log will not shrink past that point.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply