August 9, 2014 at 8:23 am
As there are lack of space in the DB hdd and i got several databases in there. I purged lots of records (600GB free space) in one of the database. So now, i need to shrink that db to release space for the other db.
I executed the sql belows and no space was released.
=================
use DBData
set nocount on
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkMB int
-- Set Name of Database file to shrink
set @DBFileName = 'DB_Data'
-- Set Desired file free space in MB after shrink
--set @TargetFreeMB = 50000
-- Set Increment to shrink file by in MB
set @ShrinkMB= 10240
-- Show Size, Space Used, Unused Space, and Name of all database files
select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB]= convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB]= convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,[DBFileName]= a.name from sysfiles a
--select * from sysfiles
declare @sql varchar(8000)
declare @SizeMB float
declare @UsedMB float
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.0
--select @SizeMB, @UsedMB, @TargetFreeMB, @ShrinkMB
set @sql = 'dbcc shrinkfile ( '+@DBFileName+', ' + convert(varchar(20),convert(numeric(10,0),@SizeMB-@ShrinkMB))+' ) WITH NO_INFOMSGS'
print 'Start ' + @sql + ' at ' + convert(varchar(30),getdate(),121)
exec ( @sql )
print 'Done ' + @sql + ' at '+convert(varchar(30),getdate(),121)
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.0
print 'SizeMB=' + convert(varchar(20),@SizeMB) + ' UsedMB=' + convert(varchar(20),@UsedMB)
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
====================
i tried to shrink in smaller shrink size (@ShrinkMB= 512), but the result was same. Do you guys have any idea on it? Thank you very much
August 11, 2014 at 3:50 am
Since shrinking of database is not advised due to lot of fragmentation it creates. I hope the db is not shrinked , may be its online or in use. In a case like this since you need create room for other db's If the db's log file is much larger , try to shrink the log file first which causes less impact. if you got sufficient space after that don't touch the datafile. also the log file cannot be shrunk if it is also in use.
August 11, 2014 at 4:42 am
Can you run it without the WITH NO_INFOMSGS & see what the output says?
Also add print ( @sql ) directly before/after exec ( @sql ) so you can see the command being executed.
August 11, 2014 at 9:03 pm
If there is cluster index on the purge table, recreate the cluster index and then shrink the database, that should release the space to OS.
Regards,
SQLisAwe5oMe.
August 11, 2014 at 9:17 pm
Are you getting any errors?
Have you checked for any blocking?
Or is it just that the shrink is running a really long time and never ending?
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
August 11, 2014 at 9:58 pm
Gazareth (8/11/2014)
Can you run it without the WITH NO_INFOMSGS & see what the output says?Also add print ( @sql ) directly before/after exec ( @sql ) so you can see the command being executed.
Thanks, we will try to remove WITH NO_INFOMSGS and execute the sql this weekend.
August 11, 2014 at 9:59 pm
SQLisAwE5OmE (8/11/2014)
If there is cluster index on the purge table, recreate the cluster index and then shrink the database, that should release the space to OS.
Or can we perform rebuild index rather than recreate the clustered index before the shrink?
August 11, 2014 at 10:04 pm
SQLRNNR (8/11/2014)
Are you getting any errors?Have you checked for any blocking?
Or is it just that the shrink is running a really long time and never ending?
No, GUI showed the sql query was executed with no error. It just took about 3 mins to complete the process.
It's weird that we restored a older backup(a month ago, also got ~600GB free space inside the DB data file) to another SQL environment and the space could be released by shrink successfully.
August 11, 2014 at 10:57 pm
Run the script from this link to get the actual sizes and free space in the database.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply