March 13, 2018 at 11:37 am
I am having DB with Full recovery mode and need to run the shrink DB script. My question is that, Is it mandatory to change the dB as Simple recovery mode and then run the Shrink DB sc
USE master
GO
ALTER DATABASE DB_Name SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE (TransactionLogFileName, 1)
GO
ALTER DATABASE DB_Name SET RECOVERY FULL WITH NO_WAIT
GO
Thanks in Advance.
March 13, 2018 at 12:09 pm
It's not required to set the DB to Simple to shrink a transaction log, but the question you need to ask and answer first is the why?
Why are you needing / wanting to shrink the log?
Is it because the DB is in Full and no one has been doing Transaction Log backups, or not taking them frequently enough to keep the size under control?
Is it because there was a one-time event that caused the log to grow?
If it's the first, then solve the root problem first, and either set up a Transaction Log backup process, or increase the frequency of the log backups, THEN shrink the log after the first backup or two has run.
If it's the second, then again, wait for a regular log backup, or if possible take an out-of-sequence log backup, then shrink the file.
BUT don't just switch it to Simple, shrink, and switch back to full. If you're already taking log backups, this will break the log chain and you won't be able to recover to a point-in-time until you take a new full backup and a new log backup.
Additionally, shrinking it all the way down as small as possible isn't going to keep it that small, no matter what. Your log file *will* grow again, how much will depend on how big it needs to be.
March 13, 2018 at 12:15 pm
jasona.work - Tuesday, March 13, 2018 12:09 PMIt's not required to set the DB to Simple to shrink a transaction log, but the question you need to ask and answer first is the why?Why are you needing / wanting to shrink the log?
Is it because the DB is in Full and no one has been doing Transaction Log backups, or not taking them frequently enough to keep the size under control?
Is it because there was a one-time event that caused the log to grow?If it's the first, then solve the root problem first, and either set up a Transaction Log backup process, or increase the frequency of the log backups, THEN shrink the log after the first backup or two has run.
If it's the second, then again, wait for a regular log backup, or if possible take an out-of-sequence log backup, then shrink the file.BUT don't just switch it to Simple, shrink, and switch back to full. If you're already taking log backups, this will break the log chain and you won't be able to recover to a point-in-time until you take a new full backup and a new log backup.
Additionally, shrinking it all the way down as small as possible isn't going to keep it that small, no matter what. Your log file *will* grow again, how much will depend on how big it needs to be.
Thank you Josana for your response. We are taking log backup every 1 hour in this server. But this log files is growing and going to exceed the drive space.
March 13, 2018 at 12:20 pm
Subu30 - Tuesday, March 13, 2018 12:15 PMjasona.work - Tuesday, March 13, 2018 12:09 PMIt's not required to set the DB to Simple to shrink a transaction log, but the question you need to ask and answer first is the why?Why are you needing / wanting to shrink the log?
Is it because the DB is in Full and no one has been doing Transaction Log backups, or not taking them frequently enough to keep the size under control?
Is it because there was a one-time event that caused the log to grow?If it's the first, then solve the root problem first, and either set up a Transaction Log backup process, or increase the frequency of the log backups, THEN shrink the log after the first backup or two has run.
If it's the second, then again, wait for a regular log backup, or if possible take an out-of-sequence log backup, then shrink the file.BUT don't just switch it to Simple, shrink, and switch back to full. If you're already taking log backups, this will break the log chain and you won't be able to recover to a point-in-time until you take a new full backup and a new log backup.
Additionally, shrinking it all the way down as small as possible isn't going to keep it that small, no matter what. Your log file *will* grow again, how much will depend on how big it needs to be.
Thank you Josana for your response. We are taking log backup every 1 hour in this server. But this log files is growing and going to exceed the drive space.
Then it sounds like you aren't taking the log backups frequently enough to control the growth. You may want to look at increasing the frequency of the log backups.
March 13, 2018 at 12:34 pm
Lynn Pettis - Tuesday, March 13, 2018 12:20 PMSubu30 - Tuesday, March 13, 2018 12:15 PMjasona.work - Tuesday, March 13, 2018 12:09 PMIt's not required to set the DB to Simple to shrink a transaction log, but the question you need to ask and answer first is the why?Why are you needing / wanting to shrink the log?
Is it because the DB is in Full and no one has been doing Transaction Log backups, or not taking them frequently enough to keep the size under control?
Is it because there was a one-time event that caused the log to grow?If it's the first, then solve the root problem first, and either set up a Transaction Log backup process, or increase the frequency of the log backups, THEN shrink the log after the first backup or two has run.
If it's the second, then again, wait for a regular log backup, or if possible take an out-of-sequence log backup, then shrink the file.BUT don't just switch it to Simple, shrink, and switch back to full. If you're already taking log backups, this will break the log chain and you won't be able to recover to a point-in-time until you take a new full backup and a new log backup.
Additionally, shrinking it all the way down as small as possible isn't going to keep it that small, no matter what. Your log file *will* grow again, how much will depend on how big it needs to be.
Thank you Josana for your response. We are taking log backup every 1 hour in this server. But this log files is growing and going to exceed the drive space.
Then it sounds like you aren't taking the log backups frequently enough to control the growth. You may want to look at increasing the frequency of the log backups.
Agreed. If you're running that close to the drive being full, you might want to look at getting more drive space.
March 13, 2018 at 12:34 pm
Lynn Pettis - Tuesday, March 13, 2018 12:20 PMSubu30 - Tuesday, March 13, 2018 12:15 PMjasona.work - Tuesday, March 13, 2018 12:09 PMIt's not required to set the DB to Simple to shrink a transaction log, but the question you need to ask and answer first is the why?Why are you needing / wanting to shrink the log?
Is it because the DB is in Full and no one has been doing Transaction Log backups, or not taking them frequently enough to keep the size under control?
Is it because there was a one-time event that caused the log to grow?If it's the first, then solve the root problem first, and either set up a Transaction Log backup process, or increase the frequency of the log backups, THEN shrink the log after the first backup or two has run.
If it's the second, then again, wait for a regular log backup, or if possible take an out-of-sequence log backup, then shrink the file.BUT don't just switch it to Simple, shrink, and switch back to full. If you're already taking log backups, this will break the log chain and you won't be able to recover to a point-in-time until you take a new full backup and a new log backup.
Additionally, shrinking it all the way down as small as possible isn't going to keep it that small, no matter what. Your log file *will* grow again, how much will depend on how big it needs to be.
Thank you Josana for your response. We are taking log backup every 1 hour in this server. But this log files is growing and going to exceed the drive space.
Then it sounds like you aren't taking the log backups frequently enough to control the growth. You may want to look at increasing the frequency of the log backups.
Thanks Lynn. I will check this.
March 15, 2018 at 8:23 pm
Subu30 - Tuesday, March 13, 2018 12:34 PMLynn Pettis - Tuesday, March 13, 2018 12:20 PMSubu30 - Tuesday, March 13, 2018 12:15 PMjasona.work - Tuesday, March 13, 2018 12:09 PMIt's not required to set the DB to Simple to shrink a transaction log, but the question you need to ask and answer first is the why?Why are you needing / wanting to shrink the log?
Is it because the DB is in Full and no one has been doing Transaction Log backups, or not taking them frequently enough to keep the size under control?
Is it because there was a one-time event that caused the log to grow?If it's the first, then solve the root problem first, and either set up a Transaction Log backup process, or increase the frequency of the log backups, THEN shrink the log after the first backup or two has run.
If it's the second, then again, wait for a regular log backup, or if possible take an out-of-sequence log backup, then shrink the file.BUT don't just switch it to Simple, shrink, and switch back to full. If you're already taking log backups, this will break the log chain and you won't be able to recover to a point-in-time until you take a new full backup and a new log backup.
Additionally, shrinking it all the way down as small as possible isn't going to keep it that small, no matter what. Your log file *will* grow again, how much will depend on how big it needs to be.
Thank you Josana for your response. We are taking log backup every 1 hour in this server. But this log files is growing and going to exceed the drive space.
Then it sounds like you aren't taking the log backups frequently enough to control the growth. You may want to look at increasing the frequency of the log backups.
Thanks Lynn. I will check this.
Check what's bashing the log file so hard. For example, if you have a whole lot of indexes that have a 100% Fill Factor and you defrag them on a regular basis, the page splits can absolutely crush the log file. So can crazy inserts that are immediately followed by one or more updates. Of course, there's also the really nasty way of doing updates where someone inserts the updated row and deletes the old row.
Then there's the index maintenance itself. Reorgs are always fully logged. In the Full Recovery Model, Rebuilds are also fully logged. If you have indexes being rebuilt or reorged every day because of a too high Fill Factor (100% being particularly bad), then your index maintenance could be a large part of the reason for an overly large log file.
I don't use the term often but automating shrinks of the log file is stupid because 1) unless you find what's making it grow, it's going to be a never ending battle and 2) unless you shrink and regrow the log file in a simple but very well planned evolution, you could be killing performance with a whole lot of unwanted VLFs.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply