DBCC ShrinkFile Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DBCC ShrinkFile Error

Author

Topic

arifliminto86
Starting Member

7 Posts

Posted - 2008-02-06 : 21:43:42

hi i got problem when i want to shrink file into one of my database
it always cause error
"A severe error occurred on the current command. The results, if any, should be discarded."

it happend in DBCC ShrinkFile (@name, 0)
i dont know why it occurs anybody can help?

thanks


-- shrink all files within the database
Declare @curFiles Cursor
Declare @Name sysname

Set @curFiles = Cursor Local Fast_Forward Read_Only For
	Select RTrim(LTrim(name)) from sysfiles

Open @curFiles

Fetch Next From @curFiles Into @Name
While @@Fetch_Status = 0
Begin

	-- Cause problem because transaction log backup had run at the same time 
    --that the shrink was occurring which is what caused this latch problem
	DBCC ShrinkFile (@name, 0)
	Fetch Next From @curFiles Into @Name
End
Close @curFiles;
deallocate @curFiles;
go

arifliminto86

[edit by tkizer]: moved thread out of Data Corruption forum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-06 : 22:12:28

You seem to have quoted my blog in your code (http://weblogs.sqlteam.com/tarad/archive/2007/11/08/60394.aspx). Did you read the fix in it as well?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

arifliminto86
Starting Member

7 Posts

Posted - 2008-02-06 : 22:35:01

hi tkizer, yeah i read your blog, but i could not fiqure it out for solving it by disable transaction log backups while shrinking the database and enable it after that ? can you help me Thanks

arifliminto86

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-07 : 00:46:56

You have to restart the SQL instance in order to get rid of the error. Once the error is present, there is no other way to get rid of it except to restart the SQL instance. This is directly from MS per the case I opened with them.

After the restart, you can avoid the error by disabling the job during the shrink process. Make sure to enable your job once you are done.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources