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 y
ou 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 effec
t those will have on
your backup routines. In Full recovery mode, everything is logged. An altern
ative 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...[
vbcol=seagreen]
>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
>[/vbcol]|||> 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...
happen?[vbcol=seagreen]
to an[vbcol=seagreen]
>|||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 ret
urns at least one error
message with severity level > 10. And CHECKDB returns error messages when SQ
L 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...[vb
col=seagreen]
> you don't want to mess
> 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?
>
> effect those will have on
> alternative is DBCC
> in minimally logged 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 i
n
> message news:ObfFQdQ7EHA.2124@.TK2MSFTNGP15.phx.gbl...
> you don't want to mess
> http://www.karaszi.com/SQLServer/info_no_mapi.asp
> effect those will have on
> alternative is DBCC
> in minimally logged mode
> news:%23FV8uwP7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> happen?
> to an
>[/vbcol]|||Hi,
I like a little more control when reindexing my tables and wrote a script th
at 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 ano
ther way of defrag, but I perfer to read the log file each Monday morning ra
ther than an email for any possible errors. Don't forget to replace the DAT
ABASE name with your own.
Jon
Message posted via http://www.droptable.com|||Also, IMO must read regarding defragmenting indexes in SQL Server:
http://www.microsoft.com/technet/pr...n/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 droptable.com" <forum@.droptable.com> wrote in message
news:ca85b0fb23ea44529061f1f9f1ba000c@.SQ
droptable.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 a
nother 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.droptable.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 leve
l.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Campbell via droptable.com" <forum@.droptable.com> wrote in message
news:ca85b0fb23ea44529061f1f9f1ba000c@.SQ
droptable.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 a
nother 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.droptable.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...
> 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...
if[vbcol=seagreen]
run[vbcol=seagreen]
option.[vbcol=seagreen]
in[vbcol=seagreen]
if[vbcol=seagreen]
run[vbcol=seagreen]
be[vbcol=seagreen]
grow[vbcol=seagreen]
>