2

We have a db which reports errors when trying to do a backup:

enter image description here

Starting to dig in, the first thing that was suggested was to run DBCC CHECKDB. I've been trying to run that all day now, and googling different things, and different options, but just cannot get it to run. Running:

DBCC CHECKDB ('XiaconBackupServer') WITH NO_INFOMSGS, ALL_ERRORMSGS

yields this output, and absolutely nothing else:

Msg 8921, Level 16, State 1, Line 2 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

There are no other errors, or any other output, so check previous errors is completely unhelpful. Tried running the same command on all the other DBs on the server and they work fine, so it's definitely something isolated just to this DB.

The other thing it mentions is disk space for tempdb, and I saw someone say in a post that they needed to manually increase the tempdb log file for it to work. Using the ESTIMATE option with CHECKDB says it needs all of about 4MB. So I've gone overboard, and manually sized my tempdb files (data and log) up to a minimum of 100MB. And they are set to autogrow on a drive which has about 300GB free, so tempdb space should not be the issue either.

Is there anything else we can check, or is this db likely hosed?

We are running SQL Server 2014 Express on Windows Server 2008 R2.
(Yes, I know they are both way old version, ... out of my control.)

  • 1
    I've seen situations where "check previous errors" didn't mean previous errors in the same query window, or previous errors that popped up in message boxes, but meant previous errors in the SQL Server logs. (In SSMS: Management --> SQL Server Logs.) Take a look in there and see if there are any errors shortly before the one displayed when you ran DBCC CheckDB. – Doug Deden Feb 7 at 23:03
  • 1
    I agree. Walk the errorlog file, looking for errors both from the backup command and from DBCC CHECKDB. I don't trust the size estimate from CHECKDB, nor do I trust autogrow. So, I'd grow tempdb to, say 1 GB, or 2, 3, 4, 5 and see if that sorts it. You can always let it grow back to a smaller size at next startup. See sqlblog.karaszi.com/managing-tempdb for managing your tempdb. – Tibor Karaszi Feb 8 at 6:20
  • Thanks for the comments. Walking back through the server logs helped us trace it down. Dang meaningless outputs. Narrowed it down it seems to a corrupt index on one table. If one of you wants to post an answer to that effect, I'll accept it. – eidylon Feb 15 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.