How to clear SQL Server Log Files

Recently, it was found that the log files of a system SQL Server database are large and need to be cleaned up. I thought it was a very simple task. SQLServerManagementStudio has a ready-made menu function. Just click twice. However, it does not work in actual operations. Because data is involved and Misoperation causes irreparable losses

Recently, it was found that the log files of a system SQL Server database are large and need to be cleaned up. I thought it was a very simple task. SQL Server Management Studio has a ready-made menu function. Just click twice. However, it does not work in actual operations. Because data is involved and Misoperation causes irreparable losses

Recently, it was found that the log files of a system SQL Server database are large and need to be cleaned up. I thought it was a very simple task. SQL Server Management Studio has a ready-made menu function. Just click twice. However, it does not work in actual operations. Because it involves data and worries about the irreparable loss caused by misoperations, I did some research on the Internet and did not try it out before I dared to start with the actual database.

The specific operation process should be as follows:

1. Back up the database before you start.

In the dark of night, the system is usually relatively idle, and it may be easy to handle, there is a database backup, in case of a problem found the next morning, at least can be restored to yesterday's work point, the risk is relatively small.

2. Open SQL Server Management Studio (SSMS), open the query page, and execute the following SQL statement:

Dump transaction [database name] WITH NO_LOG

This statement is used to clear logs of a specified database.

3. Right-click the corresponding database name in SSMS, select "task-shrink-file" from the menu, and select "log" for the file type in the open "shrink file" window ", select the log file name (if multiple log files are available). There are three options under the "contract operation:

1. Release unused space.

The unused space in the file is released to the operating system, and the file is reduced to the last allocated area. This option does not work very much, because it is already very large when you want to clean up log files, and the allocated space is also used up.

2. re-organize the page before releasing unused space and contract the file to (the file size needs to be set );

This method is equivalent to running dbcc shrinkfile of the specified target file. You must enter the log file size in the text box that follows, which can be 0.

3. Clear files by migrating data to other files in the same file group.

This method is equivalent to running dbcc shrinkfile with the EMPTYFILE option.

Here I chose to use the 2nd option.

Of course, here is only a practical method that can be successfully implemented. For more complex application scenarios, you need to have a deep understanding of dump transaction and dbcc shrinkfile. For more information, see Microsoft related documents.