SQL Server Performance Forum – Threads Archive
Appropriate time to shrink tlog
I can’t figure out when is the best time to shrink the tlog. Naturally after business hours, but how does it relate to ability to recover? Here is our environment. SQL2000 SP3Full recovery model
Full DB backup 8:30 PM
TLog backups every 2 hours between 6:00 AM and 8:00 PM Seems like we need to shrink tlog almost every night. Is it best to do this just before or just after the full DB backup? Does this impact the ability to do a point in time recovery? We use a statement like this to shrink the tlog file. Use TappeApp
GO
DUMP TRANSACTION TAPPEAPP WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(TAPPEAPP_LOG, TRUNCATEONLY)
GO Thanks for any advice.
If you do a full DB backup at 8:30pm and log backups every two hours between 6am and 8pm, then you should never have to shrink the log, as it will be truncated each time a log/database backup is done. The log file would be as big as the busiest 2 hour period (or the overnight from 8.30pm to 6am) If you shrink the log then you will not be able to roll forward past that point, so doing it before a db backup would be better than afterwards Cheers
Twan
Thanks, That brings up the issue of why our log gets so big so fast. The mdf file is about 9G. After just a couple of days the log gets up to about 9G also. This is with our daily backups and regular tlog backups. Our backups are handled through a maintenance plan and no errors are reported. But it just keeps growing. What seems to be happening is that the log file does not go down in size after any of the backup operations. Thus after a few days we shrink the log to save some disk space. Any thoughts are welcome.
Do you have replication set up on the database? If so, are the subscribers all still valid? Is the distributor still valid?
Do you want log backups (i.e. do you want to be able to roll forward through logs if the database fails partway through the day, or would you just restore the last db backup)? If so you could set the recovery model to simple
Are there any long transactions which update lots of data? Cheers
Twan
No replication. We want log backups so we could restore in the middle of the day. Yes, our app does have some long transactions that hit many records. I have observed that the hourly tlog backups may be betwwen 10 – 150MB. But after the backups the log file does not seem to go down in size. Some times the log is huge (8GB) and a backup will create a small (10MB) TRN file indicating light activity, but the log does not drop in size. Eventually we shrink it manually. Our settings are ‘auto grow file 10%’ and ‘unrestricted growth’. These were the defaults so I just left them as is. Thanks
The file size has nothing to do with the backup. When backup of database is done, the logical space utilization of log decreases and there is lot of empty space in the log but the physical file size does not come down. It will come down if AUTO SHRINK is enables but I recommend keeping the setting off for production systems as it impacts performance when shrink operation is executed. Also the system will suffer from bad performance when the file expands as it needs the space. As suggested, if the log is backed up regularly, the log file size will be the maximum file size for a period of 2 hours. The solutions that I see are increase the backup frequency and dicide the transactions into smaller sizes. I am not sure if changing recovery interval can help. Peers, what doi you say?
Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
BACKUP LOG or truncating the Tlog will not reduce the PHYSICAL size of Tlog you need to use DBCC SHRINKFILE and its not good practice to offer a daily job to do so, instead try to accomplish a set of size for Tlog after monitoring all the jobs/event on the database. Ensure enough disk space is available on the array where Tlogs are located and maintain regular backups. Gaurav, I would suggest not to touch RECOVERY interval if everything is going fine as of now. In the production environment its better to keep FULL for the sake of recovery. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Mazie,
after you run your dbcc shrinkfile operations on the log file, what is the size of the .ldf file? Is the file size reduced to the original file size at database creation?
It depends on target_size defined and can you confirm the frequency of the Tlog backup. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
We backup the tlog every 2 hours between 6:00 am and 8:00 pm. I will look into backing up in 1 hour increments. Are you recommending setting a max size on the tlog file? How do you calculate that, is it something like double the size you believe will be required? What happens if your log tries to grow beyond the limit? Thanks
Why not make it every half-an-hour, where I believe there is no issues with current disk space.
This way you can reduce the logical size of Tlog which helps to keep consistent size of Tlog. To calculate a size for Tlog then perform a small test as follows:
After shrinking the Tlog you need to start and take into account of size.
Say after every Transaction log backup consider taking size (try to change the backup schedule from 2 hours to half-an-hour and test).
Take sizes after DB maintenance plan completion. This way you will get a consistent idea of space required by the Tlog.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks, I will follow that procedure. Should I leave some extra space beyond what the typical maximum appears to be?
Yes, and ensure again no issues with physical (disk) free space where Tlog resides. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi Guarav, by changing the recovery interval you mean using sp_configure rather than changing the mode from full to simple…? If so, then no that won’t affect the log file size in a recovery mode=full set up, only the frequency of checkpoints. In terms of setting a max size for the log file, I would be wary of this, since if the limit is reached your database will no longer be usable until you expand the log or truncate the data in it. as mentioned by others increasing the frequency of backups MAY help as long as you don’t have a single transaction that uses an enormous amount of log space, since the log will still expand to cover that transaction. Cheers
Twan
]]>