Schedule DBCC CHECKDB in SQL Server

1. In Microsoft SQL Server Management Studio, right-click SQL Server Agent, then click New, then click Job

2. Input Name, “JOB_DBCC_CHECKDB”

3. Click Steps, then click New

4. Input Step Name, “DBCC CHECKDB”

5. Paste the following Command

DECLARE @pDbName varchar(1000)

DECLARE c CURSOR FOR SELECT name FROM [sys].[databases] WHERE name <> 'tempdb'

OPEN c

FETCH NEXT FROM c INTO @pDbName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('DBCC CHECKDB(' + @pDbName + ');')

    FETCH NEXT FROM c INTO @pDbName
END

CLOSE c

DEALLOCATE c

6. Click OK

7. Click Schedules, then click New

8. Input Name, “Weekly Sunday 00:00”

9. Setup the appropriate schedule

10. Click OK

11. Click OK again

Advertisements

2 thoughts on “Schedule DBCC CHECKDB in SQL Server

  1. It’d really help me out if you could share how exactly you get your code snippets to appear as they do in your posts. I’ve been using the tags but it doesn’t look quite as pretty as yours, would you mind sharing? :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s