dbcc checkdb/ reindex questions

I want to start running dbcc checkdb every night. But I want to only be
emailed when the is a problem in the db. Is there a way for this to happen?
Also, is there a way to run dbcc dbreindex with it making my tlog grow to an
enormous size?
--
SQL2K SP3
TIA, ChrisR1. Create and Agent Job, in the job specify alert operator on fail. Or, if you don't want to mess
with MAPI and Outlook, see http://www.karaszi.com/SQLServer/info_no_mapi.asp
2. Check out Bulk Logged or Simple recovery mode. Just be aware of the effect those will have on
your backup routines. In Full recovery mode, everything is logged. An alternative is DBCC
INDEXDEFRAG, which *might* produce less log records, but it will not run in minimally logged mode
regardless of recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"ChrisR" <bla@.noemail.com> wrote in message news:%23FV8uwP7EHA.2196@.TK2MSFTNGP14.phx.gbl...
>I want to start running dbcc checkdb every night. But I want to only be
> emailed when the is a problem in the db. Is there a way for this to happen?
> Also, is there a way to run dbcc dbreindex with it making my tlog grow to an
> enormous size?
> --
> SQL2K SP3
> TIA, ChrisR
>|||> 1. Create and Agent Job, in the job specify alert operator on fail. Or, if
you don't want to mess
> with MAPI and Outlook, see
http://www.karaszi.com/SQLServer/info_no_mapi.asp
>
I thought about that, but wont that just email me when the job doesnt
succeed? Noth when theres an actual db problem?
> 2. Check out Bulk Logged or Simple recovery mode. Just be aware of the
effect those will have on
> your backup routines. In Full recovery mode, everything is logged. An
alternative is DBCC
> INDEXDEFRAG, which *might* produce less log records, but it will not run
in minimally logged mode
> regardless of recovery mode.
>
I thought about that too. Im in Full mode now and switching isnt an option.
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ObfFQdQ7EHA.2124@.TK2MSFTNGP15.phx.gbl...
> 1. Create and Agent Job, in the job specify alert operator on fail. Or, if
you don't want to mess
> with MAPI and Outlook, see
http://www.karaszi.com/SQLServer/info_no_mapi.asp
> 2. Check out Bulk Logged or Simple recovery mode. Just be aware of the
effect those will have on
> your backup routines. In Full recovery mode, everything is logged. An
alternative is DBCC
> INDEXDEFRAG, which *might* produce less log records, but it will not run
in minimally logged mode
> regardless of recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "ChrisR" <bla@.noemail.com> wrote in message
news:%23FV8uwP7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> >I want to start running dbcc checkdb every night. But I want to only be
> > emailed when the is a problem in the db. Is there a way for this to
happen?
> > Also, is there a way to run dbcc dbreindex with it making my tlog grow
to an
> > enormous size?
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >
>|||Hi Chris,
For Question 1, I just wrote small script for you, Let me know if this works
for you...
DECLARE @.dbname VARCHAR(100)
DECLARE @.sql_cmd_line VARCHAR(255)
DECLARE @.sql_error_nbr INT
SET @.dbname = 'your db name'
SELECT @.sql_cmd_line = 'dbcc checkdb(''' + @.dbname + ''', NOINDEX)' + 'WITH
NO_INFOMSGS' -- You can remove NOINDEX if you want
PRINT @.sql_cmd_line
EXEC (@.sql_cmd_line)
SELECT @.sql_error_nbr = @.@.error
IF @.sql_error_nbr <> 0
EXEC master..xp_sendmail @.recipients = 'your email address',
@.subject = 'DBCC checkdb error result',
@.message = 'DBCC checkdb has resulted an error. Check the database
consistency'
ELSE
PRINT 'No error reported by DBCC checkdb'
Thanks
GYK|||> I thought about that, but wont that just email me when the job doesnt
> succeed? Noth when theres an actual db problem?
For TSQL jobsteps, you get failure if any of the SQL command you execute returns at least one error
message with severity level > 10. And CHECKDB returns error messages when SQL Server finds some
corruption problem!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"ChrisR" <bla@.noemail.com> wrote in message news:uzxKeFR7EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> 1. Create and Agent Job, in the job specify alert operator on fail. Or, if
> you don't want to mess
>> with MAPI and Outlook, see
> http://www.karaszi.com/SQLServer/info_no_mapi.asp
> I thought about that, but wont that just email me when the job doesnt
> succeed? Noth when theres an actual db problem?
>> 2. Check out Bulk Logged or Simple recovery mode. Just be aware of the
> effect those will have on
>> your backup routines. In Full recovery mode, everything is logged. An
> alternative is DBCC
>> INDEXDEFRAG, which *might* produce less log records, but it will not run
> in minimally logged mode
>> regardless of recovery mode.
> I thought about that too. Im in Full mode now and switching isnt an option.
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ObfFQdQ7EHA.2124@.TK2MSFTNGP15.phx.gbl...
>> 1. Create and Agent Job, in the job specify alert operator on fail. Or, if
> you don't want to mess
>> with MAPI and Outlook, see
> http://www.karaszi.com/SQLServer/info_no_mapi.asp
>> 2. Check out Bulk Logged or Simple recovery mode. Just be aware of the
> effect those will have on
>> your backup routines. In Full recovery mode, everything is logged. An
> alternative is DBCC
>> INDEXDEFRAG, which *might* produce less log records, but it will not run
> in minimally logged mode
>> regardless of recovery mode.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "ChrisR" <bla@.noemail.com> wrote in message
> news:%23FV8uwP7EHA.2196@.TK2MSFTNGP14.phx.gbl...
>> >I want to start running dbcc checkdb every night. But I want to only be
>> > emailed when the is a problem in the db. Is there a way for this to
> happen?
>> > Also, is there a way to run dbcc dbreindex with it making my tlog grow
> to an
>> > enormous size?
>> >
>> > --
>> > SQL2K SP3
>> >
>> > TIA, ChrisR
>> >
>> >
>>
>|||Hi,
I like a little more control when reindexing my tables and wrote a script that runs every Monday morning:
-- generic defrag all tables in the db
DECLARE @.tbl varchar(55),@.pk varchar(55),@.idx varchar(55)
DECLARE @.sql nvarchar(255)
DECLARE c CURSOR FOR
select distinct so.name,o.name,c.name
from dbo.sysindexes c
join dbo.syscolumns o on o.id = c.id
join dbo.sysobjects so on so.id = c.id
where o.xtype = 56 and o.status = 128
and o.name <> 'id'
and substring(so.name,1,3) = 'tbl'
and (substring(c.name,1,2) = 'IX' or substring(c.name,1,2) = 'pk')
order by so.name
OPEN c
FETCH NEXT FROM c INTO @.tbl,@.pk,@.idx
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DBCC INDEXDEFRAG ([DATABASE],' + @.tbl + ',' + @.idx + ')'
EXECUTE sp_executesql @.sql
PRINT 'Results of defrag on table' + @.tbl + ' was ' + @.@.error
FETCH NEXT FROM c INTO @.tbl,@.pk,@.idx
END
CLOSE c
DEALLOCATE c
We use the prefix tbl for table and a pk as a primary key. This is just another way of defrag, but I perfer to read the log file each Monday morning rather than an email for any possible errors. Don't forget to replace the DATABASE name with your own.
Jon
--
Message posted via http://www.sqlmonster.com|||Also, IMO must read regarding defragmenting indexes in SQL Server:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Campbell via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ca85b0fb23ea44529061f1f9f1ba000c@.SQLMonster.com...
> Hi,
> I like a little more control when reindexing my tables and wrote a script that runs every Monday
> morning:
> -- generic defrag all tables in the db
> DECLARE @.tbl varchar(55),@.pk varchar(55),@.idx varchar(55)
> DECLARE @.sql nvarchar(255)
> DECLARE c CURSOR FOR
> select distinct so.name,o.name,c.name
> from dbo.sysindexes c
> join dbo.syscolumns o on o.id = c.id
> join dbo.sysobjects so on so.id = c.id
> where o.xtype = 56 and o.status = 128
> and o.name <> 'id'
> and substring(so.name,1,3) = 'tbl'
> and (substring(c.name,1,2) = 'IX' or substring(c.name,1,2) = 'pk')
> order by so.name
> OPEN c
> FETCH NEXT FROM c INTO @.tbl,@.pk,@.idx
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DBCC INDEXDEFRAG ([DATABASE],' + @.tbl + ',' + @.idx + ')'
> EXECUTE sp_executesql @.sql
> PRINT 'Results of defrag on table' + @.tbl + ' was ' + @.@.error
> FETCH NEXT FROM c INTO @.tbl,@.pk,@.idx
> END
> CLOSE c
> DEALLOCATE c
> We use the prefix tbl for table and a pk as a primary key. This is just another way of defrag,
> but I perfer to read the log file each Monday morning rather than an email for any possible
> errors. Don't forget to replace the DATABASE name with your own.
> Jon
> --
> Message posted via http://www.sqlmonster.com|||While on the topic, in Books Online, under DBCC SHOWCONTIG, there's a script that will only defrag
if the indexes has a fragmentation level over a user-defined percentage level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Campbell via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ca85b0fb23ea44529061f1f9f1ba000c@.SQLMonster.com...
> Hi,
> I like a little more control when reindexing my tables and wrote a script that runs every Monday
> morning:
> -- generic defrag all tables in the db
> DECLARE @.tbl varchar(55),@.pk varchar(55),@.idx varchar(55)
> DECLARE @.sql nvarchar(255)
> DECLARE c CURSOR FOR
> select distinct so.name,o.name,c.name
> from dbo.sysindexes c
> join dbo.syscolumns o on o.id = c.id
> join dbo.sysobjects so on so.id = c.id
> where o.xtype = 56 and o.status = 128
> and o.name <> 'id'
> and substring(so.name,1,3) = 'tbl'
> and (substring(c.name,1,2) = 'IX' or substring(c.name,1,2) = 'pk')
> order by so.name
> OPEN c
> FETCH NEXT FROM c INTO @.tbl,@.pk,@.idx
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DBCC INDEXDEFRAG ([DATABASE],' + @.tbl + ',' + @.idx + ')'
> EXECUTE sp_executesql @.sql
> PRINT 'Results of defrag on table' + @.tbl + ' was ' + @.@.error
> FETCH NEXT FROM c INTO @.tbl,@.pk,@.idx
> END
> CLOSE c
> DEALLOCATE c
> We use the prefix tbl for table and a pk as a primary key. This is just another way of defrag,
> but I perfer to read the log file each Monday morning rather than an email for any possible
> errors. Don't forget to replace the DATABASE name with your own.
> Jon
> --
> Message posted via http://www.sqlmonster.com|||Got sidetracked for a couple of days. Thanks to all.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uuXO1TS7EHA.2516@.TK2MSFTNGP09.phx.gbl...
> > I thought about that, but wont that just email me when the job doesnt
> > succeed? Noth when theres an actual db problem?
> For TSQL jobsteps, you get failure if any of the SQL command you execute
returns at least one error
> message with severity level > 10. And CHECKDB returns error messages when
SQL Server finds some
> corruption problem!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "ChrisR" <bla@.noemail.com> wrote in message
news:uzxKeFR7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> 1. Create and Agent Job, in the job specify alert operator on fail. Or,
if
> > you don't want to mess
> >> with MAPI and Outlook, see
> > http://www.karaszi.com/SQLServer/info_no_mapi.asp
> >>
> >
> > I thought about that, but wont that just email me when the job doesnt
> > succeed? Noth when theres an actual db problem?
> >
> >> 2. Check out Bulk Logged or Simple recovery mode. Just be aware of the
> > effect those will have on
> >> your backup routines. In Full recovery mode, everything is logged. An
> > alternative is DBCC
> >> INDEXDEFRAG, which *might* produce less log records, but it will not
run
> > in minimally logged mode
> >> regardless of recovery mode.
> >>
> >
> > I thought about that too. Im in Full mode now and switching isnt an
option.
> > Thanks.
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:ObfFQdQ7EHA.2124@.TK2MSFTNGP15.phx.gbl...
> >> 1. Create and Agent Job, in the job specify alert operator on fail. Or,
if
> > you don't want to mess
> >> with MAPI and Outlook, see
> > http://www.karaszi.com/SQLServer/info_no_mapi.asp
> >>
> >> 2. Check out Bulk Logged or Simple recovery mode. Just be aware of the
> > effect those will have on
> >> your backup routines. In Full recovery mode, everything is logged. An
> > alternative is DBCC
> >> INDEXDEFRAG, which *might* produce less log records, but it will not
run
> > in minimally logged mode
> >> regardless of recovery mode.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "ChrisR" <bla@.noemail.com> wrote in message
> > news:%23FV8uwP7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> >> >I want to start running dbcc checkdb every night. But I want to only
be
> >> > emailed when the is a problem in the db. Is there a way for this to
> > happen?
> >> > Also, is there a way to run dbcc dbreindex with it making my tlog
grow
> > to an
> >> > enormous size?
> >> >
> >> > --
> >> > SQL2K SP3
> >> >
> >> > TIA, ChrisR
> >> >
> >> >
> >>
> >>
> >
> >
>