Automating DBCC Checkdb

Sql Server 2000
If I ran a weekend job to perform a DBCC Checkdb on my databases, is there a
n alert that I can monitor for which will inform me of any problems discover
ed? Or is there a method to get the output of the DBCC Checkdb to a text fi
le? When running this command as a scheduled job, I don't see the output I
am in the habit of perusing for errors.
Please advise. Or tell me how you approach executing database maintenance.
Thanx!Here's what I'd do:
Define two jobsteps, "at the end" of the job. One has subject "success", the
other "error". In these, you use xp_sendmail or xp_smtp_sendmail (I
recommend this: www.sqldev.net) In the "real" jobsteps, define "next step"
on success and on failure you go to the one where you send the email with
"failure".
In the DBCC jobstep, you define an output file, and in the mail jobsteps,
you specify that file as an attachment.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OiAUHxC5DHA.2776@.TK2MSFTNGP09.phx.gbl...
Sql Server 2000
If I ran a weekend job to perform a DBCC Checkdb on my databases, is there
an alert that I can monitor for which will inform me of any problems
discovered? Or is there a method to get the output of the DBCC Checkdb to a
text file? When running this command as a scheduled job, I don't see the
output I am in the habit of perusing for errors.
Please advise. Or tell me how you approach executing database maintenance.
Thanx!|||Use sqlmaint.exe utility as described in BOL. It can sends out put to a file
. The file could be in text and /or html format.
EXEC xp_sqlmaint '-S SQLSERVER -PlanName "DBMAINTPLAN 1" -CkDB -Rpt D:\Dbm
aint\DBCC.out -DelTxtRpt 2months -HtmlRpt D:\Dbmaint\DBCC_result.htm'
Richard
"JLS" <jlshoop@.hotmail.com> wrote in message news:OiAUHxC5DHA.2776@.TK2MSFTNG
P09.phx.gbl...
Sql Server 2000
If I ran a weekend job to perform a DBCC Checkdb on my databases, is there a
n alert that I can monitor for which will inform me of any problems discover
ed? Or is there a method to get the output of the DBCC Checkdb to a text fi
le? When running this command as a scheduled job, I don't see the output I
am in the habit of perusing for errors.
Please advise. Or tell me how you approach executing database maintenance.
Thanx!|||Thanx! Great advise, I like being pointed in the right direction instead of
spinning my wheels all over BOL.
"Richard Ding" <dingr@.cleanharbors.com> wrote in message news:e8DcWfE5DHA.24
32@.TK2MSFTNGP10.phx.gbl...
Use sqlmaint.exe utility as described in BOL. It can sends out put to a file
. The file could be in text and /or html format.
EXEC xp_sqlmaint '-S SQLSERVER -PlanName "DBMAINTPLAN 1" -CkDB -Rpt D:\Dbm
aint\DBCC.out -DelTxtRpt 2months -HtmlRpt D:\Dbmaint\DBCC_result.htm'
Richard
"JLS" <jlshoop@.hotmail.com> wrote in message news:OiAUHxC5DHA.2776@.TK2MSFTNG
P09.phx.gbl...
Sql Server 2000
If I ran a weekend job to perform a DBCC Checkdb on my databases, is there a
n alert that I can monitor for which will inform me of any problems discover
ed? Or is there a method to get the output of the DBCC Checkdb to a text fi
le? When running this command as a scheduled job, I don't see the output I
am in the habit of perusing for errors.
Please advise. Or tell me how you approach executing database maintenance.
Thanx!|||Thanx! Exactly the information I was looking for!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OCrGg$C5DHA.2344@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Here's what I'd do:
> Define two jobsteps, "at the end" of the job. One has subject "success",

---

the

quote:

---

> other "error". In these, you use xp_sendmail or xp_smtp_sendmail (I
> recommend this: www.sqldev.net) In the "real" jobsteps, define "next step"
> on success and on failure you go to the one where you send the email with
> "failure".
> In the DBCC jobstep, you define an output file, and in the mail jobsteps,
> you specify that file as an attachment.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "JLS" <jlshoop@.hotmail.com> wrote in message
> news:OiAUHxC5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> Sql Server 2000
> If I ran a weekend job to perform a DBCC Checkdb on my databases, is there
> an alert that I can monitor for which will inform me of any problems
> discovered? Or is there a method to get the output of the DBCC Checkdb to

---

a

quote:

---

> text file? When running this command as a scheduled job, I don't see the
> output I am in the habit of perusing for errors.
> Please advise. Or tell me how you approach executing database

---

maintenance.

quote:

---

> Thanx!
>
>

---

autogrow history

Hi,
Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.
Thanks,
SusanThis kind of error should be logged on SQL Server Logs.

Originally posted by dbadba
Hi,

Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.

Thanks,
Susan|||Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Laura,

As far as I know, there is no way to create an alert based on autogrow event, unless you fix a log size limit and then add an alert using "error 9002, severity 19 - The log file for database xxxx is full". I think there is even a template in SQL 2000 Alerts for that.
Another approach would be to define a trace based on transaction log autogrow event. You can use SQL Profiler for tracing; or it can generates the SQL script for you, and then you can include it in a stored procedure that can be scheduled to start at a specific time.

Hope it helps !

Originally posted by dbadba
Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Thanks! I will check into both of the options you suggested.|||if you want to prevent this kind of error from happening you can also create an alert that is triggered when a log is more than a certain percentage full.|||Thank you very much for the suggestion! I appreciate it.|||UNtested but this could be run regularly to email you when the log is over 50% full

create procedure mylog @.dbname varchar(50)
as
declare @.logspace int
, @.wo_num varchar(30) , @.deleted_user varchar(255),@.emails varchar(255) , @.messages varchar(100),
@.subjecttext varchar(100)
truncate table tempdb..logspace
insert tempdb..logspace (dbname,logsize,percentused,status)
execute('dbcc perflog')
set @.logspace = (select percentused from tempdb..logspace where dbname = @.dbname)
print @.logspace
if @.logspace > 50
set @.messages = 'Log Space is over 50%'
set @.subjecttext = @.dbname + 'log is over 50% full'
set @.emails = 'joesmoe@.kokomo.com'
exec master..xp_sendmail @.recipients = @.emails , @.message = @.messages,@.subject = @.subjecttext
else
return

HTH