would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
while its running. Ive got a lot of extra space on some data files that I
want to shrink and was wondering if its safe to do it during our peak
hours... What does it do internally ? Any locking ,etc..Using SQL 2000Yes. It issues a lot of IO and takes short term X page locks. In internal
tests we've seen up to 20% drop in transaction throughput, depending on the
exact workload and hardware configuration. This is unavoidable due to the
operations shrink has to perform.
What proportion of the database size is free-space? Consider not doing the
shrink unless you're really desperate for the disk space or you *know* the
database size won't grow again. If you shrink, the odds are that the
database will have to grow again anyway. As always, depends on your exact
workload etc etc
It is always 'safe' to do a shrink.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uBcDfwnFEHA.2876@.TK2MSFTNGP09.phx.gbl...
> would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
> while its running. Ive got a lot of extra space on some data files that I
> want to shrink and was wondering if its safe to do it during our peak
> hours... What does it do internally ? Any locking ,etc..Using SQL 2000
>
Showing posts with label cause. Show all posts
Showing posts with label cause. Show all posts
Thursday, March 29, 2012
Thursday, March 22, 2012
DBCC Results into a Table Brings Errors!
If you run the script below, it will cause an error. However, if you
run the select portion after the error, it will have accomplished the
desired result. Unfortunately, the table has to be manually dropped
after the proc is run. Any ideas why'? USing the GO keyword is not
an option as it will blow the rest of the script.
CREATE TABLE #db_file_information(
fileid integer,
theFileGroup integer,
Total_Extents integer,
Used_Extents integer,
db varchar(30),
file_Path_name varchar(300)--,
-- File_Free_space decimal(15,2),
-- Percent_Free decimal(15,2)
)
-- Get the size of the datafiles
insert into #db_file_information exec('DBCC showfilestats')
-- add two columns to the temp table
alter table #db_file_information add dude as
((Total_Extents-Used_Extents)/(Total_extents*1.0))
select * from #db_file_information
drop table #db_file_informationTry,
use northwind
go
CREATE TABLE #db_file_information(
Fileid bigint,
[FileGroup] bigint,
TotalExtents bigint,
UsedExtents bigint,
[Name] sysname,
[FileName] varchar(260)
)
go
-- Get the size of the datafiles
insert into #db_file_information
exec('DBCC showfilestats with NO_INFOMSGS')
-- add two columns to the temp table
alter table #db_file_information
add dude as ((TotalExtents - UsedExtents) / nullif((TotalExtents * 1.0), 0))
go
select * from #db_file_information
go
drop table #db_file_information
go
AMB
"dpaskiet@.comcast.net" wrote:
> If you run the script below, it will cause an error. However, if you
> run the select portion after the error, it will have accomplished the
> desired result. Unfortunately, the table has to be manually dropped
> after the proc is run. Any ideas why'? USing the GO keyword is not
> an option as it will blow the rest of the script.
>
> CREATE TABLE #db_file_information(
> fileid integer,
> theFileGroup integer,
> Total_Extents integer,
> Used_Extents integer,
> db varchar(30),
> file_Path_name varchar(300)--,
> -- File_Free_space decimal(15,2),
> -- Percent_Free decimal(15,2)
> )
> -- Get the size of the datafiles
> insert into #db_file_information exec('DBCC showfilestats')
> -- add two columns to the temp table
> alter table #db_file_information add dude as
> ((Total_Extents-Used_Extents)/(Total_extents*1.0))
>
> select * from #db_file_information
>
> drop table #db_file_information
>
run the select portion after the error, it will have accomplished the
desired result. Unfortunately, the table has to be manually dropped
after the proc is run. Any ideas why'? USing the GO keyword is not
an option as it will blow the rest of the script.
CREATE TABLE #db_file_information(
fileid integer,
theFileGroup integer,
Total_Extents integer,
Used_Extents integer,
db varchar(30),
file_Path_name varchar(300)--,
-- File_Free_space decimal(15,2),
-- Percent_Free decimal(15,2)
)
-- Get the size of the datafiles
insert into #db_file_information exec('DBCC showfilestats')
-- add two columns to the temp table
alter table #db_file_information add dude as
((Total_Extents-Used_Extents)/(Total_extents*1.0))
select * from #db_file_information
drop table #db_file_informationTry,
use northwind
go
CREATE TABLE #db_file_information(
Fileid bigint,
[FileGroup] bigint,
TotalExtents bigint,
UsedExtents bigint,
[Name] sysname,
[FileName] varchar(260)
)
go
-- Get the size of the datafiles
insert into #db_file_information
exec('DBCC showfilestats with NO_INFOMSGS')
-- add two columns to the temp table
alter table #db_file_information
add dude as ((TotalExtents - UsedExtents) / nullif((TotalExtents * 1.0), 0))
go
select * from #db_file_information
go
drop table #db_file_information
go
AMB
"dpaskiet@.comcast.net" wrote:
> If you run the script below, it will cause an error. However, if you
> run the select portion after the error, it will have accomplished the
> desired result. Unfortunately, the table has to be manually dropped
> after the proc is run. Any ideas why'? USing the GO keyword is not
> an option as it will blow the rest of the script.
>
> CREATE TABLE #db_file_information(
> fileid integer,
> theFileGroup integer,
> Total_Extents integer,
> Used_Extents integer,
> db varchar(30),
> file_Path_name varchar(300)--,
> -- File_Free_space decimal(15,2),
> -- Percent_Free decimal(15,2)
> )
> -- Get the size of the datafiles
> insert into #db_file_information exec('DBCC showfilestats')
> -- add two columns to the temp table
> alter table #db_file_information add dude as
> ((Total_Extents-Used_Extents)/(Total_extents*1.0))
>
> select * from #db_file_information
>
> drop table #db_file_information
>
Sunday, March 11, 2012
dbcc indexdefrag
Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||In addition to the other posts:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||In addition to the other posts:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
dbcc indexdefrag
Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
Thursday, March 8, 2012
dbcc indexdefrag
Does dbcc indexdefrag cause transaction log growth? I've got a bit database
,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardles
s
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardles
s
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
Sunday, February 19, 2012
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
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
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
dbcc checkdb question
Are there any circumstances under which a dbcc checkdb with
no_infomsgs can *cause* a database corruption?
I've never heard about such...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pinney.colton@.gmail.com> wrote in message
news:1186518584.339570.70520@.57g2000hsv.googlegrou ps.com...
> Are there any circumstances under which a dbcc checkdb with
> no_infomsgs can *cause* a database corruption?
>
no_infomsgs can *cause* a database corruption?
I've never heard about such...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pinney.colton@.gmail.com> wrote in message
news:1186518584.339570.70520@.57g2000hsv.googlegrou ps.com...
> Are there any circumstances under which a dbcc checkdb with
> no_infomsgs can *cause* a database corruption?
>
dbcc checkdb question
Are there any circumstances under which a dbcc checkdb with
no_infomsgs can *cause* a database corruption?I've never heard about such...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pinney.colton@.gmail.com> wrote in message
news:1186518584.339570.70520@.57g2000hsv.googlegroups.com...
> Are there any circumstances under which a dbcc checkdb with
> no_infomsgs can *cause* a database corruption?
>
no_infomsgs can *cause* a database corruption?I've never heard about such...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pinney.colton@.gmail.com> wrote in message
news:1186518584.339570.70520@.57g2000hsv.googlegroups.com...
> Are there any circumstances under which a dbcc checkdb with
> no_infomsgs can *cause* a database corruption?
>
dbcc checkdb question
Are there any circumstances under which a dbcc checkdb with
no_infomsgs can *cause* a database corruption?I've never heard about such...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pinney.colton@.gmail.com> wrote in message
news:1186518584.339570.70520@.57g2000hsv.googlegroups.com...
> Are there any circumstances under which a dbcc checkdb with
> no_infomsgs can *cause* a database corruption?
>
no_infomsgs can *cause* a database corruption?I've never heard about such...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pinney.colton@.gmail.com> wrote in message
news:1186518584.339570.70520@.57g2000hsv.googlegroups.com...
> Are there any circumstances under which a dbcc checkdb with
> no_infomsgs can *cause* a database corruption?
>
Subscribe to:
Posts (Atom)