August 23, 2011 at 5:50 am
Hi, I keep getting the error Msg 0, Level 11, State 0, Line 0 when doing a reorg various indexes in my database. If I alter the index and rebuild it is fine. DBCC checks on the table that the error occurs on at that moment returns no errors. The DB is 4TB and very busy I cannot do a DBCC check on the entire DB at this time. I have had to change my scripts to alter instead of reorganize.
I don't always test my SQL scripts, but when I do, I test in Production.
August 23, 2011 at 5:53 am
What's the message on that error?
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
August 23, 2011 at 5:56 am
This is the full message
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Thanks for your response.
I don't always test my SQL scripts, but when I do, I test in Production.
August 23, 2011 at 6:01 am
You need to do a checkDB on that. If not on the live DB then on a restored copy. This is not a luxury, nice-to-have, it's something you must do regularly.
That message is an indication that there is corruption and you won't know how bad or how to fix until you have run a complete checkDB.
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
August 23, 2011 at 6:07 am
Ok thanks for your response I do very much appreciate it. I do realize I need to do this but I cannot do it at this time and the reasons are not good. The real fact is I don't have enough space to create the sparse files on every drive that DBCC creates. We are moving to a new SAN very soon but the SAN we are on is out of space. It's just a bad situation I have to try and deal with.
I don't always test my SQL scripts, but when I do, I test in Production.
August 23, 2011 at 6:36 am
Just bear in mind that you don't know what is wrong. It may be something trivial, it may be something that brings the system down tomorrow.
Got somewhere else you can restore (or use RedGate's VirtualRestore) and CheckDB that (with TABLOCK if necessary)?
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
August 23, 2011 at 7:52 am
I don't currently have anywhere with that much space to restore to. (Were a pretty small company. ) Our development version of the DB isn't as large. I do wish I had Redgate I have asked for it.
Once we do this migration I will have plenty of space the only plan I currently have is to make it to then. The one thing I don't understand is why a DBCC check on the table comes back ok. I would think if there was corruption it would show on the table.
I don't always test my SQL scripts, but when I do, I test in Production.
August 23, 2011 at 9:04 am
It may not be the table, could be the allocation structures, could be the system catalog.
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
August 29, 2011 at 3:51 pm
Hi again. I was wondering if you know of a way of predicting the space needed to run DBCC checks?
DBCC list the estimate only but this doesn't tell the sparse files only TempDB
Thanks.
I don't always test my SQL scripts, but when I do, I test in Production.
August 30, 2011 at 3:48 am
No, because that's completely dependant on how much concurrent activity there is during the CheckDB
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply