David Sankovsky
asked on
MS SQL 2005 Srink database in chunks
Hello experts, I have a 2005 MSSQL server with a databse with a lot of free space in it.
Before any warnings about how bad it is to shrink databases, I know, and the client dimissed all my warnings.
As I'm not good in writing Queries or stored procedures, I require your help.
I know a stored procedure that will shrink the database in chunks, where the target size each time should be available free space minus 200MB. As in, if the current free space is 3 GB, that target size should be 2872 MB, next run should be 2672 etc untill the caclucation return a negative numer (i,e current free space is less than 200MB).
How would I go about creating such an SP?
Before any warnings about how bad it is to shrink databases, I know, and the client dimissed all my warnings.
As I'm not good in writing Queries or stored procedures, I require your help.
I know a stored procedure that will shrink the database in chunks, where the target size each time should be available free space minus 200MB. As in, if the current free space is 3 GB, that target size should be 2872 MB, next run should be 2672 etc untill the caclucation return a negative numer (i,e current free space is less than 200MB).
How would I go about creating such an SP?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have a 2005 MSSQL server with a databse with a lot of free space in it.In data or transaction log files?
You can try with the TRUNCATE_ONLY option first to see how much space is released. If a lot then you might have it done without really changing the data pages (that's the bad thing with shrinking data files).
DBCC SHRINKFILE (file_name, TRUNCATEONLY)
ASKER
It worked, Thanks a lot :)
What Is the issue that lead to the need to shrink the DB?