Log File Size does not shrink

Question

Log File Size does not shrink

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

GilaMonster (1/21/2014)
What is the exact command you are using to backup the log (or if a maintenance plan, what are the exact options)?

The T-SQL Code from the Maintenance Plan is as follows:

BACKUP LOG [DW] TO DISK = N'D:\Backup\Differential\DW\DW_backup_2014_01_22_083647_9381484.trn' WITH NOFORMAT, NOINIT, NAME = N'DW_backup_2014_01_22_083647_8581484', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'DW' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DW' )

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DW'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'D:\Backup\Differential\DW\DW_backup_2014_01_22_083647_9381484.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

The Shrink T-SQL Job is currently set as follows:

DBCC SHRINKFILE (DW_log,500)

The SHRINKFILE Command does not work. I have to right click on DB, Task Shrink, Files

Thank you!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Keith Tate (1/21/2014)
I would stop shrinking the log file. If you want to keep the size of the file smaller take your tlog backups more often than once an hour. You are causing performance problems by shrinking and then growing the log file all day long. The log file can not take advance of instant file initialization so it has to zero out the file every time it grows. Also, what is the autogrowth setting for you log file? Is it 10% and now that you DW is larger maybe it shouldn't grow by percent, but instead by a set amount of space.
Shrinking the file is only ignoring the actual problem (and could be causing more performance problems)

I agree with you about shrinking the file.

I have it set for every hour but if I manually kick off the transaction Log Backup 5 or 10 minutes earlier it does not shrink the file. I have to go through the GUI.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

You shouldn't even be shrinking the log. If the log is growing, make the log backup more frequent or identify what is causing the log growth and resolve it.

btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234064 More actions · Answer 4

the first step you need to do is to increase the frequency of your t-log backup. Hourly is definitely not frequent enough, try half hourly or quarter hourly and STOP shrinking the log 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

GilaMonster (1/22/2014)
You shouldn't even be shrinking the log. If the log is growing, make the log backup more frequent or identify what is causing the log growth and resolve it.
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.

ok, I will not shrink the log but it seemed not matter how frequent I back up the transaction log it does not remain at a reasonable size.

I understand that going through the GUI dos a DBCC ShrinkFile but I will execute the DBCC Shrink file and there is no change in size. I go through the GUI and it shrinks the file.

This just started happening this weekend.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

The Transaction Log File was backed up 3 minutes ago.

Right after the backup the transaction log file was over 5 GB.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Log backups don't shrink the log, they're not expected to shrink the log. They mark the space within the log file as reusable. What you describe is normal and expected.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 8

I'm with Gail. This is what you expect. The log (a misnamed entity) isn't a log file like an IIS log file. The size is set and it is filled, and space reused when it can be.

You need the log size you need, based on workload and frequency of log backups. Get frequency set, then look at the backup sizes. Find the peak, add a pad, and set the log size there. If you have infrequent operations, like ETL loads, make sure you consider those operations in sizing.

Shrinking should be rare for the log file, when you've had some unusual, ad hoc or one time operation.

Perry Whittle SSC Guru Points: 234064 More actions · Answer 9

Welsh Corgi (1/22/2014)
The Transaction Log File was backed up 3 minutes ago.
Right after the backup the transaction log file was over 5 GB.

Yes but what was the free space inside the file?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Another backup, that log file has not changed in size.

Next backup in 4 minutes.

Turned off shrinking.

I do not agree with shrinking either.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

Welsh Corgi (1/22/2014)
Another backup, that log file has not changed in size.

No, it won't. Log backups do not shrink the file. Though, if it hasn't changed in size that means it's not growing, which means you don't have a problem.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281344 More actions · Answer 12

Welsh Corgi (1/22/2014)
GilaMonster (1/22/2014)
You shouldn't even be shrinking the log. If the log is growing, make the log backup more frequent or identify what is causing the log growth and resolve it.
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.
ok, I will not shrink the log but it seemed not matter how frequent I back up the transaction log it does not remain at a reasonable size.

What do you consider to be a reasonable size? It seems that until you find the query that is causing the log to grow to 100GB, the reasonable size should be 100GB. Find that query, fix it and then set a new "reasonable" size for your log file.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

GilaMonster (1/22/2014)
Welsh Corgi (1/22/2014)
Another backup, that log file has not changed in size.
No, it won't. Log backups do not shrink the file. Though, if it hasn't changed in size that means it's not growing, which means you don't have a problem.

Gail,

I just got your book - Troubleshooting SQL Server in the mail a few minutes ago.

I noticed the last chapter touches on Transaction Log Backups.

Thanks to all that posted replies.

Cheers.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

I found the following command very useful as to what exactly preventing the log file from shrinking:

SELECT log_reuse_wait_desc, * FROM sys.databases

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 15

Another command that was very useful in helping me understand what was going on was the following:

DBCC LogInfo

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/