August 9, 2012 at 11:31 am
I'm trying to figure out why this log file won't shrink down past a certain size. I'm doing it on a brand new database I'm creating and nothing has happened in it since creation and I'm the only one on the machine. I create it with a 5GB log file then try every method I can think of to shrink it down to say 20MB. It is in full recovery so I have done a full backup, then a tlog backup then tried to shrink. I've set it to simple recovery, done a checkpoint and tried to shrink. Nothing I do can get it down past 624.88MB. Any ideas?
Here are the steps to recreate:
CREATE DATABASE [Test] ON PRIMARY
( NAME = N'Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf' , SIZE = 1024000KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf' , SIZE = 5120000KB , FILEGROWTH = 10%)
GO
USE [Test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Test] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
Then try running the following to shrink it.
USE [Test]
GO
DBCC SHRINKFILE (N'Test_log' , 0, TRUNCATEONLY)
GO
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
August 9, 2012 at 12:06 pm
run select * from sys.databases and check log_reuse_wait_desc
you might have an active transaction to check that you can use dbcc opentran('DBName')
Pooyan
August 9, 2012 at 12:14 pm
SQLJocky (8/9/2012)
I'm trying to figure out why this log file won't shrink down past a certain size. I'm doing it on a brand new database I'm creating and nothing has happened in it since creation and I'm the only one on the machine. I create it with a 5GB log file then try every method I can think of to shrink it down to say 20MB. It is in full recovery so I have done a full backup, then a tlog backup then tried to shrink. I've set it to simple recovery, done a checkpoint and tried to shrink. Nothing I do can get it down past 624.88MB. Any ideas?Here are the steps to recreate:
CREATE DATABASE [Test] ON PRIMARY
( NAME = N'Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf' , SIZE = 1024000KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf' , SIZE = 5120000KB , FILEGROWTH = 10%)
GO
USE [Test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Test] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
Then try running the following to shrink it.
USE [Test]
GO
DBCC SHRINKFILE (N'Test_log' , 0, TRUNCATEONLY)
GO
I would defer the actual answer to this question to some one more familiar with VLFs (Virtual Log Files), but an educated guess would be that 624.88MB is the size of the current (active) VLF.
August 9, 2012 at 12:31 pm
pooyan_pdm (8/9/2012)
run select * from sys.databases and check log_reuse_wait_descyou might have an active transaction to check that you can use dbcc opentran('DBName')
I did that and it came back with "NOTHING" .
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
August 9, 2012 at 12:35 pm
I know it does not make that much scence but try backing up the log two times in a row it might help
Pooyan
August 9, 2012 at 12:50 pm
Lynn Pettis (8/9/2012)
SQLJocky (8/9/2012)
I'm trying to figure out why this log file won't shrink down past a certain size. I'm doing it on a brand new database I'm creating and nothing has happened in it since creation and I'm the only one on the machine. I create it with a 5GB log file then try every method I can think of to shrink it down to say 20MB. It is in full recovery so I have done a full backup, then a tlog backup then tried to shrink. I've set it to simple recovery, done a checkpoint and tried to shrink. Nothing I do can get it down past 624.88MB. Any ideas?Here are the steps to recreate:
CREATE DATABASE [Test] ON PRIMARY
( NAME = N'Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf' , SIZE = 1024000KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf' , SIZE = 5120000KB , FILEGROWTH = 10%)
GO
USE [Test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Test] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
Then try running the following to shrink it.
USE [Test]
GO
DBCC SHRINKFILE (N'Test_log' , 0, TRUNCATEONLY)
GO
I would defer the actual answer to this question to some one more familiar with VLFs (Virtual Log Files), but an educated guess would be that 624.88MB is the size of the current (active) VLF.
Lynn is correct. The 5000MB log file will be created with 16 VLFs of 312.5MB. A database must have at least 2 VLFs so 312.5 + 312.5 = 625MB.
You can see this by running DBCC LOGINFO after the database creation and after the shrink operation.
August 9, 2012 at 1:05 pm
Run DBCC LOGINFO for that database. This will show you the number of VLF's defined in the log file for that database.
If you initially created the log file at 5GB - there would have been 16 VLF's created of equal size. That would make each VLF about 320MB.
When you shrink the log file - SQL Server requires at least 2 VLF's (as far as I know) and that gets you to 640MB.
If you need to make the log file smaller, I think your best option is going to be recreating the database with the right sizes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 9, 2012 at 1:05 pm
JeremyE (8/9/2012)
Lynn Pettis (8/9/2012)
SQLJocky (8/9/2012)
I'm trying to figure out why this log file won't shrink down past a certain size. I'm doing it on a brand new database I'm creating and nothing has happened in it since creation and I'm the only one on the machine. I create it with a 5GB log file then try every method I can think of to shrink it down to say 20MB. It is in full recovery so I have done a full backup, then a tlog backup then tried to shrink. I've set it to simple recovery, done a checkpoint and tried to shrink. Nothing I do can get it down past 624.88MB. Any ideas?Here are the steps to recreate:
CREATE DATABASE [Test] ON PRIMARY
( NAME = N'Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf' , SIZE = 1024000KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf' , SIZE = 5120000KB , FILEGROWTH = 10%)
GO
USE [Test]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Test] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
Then try running the following to shrink it.
USE [Test]
GO
DBCC SHRINKFILE (N'Test_log' , 0, TRUNCATEONLY)
GO
I would defer the actual answer to this question to some one more familiar with VLFs (Virtual Log Files), but an educated guess would be that 624.88MB is the size of the current (active) VLF.
Lynn is correct. The 5000MB log file will be created with 16 VLFs of 312.5MB. A database must have at least 2 VLFs so 312.5 + 312.5 = 625MB.
You can see this by running DBCC LOGINFO after the database creation and after the shrink operation.
So actually, I was half right. I said there was only one VLF when there would be two. That's okay, I learned something.
August 9, 2012 at 1:49 pm
Thank you all. This has been very informative. This was all to help answer a question on shrinking log files in 2008. The real issue is that someone created a log file with initial size of 20GB on a prod server and we couldn't get it to shrink to a more realistic size and therefore were testing on a new test DB to see why that would be.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
August 10, 2012 at 5:58 am
A log file must always have at least 2 VLFs. Also, log records can never be moved, so a log cannot be shrunk past the last active VLF in the file.
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
August 10, 2012 at 6:31 am
Experts,
If i have a 10 MB log file and if i add 40 MB will it create a total of 4+4 VLF or will it accomodate the new 40MB to old 4 VLFs?
August 10, 2012 at 6:37 am
VLFs, once created, are never expanded or shrunk in any way.
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
August 10, 2012 at 6:51 am
GilaMonster (8/10/2012)
VLFs, once created, are never expanded or shrunk in any way.
Hi Gail,
Thanks for the reply. Can you please be more elaborate?
As far as i know chunks less than 64MB = 4 VLFs so for first 10MB it will create 4VLF and if i add 40MB what will happen? will it create another 4 VLFs or will accomodate in already existing VLFs?
August 10, 2012 at 6:57 am
Ratheesh.K.Nair (8/10/2012)
As far as i know chunks less than 64MB = 4 VLFs so for first 10MB it will create 4VLF and if i add 40MB what will happen? will it create another 4 VLFs or will accomodate in already existing VLFs?
As I already said, VLFs are NEVER expanded or shrunk once created, hence 'accomodate in already existing VLFs' is not an option.
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
August 10, 2012 at 7:43 am
Thanks a lot Gail
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply