October 22, 2014 at 9:15 am
I never thought I would want to shrink a database but here I am. I was trying to figure out why the database was growing so quickly and finally found there was a vendor bug in our software package that never truncated a table used in a report. So after truncating that table and doing some never-done cleanup of other tables my 126GB database has 44% free space. I would like to bring the database back down to about 76GB in order to get a little more headroom on this space challenged server which will hopefully be retired next year.
I was successful in reclaiming space on 2 of our three application databases...and the "test" version of the 126GB database. But the "live" 126GB database will not shrink.
I tried using the right-click\tasks\shrink file, and right-click\tasks\shrink database. Both processes run a few seconds then stop without any error or success messages and the file size is unchanged.
After reading several articles here and elsewhere I tried the code below. Again, no error messages and no file size reduction (except the log file size.)
backup log [AXC] with TRUNCATE_ONLY;
dbcc shrinkfile ('axc_log', 0, TRUNCATEONLY)
GO
DBCC SHRINKDATABASE ( [AXC], 10)
GO
backup log [AXC] with TRUNCATE_ONLY;
dbcc shrinkfile ('axc_log', 0, TRUNCATEONLY)
GO
DBCC SHRINKDATABASE ( [AXC], 10)
go
I moved the backups to another disk in order to gain as much headroom as possible thinking that might be the issue but still no luck.
Free space on my data drive is 357GB, Free space on the C: drive (where tempdb is stored) is 65GB. tempdb itself is about 5GB in size with 99% free space.
This is SQL 2005, SP3 running on Windows Server 2008 R2, SP1 64bit.
I'm not sure what to try next.
October 22, 2014 at 9:19 am
I meant to post this in my orginal message. This is the results of the DBCC SHRINKDATABASE:
DbIdFileId CurrentSizeMinimumSizeUsedPagesEstimatedPages
101 1585015248000 84627048462704
102 696 625 696 624
(2 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
January 14, 2015 at 11:17 pm
pvanhouse,
you need to do your shrink database with a notruncate option followed by truncateonly. The first option will move data at the end of the data file to unused, available space toward the beginning of the file, leaving free space at the end. Then the TRUNCATEONLY will release the free space at the end of the file back to the operating system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply