June 15, 2006 at 9:37 am
I have a database with 135Gb allocated to the mdf and 6 Gb allocated to the ldf. I need to cut these back to 75 Gb and 3 Gb respectively. My plan is to backup the original database and drop it. The next step would be to create a new database with the new allocations and then restore the backup to the new database. Does this sound like a reasonable approach? I want to be sure that the allocations will not change when the original database is restored. Thanks in advance for your help!
June 15, 2006 at 10:09 am
The restored database will use the original file sizes.
DBCC SHRINKFILE is the way to go.
I'd advise taking a full backup first, expanding the log file considerably and taking a log backup. From there, you'll perform a series of DBCC SHRINKFILE and BACKUP LOG batches until all of your mdf and ndf files are down to the right size (or just the mdf if it's all in one file).
Then you'll perform a series of DBCC SHRINKFILE and BACKUP LOG batches on your ldf files (it often takes a couple passes through this to get all the transactions written to the backup and all the active segments to the right part of the file) until they are your target size.
-Eddie
Eddie Wuerch
MCM: SQL
June 15, 2006 at 10:25 am
I've tried the Shrink/Backup approach without success. It never goes below the size allocated in the database properties. To backup the log I’m using:
BACKUP LOG @DBName, WITH TRUNCATE_ONLY
Is the truncate only causing the problem?
June 16, 2006 at 4:39 am
BACKUP LOG ... WITH TRUNCATE ONLY is doing what it says on the can. Run the backup log process but ONLY truncate the log file.
You need to do a normal BACKUP LOG to empty the old data out of your log file. An alternative would be to put your database into Simple recovery mode while you are doing the DBCC SHRINKFILE.
Always run the shrinkfile on the mdf and ndf files before running it on your log files. This is because a shrink on your data files will write information to the log as it moves tables to empty the space to be released. If you shrink the log first you will have to shrink it again last...
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 16, 2006 at 1:05 pm
See if you have white space to performing that...
See if you can use shrinkdatabase instead of shrinkfile, but it takes longer...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply