home made corruption checker

I want to have my own DBCC tool. The problem I have with the Maintenance Pla
n
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 Pl
an
> DBCC is I only want to be emailed when I have corruption. Not every day fo
r
> every DB. I wrote a proc, but what Im finding is that @.@.error still return
s a
> 0 even if there are certain types of corruption, so my proc is flawed. Doe
s
> 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

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,
ChrisR
Chris,
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,
> ChrisR

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

Higher level of fragmentation and data corruption.

CAN maintaining higher level of fragmentation result in data corruption over
time? OR they are totally unrelated.
I would like to know because rebuilding indexes take resources and that is
an issue for a 24 x 7 environment. In other words, should we keep data device
safely fragmented for sometime if we have some hard disk space available that
allows us to maintain some abnormal growth, allowing us not to rebuild
indexes?
Can someone with SQL Server Internal Data Storage Knowledge please reply?
Also, please let me know if my question is not very clear enough and you
need further explanation.
Regards,
MZeeshan
Fragmentation in and of itself should not cause data corruption. Most
corruption these days is due to hardware issues and not the database itself.
I don't get what you are stating about the extra space and abnormal growth.
Are you staying you have lots of page splits and that is causing your data
size to increase? If so you do not have the clustered index on the right
column or you need to adjust your fill factor.
Andrew J. Kelly SQL MVP
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
> over
> time? OR they are totally unrelated.
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
> device
> safely fragmented for sometime if we have some hard disk space available
> that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan
|||Inline...
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
over
> time? OR they are totally unrelated.
>
Absolutely not - they are unrelated. Corruption is caused by hardware
problems (and highly-infrequent bugs in various layers of software and
firmware, obviously)

> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
device
> safely fragmented for sometime if we have some hard disk space available
that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
I'm your man. What are you trying to achieve? Minimal space usage, minimal
perf drop from disk/index fragmentation? Are you correlating a perf drop
with increasing fragmentation, or are you just worried about it?

> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan

Higher level of fragmentation and data corruption.

CAN maintaining higher level of fragmentation result in data corruption over
time? OR they are totally unrelated.
I would like to know because rebuilding indexes take resources and that is
an issue for a 24 x 7 environment. In other words, should we keep data device
safely fragmented for sometime if we have some hard disk space available that
allows us to maintain some abnormal growth, allowing us not to rebuild
indexes?
Can someone with SQL Server Internal Data Storage Knowledge please reply?
Also, please let me know if my question is not very clear enough and you
need further explanation.
--
Regards,
MZeeshanFragmentation in and of itself should not cause data corruption. Most
corruption these days is due to hardware issues and not the database itself.
I don't get what you are stating about the extra space and abnormal growth.
Are you staying you have lots of page splits and that is causing your data
size to increase? If so you do not have the clustered index on the right
column or you need to adjust your fill factor.
--
Andrew J. Kelly SQL MVP
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
> over
> time? OR they are totally unrelated.
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
> device
> safely fragmented for sometime if we have some hard disk space available
> that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan|||Inline...
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
over
> time? OR they are totally unrelated.
>
Absolutely not - they are unrelated. Corruption is caused by hardware
problems (and highly-infrequent bugs in various layers of software and
firmware, obviously)
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
device
> safely fragmented for sometime if we have some hard disk space available
that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
I'm your man. What are you trying to achieve? Minimal space usage, minimal
perf drop from disk/index fragmentation? Are you correlating a perf drop
with increasing fragmentation, or are you just worried about it?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan

Higher level of fragmentation and data corruption.

CAN maintaining higher level of fragmentation result in data corruption over
time? OR they are totally unrelated.
I would like to know because rebuilding indexes take resources and that is
an issue for a 24 x 7 environment. In other words, should we keep data devic
e
safely fragmented for sometime if we have some hard disk space available tha
t
allows us to maintain some abnormal growth, allowing us not to rebuild
indexes?
Can someone with SQL Server Internal Data Storage Knowledge please reply?
Also, please let me know if my question is not very clear enough and you
need further explanation.
Regards,
MZeeshanFragmentation in and of itself should not cause data corruption. Most
corruption these days is due to hardware issues and not the database itself.
I don't get what you are stating about the extra space and abnormal growth.
Are you staying you have lots of page splits and that is causing your data
size to increase? If so you do not have the clustered index on the right
column or you need to adjust your fill factor.
Andrew J. Kelly SQL MVP
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
> over
> time? OR they are totally unrelated.
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
> device
> safely fragmented for sometime if we have some hard disk space available
> that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan|||Inline...
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
over
> time? OR they are totally unrelated.
>
Absolutely not - they are unrelated. Corruption is caused by hardware
problems (and highly-infrequent bugs in various layers of software and
firmware, obviously)

> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
device
> safely fragmented for sometime if we have some hard disk space available
that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
I'm your man. What are you trying to achieve? Minimal space usage, minimal
perf drop from disk/index fragmentation? Are you correlating a perf drop
with increasing fragmentation, or are you just worried about it?

> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan