|
Is the transaction log file 20GB? Most frequent reason: database in FULL recovery model, but only FULL backups are done (no transaction log backups). In such a case put the database into SIMPLE recovery model for automatic log file management. Example script:
USE [master]
GO
ALTER DATABASE [AdventureWorks2008] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Backup the transaction log or the database (full backup) prior to log file shrinking. Exit all users from DB.
Simplest method: in SSMS Object Explorer change Recovery Model to SIMPLE (DB properties, options). Then shrink log file (DB tasks, shrink, file, File Type : log). Change database back to original FULL Recovery Model only if you plan to do transaction log backup for disaster recovery. For FULL DB backup only disaster recovery, leave database in SIMPLE mode.
Related warning article: Why you want to be restrictive with shrink of database files
The size of the
log files, if there are inactive portions, are physically reduced
in the following situations:
1. A DBCC SHRINKDATABASE
statement is executed
USE master;
DBCC SHRINKDATABASE (AdventureWorks, 10);
GO
NOTE:
A. A database shrink operation may increase fragmentation of the indexes. Therefore you should not shrink the database or a data file(s) after rebuilding indexes. Repeated shrink and growth of the database may lead to file system level fragmentation as well.
B. A database needs "elbow room" for proper operations. Therefore prior to shrinking the database, other options should be considered such as purging/archiving not needed/rarely used data, or adding more disk space.
Alternate script:
-- First do a FULL BACKUP with verification - sql server shrink database
-- This script will not work in SQL Server 2008 - sql server shrink log file
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY
go
DBCC SHRINKDATABASE (AdventureWorks, 10, TRUNCATEONLY)
go
2. A DBCC SHRINKFILE
statement referencing a log file is executed
USE AdventureWorks;
DBCC SHRINKFILE (AdventureWorks_Log, 10)
GO
3. An autoshrink
operation occurs (database option turned on)
NOTE: It is recommended that you keep the AUTOSHRINK option off, unless you have some requirement to keep it on (see Note for 1.).
Solutions for Transaction Log Size Reduction
Execute DBCC OPENTRAN to check for open transactions. If any, resolve issue prior to shrinking.
-- DBCC OPENTRAN Simulation
use AdventureWorks;
BEGIN TRANSACTION
update HumanResources.Department
set ModifiedDate=dateadd(day,0,ModifiedDate)
go
/***** OPEN A NEW CONNECTION FOR THE FOLLOWING *****/
DBCC OPENTRAN
/* DBCC OPENTRAN results:
Transaction information for database 'AdventureWorks'.
Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : user_transaction
LSN : (1541:376:414)
Start time : Dec 19 2008 7:01:47:670AM
SID : 0x01050000000000051500000033027d44f1457f89cf9b73b7ed030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
-- Use the SPID from above
DBCC INPUTBUFFER(55)
/* Results
EventType Parameters EventInfo
Language Event 0 use AdventureWorks; BEGIN TRANSACTION
update HumanResources.Department
set ModifiedDate=dateadd(day,0,ModifiedDate)
*/
/***** END OF OPEN A NEW CONNECTION FOR THE FOLLOWING *****/
ROLLBACK TRANSACTION
go
Shrinking a log
depends on first truncating the log. In simple recovery mode,
truncation is automatic. In full recovery, it occurs upon log
backup (below) or full backup.
BACKUP LOG AdventureWorks TO DISK='f:\data\backup\AWlog.bak'
Log truncation does not reduce the size
of a physical log file. However, it does reduce the size of the
logical log and marks as inactive the virtual logs that do not
hold any part of the logical log. A log shrink operation removes
enough inactive virtual logs to reduce the log file to the requested
size. The log_reuse_wait_desc flag in sys.databases and DBCC LOGINFO command can be helpful to resolve block to shrinking.
SELECT DBName=name, log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait_desc != 'NOTHING'
ORDER BY database_id
DBCC LOGINFO
/*
FileIdFileSize StartOffset FSeqNo Status Parity CreateLSN
2 458752 8192 24 0 128 0
2 458752 466944 25 0 128 0
2 458752 925696 26 0 128 0
2 712704 1384448 30 2 128 0
*/
Status = 2 (active VLF) has to be on the top or SQL Server will not shrink the log. Successive application of combination of transaction log backups and adding dummy transactions on dummy table will move "2" to the TOP.
Article: Why you want to be restrictive with shrink of database files
If there is no shrinking, detach/attach db, put db into single user (SINGLE_USER) mode, and attempt SQL shrink log file again. Return database to MULTI_USER mode. Also check for live or disabled/deleted replication interference with shrinking.
Complete Transaction Log File Rebuild
If none of the above SQL shrink log file methods work, you can create a brand-new empty transaction log file (and delete the current log file which is renamed later when satisfied with db operations) the following way:
Assume the database name is dbOMEGA;
Exit all users from the database
Backup database dbOMEGA
In SQL Server Management Studio Object Explorer:
Right click on dbOMEGA -> Properties -> Files -> make note of the PATHs for .mdf and .ldf
Select dbOMEGA -> Tasks -> Detach... -> Click on OK
In the log file folder for dbOMEGA (from Properties) -> rename the dbOMEGA_log.ldf to dbOMEGA_log_to_be_deleted.ldf
Click on Databases in OE -> Attach... -> Click Add -> add the database dbOMEGA .mdf file (MDF file location), select the log file and click on the REMOVE button, click on OK at the bottom
After verifying and using the database with the new transaction log, the old log file can be deleted (like a week later)
Related Microsoft Help, KB and Support Articles
How to stop the transaction log of a SQL Server database from growing unexpectedly
Factors That Can Delay Log Truncation
tempdb, Please allow me to shrink you
|
