October 17, 2016 at 2:52 pm
We are running weekly DBCC CheckDB job. The error is "The database could not be exclusively locked to perform the operation". If I will set database to single user mode, I am risking that some ETL and other processes that are running at weekend will fail.
They recommend to "See Books Online for details of when this behavior is expected and what workarounds exist". What workaround do you use in your environments?
Thanks
October 17, 2016 at 3:59 pm
CheckDB shouldn't need to lock the DB. It'll only try to do that if it can't create the usual database snapshot it uses.
Are there any other messages in the error log around that time?
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
October 17, 2016 at 4:05 pm
Here are all the messages:
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
October 17, 2016 at 4:17 pm
And about an error log, there are only 2 records around that time indicating that the database was set to single- than to multi-user. But these were my tests that this is the case (I tested it in lower environment).
I also want to add that this database is 1 TB in size (not counting log files). And the size of tempdb is only 100 GB. In production where this failure originally showed up the tempdb size is 180 GB. Maybe this is the reason? But what our options would be if we can't afford 1 TB for tempdb?
October 17, 2016 at 4:28 pm
SQL Guy 1 (10/17/2016)
Maybe this is the reason?
No.
When last did checkDB succeed?
Can you manually create a database snapshot on that database?
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
October 18, 2016 at 8:45 am
Checkdb is running only at the weekends. And our scheduling software (it's not SQL Server agent) keeps history only for last month. Within this month there was not a single successful execution, so I can't tell when it has last succeeded.
Yes, I can easily create a snapshot on that database on a test server (haven't tried on production), even if other sessions connected to it.
October 18, 2016 at 9:57 am
Fortunately SQL stores the last good DBCC date in the header data for the db.
Run this command:
DBCC DBINFO('<your_db_name>') WITH TABLERESULTS
And look for a "Field" value of "dbi_dbccLastKnownGood" (or similar): the corresponding "VALUE" is the datetime.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 18, 2016 at 11:15 am
Thanks Scott, this is good DBCC command, I never knew about it.
dbi_dbccLastKnownGood = 2016-07-10 02:07:23.070
October 18, 2016 at 11:41 am
SQL Guy 1 (10/18/2016)
Yes, I can easily create a snapshot on that database on a test server (haven't tried on production), even if other sessions connected to it.
That's not what I asked. (active sessions don't block snapshot creation)
Can you create a manual snapshot on the DB where checkDB is failing?
When was SQL last restarted?
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
October 18, 2016 at 11:58 am
Yes, I manually created it, with a script like this:
create database my_db_ss on
(name = my_db, filename = '.....ss'),
... 22 ss files ...
as snapshot of my_db
Checkdb fails in both prod and test, with the same error. But I created snapshot only in test, without any errors.
Prod was last restarted at 2016-09-03
Dev was last restarted at 2016-06-27
Also want to add that this db, besides primary and log, also has 22 ndf files.
October 18, 2016 at 12:07 pm
October 18, 2016 at 12:13 pm
Weird...
Can you try to run CheckDB on that snapshot that you manually created?
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
October 18, 2016 at 12:46 pm
Thanks Gail, now I can easily run checkdb against this snapshot (regardless whether other connections connected to it or not), and I expect it to run for hours.
And thanks Jacob, this k.b. is exactly my case. We have historical partitions which are represented by most of ndf files set to read-only, and we have concurrent connections accessing this database.
October 18, 2016 at 3:26 pm
SQL Guy 1 (10/18/2016)
Thanks Gail, now I can easily run checkdb against this snapshot (regardless whether other connections connected to it or not), and I expect it to run for hours.
Just make sure that you drop the snapshot afterwards, don't want it hanging around.
As a 'long-term workaround', you can have a job create a snapshot, run CheckDB and then drop the snapshot. Just make sure that you do that in a quiet time.
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
October 19, 2016 at 7:57 am
Yes, this is what I am doing now, thank you. And this job is scheduled to run only at weekend.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply