jump to navigation

Multithreaded DBCC Checkdb when MAXDOP = 1 March 4, 2010

Posted by Chirag in Database Maintenance Plans, SQL 2005, SQL 2008, SQL Server 2008, T-SQL.
Tags: , , , , ,
1 comment so far

I know I am posting after a long time, so have just decided to wipe of the dust off my blogging keyboard, bring out my rusty creative writing skills and start posting again.

So here goes again… with a post to request your support and vote on a connect item if you feel my pain.

We have a customer who has several servers with many VLDB’s hosted on each of them. We run a nightly maintenance plan for general housekeeping and this tends to take a very long time to finish. An average run of CHECKDB with the PHYSICAL_ONLY option on weekdays takes over 4 hours and full CHECKDB on Sunday’s can take up to 6-8 hours to finish. This causes the job to overshoot the nightly maintenance window and in turn translates to performance problems for query response times as it is a 24×7 shop.

Besides all the necessary checks that CHECKDB does to check the integrity of the database it can run single threaded or multithreaded if more  processors are available. For reasons of query response and runtime predictability requirements the client has MAXDOP = 1 configured at the SQL Server instance level. This causes CHECKDB to run single threaded and contributes to extending the overall runtime of CHECKDB.

If like me you feel that there should be a setting to allow us to force CHECKDB to run multi threaded even if MAXDOP = 1 then please vote on this connect item: https://connect.microsoft.com/SQLServer/feedback/details/538754/introduce-setting-to-force-dbcc-checkdb-to-run-multi-threaded-when-maxdop-1

I have requested this to be implemented as:

– Trace Flag to allow global setting

– Switch on the DBCC CHECKDB command

Many thanks in advance if you vote and hopefully Microsoft introduce this soon!


Clean up dropped Subscriptions September 14, 2008

Posted by Chirag in Replication, SQL 2005, SQL 2008, T-SQL.
Tags: , , , , , , ,
add a comment

If you are working in an environment utilising replication, you may find orphaned subscriptions show up on subscribers long after they have been dropped at the publisher. Well, why they aren’t cleared up when one drops a subscription or subsequently the publication itself.. you may ask… that’s a different question all together.

You can run a not so widely known command at the subscriber to clear out any orphaned subscriptions still listed on the subscribers. In fact, should I dare suggest it would actually be a great idea to implement this within your drop subscription process.

The command you can use is “sp_subscription_cleanup”. It has the following syntax:


[ @publisher = ] publisher ,

[ @publisher_db = ] publisher_db

[ , [ @publication = ] publication]

@publisher => is the name of the Publisher

@publisher_db => is the name of the Publisher database

@publication => is the name of the publication, with a default of NULL. If NULL, subscriptions using a shared agent publication in the publishing database will be deleted

Execute this at the Subscriber on the subscription database to remove replication meta data from it.

Service Broker queue not being processed September 14, 2008

Posted by Chirag in Service Broker, SQL 2005, SQL 2008, T-SQL.
Tags: , , ,
add a comment

We were troubleshooting a problem with Service broker, and realised the queue was being populated but wasn’t being processed.

Further investigation revealed that since our database had been migrated from SQL 2000 to SQL 2005 the “is_broker_enabled” was set to “off” and hence the queue wasn’t being processed.

To enable it we ran the following command


Now, this command when executed requires the session to have exclusive access to the database, so make sure you either close all other sessions active as well as inactive; or run the command with the termination options:

ROLLBACK AFTER integer [SECONDS] – Specifies whether to roll back after the specified number of seconds.

ROLLBACK IMMEDIATE – Specifies whether to roll back immediately.

NO_WAIT – Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.