June 24, 2014 at 4:04 am
Dear All,
I wanted to create a job in SQL which would automatically shrink a Transactional log file or files when the transactional log backup gets to a particular size please?
Thank you in advance!
June 24, 2014 at 4:28 am
tt-615680 (6/24/2014)
Dear All,I wanted to create a job in SQL which would automatically shrink a Transactional log file or files when the transactional log backup gets to a particular size please?
Thank you in advance!
Create a job for each 2 hours or so based on your business process and check the trans log size when it grows to particular size the job will run the shrink command based on your size
something like
get the @size =(log size )%
if (@size>90%)
begin
Shrink trans log
End
June 24, 2014 at 4:37 am
Don't do this. It just wastes resources and causes fragmentation. If your log files are getting large, then back up your transaction log more frequently. Bear in mind that your log file(s) need to have at least enough space for your largest single transaction.
John
June 24, 2014 at 4:43 am
I agree with John Mitchell.
June 24, 2014 at 5:20 am
Don't do that.
If your log it growing over and over again, you need to determine why it's growing. Deal with that cause. Set the log to an appropriate size. Make sure you have log backups in place. Manage the log space appropriately[/url].
Don't set up automatic shrinking. It's not the right way to get the job done.
"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
June 24, 2014 at 6:12 am
Please don't do that. Horrible practice.
If you manage the log properly, you won't need to shrink it. Please take a read through http://www.sqlservercentral.com/articles/Administration/64582/
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply