June 17, 2014 at 1:23 pm
We are running SQL Server 2012 on Windows Server 2008. I attempted to shrink a database (that was 34 GB) using:
DBCC SHRINKDATABASE(DBName, 20)
The database file shrunk just fine but the log file did not shrink as much. (NOTE: We deleted a lot of data out of the database before the ShrinkDatabase command was executed.) The database file went from 17 GB to 800 MB. The Log File went from 17 GB to about 14 GB. Did the log file not shrink as much because the DBCC SHRINKDATABASE command is also writing to the log file?
--Took 12 minutes to shrink a 34 GB DB.
--The transaction log backups run every 10 minutes (2:00, 2:10, 2:20, 2:30, etc.)
---At 2:00 the log backup size was 3 MB.
---The DBCC SHRINKDATABASE (DBName, 20) command started.
---At 2:10 the log backup size was 1.7 GB.
---At 2:20 the log backup size was 1.8 GB.
---The DBCC SHRINKDATABASE (DBName, 20) command finished.
---At 2:30 the log backup size was 3 MB.
---Executed the DBCC SHRINKFILE (DBName_Log, 100) command.
---At 2:40 the log backup size was 105 KB. Is this significantly lower (100 KB compared to 100 MB in the previous ShrinkFile command) because the 2:40 log backup truncated the log?
With SQL Server 2012, in order to shrink a database, are the following two commands required (as long as the transaction logs are being backed up?
DBCC SHRINKDATABASE (DBName, 20)
DBCC SHRINKFILE (DBName_Log, 100)
Does these commands affect the backup chain/sequence or archive bit? (I don't think so.)
I tried using the GUI to shrink the database (right click Database, Tasks, Shrink, Database) but it appeared to have shrunk the data file but not the log file. And, I am guessing that is because of what I layed out above which is in order to shrink the log file, the log backup has to run and then the log file has to be shrunk. Is my assessment correct?
Thanks, Kevin
June 17, 2014 at 1:45 pm
kevinsql7 (6/17/2014)
Did the log file not shrink as much because the DBCC SHRINKDATABASE command is also writing to the log file?
No. The log can only be shrunk to the end of the last active VLF, whereever that is within the log file.
With SQL Server 2012, in order to shrink a database, are the following two commands required (as long as the transaction logs are being backed up?
No. ShrinkDatabase just does a shrinkfile on each file.
DBCC SHRINKDATABASE (DBName, 20)
DBCC SHRINKFILE (DBName_Log, 100)
Does these commands affect the backup chain/sequence or archive bit? (I don't think so.)
No.
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
June 17, 2014 at 3:57 pm
Kevin,
First thing run this to verify there are no active transactions:
DBCC OPENTRAN
If this returns nothing run this to view the Virtual Log Files on the database:
DBCC LOGINFO
If the last log file is Status of anything other than 0 then you can't shrink it any further. If you have tons of Status=0 behind the last Status=2 then run a Log Backup of the database and check the LOGINFO again. If the Status=2 is still there at least hopefully you'll have some VLF's with Status=0 after the 2, and if so a Shrink will get rid of these.
Sometimes when the Lgg file is really large I have to run a backup then shrink a couple of times to get it down to size, I have no idea why. I actually have an open conversation with a MS tech on one of the MS Forums about this. SharePoint databases are REALLY bad at this (I know, shrinking a DB, especially SharePoInt isn't good -- but sometimes there's no option).
HTH.
June 18, 2014 at 3:59 am
Do not forget that your physical log file contains what are called virtual log files. If the VLF in use starts 14GB into your log file, then the physical log file can only be shrunk down to 14GB.
You need to wait until a VLF located near the start of the physical log file is in use, and the VLFs near the end of the physical file have been backed up, and the log serial of the oldest open transaction has moved to a VLF near the start of the physical file and the log serial of the oldest outstanding replication operation has moved to a VLF near the start of the physical file (if you are using replication) and maybe a few other things before you can shrink the log file further.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 24, 2014 at 9:42 am
Thanks GilaMonster, samalex, and EdVassie. The information you provided was a huge help to me.
GilaMonster,
Thanks GilaMonster for the information you provided. Just to clarify, I take it that the dbcc shrinkdatabase command does not write to the transaction log. And I take it that as long as the transaction logs are being backed up, I only need to run the dbcc shrinkdatabase command because it will shrink both files (data and log).
Samalex,
Thanks for the DBCC commands and information on the VLFs. Just to clarify, I take it that if the transaction log is really large then I may have to run a log backup and then run a dbcc shrinkfile (not dbcc shrinkdatabase) on the log file a couple of times.
EdVassie,
Thank you for the information on the VLF. It was very helpful.
Kevin
June 24, 2014 at 10:20 am
kevinsql7 (6/24/2014)
Just to clarify, I take it that the dbcc shrinkdatabase command does not write to the transaction log.
It's a modification to the database. It is logged. So's shrinkfile. ALL modifications to the database are logged, no exceptions.
And I take it that as long as the transaction logs are being backed up, I only need to run the dbcc shrinkdatabase command because it will shrink both files (data and log).
You don't *need* to run shrink at all. You shouldn't be running shrink unless something unusual has happened to leave a lot of free space that you don't expect to be reused in a reasonable amount of time
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
June 24, 2014 at 11:11 am
Hi GilaMonster,
Can u please explain disadvantages of shrinking data and log files.?
IF the drive where your log file is situated gets full, can't you shrink the Log file to free some space.? If not, what should be the best practice in that case.?
And, Is it safe to shrink Data Files.?
June 24, 2014 at 12:00 pm
Mr. Kapsicum (6/24/2014)
IF the drive where your log file is situated gets full, can't you shrink the Log file to free some space.?
Shrink releases free space in the file to the OS. 'Full' means no free space in the file. Given that, what do you think?
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply