Is there a way to simulate maintenance plan option for
integrity checking called: attempt to repair minor
problems using some dbcc option preferably in mutli user
mode for MSSQL2000.Milan
The command you want is DBCC CHECKDB. It allows three
levels of repair. See Books on line for format and options.
Regards
John|||However, please be aware of two things:
1) you cannot run repair in multi-user mode - the database must be in
single-user mode
2) you should not automatically repair errors. You should always work out
why an error happened (so you can prevent it in future) and whether the
repair may lose some of your data. A preferable strategem is always to
restore from your most recent backups.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:079201c3502d$575fc370$a601280a@.phx.gbl...
> Milan
> The command you want is DBCC CHECKDB. It allows three
> levels of repair. See Books on line for format and options.
> Regards
> John
Showing posts with label integrity. Show all posts
Showing posts with label integrity. Show all posts
Wednesday, March 21, 2012
Thursday, March 8, 2012
DBCC fails, why?
Every time I run DBCC to do an integrity check I get the following errors...
and the first one i dont understand it says Expected value 0_PCT_FULL,
actual value 100_PCT_FULL, I checked and it was set to 0...
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:628) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:632) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:640) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:3410) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:3510) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:4948) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
CHECKDB found 0 allocation errors and 6 consistency errors in table
'sysmaintplan_logdetail' (object ID 949578421).
CHECKDB found 0 allocation errors and 6 consistency errors in database
'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (msdb).
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:158) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:161) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:338) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:448) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'BENE_Testing'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (BENE_Testing).
here is the SQL used for this
USE [master]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [model]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [msdb]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BENE_Live]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BENE_Users]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BDB_FileTransferImports]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [RESC_Intranet]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [RPTS_Reports]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [RSMN_Messaging]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BUGS_GeminiWeb]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BENE_Testing]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [ReportServer]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [ReportServerTempDB]
GO
DBCC CHECKDB WITH NO_INFOMSGSYou have a corruption in your database. I'm surprised that allow data loss i
s considered as the
minimum repair level for these messages. Download Books Online for SQL Serve
r 2000, and search for
the error numbers there for further explanations (these error numbers isn't
documented yet in BOL
2005). Also, you might want to check out
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Henry" <nospam@.nospam.com> wrote in message news:ulc0G36YGHA.4620@.TK2MSFTNGP04.phx.g
bl...
> Every time I run DBCC to do an integrity check I get the following errors.
.
> and the first one i dont understand it says Expected value 0_PCT_FULL, act
ual value 100_PCT_FULL,
> I checked and it was set to 0...
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:628) in object ID 9495784
21, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:632) in object ID 9495784
21, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:640) in object ID 9495784
21, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:3410) in object ID 949578
421, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:3510) in object ID 949578
421, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:4948) in object ID 949578
421, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> CHECKDB found 0 allocation errors and 6 consistency errors in table 'sysma
intplan_logdetail'
> (object ID 949578421).
> CHECKDB found 0 allocation errors and 6 consistency errors in database 'ms
db'.
> repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (msdb).
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:158) in object ID 60, ind
ex ID 1, partition ID
> 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected
value 0_PCT_FULL,
> actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:161) in object ID 60, ind
ex ID 1, partition ID
> 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected
value 0_PCT_FULL,
> actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:338) in object ID 60, ind
ex ID 1, partition ID
> 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected
value 0_PCT_FULL,
> actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:448) in object ID 60, ind
ex ID 1, partition ID
> 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected
value 0_PCT_FULL,
> actual value 100_PCT_FULL.
> CHECKDB found 0 allocation errors and 4 consistency errors in table 'sys.s
ysobjvalues' (object ID
> 60).
> CHECKDB found 0 allocation errors and 4 consistency errors in database 'BE
NE_Testing'.
> repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB
> (BENE_Testing).
>
>
> here is the SQL used for this
>
> USE [master]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [model]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [msdb]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BENE_Live]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BENE_Users]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BDB_FileTransferImports]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [RESC_Intranet]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [RPTS_Reports]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [RSMN_Messaging]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BUGS_GeminiWeb]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BENE_Testing]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [ReportServer]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [ReportServerTempDB]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
>|||well I repaired one of them successfully with no data loss... but the msdb
databsae of course i cant repair with that allow data loss because it
requires single user mode, but you cant take a system db into that... so a
little lost on what to do with that one
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uyYjlM7YGHA.4916@.TK2MSFTNGP04.phx.gbl...
> You have a corruption in your database. I'm surprised that allow data loss
> is considered as the minimum repair level for these messages. Download
> Books Online for SQL Server 2000, and search for the error numbers there
> for further explanations (these error numbers isn't documented yet in BOL
> 2005). Also, you might want to check out
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:ulc0G36YGHA.4620@.TK2MSFTNGP04.phx.gbl...
>|||RESTORE DATABASE msdb ? Assuming of course you do backup of your system data
bases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Henry" <nospam@.nospam.com> wrote in message news:OaEPGX7YGHA.1764@.TK2MSFTNGP05.phx.g
bl...
> well I repaired one of them successfully with no data loss... but the msdb
databsae of course i
> cant repair with that allow data loss because it requires single user mode
, but you cant take a
> system db into that... so a little lost on what to do with that one
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uyYjlM7YGHA.4916@.TK2MSFTNGP04.phx.gbl...
>|||we do backups, but the big problem is, apparently this has been like this
for months now!...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3rbdM8YGHA.428@.TK2MSFTNGP02.phx.gbl...
> RESTORE DATABASE msdb ? Assuming of course you do backup of your system
> databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:OaEPGX7YGHA.1764@.TK2MSFTNGP05.phx.gbl...
>|||Come to think about it, how did you conclude that you can't set msdb to sing
le user? I just tried it
on both a 2000 sp3 instance as well as 2005 instance. Both were successful.
You have to stop Agent
first, of course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Henry" <nospam@.nospam.com> wrote in message news:uyZEFr8YGHA.4936@.TK2MSFTNGP05.phx.g
bl...
> we do backups, but the big problem is, apparently this has been like this
for months now!...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3rbdM8YGHA.428@.TK2MSFTNGP02.phx.gbl...
>|||didn't try stoping the anget, just tried altering the db to single user mode
and it failed and said it couldnt be... ill try it without the agent and see
what happens (this is sql 2005 btw)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23eo3O58YGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Come to think about it, how did you conclude that you can't set msdb to
> single user? I just tried it on both a 2000 sp3 instance as well as 2005
> instance. Both were successful. You have to stop Agent first, of course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:uyZEFr8YGHA.4936@.TK2MSFTNGP05.phx.gbl...
>|||it works with stoping the agent, thanks! wouldnt of thought of that..
"Brian Henry" <nospam@.nospam.com> wrote in message
news:%238qrve9YGHA.3448@.TK2MSFTNGP04.phx.gbl...
> didn't try stoping the anget, just tried altering the db to single user
> mode and it failed and said it couldnt be... ill try it without the agent
> and see what happens (this is sql 2005 btw)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23eo3O58YGHA.3704@.TK2MSFTNGP03.phx.gbl...
>
and the first one i dont understand it says Expected value 0_PCT_FULL,
actual value 100_PCT_FULL, I checked and it was set to 0...
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:628) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:632) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:640) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:3410) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:3510) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:4948) in object ID
949578421, index ID 0, partition ID 72057594042253312, alloc unit ID
72057594046644224 (type LOB data). Expected value 0_PCT_FULL, actual value
100_PCT_FULL.
CHECKDB found 0 allocation errors and 6 consistency errors in table
'sysmaintplan_logdetail' (object ID 949578421).
CHECKDB found 0 allocation errors and 6 consistency errors in database
'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (msdb).
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:158) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:161) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:338) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:448) in object ID 60, index
ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type
LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'BENE_Testing'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (BENE_Testing).
here is the SQL used for this
USE [master]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [model]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [msdb]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BENE_Live]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BENE_Users]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BDB_FileTransferImports]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [RESC_Intranet]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [RPTS_Reports]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [RSMN_Messaging]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BUGS_GeminiWeb]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [BENE_Testing]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [ReportServer]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [ReportServerTempDB]
GO
DBCC CHECKDB WITH NO_INFOMSGSYou have a corruption in your database. I'm surprised that allow data loss i
s considered as the
minimum repair level for these messages. Download Books Online for SQL Serve
r 2000, and search for
the error numbers there for further explanations (these error numbers isn't
documented yet in BOL
2005). Also, you might want to check out
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Henry" <nospam@.nospam.com> wrote in message news:ulc0G36YGHA.4620@.TK2MSFTNGP04.phx.g
bl...
> Every time I run DBCC to do an integrity check I get the following errors.
.
> and the first one i dont understand it says Expected value 0_PCT_FULL, act
ual value 100_PCT_FULL,
> I checked and it was set to 0...
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:628) in object ID 9495784
21, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:632) in object ID 9495784
21, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:640) in object ID 9495784
21, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:3410) in object ID 949578
421, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:3510) in object ID 949578
421, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:4948) in object ID 949578
421, index ID 0,
> partition ID 72057594042253312, alloc unit ID 72057594046644224 (type LOB
data). Expected value
> 0_PCT_FULL, actual value 100_PCT_FULL.
> CHECKDB found 0 allocation errors and 6 consistency errors in table 'sysma
intplan_logdetail'
> (object ID 949578421).
> CHECKDB found 0 allocation errors and 6 consistency errors in database 'ms
db'.
> repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (msdb).
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:158) in object ID 60, ind
ex ID 1, partition ID
> 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected
value 0_PCT_FULL,
> actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:161) in object ID 60, ind
ex ID 1, partition ID
> 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected
value 0_PCT_FULL,
> actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:338) in object ID 60, ind
ex ID 1, partition ID
> 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected
value 0_PCT_FULL,
> actual value 100_PCT_FULL.
> Msg 8914, Level 16, State 1, Line 1
> Incorrect PFS free space information for page (1:448) in object ID 60, ind
ex ID 1, partition ID
> 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected
value 0_PCT_FULL,
> actual value 100_PCT_FULL.
> CHECKDB found 0 allocation errors and 4 consistency errors in table 'sys.s
ysobjvalues' (object ID
> 60).
> CHECKDB found 0 allocation errors and 4 consistency errors in database 'BE
NE_Testing'.
> repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB
> (BENE_Testing).
>
>
> here is the SQL used for this
>
> USE [master]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [model]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [msdb]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BENE_Live]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BENE_Users]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BDB_FileTransferImports]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [RESC_Intranet]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [RPTS_Reports]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [RSMN_Messaging]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BUGS_GeminiWeb]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [BENE_Testing]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [ReportServer]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
> GO
> USE [ReportServerTempDB]
> GO
> DBCC CHECKDB WITH NO_INFOMSGS
>|||well I repaired one of them successfully with no data loss... but the msdb
databsae of course i cant repair with that allow data loss because it
requires single user mode, but you cant take a system db into that... so a
little lost on what to do with that one
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uyYjlM7YGHA.4916@.TK2MSFTNGP04.phx.gbl...
> You have a corruption in your database. I'm surprised that allow data loss
> is considered as the minimum repair level for these messages. Download
> Books Online for SQL Server 2000, and search for the error numbers there
> for further explanations (these error numbers isn't documented yet in BOL
> 2005). Also, you might want to check out
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:ulc0G36YGHA.4620@.TK2MSFTNGP04.phx.gbl...
>|||RESTORE DATABASE msdb ? Assuming of course you do backup of your system data
bases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Henry" <nospam@.nospam.com> wrote in message news:OaEPGX7YGHA.1764@.TK2MSFTNGP05.phx.g
bl...
> well I repaired one of them successfully with no data loss... but the msdb
databsae of course i
> cant repair with that allow data loss because it requires single user mode
, but you cant take a
> system db into that... so a little lost on what to do with that one
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uyYjlM7YGHA.4916@.TK2MSFTNGP04.phx.gbl...
>|||we do backups, but the big problem is, apparently this has been like this
for months now!...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3rbdM8YGHA.428@.TK2MSFTNGP02.phx.gbl...
> RESTORE DATABASE msdb ? Assuming of course you do backup of your system
> databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:OaEPGX7YGHA.1764@.TK2MSFTNGP05.phx.gbl...
>|||Come to think about it, how did you conclude that you can't set msdb to sing
le user? I just tried it
on both a 2000 sp3 instance as well as 2005 instance. Both were successful.
You have to stop Agent
first, of course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Henry" <nospam@.nospam.com> wrote in message news:uyZEFr8YGHA.4936@.TK2MSFTNGP05.phx.g
bl...
> we do backups, but the big problem is, apparently this has been like this
for months now!...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3rbdM8YGHA.428@.TK2MSFTNGP02.phx.gbl...
>|||didn't try stoping the anget, just tried altering the db to single user mode
and it failed and said it couldnt be... ill try it without the agent and see
what happens (this is sql 2005 btw)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23eo3O58YGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Come to think about it, how did you conclude that you can't set msdb to
> single user? I just tried it on both a 2000 sp3 instance as well as 2005
> instance. Both were successful. You have to stop Agent first, of course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:uyZEFr8YGHA.4936@.TK2MSFTNGP05.phx.gbl...
>|||it works with stoping the agent, thanks! wouldnt of thought of that..
"Brian Henry" <nospam@.nospam.com> wrote in message
news:%238qrve9YGHA.3448@.TK2MSFTNGP04.phx.gbl...
> didn't try stoping the anget, just tried altering the db to single user
> mode and it failed and said it couldnt be... ill try it without the agent
> and see what happens (this is sql 2005 btw)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23eo3O58YGHA.3704@.TK2MSFTNGP03.phx.gbl...
>
Sunday, February 19, 2012
DBCC CHECKDB WITH NO_INFOMSGS Error
When I run either "DBCC CHECKDB WITH NO_INFOMSGS" or using "check database integrity" in maintenance plan, I always got an error below:
The In-row data RSVD page count for object "tablename", index ID 0, partition ID 76911687696384, alloc unit ID 76911687696384 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1173579219).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.
Can anyone help me please?
Regards,
What happens after you run DBCC UPDATEUSAGE?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thank you Denis. It was perfect after running DBCC UPDATEUSAGE('dbname').
Cheers,
Tuesday, February 14, 2012
Dbcc Checkdb
Hi All,
How can I make the dbcc checkdb fail so I can get the errors displayed in the report log? DBCC Checkdb is the step in the integrity job that I run once a month. What I am trying to do is when the dbcc checkdb fails for any reason, I want to get notified so I can correct the problem. I don't want to use repair fast or any other repair parameters that you can select when you run dbcc checkdb function because most of my dbs are 24x7.
Thanks.I was going for
USE Northwind
GO
CREATE TABLE myTable99(Check_Log varchar(8000), Log_Time datetime DEFAULT GetDate())
GO
INSERT INTO myTable99(Check_Log) DBCC CHECKDB
But it doesn't work|||I don't know what inka exactly wants but if you want to catch the output of DBCC CHECKDB for later evaluation and processing...
DECLARE @.cmd VARCHAR(255)
CREATE TABLE #result (txt VARCHAR(1000))
SET @.cmd = 'isql -SMySQLInstance -E -dNorthwind -Q"DBCC CHECKDB"'
INSERT #result
EXEC master..xp_cmdshell @.cmd
SELECT * FROM #result
DROP TABLE #result
Looking at it I think this is a bit silly :S and there should be another way to do this!|||One of the suggestions I was given was to create a bad table in a db and dbcc checkdb will report on it. I am not sure how to create a bad table so dbcc checkdb will have an error.|||Now why didn't I think about that...Damn DB2
DECLARE @.cmd VARCHAR(255)
CREATE TABLE #result (Log_Batch int, Log_Row int IDENTITY(1,1), Check_Log varchar(8000), Log_Time datetime DEFAULT GetDate())
SET @.cmd = 'osql -S<yourServerName> -E -dNorthwind -Q"DBCC CHECKDB"'
INSERT #result(Check_Log)
EXEC master..xp_cmdshell @.cmd
UPDATE #result SET Log_Batch = (SELECT MAX(COALESCE(Log_Batch,0))+1 FROM #result) WHERE Log_Batch IS NULL
IF EXISTS (SELECT * FROM #result o
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database'
AND Log_Batch IN (SELECT MAX(Log_Batch) FROM #result))
PRINT 'No Errors'
ELSE
PRINT 'Do some logic to page or email you'
DROP TABLE #result|||One of the suggestions I was given was to create a bad table in a db and dbcc checkdb will report on it. I am not sure how to create a bad table so dbcc checkdb will have an error.
Tell me your boss told you to do this, and if not who did?|||I am not sure how the code that you sent me will create a bad table. Can you explain?
Thanks.|||You can use the code Brett posted to signal you when DBCC CHECKDB generates an error. Creating a "bad" table (beats me how and why you want to do that on purpose, it will only create more problems) and running DBCC CHECKDB will always generate an error even when everything else is fine. And then you still have the problem of catching it.|||The reason I want to create a "bad" table and run dbcc checkdb is because I want to see what the error looks like. This will be one time deal and I am doing it in development. Once I see what the error looks like, then I will drop the table.|||running DBCC CHECKDB will always generate an error even when everything else is fine. And then you still have the problem of catching it.
Excuse me? Do you mean it will always produce outptut? I'm cornfused|||I am not sure how the code that you sent me will create a bad table. Can you explain?
Thanks.
Sure be glad to...it does not create a "bad" table (who told you to do this again?)...what id does do is tell you when you had an error...did you at least cut and paste the code and run it?
Sample CHECK DB utput below
DBCC results for 'Northwind'.
DBCC results for 'sysobjects'.
There are 295 rows in 5 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 243 rows in 11 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1086 rows in 34 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 241 rows in 28 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 148 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 26 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 12 rows in 1 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 593 rows in 5 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 13 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'Orders'.
There are 830 rows in 20 pages for object 'Orders'.
DBCC results for 'Products'.
There are 77 rows in 1 pages for object 'Products'.
DBCC results for 'tempStringTest'.
There are 3 rows in 1 pages for object 'tempStringTest'.
DBCC results for 'dd3'.
There are 0 rows in 0 pages for object 'dd3'.
DBCC results for 'SEEDS1'.
There are 11 rows in 1 pages for object 'SEEDS1'.
DBCC results for 'SEEDS2'.
There are 4 rows in 1 pages for object 'SEEDS2'.
DBCC results for 'fund_info'.
There are 0 rows in 0 pages for object 'fund_info'.
DBCC results for 'fund_history'.
There are 29510 rows in 325 pages for object 'fund_history'.
DBCC results for 'fund_nav_info'.
There are 93 rows in 2 pages for object 'fund_nav_info'.
DBCC results for 'ProcessLog'.
There are 9 rows in 1 pages for object 'ProcessLog'.
DBCC results for 'Order Details'.
There are 2155 rows in 9 pages for object 'Order Details'.
DBCC results for 'TMP_Order Details_2005_08_16_13_32_30'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_32_30'.
DBCC results for 'TMP_Order Details_2005_08_16_13_32_54'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_32_54'.
DBCC results for 'TMP_Order Details_2005_08_16_13_33_00'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_33_00'.
DBCC results for 'TMP_Order Details_2005_08_16_13_33_03'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_33_03'.
DBCC results for 'data1'.
There are 6 rows in 1 pages for object 'data1'.
DBCC results for 'testPrimary'.
There are 4 rows in 1 pages for object 'testPrimary'.
DBCC results for 'testRelated'.
There are 6 rows in 1 pages for object 'testRelated'.
DBCC results for 'emp'.
There are 0 rows in 0 pages for object 'emp'.
DBCC results for 'myAgents99'.
There are 14 rows in 1 pages for object 'myAgents99'.
DBCC results for 'myCustomers99'.
There are 5 rows in 1 pages for object 'myCustomers99'.
DBCC results for 'patients'.
There are 9 rows in 1 pages for object 'patients'.
DBCC results for 'LOG_TABLE'.
There are 1 rows in 1 pages for object 'LOG_TABLE'.
DBCC results for 'Trace_D060911'.
There are 36 rows in 1 pages for object 'Trace_D060911'.
DBCC results for 'spid_Sleep'.
There are 29 rows in 1 pages for object 'spid_Sleep'.
DBCC results for 'myTable99'.
There are 0 rows in 0 pages for object 'myTable99'.
DBCC results for 'X105002.myorders'.
There are 830 rows in 21 pages for object 'X105002.myorders'.
DBCC results for 'X105002.jimtable1'.
There are 825 rows in 21 pages for object 'X105002.jimtable1'.
DBCC results for 'tblStateProv'.
There are 0 rows in 0 pages for object 'tblStateProv'.
DBCC results for 'tblUserDetails'.
There are 0 rows in 0 pages for object 'tblUserDetails'.
DBCC results for 'Periods'.
There are 3 rows in 1 pages for object 'Periods'.
DBCC results for 'CustomerCustomerDemo'.
There are 0 rows in 0 pages for object 'CustomerCustomerDemo'.
DBCC results for 'CustomerDemographics'.
There are 0 rows in 0 pages for object 'CustomerDemographics'.
DBCC results for 'Region'.
There are 4 rows in 1 pages for object 'Region'.
DBCC results for 'Territories'.
There are 53 rows in 1 pages for object 'Territories'.
DBCC results for 'EmployeeTerritories'.
There are 49 rows in 1 pages for object 'EmployeeTerritories'.
DBCC results for '1GEN_PROFILE_INS_060711'.
There are 106 rows in 2 pages for object '1GEN_PROFILE_INS_060711'.
DBCC results for 'TMPPARTMSTR'.
There are 4 rows in 1 pages for object 'TMPPARTMSTR'.
DBCC results for 'a'.
There are 340 rows in 4 pages for object 'a'.
DBCC results for 'TMPPRODUCT_MASTER'.
There are 3 rows in 1 pages for object 'TMPPRODUCT_MASTER'.
DBCC results for 'sp_depends_xref'.
There are 462 rows in 8 pages for object 'sp_depends_xref'.
DBCC results for 'n'.
There are 999 rows in 2 pages for object 'n'.
DBCC results for 'dd'.
There are 0 rows in 0 pages for object 'dd'.
DBCC results for 'ProductLocation'.
There are 2 rows in 1 pages for object 'ProductLocation'.
DBCC results for 'dd1'.
There are 0 rows in 0 pages for object 'dd1'.
DBCC results for 'Synergy_Signon'.
There are 0 rows in 0 pages for object 'Synergy_Signon'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for 'dd2'.
There are 0 rows in 0 pages for object 'dd2'.
DBCC results for 'myDST99'.
There are 26 rows in 1 pages for object 'myDST99'.
DBCC results for 'Trigger_Log'.
There are 0 rows in 0 pages for object 'Trigger_Log'.
DBCC results for 'myTrades99'.
There are 9 rows in 1 pages for object 'myTrades99'.
DBCC results for 'Synergy_Signon2'.
There are 1019 rows in 12 pages for object 'Synergy_Signon2'.
DBCC results for 'myReceipts99'.
There are 12 rows in 1 pages for object 'myReceipts99'.
DBCC results for 'Sproc_Log'.
There are 0 rows in 0 pages for object 'Sproc_Log'.
DBCC results for 'Joe99'.
There are 8 rows in 1 pages for object 'Joe99'.
DBCC results for 'Trace_D061003'.
There are 24 rows in 1 pages for object 'Trace_D061003'.
DBCC results for 'tM_FR_STOPS'.
There are 14 rows in 1 pages for object 'tM_FR_STOPS'.
DBCC results for 'module'.
There are 2 rows in 1 pages for object 'module'.
DBCC results for 'tM_FR_WIP_UnitID_FastStops'.
There are 2 rows in 1 pages for object 'tM_FR_WIP_UnitID_FastStops'.
DBCC results for 'module_content'.
There are 2 rows in 1 pages for object 'module_content'.
DBCC results for 'Directory_Contents_Stage'.
There are 10 rows in 1 pages for object 'Directory_Contents_Stage'.
DBCC results for 'myTableSearch99'.
There are 0 rows in 0 pages for object 'myTableSearch99'.
DBCC results for 'page'.
There are 2 rows in 1 pages for object 'page'.
DBCC results for 'Directory_Contents'.
There are 68 rows in 1 pages for object 'Directory_Contents'.
DBCC results for 'role'.
There are 2 rows in 1 pages for object 'role'.
DBCC results for 'role_permissions'.
There are 1 rows in 1 pages for object 'role_permissions'.
DBCC results for 'user_permissions'.
There are 1 rows in 1 pages for object 'user_permissions'.
DBCC results for 'tt'.
There are 4 rows in 1 pages for object 'tt'.
DBCC results for 'CONFIG_CODE'.
There are 0 rows in 0 pages for object 'CONFIG_CODE'.
DBCC results for 'Organization'.
There are 0 rows in 0 pages for object 'Organization'.
DBCC results for 'SCRAMBLE_SYSTEM'.
There are 0 rows in 0 pages for object 'SCRAMBLE_SYSTEM'.
DBCC results for 'Trace'.
There are 10515 rows in 231 pages for object 'Trace'.
DBCC results for 'SCRAMBLE_FILE'.
There are 0 rows in 0 pages for object 'SCRAMBLE_FILE'.
DBCC results for 'CONFIG_FILE'.
There are 0 rows in 0 pages for object 'CONFIG_FILE'.
DBCC results for 'trace2'.
There are 10515 rows in 231 pages for object 'trace2'.
DBCC results for 'Employees'.
There are 9 rows in 1 pages for object 'Employees'.
DBCC results for 'myOrg99'.
There are 6 rows in 1 pages for object 'myOrg99'.
DBCC results for 'GroupRules'.
There are 0 rows in 1 pages for object 'GroupRules'.
DBCC results for 'Categories'.
There are 8 rows in 1 pages for object 'Categories'.
DBCC results for 'Answers'.
There are 0 rows in 1 pages for object 'Answers'.
DBCC results for 'myAccounts99'.
There are 3 rows in 1 pages for object 'myAccounts99'.
DBCC results for 'Customers'.
There are 91 rows in 3 pages for object 'Customers'.
DBCC results for 'Shippers'.
There are 3 rows in 1 pages for object 'Shippers'.
DBCC results for 'Mark99'.
There are 25 rows in 1 pages for object 'Mark99'.
DBCC results for 'Suppliers'.
There are 29 rows in 1 pages for object 'Suppliers'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Northwind'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.|||Do you have a sample checkdb output that has errors?|||Excuse me? Do you mean it will always produce outptut? I'm cornfused
I meant that if you have a bad table it will always produce an error. The point of having a bad table was unclear to me.
Do you have a sample checkdb output that has errors?
Luckily not at the moment ;) But with a little googling you should find some...
How can I make the dbcc checkdb fail so I can get the errors displayed in the report log? DBCC Checkdb is the step in the integrity job that I run once a month. What I am trying to do is when the dbcc checkdb fails for any reason, I want to get notified so I can correct the problem. I don't want to use repair fast or any other repair parameters that you can select when you run dbcc checkdb function because most of my dbs are 24x7.
Thanks.I was going for
USE Northwind
GO
CREATE TABLE myTable99(Check_Log varchar(8000), Log_Time datetime DEFAULT GetDate())
GO
INSERT INTO myTable99(Check_Log) DBCC CHECKDB
But it doesn't work|||I don't know what inka exactly wants but if you want to catch the output of DBCC CHECKDB for later evaluation and processing...
DECLARE @.cmd VARCHAR(255)
CREATE TABLE #result (txt VARCHAR(1000))
SET @.cmd = 'isql -SMySQLInstance -E -dNorthwind -Q"DBCC CHECKDB"'
INSERT #result
EXEC master..xp_cmdshell @.cmd
SELECT * FROM #result
DROP TABLE #result
Looking at it I think this is a bit silly :S and there should be another way to do this!|||One of the suggestions I was given was to create a bad table in a db and dbcc checkdb will report on it. I am not sure how to create a bad table so dbcc checkdb will have an error.|||Now why didn't I think about that...Damn DB2
DECLARE @.cmd VARCHAR(255)
CREATE TABLE #result (Log_Batch int, Log_Row int IDENTITY(1,1), Check_Log varchar(8000), Log_Time datetime DEFAULT GetDate())
SET @.cmd = 'osql -S<yourServerName> -E -dNorthwind -Q"DBCC CHECKDB"'
INSERT #result(Check_Log)
EXEC master..xp_cmdshell @.cmd
UPDATE #result SET Log_Batch = (SELECT MAX(COALESCE(Log_Batch,0))+1 FROM #result) WHERE Log_Batch IS NULL
IF EXISTS (SELECT * FROM #result o
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database'
AND Log_Batch IN (SELECT MAX(Log_Batch) FROM #result))
PRINT 'No Errors'
ELSE
PRINT 'Do some logic to page or email you'
DROP TABLE #result|||One of the suggestions I was given was to create a bad table in a db and dbcc checkdb will report on it. I am not sure how to create a bad table so dbcc checkdb will have an error.
Tell me your boss told you to do this, and if not who did?|||I am not sure how the code that you sent me will create a bad table. Can you explain?
Thanks.|||You can use the code Brett posted to signal you when DBCC CHECKDB generates an error. Creating a "bad" table (beats me how and why you want to do that on purpose, it will only create more problems) and running DBCC CHECKDB will always generate an error even when everything else is fine. And then you still have the problem of catching it.|||The reason I want to create a "bad" table and run dbcc checkdb is because I want to see what the error looks like. This will be one time deal and I am doing it in development. Once I see what the error looks like, then I will drop the table.|||running DBCC CHECKDB will always generate an error even when everything else is fine. And then you still have the problem of catching it.
Excuse me? Do you mean it will always produce outptut? I'm cornfused|||I am not sure how the code that you sent me will create a bad table. Can you explain?
Thanks.
Sure be glad to...it does not create a "bad" table (who told you to do this again?)...what id does do is tell you when you had an error...did you at least cut and paste the code and run it?
Sample CHECK DB utput below
DBCC results for 'Northwind'.
DBCC results for 'sysobjects'.
There are 295 rows in 5 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 243 rows in 11 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1086 rows in 34 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 241 rows in 28 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 148 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 26 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 12 rows in 1 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 593 rows in 5 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 13 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'Orders'.
There are 830 rows in 20 pages for object 'Orders'.
DBCC results for 'Products'.
There are 77 rows in 1 pages for object 'Products'.
DBCC results for 'tempStringTest'.
There are 3 rows in 1 pages for object 'tempStringTest'.
DBCC results for 'dd3'.
There are 0 rows in 0 pages for object 'dd3'.
DBCC results for 'SEEDS1'.
There are 11 rows in 1 pages for object 'SEEDS1'.
DBCC results for 'SEEDS2'.
There are 4 rows in 1 pages for object 'SEEDS2'.
DBCC results for 'fund_info'.
There are 0 rows in 0 pages for object 'fund_info'.
DBCC results for 'fund_history'.
There are 29510 rows in 325 pages for object 'fund_history'.
DBCC results for 'fund_nav_info'.
There are 93 rows in 2 pages for object 'fund_nav_info'.
DBCC results for 'ProcessLog'.
There are 9 rows in 1 pages for object 'ProcessLog'.
DBCC results for 'Order Details'.
There are 2155 rows in 9 pages for object 'Order Details'.
DBCC results for 'TMP_Order Details_2005_08_16_13_32_30'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_32_30'.
DBCC results for 'TMP_Order Details_2005_08_16_13_32_54'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_32_54'.
DBCC results for 'TMP_Order Details_2005_08_16_13_33_00'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_33_00'.
DBCC results for 'TMP_Order Details_2005_08_16_13_33_03'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_33_03'.
DBCC results for 'data1'.
There are 6 rows in 1 pages for object 'data1'.
DBCC results for 'testPrimary'.
There are 4 rows in 1 pages for object 'testPrimary'.
DBCC results for 'testRelated'.
There are 6 rows in 1 pages for object 'testRelated'.
DBCC results for 'emp'.
There are 0 rows in 0 pages for object 'emp'.
DBCC results for 'myAgents99'.
There are 14 rows in 1 pages for object 'myAgents99'.
DBCC results for 'myCustomers99'.
There are 5 rows in 1 pages for object 'myCustomers99'.
DBCC results for 'patients'.
There are 9 rows in 1 pages for object 'patients'.
DBCC results for 'LOG_TABLE'.
There are 1 rows in 1 pages for object 'LOG_TABLE'.
DBCC results for 'Trace_D060911'.
There are 36 rows in 1 pages for object 'Trace_D060911'.
DBCC results for 'spid_Sleep'.
There are 29 rows in 1 pages for object 'spid_Sleep'.
DBCC results for 'myTable99'.
There are 0 rows in 0 pages for object 'myTable99'.
DBCC results for 'X105002.myorders'.
There are 830 rows in 21 pages for object 'X105002.myorders'.
DBCC results for 'X105002.jimtable1'.
There are 825 rows in 21 pages for object 'X105002.jimtable1'.
DBCC results for 'tblStateProv'.
There are 0 rows in 0 pages for object 'tblStateProv'.
DBCC results for 'tblUserDetails'.
There are 0 rows in 0 pages for object 'tblUserDetails'.
DBCC results for 'Periods'.
There are 3 rows in 1 pages for object 'Periods'.
DBCC results for 'CustomerCustomerDemo'.
There are 0 rows in 0 pages for object 'CustomerCustomerDemo'.
DBCC results for 'CustomerDemographics'.
There are 0 rows in 0 pages for object 'CustomerDemographics'.
DBCC results for 'Region'.
There are 4 rows in 1 pages for object 'Region'.
DBCC results for 'Territories'.
There are 53 rows in 1 pages for object 'Territories'.
DBCC results for 'EmployeeTerritories'.
There are 49 rows in 1 pages for object 'EmployeeTerritories'.
DBCC results for '1GEN_PROFILE_INS_060711'.
There are 106 rows in 2 pages for object '1GEN_PROFILE_INS_060711'.
DBCC results for 'TMPPARTMSTR'.
There are 4 rows in 1 pages for object 'TMPPARTMSTR'.
DBCC results for 'a'.
There are 340 rows in 4 pages for object 'a'.
DBCC results for 'TMPPRODUCT_MASTER'.
There are 3 rows in 1 pages for object 'TMPPRODUCT_MASTER'.
DBCC results for 'sp_depends_xref'.
There are 462 rows in 8 pages for object 'sp_depends_xref'.
DBCC results for 'n'.
There are 999 rows in 2 pages for object 'n'.
DBCC results for 'dd'.
There are 0 rows in 0 pages for object 'dd'.
DBCC results for 'ProductLocation'.
There are 2 rows in 1 pages for object 'ProductLocation'.
DBCC results for 'dd1'.
There are 0 rows in 0 pages for object 'dd1'.
DBCC results for 'Synergy_Signon'.
There are 0 rows in 0 pages for object 'Synergy_Signon'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for 'dd2'.
There are 0 rows in 0 pages for object 'dd2'.
DBCC results for 'myDST99'.
There are 26 rows in 1 pages for object 'myDST99'.
DBCC results for 'Trigger_Log'.
There are 0 rows in 0 pages for object 'Trigger_Log'.
DBCC results for 'myTrades99'.
There are 9 rows in 1 pages for object 'myTrades99'.
DBCC results for 'Synergy_Signon2'.
There are 1019 rows in 12 pages for object 'Synergy_Signon2'.
DBCC results for 'myReceipts99'.
There are 12 rows in 1 pages for object 'myReceipts99'.
DBCC results for 'Sproc_Log'.
There are 0 rows in 0 pages for object 'Sproc_Log'.
DBCC results for 'Joe99'.
There are 8 rows in 1 pages for object 'Joe99'.
DBCC results for 'Trace_D061003'.
There are 24 rows in 1 pages for object 'Trace_D061003'.
DBCC results for 'tM_FR_STOPS'.
There are 14 rows in 1 pages for object 'tM_FR_STOPS'.
DBCC results for 'module'.
There are 2 rows in 1 pages for object 'module'.
DBCC results for 'tM_FR_WIP_UnitID_FastStops'.
There are 2 rows in 1 pages for object 'tM_FR_WIP_UnitID_FastStops'.
DBCC results for 'module_content'.
There are 2 rows in 1 pages for object 'module_content'.
DBCC results for 'Directory_Contents_Stage'.
There are 10 rows in 1 pages for object 'Directory_Contents_Stage'.
DBCC results for 'myTableSearch99'.
There are 0 rows in 0 pages for object 'myTableSearch99'.
DBCC results for 'page'.
There are 2 rows in 1 pages for object 'page'.
DBCC results for 'Directory_Contents'.
There are 68 rows in 1 pages for object 'Directory_Contents'.
DBCC results for 'role'.
There are 2 rows in 1 pages for object 'role'.
DBCC results for 'role_permissions'.
There are 1 rows in 1 pages for object 'role_permissions'.
DBCC results for 'user_permissions'.
There are 1 rows in 1 pages for object 'user_permissions'.
DBCC results for 'tt'.
There are 4 rows in 1 pages for object 'tt'.
DBCC results for 'CONFIG_CODE'.
There are 0 rows in 0 pages for object 'CONFIG_CODE'.
DBCC results for 'Organization'.
There are 0 rows in 0 pages for object 'Organization'.
DBCC results for 'SCRAMBLE_SYSTEM'.
There are 0 rows in 0 pages for object 'SCRAMBLE_SYSTEM'.
DBCC results for 'Trace'.
There are 10515 rows in 231 pages for object 'Trace'.
DBCC results for 'SCRAMBLE_FILE'.
There are 0 rows in 0 pages for object 'SCRAMBLE_FILE'.
DBCC results for 'CONFIG_FILE'.
There are 0 rows in 0 pages for object 'CONFIG_FILE'.
DBCC results for 'trace2'.
There are 10515 rows in 231 pages for object 'trace2'.
DBCC results for 'Employees'.
There are 9 rows in 1 pages for object 'Employees'.
DBCC results for 'myOrg99'.
There are 6 rows in 1 pages for object 'myOrg99'.
DBCC results for 'GroupRules'.
There are 0 rows in 1 pages for object 'GroupRules'.
DBCC results for 'Categories'.
There are 8 rows in 1 pages for object 'Categories'.
DBCC results for 'Answers'.
There are 0 rows in 1 pages for object 'Answers'.
DBCC results for 'myAccounts99'.
There are 3 rows in 1 pages for object 'myAccounts99'.
DBCC results for 'Customers'.
There are 91 rows in 3 pages for object 'Customers'.
DBCC results for 'Shippers'.
There are 3 rows in 1 pages for object 'Shippers'.
DBCC results for 'Mark99'.
There are 25 rows in 1 pages for object 'Mark99'.
DBCC results for 'Suppliers'.
There are 29 rows in 1 pages for object 'Suppliers'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Northwind'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.|||Do you have a sample checkdb output that has errors?|||Excuse me? Do you mean it will always produce outptut? I'm cornfused
I meant that if you have a bad table it will always produce an error. The point of having a bad table was unclear to me.
Do you have a sample checkdb output that has errors?
Luckily not at the moment ;) But with a little googling you should find some...
DBCC CheckDB
Hi:
As part of database maintenance plan, I am using the option (checkbox) for checking the integrity of the database before backing up database or transaction log.
I am not sure when this database integrity check happens (DBCC CheckDB). They seem to be happening at different times not necessarily right before database backup. Does anyone have more details on this?
Also I am backing up my user and system databases every night with database integrity checkup. This is a live eCommerce site. Usually database integrity check should not take long but if it does, will it block users. Is it good idea to do it every night? I was thinking of backing up system database only on weekends.
Thanks
Hi Mike. From the sounds of it, you're referring to a SQL 2000 system I assume.
Can't say really if it's a good idea or not to perform the checkdb nightly, I can surely say it's not a bad thing to do it, other than if it causes problems like you stated (i.e. blocking)...another possible problem could be that it 'thrashes' the buffer pool, and possibly leaves unneeded data in your cache rather than data that is normally there, so in the morning you may see some slowdown on queries until the wanted data resides in cache again.
One thing many people will do on larger, OLTP type systems is perform a checkdb on the production system weekly (or something like that) and perform a nightly checkdb on a restore of the live database on a test server.
As for the schedule of when they occur, if you go to the 'integrity' tab of the existing maintanence plan, at the bottom of the screen there will be a schedule listed there for the integrity check portion of the plan only. Each of the tabs (Optimizations, Integrity, Backup, etc.) have a seperate schedule assigned that defines when that particular operation is begun. Additionaly, you could check the schedule of the SQL Agent Jobs that are created to actually kick off the maintanence tasks.
HTH
Subscribe to:
Posts (Atom)