April 16, 2009 at 5:17 am
Hi All,
Is it possible to write a database cursor which would run for say the first 5 databases in the list, stop at that point, and then pick up where it left off and do the next 5 when it was next run?
I'll explain what I'm trying to do here...
I am setting up an automated job to run DBCC CHECKDB against all user databases. We previously used to run this for all databases in one night, once a week with a maintenance plan, but since some of the SQL instances have 80+ databases mounted, this simply takes too long.
The solution to this would be to have a system which would run on a nightly basis but only do a handful of databases each time and run in a loop over the course of a few days.
I have a working script so far which will run for all databases (i've left out the @sql since it's pretty lengthy)
Can anyone point me in the right direction to get this to run x db's at a time?
declare dbcursor cursor for
select name from master.dbo.sysdatabases where name like 'xxx%' or name in ('yyy', 'zzz')
open dbcursor
declare @dbname varchar(50)
declare @sql nvarchar(4000)
fetch next from dbcursor into @dbname
while @@fetch_status = 0
begin
set @sql = 'script'
--print @sql
exec sp_executesql @sql
fetch next from dbcursor into @dbname
end
close dbcursor
deallocate dbcursor
April 16, 2009 at 7:05 am
You can load all the databases in a table and have an dbcc'd flag or the date time dbcc completed. You can then have you cursor load the databases using the date time stamp (round robin way) or simply pick up based on the flag.
What you would also need to do is have another step in this process or another job which would update the table (in case you add or drop databases) which holds all the information or even a DDL trigger if you're on 2005.
April 16, 2009 at 10:27 am
I had a go at the timestamp method - works a treat.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply