January 20, 2012 at 1:47 am
HI All,
I'm currently looking after our databases while we look to replace out DBA, I've been tasked with removing old data that is no longer needed.
Ive cleared a table out of 30GB of data however no matter what command i use I cannot release this space back to the file system?? DBCC shrink database does nothing at all?
Has anyone any idea's??
Thanks
January 20, 2012 at 1:51 am
Have you rebuild the clustered index? If not, that space is likely to be spread through the table, not free pages that can be released.
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
January 20, 2012 at 1:53 am
a years worth of data was inserted into a new table with indexes being applied at this point, the old was table deleted completly!
January 20, 2012 at 1:58 am
How much free space is there in the database?
How long did you leave things after the drop? Drop table is not an instantaneous removal of data.
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
January 20, 2012 at 2:01 am
there's 28GB free inside the database, was going to leave 8GB ish for natural growth, the table was truncated then dropped. the removal was completed 3 days ago and the SQL instance was restarted yesterday.
January 20, 2012 at 2:16 am
Ok, so DBCC ShrinkFile (<file name of data file>,<target size>)
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
January 20, 2012 at 2:18 am
used it and it doesnt do anything!!!!!
January 20, 2012 at 2:22 am
Have you tried running the more granular shrink commands (I'm thinking your default file size is set high!):
The values are in MB
DBCC SHRINKFILE (N'YourDbName' , 5000)
GO
DBCC SHRINKFILE (N'YourDbName' , 0, TRUNCATEONLY)
GO
http://msdn.microsoft.com/en-us/library/aa258824(v=sql.80).aspx
January 20, 2012 at 2:40 am
What is the size of your model database? DBCC SHRINKFILE won't shrink your database any smaller than that.
John
January 20, 2012 at 2:42 am
Robert Murphy UK1 (1/20/2012)
DBCC SHRINKFILE (N'YourDbName' , 5000)GO
DBCC SHRINKFILE (N'YourDbName' , 0, TRUNCATEONLY)
Why would you run shrinkFile with truncateony (release unused space at the end of the file) right after a normal shrink (move free space to the end of the file and release that unused space)? The normal shrink would have already released any free space at the end of the file.
p.s. ShrinkFile takes the filename not the DB name as first parameter
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
January 20, 2012 at 2:44 am
hewitg (1/20/2012)
used it and it doesnt do anything!!!!!
Try shrinking in small chunks, couple GB at a time. Also check while Shrink's running if it's waiting for anything (like a latch on a page).
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply