Overview
Every Microsoft SQL Server 2000 database has a transaction log that records all
transactions and the database modifications made by each transaction. This record of
transactions and their modifications supports three operations:
- Recovery of individual transactions
- Recovery of all incomplete transactions when SQL Server is started
- Rolling a restored database forward to the point of failure
If log records were never deleted from the transaction log, the logical log would grow
until it filled all the available space on the disks holding the physical log
files. At some point in time, old log records no longer necessary for recovering or
restoring a database must be deleted to make way for new log records.
SQL Server 2000 has to options to decrease the size of the Transaction Log:
| |
1. |
Log truncation does not reduce the
size of a physical log file, it reduces the size of the logical log and marks as
inactive the virtual logs that do not hold any part of the logical log. |
|
2. |
Log shrinking removes enough inactive
virtual logs to reduce the log file to the requested size. |
Example
Here is an example how boths steps can be performed:
Database is in FULL Recovery Mode
# For this example we switch to FULL
Mode
USE master
ALTER DATABASE Curia SET RECOVERY FULL;
GO
The command(s) completed successfully.
# Add logical Devices for the Backup (The
directories must exist!)
EXEC sp_addumpdevice 'disk', 'curia_dat',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\curia_dat.dat'
GO
(1 row(s) affected)
'Disk' device added.
EXEC sp_addumpdevice 'disk', 'curia_log',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\curia_log.dat'
GO
(1 row(s) affected)
'Disk' device added.
# Create a Backup before
Truncating / Shrinking
BACKUP DATABASE Curia TO curia_dat
GO
Processed 26392 pages for database 'Curia', file
'MigrationBasisplus_Data' on file 9.
Processed 1 pages for database 'Curia', file 'MigrationBasisplus_Log' on file 9.
BACKUP DATABASE successfully processed 26393 pages in 9.756 seconds (22.161
MB/sec).
BACKUP LOG Curia TO curia_log
GO
Processed 1 pages for database 'Curia', file
'MigrationBasisplus_Log' on file 5.
BACKUP LOG successfully processed 1 pages in 0.065 seconds (0.039 MB/sec).
# Truncate the Transaction Log
BACKUP LOG Curia WITH TRUNCATE_ONLY
GO
The command(s) completed successfully.
# Drop logical Devices
sp_dropdevice 'curia_dat'
GO
Device dropped.
sp_dropdevice 'curia_log'
GO
Device dropped.
# Get the Name of the Transaction Log
USE curia
SELECT name FROM dbo.sysfiles
GO
# Shrink the physical Size of the Transaction Log to
20MB
USE curia
DBCC SHRINKFILE (MigrationBasisplus_Log, 20)
GO
Database is in SIMPLE Recovery Mode
# For this example we switch to SIMPLE
Mode
USE master
ALTER DATABASE Curia SET RECOVERY SIMPLE;
GO
The command(s) completed successfully.
# Add logical Device for the Backup (The directories must
exist!)
EXEC sp_addumpdevice 'disk', 'curia_dat',
'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\curia_dat.dat'
GO
(1 row(s) affected)
'Disk' device added.
# Create a Backup before Truncating / Shrinking
BACKUP DATABASE Curia TO curia_dat
GO
Processed 26392 pages for database 'Curia', file
'MigrationBasisplus_Data' on file 9.
Processed 1 pages for database 'Curia', file 'MigrationBasisplus_Log' on file 9.
BACKUP DATABASE successfully processed 26393 pages in 9.756 seconds (22.161
MB/sec).
# Truncate the Transaction Log
BACKUP LOG Curia WITH TRUNCATE_ONLY
GO
The command(s) completed successfully.
# Drop logical Device
sp_dropdevice 'curia_dat'
GO
Device dropped.
# Get the Name of the Transaction Log
USE curia
SELECT name FROM dbo.sysfiles
GO
The command(s) completed successfully.
# Shrink the physical Size of the Transaction Log to
20MB
USE curia
DBCC SHRINKFILE (MigrationBasisplus_Log, 20)
GO
More Information
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_876t.asp
|
