Как шринкануть лог транзакций sql

Last updated on: 2020-06-18

Authored by: Karoline Mills

This article describes the difference between shrinking and truncating the transaction log in Microsoft® SQL Server®. It outlines the different use scenarios and walks you through how to perform these tasks.

Understand truncating the log

If a database is a simple recovery model, the system truncates the transaction log automatically after every transaction. For databases with a full or bulk-logged recovery model, the system truncates the transaction log only after a successful transaction log backup.

A full database backup does not truncate the log. If you don’t take regular transaction log backups, the log file continues to grow until it runs out of space. While truncating the log makes the space available for use again, it does not decrease the transaction log file size. To reduce the size of the transaction log file, you need to shrink the log file.

Truncate the transaction log

Use the following steps to truncate the transaction log file in SQL Server Management Studio (SQL Server 2008 and later). Keep in mind that following these steps might result in data loss. You should not need to manually truncate the log because regular log backups should automatically perform this task.

Note: You need the sysadmin fixed server role or the db_owner fixed database role to truncate the log.

Right-click the database and select Properties -> Options.

Set the recovery model to Simple and exit the menu.

Right-click the database again and select Tasks -> Shrink -> Files.

Change the type to Log .

Under Shrink action, select Reorganize pages before releasing unused space and click OK.

When the process completes, switch the recovery model back to Full or Bulk-Logged and take a full database backup.

Understand shrinking the log

If you need to recover disk space from the transaction log file, consider shrinking the log file. Shrinking recovers space by moving data from the end of the file to unoccupied space at the front of the file. After the process creates enough space at the end of the file, it can be deallocated and returned to the file system. Shrinking logs helps after you perform an action that creates a large number of logs. You can only shrink the log if there is free space on the log file.

Shrink the transaction log

Use the following steps to truncate the transaction log file:

Note: You need the sysadmin fixed server role or the db_owner fixed database role to shrink the log.

Right-click the database and select Tasks -> Shrink -> Files.

Change the type to Log .

Under Shrink action, select Release unused space and click OK.

Related Articles:

Share this information:

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License

How to shrink the transaction log file in SQL Server

There are times when a SQL Server transaction log file grows so large that it has to be shrunk back to a more reasonable size. Before shrinking the log file it is necessary to remove the recent transaction log entries from the log. That use to be done with the BACKUP LOG. WITH TRUNCATE_ONLY command. The TRUNCATE_ONLY option was removed in SQL Server 2008. How do you remove the log entries so the transaction log file can be shrunk?

Solution

In a recent article, Issues with running DBCC SHRINKFILE on your data files wrote about why not to shrink a data file with DBCC SHRINKFILE. The issues with shrinking data files that I described do not apply to log files, which have a much different structure. Inside the transaction log are one of SQL Server’s internal structures: Virtual Log Files (VLF). The log is divided into VLF’s so that space can be allocated and re-used once the transaction log entries in the VLF have been backed up. There’s a detailed description of Virtual Log files in the article How to determine SQL Server database transaction log usage

To see how many VLF’s are contained in a database go to the database run the DBCC LOGINFO command as seen here:

Issuing a BACKUP LOG with TRUNCATE_ONLY or BACKUP LOG with NO_LOG use to be a common solution to clear out the transaction log so that it could be shrunk. It was never a great idea and the article Issues with running backup log with no_log or truncate_only in SQL Server documents why. In short the right thing to do is to make a real transaction log backup. That’s what the procedure ns_shrink_db_log does. It makes a transaction log backup and then runs DBCC SHRINKFILE on the log.

The reason that it’s a procedure at all is that sometimes it’s necessary to make multiple transaction logs and run SHRINKFILE each time before the log shrinks to the desired size. To accommodate this reality, ns_shrink_db_log runs in a loop. There are several parameters this stored procedure takes:

@db_name — database name that you want to shrink log file
@target_size_mb — the desired size of the tranascaion log
@backup_location — location of the backup files
@backup_file_name — name for the backup files. As each attempt is made the attempt number is added to the back of the file name along with the standard extension «.trn».
@maximum_attempts — governs how many times it tries.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ns_shrink_db_log]

@db_name SYSNAME = NULL
, @target_size_mb INT = 2
, @backup_location NVARCHAR ( 200 ) = NULL
, @backup_file_name NVARCHAR ( 200 ) = NULL
, @maximum_attempts INT = 10

/* Shrinks the log file of @db_name to the @target_size_mb
*
exec [dbo].[ns_shrink_db_log] ‘scratch’, 2, ‘c:\temp\’
, ‘scratch_shirnk_backup’, 4
************************************************************/
AS

SELECT @db_name = COALESCE ( @db_name , DB_NAME ())

DECLARE @logical_log_file_name SYSNAME ,
@backup_log_sql NVARCHAR ( MAX ),
@shrink_sql NVARCHAR ( MAX ),
@checkpoint_sql NVARCHAR ( MAX ),
@db_id INT = DB_ID ( @db_name ),
@start_size_mb INT ,
@final_size_mb INT ,
@attempts INT = 0 ,
@recovery_model INT ,
@recovery_model_desc SYSNAME ,
@rc INT = 0 — return code

SELECT @logical_log_file_name = name ,
@start_size_mb = size / 128
FROM MASTER ..sysaltfiles
WHERE dbid = @db_id AND fileid = 2

SELECT @recovery_model = recovery_model
, @recovery_model_desc = recovery_model_desc
FROM sys.databases
WHERE database_id = @db_id

PRINT ‘Starting size of [‘ + @db_name + ‘].[‘
+ @logical_log_file_name
+ ‘] is ‘
+ CONVERT ( VARCHAR ( 20 ), @start_size_mb ) + ‘ MB ‘
+ ‘ recovery model = ‘ + @recovery_model_desc

IF @start_size_mb <= @target_size_mb BEGIN
PRINT ‘[‘ + @db_name + ‘] does not need shrinking’
END

IF @recovery_model != 3
AND ( @backup_file_name IS NULL OR @backup_location IS NULL) BEGIN
RAISERROR ( ‘Null backup file location or name. aborting.’ , 16 , 1 )
SET @rc = 50000
GOTO get_out
END

WHILE @attempts < @maximum_attempts
AND @target_size_mb < ( SELECT CONVERT ( INT , size / 128 ) FROM MASTER ..sysaltfiles
WHERE dbid = @db_id AND
name = @logical_log_file_name ) — not target
BEGIN

SET @attempts = @attempts + 1

IF @recovery_model = 3 BEGIN
SET @checkpoint_sql = ‘use [‘ + @db_name + ‘]; ‘
+ ‘checkpoint’
PRINT @checkpoint_sql
EXEC ( @checkpoint_sql )
END
ELSE BEGIN
SET @backup_log_sql = ‘BACKUP LOG [‘ + @db_name + ‘] ‘
+ ‘ to disk = »’ + @backup_location
+ CASE WHEN RIGHT ( RTRIM ( @backup_location ), 1 ) = ‘\’
THEN » ELSE ‘\’ END
+ @backup_file_name
+ CONVERT ( VARCHAR ( 10 ), @attempts )
+ ‘.trn»’
PRINT @backup_log_sql

EXEC ( @backup_log_sql ) — See if a trunc of the log shrinks it.
END

SET @shrink_sql = ‘use [‘ + @db_name + ‘];’
+ ‘dbcc shrinkfile ([‘ + @logical_log_file_name + ‘], ‘
+ CONVERT ( VARCHAR ( 20 ), @target_size_mb ) + ‘)’
EXEC ( @shrink_sql )
END
END

SELECT @final_size_mb = size / 128
FROM MASTER ..sysaltfiles
WHERE dbid = @db_id AND name = @logical_log_file_name

PRINT ‘Final size of [‘ + @db_name + ‘].[‘
+ @logical_log_file_name
+ ‘] is ‘ +
CONVERT ( VARCHAR ( 20 ), @final_size_mb )
+ ‘ MB’

get_out:
RETURN @rc

The procedure is easy to execute.

EXEC [dbo].[ns_shrink_db_log] ‘scratch’ , 2 , ‘c:\temp\’ , ‘scratch_shrink_backup’ , 4

One thing to remember once you’ve run ns_shrink_db_log: those backup files are for real. They’re on disk and they should be included in your normal backup procedure. Most of my clients copy the .trn files off to another server for safe keeping. If possible the other server should be in a different location to allow for disaster recovery.

Before going out and shrinking the logs in all your databases because it’s so easy now, step back and reconsider why you’d ever shrink a log file. The log file grew for a reason. Logs grow because transactions modify the database and there’s more transaction activity then transaction log backups. If the logs grew to that size once, isn’t it pretty likely that they’ll grow again? In most cases the answer is yes. If there was something unusual that caused the logs to grow, you might have reason to shrink the log. However, it’s often the case that the logs will just grow back to their previous size.

One of the events that you’ll want to avoid is autogrowth on the log file during the hours that users are using the database. Log growth is slow and when it’s necessary transactions must wait for the file to grow and be initialized. Log files are not subject to «Instant File Initialization» the way data files are. You can read about that in the article Configuring Windows Instant File Initialization for SQL Server 2005.

How do you clear the SQL Server transaction log?

I’m not a SQL expert, and I’m reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?

21 Answers 21

Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again. If the log file will grow to the same size again, not very much is accomplished by shrinking it temporarily. Now, depending on the recovery goals of your database, these are the actions you should take.

First, take a full backup

Never make any changes to your database without ensuring you can restore it should something go wrong.

If you care about point-in-time recovery

(And by point-in-time recovery, I mean you care about being able to restore to anything other than a full or differential backup.)

Presumably your database is in FULL recovery mode. If not, then make sure it is:

Even if you are taking regular full backups, the log file will grow and grow until you perform a log backup — this is for your protection, not to needlessly eat away at your disk space. You should be performing these log backups quite frequently, according to your recovery objectives. For example, if you have a business rule that states you can afford to lose no more than 15 minutes of data in the event of a disaster, you should have a job that backs up the log every 15 minutes. Here is a script that will generate timestamped file names based on the current time (but you can also do this with maintenance plans etc., just don’t choose any of the shrink options in maintenance plans, they’re awful).

Note that \\backup_share\ should be on a different machine that represents a different underlying storage device. Backing these up to the same machine (or to a different machine that uses the same underlying disks, or a different VM that’s on the same physical host) does not really help you, since if the machine blows up, you’ve lost your database and its backups. Depending on your network infrastructure it may make more sense to backup locally and then transfer them to a different location behind the scenes; in either case, you want to get them off the primary database machine as quickly as possible.

Now, once you have regular log backups running, it should be reasonable to shrink the log file to something more reasonable than whatever it’s blown up to now. This does not mean running SHRINKFILE over and over again until the log file is 1 MB — even if you are backing up the log frequently, it still needs to accommodate the sum of any concurrent transactions that can occur. Log file autogrow events are expensive, since SQL Server has to zero out the files (unlike data files when instant file initialization is enabled), and user transactions have to wait while this happens. You want to do this grow-shrink-grow-shrink routine as little as possible, and you certainly don’t want to make your users pay for it.

Note that you may need to back up the log twice before a shrink is possible (thanks Robert).

So, you need to come up with a practical size for your log file. Nobody here can tell you what that is without knowing a lot more about your system, but if you’ve been frequently shrinking the log file and it has been growing again, a good watermark is probably 10-50% higher than the largest it’s been. Let’s say that comes to 200 MB, and you want any subsequent autogrowth events to be 50 MB, then you can adjust the log file size this way:

Note that if the log file is currently > 200 MB, you may need to run this first:

If you don’t care about point-in-time recovery

If this is a test database, and you don’t care about point-in-time recovery, then you should make sure that your database is in SIMPLE recovery mode.

Putting the database in SIMPLE recovery mode will make sure that SQL Server re-uses portions of the log file (essentially phasing out inactive transactions) instead of growing to keep a record of all transactions (like FULL recovery does until you back up the log). CHECKPOINT events will help control the log and make sure that it doesn’t need to grow unless you generate a lot of t-log activity between CHECKPOINT s.

Next, you should make absolute sure that this log growth was truly due to an abnormal event (say, an annual spring cleaning or rebuilding your biggest indexes), and not due to normal, everyday usage. If you shrink the log file to a ridiculously small size, and SQL Server just has to grow it again to accommodate your normal activity, what did you gain? Were you able to make use of that disk space you freed up only temporarily? If you need an immediate fix, then you can run the following:

Otherwise, set an appropriate size and growth rate. As per the example in the point-in-time recovery case, you can use the same code and logic to determine what file size is appropriate and set reasonable autogrowth parameters.

Some things you don’t want to do

Back up the log with TRUNCATE_ONLY option and then SHRINKFILE . For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. Second, if you are in FULL recovery model, this will destroy your log chain and require a new, full backup.

Detach the database, delete the log file, and re-attach. I can’t emphasize how dangerous this can be. Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc. etc.

Use the «shrink database» option. DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas, especially if you really only need to resolve a log problem issue. Target the file you want to adjust and adjust it independently, using DBCC SHRINKFILE or ALTER DATABASE . MODIFY FILE (examples above).

Shrink the log file to 1 MB. This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! Unless your database is read only (and it is, you should mark it as such using ALTER DATABASE ), this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model. What is the point of freeing up that space temporarily, just so SQL Server can take it back slowly and painfully?

Create a second log file. This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. Paul Randal also explains why multiple log files can bite you later.

Be proactive

Instead of shrinking your log file to some small amount and letting it constantly autogrow at a small rate on its own, set it to some reasonably large size (one that will accommodate the sum of your largest set of concurrent transactions) and set a reasonable autogrow setting as a fallback, so that it doesn’t have to grow multiple times to satisfy single transactions and so that it will be relatively rare for it to ever have to grow during normal business operations.

The worst possible settings here are 1 MB growth or 10% growth. Funny enough, these are the defaults for SQL Server (which I’ve complained about and asked for changes to no avail) — 1 MB for data files, and 10% for log files. The former is much too small in this day and age, and the latter leads to longer and longer events every time (say, your log file is 500 MB, first growth is 50 MB, next growth is 55 MB, next growth is 60.5 MB, etc. etc. — and on slow I/O, believe me, you will really notice this curve).

Заметки сисадмина о интересных вещах из мира IT, инструкции и рецензии. Настраиваем Компьютеры/Сервера/1С/SIP-телефонию в Москве

На SQL сервере развернуто порядка 100 тестовых баз. Разворачивают в основном рабочие базы с режимом восстановления “Полный” и периодически забывая переводить в “Простую”. В связи с чем на сервере стали занимать много мест журналы транзакций. Вначале шринк журнала проходил руками, но потом это надоело и я решил это немного автоматизировать. Написал скрипт, который, пробегаясь по всем базам, кроме системных, переводит каждую в “Простой” режим восстановления и делает шринк журнала. Скрипт повесил на задание, для автоматического выполнения по расписанию.

The code below, get a list of non system databases, set the database to readonly and then shrink the file. I have kept this code in a few SQL Server boxes using SQL Agent Job, where space is always an issue. On Sat/Sun night every week, it start running and shrink all the databases within few hours (depending upon the size of databases):

In addition, you can find the option “Auto Shrink” in your MS SQL database properties. When you enable this option, the SQL Server will be periodically checking the unused space and reducing the size of the database and log files. Microsoft does not recommend using this option for typical databases, and if you decided to use Auto Shrink, your database should be running in the Full Recovery mode.

You can enable this option in the database parameters in the Automatic section. Just change the Auto Shrink parameter value to True. After you enabled autoshrink, MS SQL will perform automatic compression only if the unused space occupies more than 25% of the total volume size.

This method is applicable to all supported versions of SQL Server: 2005, 2008, 2012, 2014, and 2016, 2017.

This is just one of the ways to quickly reduce the size of the logs. Not the greatest, but very simple and effective.

Как шринкануть лог транзакций sql