April 1, 2016 at 5:39 pm
Hello experts,
I'd like to setup a job that will take a log backup, and then shrink the log file for certain specific databases. Now, the only problem here is that the log backup supposedly runs every 1 hour, and there is a full backup job that starts around 11:00 pm, and I've seen cases that since the database is getting backed up the shrink can't run because of the backup, and the job fails. I know I can change the step to succeed even on the job failure, and come out of it as success, but I do not want to go that route. Infact, I wanted to loop into a script that will first check if the backup is running, and if it is running print, a friendly message, and exit out of the loop without doing anything, but if it is not running I need the log shrink to run, and then end the job.
In case if anyone has done something similar please let me know.
Regards,
Fievel
April 1, 2016 at 9:53 pm
How big are the log files and what do you want to shrink them to?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2016 at 10:07 pm
Jeff Moden (4/1/2016)
How big are the log files and what do you want to shrink them to?
The log files are pretty huge almost like 200 Gigs in size, and that is because the client did not use to take frequent log backups, and basically I need to release the space, and give it back to the drive using truncateonly.
April 2, 2016 at 3:28 am
But the Truncate job step will succeed during the other consecutive log backups.It will only fail once when the Full Backup is running which I believe would be only once every 12/24 hours.
How long does it take for the FULL Backup to complete ?
The following link contains a script to check the status of the job
http://www.sqlnotes.info/2012/01/13/are-jobs-currently-running/
You can use the script in the link that utilities sys.dm_exec_sessions DMV.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
April 2, 2016 at 7:28 am
Whenever your shrink the log, you're yielding space back to the operating system. However, activity that occurs in the database will be logged, so the log does need space. If you have to wait for the log to grow before the transaction is completed, that slows things down for your transaction.
I'm reading that your logs are exceptionally large because of improper backup configuration. Kudos to getting it into shape. Instead of setting up a job to shrink the log, try to figure out how much log space is needed in an hour. Bump that figure up a bit and then shrink the log to that amount. The goal is to make the log big enough to handle the activity between log backups without having to grow.
April 2, 2016 at 9:43 am
It is (sometimes) okay to shrink a database file or a log file as a one-time operation, if something happened that caused it to grow beyond the normal size.
But I have never seen a scenario where it is okay to regularly shrink any database file. If it needs the space, it needs the space. If you shrink it to a smaller space, it will regrow as soon as more space is needed. Growing is slow, so this can cause slow response or even time-outs. And consistently shrinking and re-growing will end up causing terrible amounts of fragmentation.
You write that the client had forgotten to take log backups, which has caused the log to grow. That is a good reason for shrinking. But, again, as a one-time operations. So you should not even consider putting this in a job, they are for things you want to repeat on a regular basis.
April 2, 2016 at 1:21 pm
Hugo Kornelis (4/2/2016)
It is (sometimes) okay to shrink a database file or a log file as a one-time operation, if something happened that caused it to grow beyond the normal size.But I have never seen a scenario where it is okay to regularly shrink any database file. If it needs the space, it needs the space. If you shrink it to a smaller space, it will regrow as soon as more space is needed. Growing is slow, so this can cause slow response or even time-outs. And consistently shrinking and re-growing will end up causing terrible amounts of fragmentation.
You write that the client had forgotten to take log backups, which has caused the log to grow. That is a good reason for shrinking. But, again, as a one-time operations. So you should not even consider putting this in a job, they are for things you want to repeat on a regular basis.
I totally get that, but I have a quick question. Does backing up the log clears out space, and gives the space back to the OS. I know for sure it clears the VLF's which are unused, but I don't think it releases space. If that's the case, and if I understand correctly, due to the cyclic nature of the log it should not grow the log after the VLF's have been cleared out, and should cycle back, and that maybe the case when I should not shrink it...right?
Thanks
April 3, 2016 at 2:46 am
ffarouqi (4/2/2016)
Hugo Kornelis (4/2/2016)
It is (sometimes) okay to shrink a database file or a log file as a one-time operation, if something happened that caused it to grow beyond the normal size.But I have never seen a scenario where it is okay to regularly shrink any database file. If it needs the space, it needs the space. If you shrink it to a smaller space, it will regrow as soon as more space is needed. Growing is slow, so this can cause slow response or even time-outs. And consistently shrinking and re-growing will end up causing terrible amounts of fragmentation.
You write that the client had forgotten to take log backups, which has caused the log to grow. That is a good reason for shrinking. But, again, as a one-time operations. So you should not even consider putting this in a job, they are for things you want to repeat on a regular basis.
I totally get that, but I have a quick question. Does backing up the log clears out space, and gives the space back to the OS. I know for sure it clears the VLF's which are unused, but I don't think it releases space. If that's the case, and if I understand correctly, due to the cyclic nature of the log it should not grow the log after the VLF's have been cleared out, and should cycle back, and that maybe the case when I should not shrink it...right?
Thanks
Backing up the log does not give space back to the OS. The only thing it does is that segments of the log file are marked as available for future reuse (unless something else still needs the log entries, for instance an open transaction or a replication reader).
Simple example 1: You have a 200MB log that is completely full. If you modify data, SQL Server needs to grow the log, and it will fail the transaction if it cannot.
Simple example 2: You have a 200MB log that is completely full. You backup the log, now only 5MB is still needed so 195MB is available for reuse, The log file is still 200MB, but only 5MB of it is used. If you modify data, SQL Server will cycle back to the first available VLF in the log and start writing there, so the log file will no longer grow.
I hope this helps!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply