DBCC shrink file error

Question

Post reply

DBCC shrink file error

sql-lover

SSCoach

Points: 18530
More actions
January 21, 2010 at 12:36 pm

#96945

Trying to shrink a database file (the mdf, NOT the log file) and after a while I got the following error:
"A severe error occurred on the current command. The results, if any, should be discarded"
I really need to run this command in order to release or regain some space from the database. This is MS-SQL2008 and I'm using the following code:
USE MyDatabase;
GO
DBCC SHRINKFILE (Mydatabase 37000);
GO
Current reserved space is 63GB and used space is 34.9GB. SQL is telling me that min size I can reduce is 35733 GB; I set to 37GB so not sure why is failing.
Any help is appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Run this, post any results.

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Also, don't shrink the DB to the minimum size. Leave it some space free. SQL needs some free space in the data file and if there is none, the next thing that will happen is that the file grows again. This can take time and slow your app down. Also note that you need to rebuild all indexes after a shrink operation.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 2

Ran the DBCC CHECKDB, and nothing, no errors ..

I tried to run the DBCC shrinkfile again, and failed. This time I ran a Tlog backup prior execution.

any ideas?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Can you try shrinking in smaller chunks?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 4

I recieved this db from a former dba and I overlooked the Tlog size. It was ridiculous small. About 1gb for a 80 GB database; the small size was aborting the srhink command. I changed it and problem was fixed. But curious to know if anyone else has a script to do shrink in chunks.

Thanks

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

declare @DBFileName sysname

declare @TargetSizeMB int

declare @ShrinkIncrementMB int

-- Set Name of Database file to shrink

set @DBFileName = 'ENTERDBFILENameHere'

-- Set Desired file size in MB after shrink

set @TargetSizeMB = 1000--For example

-- Set Increment to shrink file by in MB

set @ShrinkIncrementMB = 500--For example

declare @sql varchar(8000)

declare @SizeMB int

declare @UsedMB int

-- 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.

select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Loop until file at desired size

while @SizeMB > @TargetSizeMB

begin

set @sql =

'dbcc shrinkfile ( '+@DBFileName+', '+

convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '

print 'Start ' + @sql

print 'at '+convert(varchar(30),getdate(),121)

exec ( @sql )

print 'Done ' + @sql

print '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.

select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName

end

select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

MJ