I am an experienced programmer (not a DBA) and would appreciate troubleshooting guidance if no immediate answer is possible.
The database itself is relatively small - a couple of million rows spread over 500 tables - and is used primarily to cache static data owned by a remote mainframe system. It is also used for audit trail / logging and caching of transient data during capture.
The server is Microsoft SQL Server 2016 Enterprise and the database is run in 2008 compatibility mode
A housekeeping job runs every 24 hours, during which static content is refreshed (involving DROP, (re)CREATE of several hundred tables), followed by
DBCC UPDATEUSAGE (DBName) WITH COUNT_ROWS
Some time back, our support team started to report deadlock messages in the log file. The housekeeping job is always "chosen as victim".
Diagnostic efforts have shown that with
DBCC UPDATEUSAGE (DBName) WITH COUNT_ROWS removed, no deadlock occurs. When
DBCC UPDATEUSAGE (DBName, TableName) WITH COUNT_ROWS is run (manually from SSMS) over all tables, one at a time, it always completes, always very quickly, with no errors, but when run on the database as a whole, it always fails to return and has to be aborted after several hours.
This situation occurs on only one production server (out of 4) and does not occur in the similarly set up test environment. The fact that I am investigating on a production server limits my ability to experiment.
Guidance on how to diagnose from a locking, resource contention, corruption (or any other) point-of-view would be appreciated.
A second and possibly related issue
Select name from DBName..sysobjects where type = 'U' and name = 'Tablename' returns a value for
name when it is clear (from SSMS) that said table does not exist.
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Tablename' seems always correct where the query against
sysobjects is quite often incorrect.
Giving up and moving on.
No answer, no clues. The company's DBAs have a weekly housekeeping routine that runs quickly and error free, it includes DBCC CheckDB, rebuild indexes and update statistics.
I note the often-repeated advice that DBCC UPDATEUSAGE shouldn't be necessary, even when current (2022) documentation says "Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements" which does apply to our routine. (I know I know, daily is not weekly.)
The facts remain that on one server of four set up the same way (2 centres, 2 disaster recovery mirrors), DBCC UPDATEUSAGE (database) goes almost immediately into a suspended state and stays there, whereas 400+ DBCC UPDATEUSAGE (database, table) for every table in the database completes in about 3 seconds.
There has to be a sensible explanation, it probably has to do with contention for something other than a table, it's obviously not obvious. Will check back periodically.