June 18, 2009 at 7:44 am
After approx. 2 minutes into the EMPTYFILE, the error message:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Command executed:
DBCC SHRINKFILE (N'TitlePointDB_Data3' , EMPTYFILE)
Database:
CREATE DATABASE [LargeDatabase] ON PRIMARY
( NAME = N'LargeDatabase_Data', FILENAME = N'j:\SqlData\LargeDatabase.mdf' , SIZE = 870400000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0),
( NAME = N'LargeDatabase_Data12', FILENAME = N'H:\SQLData\LargeDatabase_Data12.ndf' , SIZE = 230400000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'LargeDatabase_Data13', FILENAME = N'G:\SQLData\LargeDatabase_Data13.ndf' , SIZE = 230400000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'LargeDatabase_Data14', FILENAME = N'G:\SQLData\LargeDatabase_Data14.ndf' , SIZE = 230400000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'LargeDatabase_Data15', FILENAME = N'F:\SQLData\LargeDatabase_Data15.ndf' , SIZE = 230400000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'LargeDatabase_Data16', FILENAME = N'F:\SQLData\LargeDatabase_Data16.ndf' , SIZE = 230400000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'LargeDatabase_Data2', FILENAME = N'k:\SqlData\LargeDatabase_1.mdf' , SIZE = 870400000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0),
( NAME = N'LargeDatabase_Data3', FILENAME = N'l:\SqlData\LargeDatabase_Data3.ndf' , SIZE = 870400000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0)
LOG ON
( NAME = N'LargeDatabase_log', FILENAME = N'n:\SqlLog\LargeDatabase_log.ldf' , SIZE = 40960000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'LargeDatabase_log2', FILENAME = N'O:\SqlLog\LargeDatabase_log2.ldf' , SIZE = 30720000KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
Any ideas?
Tommy
Follow @sqlscribeJune 18, 2009 at 8:35 am
Can you run a checkDB on that database please. Sometimes that message is because of corruption.
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
June 18, 2009 at 8:41 am
Thank you Gail. I'm going to attempt another CHECKDB now. It ran for quite some time the other day and the following error kept getting recorded in the error log:
Date6/16/2009 2:13:03 AM
LogSQL Server (Current - 6/18/2009 4:00:00 AM)
Sourcespid75
Message
Timeout occurred while waiting for latch: class 'DBCC_MULTIOBJECT_SCANNER', id 6BE1E65C, type 4, Task 0x00E2AF28 : 26, waittime 5100, flags 0x1a, owning task 0x00D95D38. Continuing to wait.
http://support.microsoft.com/kb/919155
Tommy
Follow @sqlscribeJune 18, 2009 at 10:30 am
These two errors together lead me to believe there's something wrong with your I/O subsystem. The CHECKDB issue is caused most commonly when the I/O subsystem is overloaded (can you check the disk queue lengths) or there are corruptions which cause I/Os to seem to go slowly.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 18, 2009 at 11:03 am
Thanks Paul. The disk queue length was 364 while the CHECKDB was running (WITH PHYSICAL_ONLY, NO_INFOMSGS, ALL_ERRORMSGS).
I'll follow-up with our storage team.
Tommy
Follow @sqlscribeJune 18, 2009 at 11:13 am
Yeah, that's a little high 🙂
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 1, 2009 at 7:27 pm
After resolving the I/O issues; CHECKDB came back clean however we were still running into this error.
After much research and work w/ MS we determined this to be an x86 platform limitation with the DBCC SHRINKFILE operation.
Our instance is on x86 hardware with 32GB of memory; 2GB VAS, 2TB database with LOB. In our particular configuration approx. 1GB visible bpool of which 60%, roughly 600MB can be consumed by the DBCC SHRINKFILE operation.
During the DbccSpaceReclaim phase of the DBCC SHRINKFILE operation, the process acquires millions of locks while attempting to expose space in the lower portion of the file. These locks do not get released until the end of the transaction. In our case, DbccSpaceReclaim acquired approx. 6.5 million locks –or- 600MB after which the process aborted with the following error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
We have a ticket open up with MS which has been escalated to the dev team. More than likely this will be addressed in a later build.
Moving to an x64 box is our best option, although there is still the LOB compaction performance issues we still have to deal with. CSS has an interesting blog about this...
How It Works: SQL Server 2005 DBCC SHRINK* May Take Longer Than SQL Server 2000
Another option would be to BCP the data out into a new table on a new file group....
Thanks Paul and Gail for your assistance, hopefully this helps someone else out there 😉
Tommy
Follow @sqlscribeJuly 28, 2009 at 11:54 am
UPDATE:
Just received confirmation from MS that the development team has agreed to address this issue in the next service release for 2005/2008.
Tommy
Follow @sqlscribeViewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply