How to shrink it?When you face a problem where a transaction log file has eaten all of your disk space and the whole system stopped responding, all you want to do is shrink it as soon as possible. Here is how you do this:
1st:use master
backup log
database name to disk = '
path'2nd:use
database namedbcc shrinkfile (
log file name, truncateonly)
Or, if the time is crytical or you don't find any space for backup, you can do this (faster, but somewhat risky) procedure:
1st:use master
backup log
database name with truncate_only
2nd:use
database namedbcc shrinkfile (
log file name, truncateonly)
3rd: (This step is optional, but strongly recommended - make a full backup of your database):
use master
backup database
database name to disk = '
path'
Why did it grow so big?Transaction log is an essential part of a SQL Server database. Every single data change that needs to be saved into a database is first being written into a log file and then periodically (when the checkpoint occurs) flush into a data file.
If a system failure occurs, log file is needed to recover the database into a consistent state. During the recovery process, SQL Server reads the log file to determine which transactions are written to the log, but are not yet flush into the data file. Such transactions must be rolled forward. On the other hand, transactions that started, but there is no flag in the log file that they were committed, must be rolled back.
Since every data change needs to be saved into a transaction log file, the log file is thus growing bigger and bigger. If you don't take proper care of it, you could eventually end up with a log file occupying all of your free disk space.
How to keep transaction log size reasonable?Growing of a transaction log file can be slowed down by performing transaction log backups regularly. When you backup a log file, SQL Server determines which parts of the log file are not needed any more and marks them. This is called truncating the log. By doing so, physical size of the log is not reduced, but the marked parts can be reused later and overwritten by new log data. Therefore, you should set up a backup strategy and it should include performing log backups.
In the first shrinking procedure (shown above) we have thus performed a transaction log backup and saved it to a disk. This also truncated the log, but the file itself didn't change its size.
To shrink the log, we had to use DBCC shrinkfile or DBCC shrinkdatabase command (We did that in step 2). This releases unneeded parts of the log to the operating system. If you have never performed backup of your log file, there wouldn't be any parts of log marked as unneeded and shrinking the log by DBCC commands could not reduce the file size.
In the second proposed solution, we used
with truncate_only option, which in fact meant that we didn't want to save the log backup anywhere to disk, but rather just truncate the log. Be very cautious when backing up with truncate_only because doing so breaks the log chain and you cannot make a "regular" log backup after that, which could potentially lead to the loss of data! It is therefore highly recommended to make a full backup immediately after backing up the log with truncate_only option. (That's what we did in step 3).
If you don't need to have point-in-time recovery, then you can keep your transaction log at a reasonable size just by changing the recovery model of your database to simple. (This could be the case with your development database, for example.) When working in simple recovery model, transaction log is being truncated automatically with each checkpoint so you don't have to backup the log (what's more, it is not even possible to backup the log in simple recovery model).
To find out which recovery model your database is using, execute the following command:
select databasepropertyex('
database name', 'recovery')
To change the recovery option for your database, run this command:
alter database
database name set recovery {full simple bulk_logged}
