March 11, 2011 at 10:08 am
Hi,
I am trying to shrink a SQL Server 2005 user dev database, but mdf file size exists same.
Below are the details
FileSizeMBUsedSpaceMBUnusedSpaceMBGrowthPctGrowthMBDBFileName
3324.563266.6357.94NULL1Test
1.50.660.8410NULLTest_Log
And Backup file size is 3267 MB (everyday full backup)
I tried below options
Since it is dev, I had put the recovery model from full to simple and shrinked the file. No use
USE MyDatabase
GO
DBCC SHRINKFILE('Test', 1)
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE('Test', 1)
GO
Also tried with below script
backup log [dbname] with truncate_only
go
DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)
go
But mdf file size remains same. Please advice.
March 11, 2011 at 2:56 pm
laddu4700 (3/11/2011)
Hi,I am trying to shrink a SQL Server 2005 user dev database, but mdf file size exists same.
Below are the details
FileSizeMBUsedSpaceMBUnusedSpaceMBGrowthPctGrowthMBDBFileName
3324.56 3266.6357.94NULL1Test
1.50.660.8410NULLTest_Log
And Backup file size is 3267 MB (everyday full backup)
I tried below options
Since it is dev, I had put the recovery model from full to simple and shrinked the file. No use
USE MyDatabase
GO
DBCC SHRINKFILE('Test', 1)
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE('Test', 1)
GO
Also tried with below script
backup log [dbname] with truncate_only
go
DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)
go
But mdf file size remains same. Please advice.
Hi laddu4700,
Your trying to shrink the file to 1 MB, and it is consuming 3267 MB.
In order to shrink the database down to 1 MB, You need to get your UnusedSpaceMB up to 3266. To do that you would need to drop the data from your tables.
It looks like you only have 57.94 MB in unused space so that is why your truncate only command is failing to shrink the database by 10%. Because it will only reclaim the free space available.
Here is a link with more info
DBCC Shrinkfile http://msdn.microsoft.com/en-us/library/ms189493.aspx
DBCC ShrinkDatabase http://msdn.microsoft.com/en-us/library/ms190488.aspx
Keep in mind if you do get rid of data and shrink your database you WILL cause fragmentation
Rebuild Index http://msdn.microsoft.com/en-us/library/ms188388.aspx
Tutorial by Brent Ozar http://www.brentozar.com/archive/2006/04/sql-server-training-for-developers-primary-keys-indexes/
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply