March 24, 2015 at 12:13 pm
SQL 2012 EE SP2 CU1 is current SQL Version/Edition.
Below is the command we are executing on weekly basis as a sql agent job.
sp_MSforeachdb 'dbcc checkdb("?") with no_infomsgs'
Error Message:
Object ID 34 (object 'sys.sysschobjs'): DBCC could not obtain a lock on this object because the lock request timeout period was exceeded. This object has been skipped and will not be processed. [SQLSTATE 42000] (Error 5245). The step failed.
I was not able to fetch any info online. Please help.
March 25, 2015 at 7:09 am
Can anybody assist me with above error message I am getting.
March 25, 2015 at 7:11 am
Try again later, should just be a transitory thing.
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
March 25, 2015 at 7:23 am
@Gail Shaw;
We run the job every week and it's been 5 weeks now we are getting this error. We did rerun of the job other than job schedule but failed with same error.
Please advise.
March 25, 2015 at 7:38 am
Try to split up your DBCC checkdb job
First try to do all your user databases in a job step, use the following command if you are using SQL Server 2012.
Try to use the DBCC traceflags 2562,2549
Trace flag 2549
The DBCC CHECKDB command builds an internal list of pages to read per unique disk drive across all database files. This logic determines unique disk drives based on the drive letter of the physical file name of each file. If the underlying disks are actually unique when the drive letters or not, the DBCC CHECKDB command would treat these as one disk. When this trace flag is enabled, each database file is assumed to be on a unique disk drive. Do not use this trace flag unless you know that each file is based on a unique physical disk.
Trace flag 2562
This trace flag includes the following changes:
Run the DBCC CHECKDB command in a single "batch" regardless of the number of indexes in the database. By default, the DBCC CHECKDB command tries to minimize tempdb resources by limiting the number of indexes or "facts" that it generates by using a "batches" concept. This trace flag forces all processing into one batch.
Improve the internal processing for determining which pages to read from the database. This reduces the contention on the DBCC_MULTIOBJECT_SCANNER latch.
One effect of using this trace flag is that the space requirements for tempdb may increase. Tempdb may grow to as much as 5% or more of the user database that is being processed by the DBCC CHECKDB command. Therefore, we recommend that you pre-size tempdb to at least 5% of your database size when you use this trace flag to avoid automatic growth, which may slow down the performance of the DBCC CHECKDB command.
DBCC TRACEON(2562,2549,-1)
----Userdatabases
DECLARE @Database varchar(100)
DECLARE DatabaseList Cursor FOR
select name from sys.databases where database_id not in (1,2,3,4)
OPEN DatabaseList
FETCH NEXT FROM DatabaseList into @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
DBCC CHECKDB (@Database);
END TRY
BEGIN CATCH
select ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM DatabaseList into @Database
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
DBCC TRACEOFF(2562,2549,-1)
After this try to check all your system databases.
DBCC TRACEON(2562,2549,-1)
----SystemDatabases
DECLARE @Database varchar(100)
DECLARE DatabaseList Cursor FOR
select name from sys.databases where database_id in (1,3,4)
OPEN DatabaseList
FETCH NEXT FROM DatabaseList into @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
DBCC CHECKDB (@Database);
END TRY
BEGIN CATCH
select ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM DatabaseList into @Database
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
DBCC TRACEOFF(2562,2549,-1)
I think this statement will pass.
March 25, 2015 at 7:46 am
QuickToLearn (3/25/2015)
@Gail Shaw;We run the job every week and it's been 5 weeks now we are getting this error. We did rerun of the job other than job schedule but failed with same error.
Please advise.
Try restarting the SQL service. CheckDB shouldn't be taking locks unless it can't create the database snapshot. Sometimes it can't create the snapshot because a previous snapshot wasn't dropped properly.
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
March 25, 2015 at 7:49 am
stijn.wynants (3/25/2015)
After this try to check all your system databases. (since the lock is on one of you system tables).
System table != system database.
The table the error is on exists in every database, system and user database.
The traceflags you mention don't affect locking behaviour. One should only be used if a database has multiple data files on different physical disks, the other can significantly increase TempDB usage and requires that TempDB be properly sized before running 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
March 25, 2015 at 8:04 am
You are totally right!
Edited previous answer, but you might just ignore the answer 🙂
This will not solve your problem
March 25, 2015 at 8:33 am
Thanks for the answers. I will try service restart.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply