dbcc freeprocache/dropcleanbuffers - please help

sql server 2000 Enterprise sp3a
I am running a loop in Query Analyzer as follows (pseudo coded) for some
testing
that I'm doing.
declare @.var int, @.id int
set @.var = 1
while (@.var < 4)
begin
begin tran
dbcc freeproccache
dbcc dropcleanbuffers
print 'start delete at : ' + cast (getdate() as varchar)
set @.id = 10
delete from mytable where @.id = 10
print 'end delete at : ' + cast (getdate() as varchar)
rollback tran
set @.var = @.var + 1
end
I'm getting weird execution times for each delete. For instance,
the first pass shows me an execution time of around 500ms which
I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
3ms. I used set statistics io on to check the reads and the number
of physical reads drops. I don't know why the dbcc commands
aren't working. Is it the transaction? Any ideas? There is no
other activity on the server... it's my personal box. Please help.You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word "clean", hence adding a
checkpoint will result in all pages clean in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> sql server 2000 Enterprise sp3a
> I am running a loop in Query Analyzer as follows (pseudo coded) for some
> testing
> that I'm doing.
>
> declare @.var int, @.id int
> set @.var = 1
> while (@.var < 4)
> begin
> begin tran
> dbcc freeproccache
> dbcc dropcleanbuffers
> print 'start delete at : ' + cast (getdate() as varchar)
> set @.id = 10
> delete from mytable where @.id = 10
> print 'end delete at : ' + cast (getdate() as varchar)
> rollback tran
> set @.var = @.var + 1
> end
>
> I'm getting weird execution times for each delete. For instance,
> the first pass shows me an execution time of around 500ms which
> I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> 3ms. I used set statistics io on to check the reads and the number
> of physical reads drops. I don't know why the dbcc commands
> aren't working. Is it the transaction? Any ideas? There is no
> other activity on the server... it's my personal box. Please help.
>|||Hi Tibor
Thank you
I ended up taking the transaction out (the begin transaction and rollback).
What I did instead
- inserted the rows into a temp table
- performed my delete
- re-inserted the rows
I repeated the delete/re-insert within the loop
When I did this, my problem went away. What do you think the transaction
was doing or not
doing? Would it be a log cache issue?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6ZDWR72GHA.4632@.TK2MSFTNGP03.phx.gbl...
> You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word
"clean", hence adding a
> checkpoint will result in all pages clean in the database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> > sql server 2000 Enterprise sp3a
> >
> > I am running a loop in Query Analyzer as follows (pseudo coded) for some
> > testing
> > that I'm doing.
> >
> >
> > declare @.var int, @.id int
> >
> > set @.var = 1
> > while (@.var < 4)
> > begin
> >
> > begin tran
> >
> > dbcc freeproccache
> > dbcc dropcleanbuffers
> >
> > print 'start delete at : ' + cast (getdate() as varchar)
> > set @.id = 10
> > delete from mytable where @.id = 10
> > print 'end delete at : ' + cast (getdate() as varchar)
> >
> > rollback tran
> >
> > set @.var = @.var + 1
> > end
> >
> >
> > I'm getting weird execution times for each delete. For instance,
> > the first pass shows me an execution time of around 500ms which
> > I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> > 3ms. I used set statistics io on to check the reads and the number
> > of physical reads drops. I don't know why the dbcc commands
> > aren't working. Is it the transaction? Any ideas? There is no
> > other activity on the server... it's my personal box. Please help.
> >
> >
>

DBCC CHECKDB Error - Help Required

I have a customer who has reported some database corruption. The output of
DBCC CHECKDB ('CheckDB') is as follows:
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'CheckDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB ).
(1 row(s) affected)
Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
results:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'LincsDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB repair_rebuild).
The statement has been terminated.
(1 row(s) affected)
I can resolve the issue by dropping and recreating index id: 2 but this
involves dropping and re-creating a lot or related constraints (this is a
primary key).
Can anyone give me any pointers as to why this corruption may have occurred.
I have not seen this on any other customer databases so I don't think it is
an application error? Also, any suggestions on a better (simpler) way to
correct this problem?
Unfortunatley, the customers last viable backup is a couple of weeks out of
date and they are reluctant to restore and re-enter all the new data again.
Any help greatly appreciated,
Dave.I think that you could solve the problem by rebuilding the clustered index
with:
DBCC DBREINDEX (1685581043, 1)
This rebuilds the nonclustered index and at the same time fix the issues in
the nonclustered index 2. I am not 100% sure it will work in this situation.
These indexing problems can have a variety of causes. Most common are server
crashes, when a data page is only incompletely written to disk, and harddisk
errors. Make sure you have torn page detection on on your database, so you
get notified after a server restart if there is a similar problem, and check
the harddisk for any issues.
--
Jacco Schalkwijk
SQL Server MVP
"David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>I have a customer who has reported some database corruption. The output of
> DBCC CHECKDB ('CheckDB') is as follows:
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'CheckDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB ).
> (1 row(s) affected)
> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
> results:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '312037'.
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'LincsDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB repair_rebuild).
> The statement has been terminated.
> (1 row(s) affected)
> I can resolve the issue by dropping and recreating index id: 2 but this
> involves dropping and re-creating a lot or related constraints (this is a
> primary key).
> Can anyone give me any pointers as to why this corruption may have
> occurred.
> I have not seen this on any other customer databases so I don't think it
> is
> an application error? Also, any suggestions on a better (simpler) way to
> correct this problem?
> Unfortunatley, the customers last viable backup is a couple of weeks out
> of
> date and they are reluctant to restore and re-enter all the new data
> again.
> Any help greatly appreciated,
> Dave.|||Rebuilding the clustered index does not necessarily rebuild the
non-clustered indexes on the table. If the clustered index was created as a
unique index (e.g. Primary key), then the non-clustered indexes are not
rebuilt. They are rebuilt if the clustered index was non-unique.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23OiY4EK2FHA.3188@.TK2MSFTNGP14.phx.gbl...
>I think that you could solve the problem by rebuilding the clustered index
>with:
> DBCC DBREINDEX (1685581043, 1)
> This rebuilds the nonclustered index and at the same time fix the issues
> in the nonclustered index 2. I am not 100% sure it will work in this
> situation.
> These indexing problems can have a variety of causes. Most common are
> server crashes, when a data page is only incompletely written to disk, and
> harddisk errors. Make sure you have torn page detection on on your
> database, so you get notified after a server restart if there is a similar
> problem, and check the harddisk for any issues.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
> news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>>I have a customer who has reported some database corruption. The output of
>> DBCC CHECKDB ('CheckDB') is as follows:
>> Server: Msg 8935, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The previous link
>> (1:76577)
>> on page (1:76578) does not match the previous page (1:253212) that the
>> parent
>> (1:64277), slot 165 expects for this page.
>> Server: Msg 8936, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
>> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
>> Server: Msg 8934, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The high key value on page
>> (1:253212) (level 0) is not less than the low key value in the parent
>> (0:1),
>> slot 0 of the next page (1:76578).
>> Server: Msg 8978, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
>> a
>> reference from previous page (1:76577). Possible chain linkage problem.
>> CHECKDB found 0 allocation errors and 4 consistency errors in table
>> 'Student' (object ID 1685581043).
>> CHECKDB found 0 allocation errors and 4 consistency errors in database
>> 'CheckDB'.
>> repair_rebuild is the minimum repair level for the errors found by DBCC
>> CHECKDB (LincsDB ).
>> (1 row(s) affected)
>> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the
>> following
>> results:
>> Server: Msg 1505, Level 16, State 1, Line 1
>> CREATE UNIQUE INDEX terminated because a duplicate key was found for
>> index
>> ID 2. Most significant primary key is '312037'.
>> Server: Msg 8935, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The previous link
>> (1:76577)
>> on page (1:76578) does not match the previous page (1:253212) that the
>> parent
>> (1:64277), slot 165 expects for this page.
>> Server: Msg 8936, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
>> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
>> Server: Msg 8934, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The high key value on page
>> (1:253212) (level 0) is not less than the low key value in the parent
>> (0:1),
>> slot 0 of the next page (1:76578).
>> Server: Msg 8978, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
>> a
>> reference from previous page (1:76577). Possible chain linkage problem.
>> Could not repair this error.
>> Repairing this error requires other errors to be corrected first.
>> Repairing this error requires other errors to be corrected first.
>> Repairing this error requires other errors to be corrected first.
>> CHECKDB found 0 allocation errors and 4 consistency errors in table
>> 'Student' (object ID 1685581043).
>> CHECKDB found 0 allocation errors and 4 consistency errors in database
>> 'LincsDB'.
>> repair_rebuild is the minimum repair level for the errors found by DBCC
>> CHECKDB (LincsDB repair_rebuild).
>> The statement has been terminated.
>> (1 row(s) affected)
>> I can resolve the issue by dropping and recreating index id: 2 but this
>> involves dropping and re-creating a lot or related constraints (this is a
>> primary key).
>> Can anyone give me any pointers as to why this corruption may have
>> occurred.
>> I have not seen this on any other customer databases so I don't think it
>> is
>> an application error? Also, any suggestions on a better (simpler) way to
>> correct this problem?
>> Unfortunatley, the customers last viable backup is a couple of weeks out
>> of
>> date and they are reluctant to restore and re-enter all the new data
>> again.
>> Any help greatly appreciated,
>> Dave.
>

DBCC CHECKDB

We have a number of databases where we are getting
consistency errors when we run DBCC CHECKDB. A sample
error is as follows:
Table error: Object ID 1961774046, index ID 0, page
(1:11920), row 1. Record check (Valid SqlVariant) failed.
Values are 4 and 0.
Server: Msg 2537, Level 16, State 1, Line 1
Table error: Object ID 1961774046, index ID 0, page
(1:11920), row 2. Record check (innerLength <= innerColInfo.CbMaxLen () && innerPCol != NULL) failed.
Values are 17 and 5.
.
CHECKDB found 0 allocation errors and 15011 consistency
errors in table 'SurveyDataElement' (object ID
1961774046).
We can't use any of the fix options as this results in
considerable data loss. Restoring backups is not an
option.
The problem itself also seems to be inconsistent. If we
detach the problem database and then attach it on another
server, running DBCC gives us error messages but for
different tables.
Any help would be seriously appreciated.
MartinFurther to previous post:
SQL Server 2000 SP3|||Martin,
If you can't restore from a backup then you don't have many options other
than to do some type of repair. This may indeed result in data loss but
that is why it is so important to do regular backups and DBCC's to catch
these types of issues before they get to this stage. I suggest you give MS
PSS a call and see if there is anything they can do as your options are now
limited.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"MartinMcD" <MMcDonnell@.dial.pipex.com> wrote in message
news:01b801c379d5$d64c5eb0$a001280a@.phx.gbl...
> We have a number of databases where we are getting
> consistency errors when we run DBCC CHECKDB. A sample
> error is as follows:
> Table error: Object ID 1961774046, index ID 0, page
> (1:11920), row 1. Record check (Valid SqlVariant) failed.
> Values are 4 and 0.
> Server: Msg 2537, Level 16, State 1, Line 1
> Table error: Object ID 1961774046, index ID 0, page
> (1:11920), row 2. Record check (innerLength <=> innerColInfo.CbMaxLen () && innerPCol != NULL) failed.
> Values are 17 and 5.
> .
> CHECKDB found 0 allocation errors and 15011 consistency
> errors in table 'SurveyDataElement' (object ID
> 1961774046).
> We can't use any of the fix options as this results in
> considerable data loss. Restoring backups is not an
> option.
> The problem itself also seems to be inconsistent. If we
> detach the problem database and then attach it on another
> server, running DBCC gives us error messages but for
> different tables.
> Any help would be seriously appreciated.
> Martin|||Andrew,
Thanks for the reply, we'll try PSS and we do have some backups - it
will just take days to sort through them.
Any idea what might cause there errors as one of the databases was
only built a week or so ago whilst others have been around for months?
I also don't understand why the same database gives different checkdb
results when attached to different servers. I am concerned that we
avoid these problems in future but as its happened on over 50% of our
databases I don't have a great deal of faith that it won't.
Martin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Martin,
>
>If you can't restore from a backup then you don't have many options other
>than to do some type of repair. This may indeed result in data loss but
>that is why it is so important to do regular backups and DBCC's to catch
>these types of issues before they get to this stage. I suggest you give MS
>PSS a call and see if there is anything they can do as your options are now
>limited.
>http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
>http://www.mssqlserver.com/faq/general-pss.asp MS PSS