DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production serve
Thank
Har
MCDB
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and adde
some indexes to them
This caused the mdf file size to increase from 82GB to 109GB. Becaus
our development and training boxes do not have enough disk space,
tried to shrink the file so that the database can fit into th
development box when I do a restore
I created a job to run the following at 3AM
DBCC SHRINKFILE (N'Nu_Data', 85000
At 6AM, the job crashed with the following error
Transaction (Process ID 66) was deadlocked on lock resources wit
another process and has been chosen as the deadlock victim. Rerun th
transaction
Our database is running transactional replication. Do I need to paus
the replication and set the database in single user mode befor
executing the DBCC SHRINKFILE (N'N_Data', 85000)
alter database N
set single_user with ROLLBACK IMMEDIATE
g
DBCC SHRINKFILE (N'Nu_Data', 85000
g
alter database N
set multi_use
g

DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi,
Execute DBCC SHRINKFILE when there is no active transacton in progress for t
hat database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a no
rmal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database.
Preferably do not schedule this as a job, rather do it manually if it is a
production server
Thanks
Hari
MCDBA
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go

DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go
Hi,
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production server
Thanks
Hari
MCDBA
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go
sql

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
reclain that free space and on some database it's dropping the size from 30gb
down to 10gb with a few gb of free space which is great, but on some it's not
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.
Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/de..._dbcc_8b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:

> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been around
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
> reclain that free space and on some database it's dropping the size from 30gb
> down to 10gb with a few gb of free space which is great, but on some it's not
> reclaiming that space. I've tried using the command with the truncate only
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% of
> the database size as free space. I'm using the update usage command as well
> to get things in line. Any help would be appreciated. Thanks.

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
reclain that free space and on some database it's dropping the size from 30gb
down to 10gb with a few gb of free space which is great, but on some it's not
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:
> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been around
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
> reclain that free space and on some database it's dropping the size from 30gb
> down to 10gb with a few gb of free space which is great, but on some it's not
> reclaiming that space. I've tried using the command with the truncate only
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% of
> the database size as free space. I'm using the update usage command as well
> to get things in line. Any help would be appreciated. Thanks.

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command t
o
reclain that free space and on some database it's dropping the size from 30g
b
down to 10gb with a few gb of free space which is great, but on some it's no
t
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/d...
b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:

> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been aroun
d
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command
to
> reclain that free space and on some database it's dropping the size from 3
0gb
> down to 10gb with a few gb of free space which is great, but on some it's
not
> reclaiming that space. I've tried using the command with the truncate onl
y
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% o
f
> the database size as free space. I'm using the update usage command as we
ll
> to get things in line. Any help would be appreciated. Thanks.

DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
thorough job than INDEXDEFRAG.
Thanks in advance.
Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> thorough job than INDEXDEFRAG.
> Thanks in advance.
|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:

> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
>

DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
thorough job than INDEXDEFRAG.
Thanks in advance.Hi
read the following page
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:629A02AB-2F02-4210-9B57-463D111C47A8@.microsoft.com...
> We have a client who switched from using DBCC DBREINDEX to DBCC
> INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a
> more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:
> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
> >
> > We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> > because DBREINDEX caused some blocking issues. We suggested that DBCC
> > DBREINDEX does a more thorough job of handling fragmentation and that they
> > should really try to figure out what blocking is being caused instead of
> > switching to INDEXDEFRAG.
> > Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> > INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> > thorough job than INDEXDEFRAG.
> > Thanks in advance.
>

DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a mor
e
thorough job than INDEXDEFRAG.
Thanks in advance.Hi
read the following page
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:629A02AB-2F02-4210-9B57-463D111C47A8@.microsoft.com...
> We have a client who switched from using DBCC DBREINDEX to DBCC
> INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a
> more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRA
G
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a m
ore
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:

> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
>

DBCC CheckDB Repair_Rebuild caused data loss.

Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBC
C command would not cause any data loss, which also noted in T-SQL Reference
s:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to back
ups, I was able to get those rows back.) Is data loss possible while using R
epair_rebuild option?
I got following messages:
...
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (
1:242059), slot 14. Did not encounter forwarded row. Possible allocation err
or.
(I believe 285 times.)
...
The error has been repaired.
(I believe 285 times.)
...
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:2712
2), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'table
name'. (object ID 1656497080).
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'da
tabasename'.
...
Thanks,
Akin HocaI tried following DBCC command: DBCC CHECKDB (dadabase name, Repair_Rebuild)
not DBCC CHECKDB (dadabase name, REPAIR_FAST)
Thanks,
Akin
-- Akin wrote: --
Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBC
C command would not cause any data loss, which also noted in T-SQL Reference
s:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to back
ups, I was able to get those rows back.) Is data loss possible while using R
epair_rebuild option?
I got following messages:
..
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (
1:242059), slot 14. Did not encounter forwarded row. Possible allocation err
or.
(I believe 285 times.)
..
The error has been repaired.
(I believe 285 times.)
..
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:2712
2), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
..
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'table
name'. (object ID 1656497080).
..
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'da
tabasename'.
..
Thanks,
Akin Hoca|||You didn't lose any data as part of the repair. What happened was that you
had 285 forwarding records point to non-existent records.
When a heap record is updated such that it grows and there is no space for
the record to expand, it is moved to a new location in the heap. This new
location is called a forwarded record. The old location then contains a
pointer to the new location. The pointer in the old location is called a
forwarding record. Non-clustered indexes on heaps refer back to the heap
records using a physical record identifier (i.e. the file/page/slot of the
record). Using the forwarded row mechanism means that updates to heap
records do not cause updates to non-clustered indexes on the heap. It's an
optimization mechanism.
So, your heap had 285 of the pointer records in that did not point to
updated copies of the original record. These were deleted by repair and this
is allowed under repair_rebuild, because no data is being lost. The
non-clustered indexes were rebuilt because they would have referenced the
285 pointer records, and so must be rebuilt to remove those references.
Please let me know if that did not make sense.
Now, the more interesting question is how did this situation arise in the
first place? That I do not know and you may want to call PSS for assistance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Akin" <anonymous@.discussions.microsoft.com> wrote in message
news:7E85386A-0102-4B30-8BF1-BB123342C065@.microsoft.com...
> I tried following DBCC command: DBCC CHECKDB (dadabase name,
Repair_Rebuild)
> not DBCC CHECKDB (dadabase name, REPAIR_FAST)
> Thanks,
> Akin
>
> -- Akin wrote: --
> Hi,
> I tried Repair_Rebuild option in 7.0 . I was thinking that the
following DBCC command would not cause any data loss, which also noted in
T-SQL References:
> DBCC CHECKDB (dadabase name, REPAIR_FAST)
> I lost 285 rows in a table that has total 103 thousand rows. (Thanks
to backups, I was able to get those rows back.) Is data loss possible while
using Repair_rebuild option?
> I got following messages:
> ...
> Server: Msg 8993, Level 16, State 1, Line 0
> Object ID 1656497080, forwarding row page (1:27122), slot 9 points to
page (1:242059), slot 14. Did not encounter forwarded row. Possible
allocation error.
> (I believe 285 times.)
> ...
> The error has been repaired.
> (I believe 285 times.)
> ...
> Repair: Deleted record for object ID 1656497080, index ID 0, on page
(1:27122), slot 9. Indexes will be rebuilt.
> (I believe 285 times.)
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in table
'tablename'. (object ID 1656497080).
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in
database 'databasename'.
> ...
> Thanks,
> Akin Hoca

DBCC CheckDB Repair_Rebuild caused data loss.

Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBCC command would not cause any data loss, which also noted in T-SQL References:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to backups, I was able to get those rows back.) Is data loss possible while using Repair_rebuild option?
I got following messages:
...
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (1:242059), slot 14. Did not encounter forwarded row. Possible allocation error.
(I believe 285 times.)
...
The error has been repaired.
(I believe 285 times.)
...
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:27122), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'tablename'. (object ID 1656497080).
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'databasename'.
...
Thanks,
Akin Hoca
I tried following DBCC command: DBCC CHECKDB (dadabase name, Repair_Rebuild)
not DBCC CHECKDB (dadabase name, REPAIR_FAST)
Thanks,
Akin
-- Akin wrote: --
Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBCC command would not cause any data loss, which also noted in T-SQL References:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to backups, I was able to get those rows back.) Is data loss possible while using Repair_rebuild option?
I got following messages:
...
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (1:242059), slot 14. Did not encounter forwarded row. Possible allocation error.
(I believe 285 times.)
...
The error has been repaired.
(I believe 285 times.)
...
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:27122), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'tablename'. (object ID 1656497080).
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'databasename'.
...
Thanks,
Akin Hoca
|||You didn't lose any data as part of the repair. What happened was that you
had 285 forwarding records point to non-existent records.
When a heap record is updated such that it grows and there is no space for
the record to expand, it is moved to a new location in the heap. This new
location is called a forwarded record. The old location then contains a
pointer to the new location. The pointer in the old location is called a
forwarding record. Non-clustered indexes on heaps refer back to the heap
records using a physical record identifier (i.e. the file/page/slot of the
record). Using the forwarded row mechanism means that updates to heap
records do not cause updates to non-clustered indexes on the heap. It's an
optimization mechanism.
So, your heap had 285 of the pointer records in that did not point to
updated copies of the original record. These were deleted by repair and this
is allowed under repair_rebuild, because no data is being lost. The
non-clustered indexes were rebuilt because they would have referenced the
285 pointer records, and so must be rebuilt to remove those references.
Please let me know if that did not make sense.
Now, the more interesting question is how did this situation arise in the
first place? That I do not know and you may want to call PSS for assistance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Akin" <anonymous@.discussions.microsoft.com> wrote in message
news:7E85386A-0102-4B30-8BF1-BB123342C065@.microsoft.com...
> I tried following DBCC command: DBCC CHECKDB (dadabase name,
Repair_Rebuild)
> not DBCC CHECKDB (dadabase name, REPAIR_FAST)
> Thanks,
> Akin
>
> -- Akin wrote: --
> Hi,
> I tried Repair_Rebuild option in 7.0 . I was thinking that the
following DBCC command would not cause any data loss, which also noted in
T-SQL References:
> DBCC CHECKDB (dadabase name, REPAIR_FAST)
> I lost 285 rows in a table that has total 103 thousand rows. (Thanks
to backups, I was able to get those rows back.) Is data loss possible while
using Repair_rebuild option?
> I got following messages:
> ...
> Server: Msg 8993, Level 16, State 1, Line 0
> Object ID 1656497080, forwarding row page (1:27122), slot 9 points to
page (1:242059), slot 14. Did not encounter forwarded row. Possible
allocation error.
> (I believe 285 times.)
> ...
> The error has been repaired.
> (I believe 285 times.)
> ...
> Repair: Deleted record for object ID 1656497080, index ID 0, on page
(1:27122), slot 9. Indexes will be rebuilt.
> (I believe 285 times.)
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in table
'tablename'. (object ID 1656497080).
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in
database 'databasename'.
> ...
> Thanks,
> Akin Hoca