December 4, 2011 at 9:42 am
Hi,
I'm using the below procedure IntegrityCheck to check the database Integrity. I'm running the procedure IntegrityCheck from sqlagent.
Issue: The scheduled sqlagent job only checks the database integrity for master, model, msdb and Admin (maintenance db)databases but NOT all databases.
But if I run the same sqlagent job manually, then its performing the Integrity check for all databases.
Please advice..
USE [Admin]
GO
/****** Object: StoredProcedure [dbo].[IntegrityCheck] Script Date: 12/04/2011 08:32:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[IntegrityCheck]
as
begin
declare database_cursor CURSOR for select name from sys.sysdatabases where name not in ('tempdb')
declare @database_name sysname
open database_cursor
fetch next from database_cursor into @database_name
while @@FETCH_STATUS=0
begin
print @database_name
dbcc checkdb(@database_name) with no_infomsgs
fetch next from database_cursor into @database_name
end
close database_cursor
deallocate database_cursor
end
GO
December 5, 2011 at 9:50 am
The above script is running fine in sql 2005. But in sql server 2008 R2, some times, it's performing integrity check for all databases and some times performing index defrag for only master, msdb, model & admin databases.
Please advice
December 6, 2011 at 5:42 am
I would try changing the select to:
select name from master.sys.databases where name not in ('tempdb')
---------------------------------------------------------------------
December 15, 2011 at 11:56 am
I would try changing the select to:
select name from master.sys.databases where name not in ('tempdb')
I changed the script to include master.sys.databases. But still, sometimes performs the Index defrag for all databases and sometimes it's NOT performing for couple of databases and sometimes skipps only one database..
I'm not able to get why the same script behaving differently each time? any clue will be appreciated.
December 18, 2011 at 11:14 pm
Mani-584606 (12/15/2011)
...But still, sometimes performs the Index defrag for all databases and sometimes it's NOT performing for couple of databases and sometimes skipps only one database..
The script you posted is related only to database integrity check. It has nothing to do with index defrag.
BTW, how to you know that some databases are skipped? Any log entries...?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply