January 25, 2010 at 2:26 pm
Hello Experts
Can you please let me know how the "DBCC shrinkfile" operation works?
--- Check the Available free space;;;;
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
I write the above query to find the available free space to the data files.
Now, if I have to shrink data files of a database, and if the free space is: 2000 Mb(10%) and total size of the data file is: 145000 MB.
-- Then how would be the query?
-- can I do the shrink (releasing the free space) in a single go or do I need to do that in small blocks?
-- Is it suggestible to opt for datafile shrink since it gives birth to fragmentation.
Pls assist...
Thanks.
January 25, 2010 at 4:18 pm
Check this out - should give you an idea of how to use the command.
http://msdn.microsoft.com/en-us/library/ms189493(SQL.90).aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 2:03 am
Sourav-657741 (1/25/2010)
-- Is it suggestible to opt for datafile shrink since it gives birth to fragmentation.
No.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply