Post reply

Internal SQL Server error fixed by DBCC SHRINKDATABASE

  • November 17, 2006 at 10:53 am

    #117800

    (Sorry if this shows up multiple times. I didn't see it after I posted the first time.)

    Hello everyone! My first post here.

    I've searched all the forums/Microsoft/Google/etc for this, but could not find anything related.

    I am getting an "Internal SQL Server error" when I run a stored proc. The exact place where it blows up is when it does an INSERT INTO a temp table from 2 permanent tables and 1 derived table. This is the first place in the stored proc that does an insert into a temp table. When I comment out the statement and allow the next INSERT INTO to run (on a different temp table), I get the same error also.

    We have over 200 other production db's (properly balanced on several servers) where this stored proc runs fine. We are using Windows 2003 Server (fully patched) and SQL Server 2000 SP4 (fully patched). The Event Viewer and SQL logs don't provide any additional info. The db in question is one of our larger ones, but the stored proc was only processing one record, not 1500 like it does usually.

    Just by chance, I discovered the error goes away after running DBCC SHRINKDATABASE. Our DBA says we don't have any scheduled jobs that shrink the db's. I usually shrink the db when I restore a production db locally, to help speed up my backups and restores. So at first I wasn't getting this error locally, until I tried the stored proc without shrinking the db.

    Why would DBCC SHRINKDATABASE fix this error?

    Should our DBA be running DBCC SHRINKDATABASE on a regular basis?

    Thank you all for your help/opinions.

    Chris

  • November 17, 2006 at 10:59 am

    #672701

    The ShrinkDatabase likely moved data and index pages around, causing internal page linkage tables to be updated and in the process potentially fixing some allocation errors.

    Rather than regularly shrinking to solve this, you should have a maintenance plan setup to check database integrity. Or the DBA should be running some of the other DBCC options that check integrity, like DBCC CheckAlloc, DBCC CheckDB etc.

     

  • November 20, 2006 at 11:11 am

    #673018

    I checked with our DBA, and DBCC CheckAlloc and DBCC CheckDB have not been returning any errors. I ran it locally on my backup and no errors were reported.

    Does anyone have any ideas?

    Thanks!

  • November 21, 2006 at 2:21 pm

    #673333

    OK, after enough digging I found KB 915436, a pretty recent SP4 hotfix, and only available by calling support (no download!). The article suggested disabling processors so SQL Server runs on just one, and it worked!

    So after re-enabling my 2nd processor, restoring the db, and applying the hotfix, everything is good now.

    I hope this helps someone in the future.

  • November 21, 2006 at 2:26 pm

    #673336

    Thanx for letting everyone know .

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

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