Check this correct - my DBCC script

Question

Check this correct - my DBCC script

learning_sql

Right there with Babe

Points: 727
More actions
November 23, 2012 at 4:48 am

#265150

Hi,
I intend to run this against all our SQL Servers to check every database (not done nearly enough)...to save me time and effort I have completed the following script (thanks to some posts here and google)...is this the best way of doing it (there is a lot of database across multiple servers and some are large)
DECLARE @dbname varchar(50) --database name
DECLARE check_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
OPEN check_cursor
FETCH NEXT FROM check_cursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC CHECKDB (@dbname) WITH NO_INFOMSGS, ALL_ERRORMSGS
PRINT N'Checking the database = ' + @dbname + ' any error messages will be listed above'
FETCH NEXT FROM check_cursor INTO @dbname
END
CLOSE check_cursor
DEALLOCATE check_cursor
EDIT - I have just noticed it stops if a db is offline ...need to make an adjustment to ensure it runs against all databases before stopping

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thinknook SSCrazy Points: 2531 More actions · Answer 1

...is this the best way of doing it (there is a lot of database across multiple servers and some are large)

I like to use Ola Hallengren script for DB maintenance (including integrity checks). Its been tested and used widely across the community.

What I usually do is have an "InstanceMaintenance" database in each SQL instance I administer, this will have the above script as well as other scripts that help diagnose/report/track issues on that instance.

I know this doesn't specifically answer your question, its just a recommendation 🙂

/>L

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].

I believe in Codd
... and Thinknook is my Chamber of Understanding

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

sysdatabases is deprecated, included only for backward compatibility with SQL 2000 and should not be used in new development.

Replacement is sys.databases

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

learning_sql Right there with Babe Points: 727 More actions · Answer 3

GilaMonster (11/23/2012)
sysdatabases is deprecated, included only for backward compatibility with SQL 2000 and should not be used in new development.
Replacement is sys.databases

noted - I am running against some SQL2000 so I added a comment to the script to remove once we have removed those servers.

Question -> The script stops if a database is offline - how can I skip these databases? Is it safe to use "select name from sysdatabases where version != 0 AND version is not null

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 4

select *

from sysdatabases

where version != 0

AND version is not null

AND status <> 512

After a quick google on sysdatabases if found this helpful tidbit.

http://msdn.microsoft.com/en-us/library/aa260406%28v=sql.80%29.aspx

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

learning_sql Right there with Babe Points: 727 More actions · Answer 5

D.Post (11/23/2012)
select * from sysdatabases where version != 0 AND version is not null
AND status <> 512
After a quick google on sysdatabases if found this helpful tidbit.
http://msdn.microsoft.com/en-us/library/aa260406%28v=sql.80%29.aspx

I will have to calculate the bits for the databases to ignore (for example my offline database status = 4194816 autoshrink + offline) thanks for that - I will use the version at the minute as that does seem to be working....however I will write the status column in for future reference too...