October 3, 2011 at 7:09 am
How can I have DBCC CHECKDB continue after the error on specific database? I have instances hosting with multple databases and would like to generate something to run DBCC CHECKDB job to run and continue to complete DBCC CHECKDB for all dbs even any of them gives errors? Anything that failed DBCC CHECKDB need DB name in output at the end to check manually?
Is this possible? I have below script but doesn't seems to work.
SET NOCOUNT ON
DECLARE @dbid INT
DECLARE @DBName SYSNAME
DECLARE @sql NVARCHAR(4000)
SET @dbid = 0
WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN
SELECT TOP 1 @dbid = dbid, @DBName = name
FROM master.dbo.sysdatabases
WHERE dbid > @dbid
ORDER BY dbid
SET @sql = 'DBCC CHECKDB([' + @DBName + '])'
EXEC sp_executesql @statement = @sql
IF @@ERROR <> 0
SELECT @DBName
END
GO
October 3, 2011 at 7:17 am
What error are you getting atm?
Just my 0.02$, I always preffer to save the output of checkdb.
I have a perm table in which I run
insert into dbo.table (columns)
EXEC ('')
I run it WITH TABLE_RESULTS, NO_INFOMSGS, ALL_ERRORMSGS
After the insert is done I run an update statement to set the dbname() of the output (if any).
Doing it that could allow you to loop.
October 3, 2011 at 7:25 am
Thanks for quick reply. I created one suspect database and DBCC CHECKDB failed for suspect database and loop stops there. There are more databases in loop that I like to run DBCC CHECKDB and just look at corrupted at the end manually but in my case loop stops as soon as find the corrupted one.
October 3, 2011 at 7:28 am
Ok then try my trick with exec & tableresults see if that works for you.
October 3, 2011 at 10:01 am
Did not work. Once script hit suspect database failed with error and doesn't check dbcc on rest of the database.
SET NOCOUNT ON
DECLARE @dbid INT
DECLARE @DBName SYSNAME
DECLARE @sql NVARCHAR(4000)
CREATE TABLE ##temp_tbl
(
Error INT,
Level INT,
State INT,
MessageText VARCHAR(7000),
RepairLevel INT,
Status INT,
DbId INT,
ObjectId int,
IndexId int,
PartitionId int,
AllocUnitId int,
[File] INT,
Page INT,
Slot INT,
RefFile INT,
RefPage INT,
RefSlot INT,
Allocation INT
)
SET @dbid = 0
WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN
SELECT TOP 1 @dbid = dbid, @DBName = name
FROM master.dbo.sysdatabases
WHERE dbid > @dbid
ORDER BY dbid
SET @sql = 'DBCC CHECKDB([' + @DBName + ']) WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS'
insert into ##temp_tbl (Error,
Level,
State,
MessageText,
RepairLevel,
Status,
DbId,
ObjectId,
IndexId,
PartitionId,
AllocUnitId,
[File],
Page,
Slot,
RefFile,
RefPage,
RefSlot,
Allocation)
exec(@SQL)
-- EXEC sp_executesql @statement = @sql
END
select * from ##temp_tbl
drop table ##temp_tbl
--RETURN 0
GO
October 3, 2011 at 10:04 am
What's the error returned by checkdb?
October 3, 2011 at 10:16 am
This is what I am expecting as I created suspect database for my testing but my above script stop here even though I have one more database to change DBCC.
Error as below.
Msg 945, Level 14, State 2, Line 1
Database 'DemoSuspect' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
October 3, 2011 at 10:29 am
Never seen this... & glad to learn about it.
What happens if you simply run the dynamic sql by itself. Do you at least get an table output of some sort?
I'm thinking you are hitting this because the error is too catastrophic / early in the process to return anything else.
Maybe I'd try a begin try to see if that allows you to keep on going but I wouldn't hold my breath.
October 3, 2011 at 11:05 am
Yes, it populate the table if everything is fine but table is empty if failed with an error for one of the database.
October 3, 2011 at 11:48 am
Amu (10/3/2011)
Yes, it populate the table if everything is fine but table is empty if failed with an error for one of the database.
So it's completely useless.
Have you tried with another db corrupt with something much less severe (like index linkage or something real easy to repair).
October 4, 2011 at 7:08 am
Yes, I am testing with different type of corruption to see how its behave.
I have change the code to use TRY...CATCH and it seems to be working.
SET NOCOUNT ON
DECLARE @dbid INT
DECLARE @DBName SYSNAME
DECLARE @sql NVARCHAR(4000)
CREATE TABLE ##failed_db (dbname SYSNAME)
SET @dbid = 0
WHILE (1=1)
BEGIN
BEGIN TRY
SELECT TOP 1 @dbid = dbid, @DBName = name
FROM master.dbo.sysdatabases
WHERE dbid > @dbid
AND DATABASEPROPERTYEX(name,'status')<>'OFFLINE'
ORDER BY dbid
IF @@ROWCOUNT <> 1
BREAK
SET @sql = 'DBCC CHECKDB([' + @DBName + ']) WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS'
EXEC(@SQL)
SELECT 'Successfully completed: ' + @DBName
END TRY
BEGIN CATCH
BEGIN
SELECT 'Failed: ' + @DBName
INSERT INTO ##failed_db
SELECT @DBname
END
END CATCH
END
IF EXISTS (SELECT TOP 1 * FROM ##failed_db)
--RAISERROR
DROP TABLE ##failed_db
GO
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply