January 29, 2007 at 9:36 am
I would like to run integrity check and repair job on a database which has a 24x7 open connection from an app. So DB cannot be put in a single user mode. What other options can I look at to accomplish the task at hand?
any idea?
January 29, 2007 at 10:30 am
Are you using DBCC CHECKDB? It doesn't require the database to be in single user mode because it used schema locks on tables rather than shared locks.
Greg
Greg
January 29, 2007 at 12:16 pm
I was using a maintenance plan to accomplish those tasks. DBCC CHECKDB doesnot release any free space. or does it?
January 29, 2007 at 2:52 pm
Maintenance plans execute DBCC commands including DBCC CHECKDB to check database integrity and DBCC SHRINKDATABASE to shrink the database files and free up space. As far as I know, they are all online operations that don't require single user access. See "Maintenance statements - DBCC" in BooksOnLine.
Did you get an error when the maintenace plan ran?
Greg
Greg
January 29, 2007 at 6:07 pm
If you check the "Attempt to repair any minor problems" option, sql server will try to change the db to single user mode to run the repairs...
It is not advisable to check this option... some times after repair sql can't able to bring the db to multi user mode...
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
MohammedU
Microsoft SQL Server MVP
January 30, 2007 at 12:47 pm
If you're using SQL 2000, I believe DBCC CHECKDB will create long-running transactions (which will cause blocking). However, if you're running SQL 2005, DBCC CHECKDB will actually create a database snapshot (in the background) which does not generate any locks in the source database while it's running.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply