SQL Server

HOW TO CLEAR SQL SERVER TRANSACTION LOG TO REGAIN SPACE

Nguồn : http://www.sqlserverlogexplorer.com/how-to-clear-transaction-with-dbcc-shrinkfile/

A Transaction Log is a file that contains all the records of transactions and the database modifications made by each transaction in SQL Server database.The log file plays a very important part of SQL Server database when it comes to disaster recovery and it should not be in corrupted state. In case of any disaster, we can recover the database with the help of information present in the transaction log. The transaction log should be truncated or cleared regularly to keep the size of log file from filling up. This page will be discussing on how to clear SQL Server Transaction Log.

Why To Clear SQL Server Transaction Log?

During SQL Server work, the transaction log grows if any database changes occur. The regular management of the size of transaction log is necessary to prevent the transaction log from becoming full. Log truncation or clear SQL Server transaction log is required to keep the log from filling up. The truncation process deletes inactive virtual log files from the logical transaction log, freeing space to be reused by the physical transaction log. The transaction log would eventually fill all the disk space allocated to its physical log files, if it is never truncated.

Three recovery models are there in SQL Server. Depending on which one is used, truncation process differs:

Simple Recovery Model The transaction log backups are not supported and the truncation process is automatic and space is available for use.

Bulk-logged Recovery Model No automatic log truncation, backups are needed regularly to mark unused space and make it available for overwriting. Log size can be reduced by using minimal logging for bulk operations.

Full Recovery ModelThe truncation process is same as Bulk-logged Recovery model. There is a high chance of growing log file since every transaction that takes place on the database,is logged into it.

The transaction log space can be monitored using command:

How to Clear SQL Server Transaction Log?

Log truncation frees space in the log file for reuse. Therefore, it is also called Log clearing. The transaction Log file is logically divided into small chunks called Virtual Log Files (VLF). Each VLF file is the unit that can be marked as available for reuse (free) or not available for reuse (used). The VLF is marked ‘active’ if it is used and ‘inactive’ if it is free. Clear SQL Server transaction log means searching and making VLFs as free.

Note: It should be kept in mind that Log Truncation is not meant to reduce the size of the physical log file. Log Shrinking is needed to reduce the physical log file.

Clearing SQL Server transaction log involves two steps. Firstly, we need to perform log backup with TRUNCATE_ONLY option and next step is to use the DBCC SHRINKFILE function to shrink file to the required size.

BACKUP LOG WITH TRUNCATE_ONLY is not a good option as it empties all the contents of our transaction log without backing it up. Many people use this command before shrinking the log file with DBCC SHRINKFILE freeing up the drive space. TRUNCATE_ONLY is not available in later versions of SQL Server.

Instead of truncating transaction logs, we can use simple recovery mode by which we do not generate logs we would not be using.

We have two options to shrink the log. They are:

a) Shrink the log in SQL Server Management Studio

Right click on the database and choose:Tasks followed by Shrink and then select Files:

Change the file type to log.

b) Shrink the log using TSQL

For Simple Recovery, following command will be used –

DBCC SHRINKFILE (LogFileName, Desired Size in MB)

For Full Recovery (Only when we don’t mind losing data in log file), the commands to be used are –

ALTER DATABASE nameDB
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (LogFileName, Desired Size in MB)
GO
ALTER DATABASE nameDB
SET RECOVERY FULL

Another option to shrink SQL transaction log file is to, backup the database log using the following command-

BACKUP LOG nameDB TO BackupDevice

This is how one can get to know how to clear SQL Server Transaction Log file in order to free up the space in it for further storage of transactions. Transactions are very important to keep track of the changes taking place in corresponding database as well as to rollback just in case it is required. Therefore, always keep a check and maintain the transaction log files.

Giải thích

– Có 3 chế độ Recovery trong SQL Server , FULL, SIMPLE và BULK LOGGED

Chế độ mặc định là FULL.
Bạn có thể vào phần Option của DB, xem trong Recovery Model.

Khi ở chế độ này,  bất kì một transaction nào, kể cả khi đã commit cũng đều được lưu trong LOG, do đó có thể dựa vào những transaction này để “quay lui (rollback)” DB về bất kì thời điểm nào. Vì thế với những DB có Transaction nhiều, DATA ít thì file LOG vẫn có thể rất lớn.

– Đầu tiên SET RECOVERY của DB về SIMPLE, ở chế độ này, sau khi transaction được COMMIT, sẽ tự động xóa. Do vậy File LOG của DB ở chế độ này thường rất nhỏ.

– Dùng DBCC SHRINKFILE để SHRINK file log xuống còn 1 Mb
Nếu không set Recovery về SIMPLE, thì sẽ ko thể xóa bỏ hết các transaction đã được COMMIT.
SHRINKFILE chỉ thu dọn và sắp xếp  và phân bố lại dữ liệu, bỏ các vùng trống để giải phóng bộ nhớ, chứ không phải xóa dữ liệu. Vì thế ở chế độ FULL, SHRINKFILE hầu như ko tác dụng, hoặc nếu có thì file LOG dung lượng giảm đi ko đáng kể.

– Sau đó SET RECOVERY về lại FULL

Trên MSDN cũng khuyên nếu muốn Backup LOG, các bạn nên chuyển về chế độ SIMPLE, hơn là backup LOG với Truncate_Only và No_LOG

Nguồn :http://www.sqlviet.com/blog/lam-sao-giam-bot-dung-luong-file-log-cua-sql-server

 

USE CManagement
GO

/*
select * from sys.database_files
declare @fileId as int = (select file_id from sys.database_files where name = ‘XY’)
DBCC SHRINKFILE (2, 50);
* */

— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE CManagement
SET RECOVERY SIMPLE
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 50);
GO

ALTER DATABASE CManagement
SET RECOVERY FULL
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s