When I run DBCC LOGINFO('DBname') I get 2 and 0 for Status column. How do I
make value 2 as zero before I can truncate the logfile?
ThanksTry to backup the transaction log. then look again.
>--Original Message--
>When I run DBCC LOGINFO('DBname') I get 2 and 0 for
Status column. How do I make value 2 as zero before I can
truncate the logfile?
>Thanks
>.
>
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Monday, March 19, 2012
dbcc inputbuffer value
Hi,
I want to store DBCC inputbuffer value in a variable and then want to
comapare the value.
How ca i store the value in a variable or table inside a script?You can try this...
create table #temp (eventType varchar(100),parameters int, EventInfo
nvarchar(200))
insert into #temp exec('dbcc inputbuffer (' + @.@.spid + ')')
select * from #temp
drop table #temp
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
I want to store DBCC inputbuffer value in a variable and then want to
comapare the value.
How ca i store the value in a variable or table inside a script?You can try this...
create table #temp (eventType varchar(100),parameters int, EventInfo
nvarchar(200))
insert into #temp exec('dbcc inputbuffer (' + @.@.spid + ')')
select * from #temp
drop table #temp
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
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...
>
Friday, February 24, 2012
Dbcc Checkident Reseed
On some tables when I execute the following statement the identity value will start at 1 other times 0. Has anyone experienced this inconsistency before?
DBCC CHECKIDENT ('TABLE_NAME', RESEED, 0)Is the miracle effect?
Can you post some code where this happens?
Please make sure to post the DDL, any DML and sample data.
Thanks|||Here ya go. Most of the tables are very simplistic. I start by deleting all of the records then executing the DBCC CHECKIDENT ('table_name', RESEED, 0) in query analyzer against the table. Here's the ddl for one of the tables. 90% of the time the identity value starts at 1 the other 10% start at 0.
t_unique_id = int
t_varchar = varchar(50)
t_flag = int with rule limiting values to 1 or 0
t_guid = char(36)
/*================================================= =============*/
/* Table: producer_type */
/*================================================= =============*/
create table dbo.producer_type (
producer_type_id t_unique_id identity,
producer_type_name t_varchar not null,
org_flag t_flag not null,
description t_long_varchar not null,
comments t_long_varchar not null,
created_by t_unique_id not null,
created_dt datetime not null,
changed_by t_unique_id not null,
changed_dt datetime not null,
logical_unit_of_work_id t_guid not null
)
go
alter table dbo.producer_type
add constraint pk_producer_type primary key (producer_type_id)
with
fillfactor= 90
go
/*================================================= =============*/
/* Index: ux_producer_type_01 */
/*================================================= =============*/
create unique index ux_producer_type_01 on dbo.producer_type (
producer_type_name
)
with
fillfactor= 90
go|||I'll take a look, but why don't you specify the seed an increment in the DDL?
I'm sure there's a default...but...
I've always set IDENTITY(1,1) (that is when I have to use it...I try to avoid it)|||Just out of curiosity...how are you building this table?
You can't do what you're doing unless you use dynamic sql...unless you're building from the application layer, and if you are, what's with the GO?
Also out of curiosity, it sounds like whatever process your doing is placing an undue amount of meaning to the identity column.
Why does it need to be reset to 1 (or 0)|||Sorry, these are in no particular order:
First, I'm building the tables with ddl and executing with query builder. I didn't want to post all of the ddl for the user defined data types, fks, etc... The 'GO' is used as a terminator for the SQL. I'm building over 150 tables, pk's, fk's, indexes, etc.. in one script. It helps me if I encounter an error. The script will stop at that point and allow me to correct the problem and continue running.
Second, the default on the identity is (1,1)
Third, we are placing extra burden on the identity value because we are trying to migrate data into the new system from an existing system and are heavily coding at the same time. The developers need to know the primary key values for reference or metadata for codiing purposes. I know the order of the values going into the tables and as a standard told all of the developers the pk would start at 1. This hurts if it periodically starts at 0 because we are constantly rebuilding the database from the ddl.
Finally, I'm regretting the decision not to turn the identity insert on and specify the values on insert. I could change my scripts but it would take some time to do. I didn't know if there was something simple I was missing with the DBCC CHECKIDENT and RESEED.
Hope this helps, sorry for the long reply.|||Are you not using Query Analyzer?
And if you are, then how does this work?
t_unique_id = int
t_varchar = varchar(50)
t_flag = int with rule limiting values to 1 or 0
t_guid = char(36)
/ *=================================================
=============*/
/* Table: producer_type */
/ *=================================================
=============*/
create table dbo.producer_type (
producer_type_id t_unique_id identity,
producer_type_name t_varchar not null,
org_flag t_flag not null,
description t_long_varchar not null,
comments t_long_varchar not null,
created_by t_unique_id not null,
created_dt datetime not null,
changed_by t_unique_id not null,
changed_dt datetime not null,
logical_unit_of_work_id t_guid not null
)
go
You can't assign the datatype to a variable like that...unless it's a different interface/language...
I'm amazed at a 10% occurance...it's not often that the same process behave differently.
Since you're blowing the data away anyway, can you just do a drop, then recreate the table?
It will save on logging.
Why not create a shell db and recreate it when you need. Then have load scripts...or better just dump a "clean slate" db, and restore over...
and I'm sure NONE of these suggestion will work for you because of either RI, developer data, yada yada yada...
And in lieu of the fact that you're having these problems...why not give it a try and build the table with IDENTITY(1,1).
My Own Opinion (MOO)
Good Luck
DBCC CHECKIDENT ('TABLE_NAME', RESEED, 0)Is the miracle effect?
Can you post some code where this happens?
Please make sure to post the DDL, any DML and sample data.
Thanks|||Here ya go. Most of the tables are very simplistic. I start by deleting all of the records then executing the DBCC CHECKIDENT ('table_name', RESEED, 0) in query analyzer against the table. Here's the ddl for one of the tables. 90% of the time the identity value starts at 1 the other 10% start at 0.
t_unique_id = int
t_varchar = varchar(50)
t_flag = int with rule limiting values to 1 or 0
t_guid = char(36)
/*================================================= =============*/
/* Table: producer_type */
/*================================================= =============*/
create table dbo.producer_type (
producer_type_id t_unique_id identity,
producer_type_name t_varchar not null,
org_flag t_flag not null,
description t_long_varchar not null,
comments t_long_varchar not null,
created_by t_unique_id not null,
created_dt datetime not null,
changed_by t_unique_id not null,
changed_dt datetime not null,
logical_unit_of_work_id t_guid not null
)
go
alter table dbo.producer_type
add constraint pk_producer_type primary key (producer_type_id)
with
fillfactor= 90
go
/*================================================= =============*/
/* Index: ux_producer_type_01 */
/*================================================= =============*/
create unique index ux_producer_type_01 on dbo.producer_type (
producer_type_name
)
with
fillfactor= 90
go|||I'll take a look, but why don't you specify the seed an increment in the DDL?
I'm sure there's a default...but...
I've always set IDENTITY(1,1) (that is when I have to use it...I try to avoid it)|||Just out of curiosity...how are you building this table?
You can't do what you're doing unless you use dynamic sql...unless you're building from the application layer, and if you are, what's with the GO?
Also out of curiosity, it sounds like whatever process your doing is placing an undue amount of meaning to the identity column.
Why does it need to be reset to 1 (or 0)|||Sorry, these are in no particular order:
First, I'm building the tables with ddl and executing with query builder. I didn't want to post all of the ddl for the user defined data types, fks, etc... The 'GO' is used as a terminator for the SQL. I'm building over 150 tables, pk's, fk's, indexes, etc.. in one script. It helps me if I encounter an error. The script will stop at that point and allow me to correct the problem and continue running.
Second, the default on the identity is (1,1)
Third, we are placing extra burden on the identity value because we are trying to migrate data into the new system from an existing system and are heavily coding at the same time. The developers need to know the primary key values for reference or metadata for codiing purposes. I know the order of the values going into the tables and as a standard told all of the developers the pk would start at 1. This hurts if it periodically starts at 0 because we are constantly rebuilding the database from the ddl.
Finally, I'm regretting the decision not to turn the identity insert on and specify the values on insert. I could change my scripts but it would take some time to do. I didn't know if there was something simple I was missing with the DBCC CHECKIDENT and RESEED.
Hope this helps, sorry for the long reply.|||Are you not using Query Analyzer?
And if you are, then how does this work?
t_unique_id = int
t_varchar = varchar(50)
t_flag = int with rule limiting values to 1 or 0
t_guid = char(36)
/ *=================================================
=============*/
/* Table: producer_type */
/ *=================================================
=============*/
create table dbo.producer_type (
producer_type_id t_unique_id identity,
producer_type_name t_varchar not null,
org_flag t_flag not null,
description t_long_varchar not null,
comments t_long_varchar not null,
created_by t_unique_id not null,
created_dt datetime not null,
changed_by t_unique_id not null,
changed_dt datetime not null,
logical_unit_of_work_id t_guid not null
)
go
You can't assign the datatype to a variable like that...unless it's a different interface/language...
I'm amazed at a 10% occurance...it's not often that the same process behave differently.
Since you're blowing the data away anyway, can you just do a drop, then recreate the table?
It will save on logging.
Why not create a shell db and recreate it when you need. Then have load scripts...or better just dump a "clean slate" db, and restore over...
and I'm sure NONE of these suggestion will work for you because of either RI, developer data, yada yada yada...
And in lieu of the fact that you're having these problems...why not give it a try and build the table with IDENTITY(1,1).
My Own Opinion (MOO)
Good Luck
DBCC CheckIdent message value to application
Hi,
I'm trying to get the value that DBCC CheckIdent is returning, I could
see the message in the query analizer, but I'm unable to get this value into
my application since its not a results set or return value its just a
message, is there a way to accomplish it?, I want to give a way in my
application for the end user to view the next "Identity value" and they
should be able to change it, but I can't get that value.
Thanks in advance
Shloma Baum| I'm trying to get the value that DBCC CheckIdent is returning, I could
| see the message in the query analizer, but I'm unable to get this value
into
| my application since its not a results set or return value its just a
| message, is there a way to accomplish it?, I want to give a way in my
| application for the end user to view the next "Identity value" and they
| should be able to change it, but I can't get that value.
--
A workaround would be to use OSQL to pipe the result of DBCC CheckIdent
into a textfile and then get the application to read that file.
Another workaround is to select the @.@.identity into a variable immediately
after an insert operation.
But what you're trying to accomplish does not scale too well. In a busy
data entry environment, there's a high probability that you will insert
duplicate values.
Hope this helps,
--
Eric Cárdenas
SQL Server support
I'm trying to get the value that DBCC CheckIdent is returning, I could
see the message in the query analizer, but I'm unable to get this value into
my application since its not a results set or return value its just a
message, is there a way to accomplish it?, I want to give a way in my
application for the end user to view the next "Identity value" and they
should be able to change it, but I can't get that value.
Thanks in advance
Shloma Baum| I'm trying to get the value that DBCC CheckIdent is returning, I could
| see the message in the query analizer, but I'm unable to get this value
into
| my application since its not a results set or return value its just a
| message, is there a way to accomplish it?, I want to give a way in my
| application for the end user to view the next "Identity value" and they
| should be able to change it, but I can't get that value.
--
A workaround would be to use OSQL to pipe the result of DBCC CheckIdent
into a textfile and then get the application to read that file.
Another workaround is to select the @.@.identity into a variable immediately
after an insert operation.
But what you're trying to accomplish does not scale too well. In a busy
data entry environment, there's a high probability that you will insert
duplicate values.
Hope this helps,
--
Eric Cárdenas
SQL Server support
Subscribe to:
Posts (Atom)