home made corruption checker

I want to have my own DBCC tool. The problem I have with the Maintenance Plan
DBCC is I only want to be emailed when I have corruption. Not every day for
every DB. I wrote a proc, but what Im finding is that @.@.error still returns a
0 even if there are certain types of corruption, so my proc is flawed. Does
anyone have another idea how I can accomplish my goals?
alter procedure admin_DBCCCheckDB
as
declare @.DBName varchar(128)
declare @.Exec varchar(128)
declare @.RC int
declare @.Error int
declare @.Subject varchar(128)
declare @.Server varchar(128)
declare @.Recipient varchar(128)
set @.Server = @.@.ServerName
set @.DBName = (select DB_Name())
set @.Exec = 'dbcc checkdb(''' + @.DBName + ''')' + 'WITH NO_INFOMSGS'
set @.Subject = 'Corruption in ' + @.DBName + '.!!!'
set @.Recipient = ''
exec (@.exec)
select @.Error = @.@.error
if @.Error <> 0
exec @.RC = master.dbo.xp_smtp_sendmail
@.FROM = @.Server
,@.TO = @.Recipient
,@.Subject = @.Subject
,@.server = N'smtpmail.azdes.gov'
,@.Port = 25
--
TIA,
ChrisRChris,
DBCC CHECKDB is not a stored procedure and I don't think using the @.@.ERROR
global variable is what you're looking for. For this just create a new job
to execute the DBCC CHECKDB statement for a given database with the
instructions to email you on failure and schedule the job.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:CCA21D09-B8B2-40BB-BD1F-CB9C04732EAA@.microsoft.com...
>I want to have my own DBCC tool. The problem I have with the Maintenance
>Plan
> DBCC is I only want to be emailed when I have corruption. Not every day
> for
> every DB. I wrote a proc, but what Im finding is that @.@.error still
> returns a
> 0 even if there are certain types of corruption, so my proc is flawed.
> Does
> anyone have another idea how I can accomplish my goals?
> alter procedure admin_DBCCCheckDB
> as
> declare @.DBName varchar(128)
> declare @.Exec varchar(128)
> declare @.RC int
> declare @.Error int
> declare @.Subject varchar(128)
> declare @.Server varchar(128)
> declare @.Recipient varchar(128)
> set @.Server = @.@.ServerName
> set @.DBName = (select DB_Name())
> set @.Exec = 'dbcc checkdb(''' + @.DBName + ''')' + 'WITH NO_INFOMSGS'
> set @.Subject = 'Corruption in ' + @.DBName + '.!!!'
> set @.Recipient = ''
> exec (@.exec)
> select @.Error = @.@.error
> if @.Error <> 0
> exec @.RC = master.dbo.xp_smtp_sendmail
> @.FROM = @.Server
> ,@.TO = @.Recipient
> ,@.Subject = @.Subject
> ,@.server = N'smtpmail.azdes.gov'
> ,@.Port = 25
> --
> TIA,
> ChrisR|||No need to use dynamic SQL for this. CHECKDB accepts a variable, so you can catch the error after
and do additional steps if you need to. But it is easy enough to have Agent just act On Success/On
Error also. It is up to you, what makes more sense and easier for you. Here's the script I ran
against a bad database to test it:
DECLARE @.db sysname, @.err int
SET @.db = 'badpubs'
DBCC CHECKDB(@.db) WITH NO_INFOMSGS
SET @.err = @.@.ERROR
IF @.err <> 0
SELECT 'Ouch, we have a problem', @.err
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:CCA21D09-B8B2-40BB-BD1F-CB9C04732EAA@.microsoft.com...
>I want to have my own DBCC tool. The problem I have with the Maintenance Plan
> DBCC is I only want to be emailed when I have corruption. Not every day for
> every DB. I wrote a proc, but what Im finding is that @.@.error still returns a
> 0 even if there are certain types of corruption, so my proc is flawed. Does
> anyone have another idea how I can accomplish my goals?
> alter procedure admin_DBCCCheckDB
> as
> declare @.DBName varchar(128)
> declare @.Exec varchar(128)
> declare @.RC int
> declare @.Error int
> declare @.Subject varchar(128)
> declare @.Server varchar(128)
> declare @.Recipient varchar(128)
> set @.Server = @.@.ServerName
> set @.DBName = (select DB_Name())
> set @.Exec = 'dbcc checkdb(''' + @.DBName + ''')' + 'WITH NO_INFOMSGS'
> set @.Subject = 'Corruption in ' + @.DBName + '.!!!'
> set @.Recipient = ''
> exec (@.exec)
> select @.Error = @.@.error
> if @.Error <> 0
> exec @.RC = master.dbo.xp_smtp_sendmail
> @.FROM = @.Server
> ,@.TO = @.Recipient
> ,@.Subject = @.Subject
> ,@.server = N'smtpmail.azdes.gov'
> ,@.Port = 25
> --
> TIA,
> ChrisRsql

High Duration SQL 2005 Profiler

Hello everybody,

I have a very stranger problem that I need to understand...

In last days I executed a plan SQL 2005 Profiler to review TSQL Duration. When reviewing the results encounter that a SP displays a value of 4037312 in field DURATION which are not normal.

Could to help me to identify why passes this?

SQL Server 2005 has the ability to show duration in microseconds.

This can be changed in the options of Profiler.

Maybe this option is turned on?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||The Duration Field show in microseconds is turned off. The generate value in the profiler for duration field in miliseconds is very high and not real...|||

This comes out to approximately 67 minutes. This is not necessarily wrong. A couple of things I've experienced in regards to long durations:

The job being executed is being blocked by something else and is continuously waiting on something else to complete before it can finish execution. The calling application is leaving the recordset open after calling. For example, if you use MS Access to open a table and don't bother to close it then it will show an extremely long duration (until the table is closed). Other applications could do the same thing if the author didn't properly include the code to close the recordset after retrieving the data. The client that's retrieving the data is on a particularly slow connection. Again - Duration is not the time it takes SQL Server to fulfill the request but the time it takes for the client to retrieve the data from the request and close the resultset. We once learned about a misconfigured network connection because durations were high while CPU and READs were low.|||The profiler duration field is in microseconds by default?|||No, its optional for SQL Server 2005.

Jens K. Suessmeyer.

http://www.sqlserver2005.de