SQL Server Performance Forum – Threads Archive
DBCC CHECKDB/Maintenance Plans
I have a Maintenance Plan that performs an Integrity Check (and attempts to fix problems). This is on a development machine (1 CPU, 512KB RAM). I have the plan run once a week on Sunday night @ 12:00am. As of yesterday, I noticed that when that job is running the database is being locked (i.e. I can’t even login to Query Analyzer). My understanding is that in SQL 2000 DBCC CHECKDB doesn’t perform any locking operation. What else could be causing this lock? Joshua Roth[email protected]
Do you have Optimizations turned on in your maintenance plan ? This will reorganize data and index pages of your tables and will normally hog all system resources. Also, it locks the tables that are currently being organized.
Yes, I have Optimizations turned on, but I have it as a separate job. I can reproduce the problem by just running the Integrity check job. Joshua Roth
[email protected]
From BOL:
DBCC CHECKDB does not acquire table locks by default. Instead, it acquires schema locks that prevent meta data changes but allow changes to the data. The schema locks acquired will prevent the user from getting an exclusive table lock required to build a clustered index, drop any index, or truncate the table. So, it does not hold any locks on the tables that prevent you from accessing the table. How is the resource utilization on the system (CPU, Memory etc.) May be the tables that you are checking are too big for a 1 CPU 512KB RAM machine.
I actually checked and it’s 1GB of memory, not 512KB. I read the BOL stuff and that’s where I saw it shouldn’t be locking me out. It’s literally locking me from accessing the database at all. I can’t even login to it. Is there anything it does that puts it into Single User Mode? Joshua Roth
[email protected]
Yes, If you have ‘Attemp to repair minor problems’ checked on, it will put the database into Single user mode.
If the database is accessed by 24/7 applications then better to untick this option as this causes bit of overhead and database inaccessible during the operation. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>