SQL Server - effect of DBCC CHECKDB and DBCC DBREINDEX on the
Transaction log - Feb 27, 2010 at 11:50 AM by Shuchi Gauri
What is the effect of DBCC CHECKDB and DBCC DBREINDEX on the Transaction log?
DBCC DBREINDEX process occurs as a single atomic transaction. This process is
logged by default and attempts to write to disk whenever a CHECKPOINT or BACKUP
LOG process is initiated. To accommodate space, SQL Server tries to find space
in data and log files.
DBCC CHECKDB performs physical consistency check on indexed views and validates
the integrity of every object in database by collecting information and there
onwards scans the log for any changes made. It merges these 2 information
sources to provide a single view of information as the scan ends. Affect of
redo and undo of transactions:
-
Log records from transactions that commit during that time to generate redo
facts.
-
Log records from transactions that rollback or don’t commit during that time
are used to generate undo facts.
SQL Server - effect of DBCC CHECKDB and DBCC DBREINDEX on the
Transaction log - May 05, 2009 at 22:00 PM by Rajmeet Ghai
What is the effect of DBCC CHECKDB and DBCC DBREINDEX on the Transaction log?
DBCC DBREINDEX is an offline operation is used to rebuild the indexes of a
table dynamically. This operation requires enough space in the data files. If
the space is not enough DBCC DBREINDEX may be unable to rebuild the indexes.
DBCC CHECKDB is used to produce a consistent view of the data by performing a
physical consistency check on indexed views, validating integrity of the
indexes, objects etc. in earlier versions of SQL, this required locking. Newer
versions involve reading the transaction log of the oldest active transaction.
REDO and UNDO of the transactions affect the volatile changes to available free
space.
SQL Server - Effect of DBCC CHECKDB and DBCC DBREINDEX on the
Transaction log - June 21, 2009 at 09:00 AM by Amit Satpute
DBCC DBREINDEX is an offline operation. It can be used to rebuild one or more
indexes for a specific table.
The underlying table of this operation is unavailable to users of the database,
while it is running.
It rebuilds indexes dynamically
During dynamic rebuild of indexes, the page density levels are restored to the
original fill-factor.
DBCC CHECKDB process performs a physical consistency check on indexed views. It
also validates the integrity of every object in a database by collecting the
information, and then scans the log for any additional changes made, merging
the two sets of information together to produce a consistent view of the data
at the end of the scan.
In SQL Server 2000, the database transaction log is used and read to get a
consistent view in order to run the CHECKDB online effectively.
The effect of REDO and UNDO of the transactions are as follows:
-
The committed transaction log records at respective time are used to generate
REDO facts.
-
The uncommitted/roll backed transaction log records during that time are used
to generate UNDO facts.
Also read
Database Consistency Checker Commands give details in form of statistics about
the SQL Server. They can be used for Maintenance of database, index, or
filegroup..............
Explain the concepts of faster differential backups.
Explain the concepts of Parallel Database consistency check (DBCC)
Define Indexed view.
Define Distributed partitioned views.
Database backup methods - Full Backups, Differential Backups, Transaction Log
Architecture Backups, File and Filegroup Backups.
What operations do SQL Server transaction logs support?
Explain the purpose of check points in a transaction log.
What is write-ahead transaction log?.............
RAID is a mechanism of storing the same data in different locations. Since the
same data is stored, it is termed as redundant............
|
