Reduce the SQL SERVER log file

As the straight left

In the process of using the database, log files are added, modified, and deleted frequently. As a result, log files are bigger than the database files themselves.

In Enterprise Manager, it does not seem possible to delete the log file directly. Limit the file size to the current level. For example, if the current log file is 500M, you can limit it to 501M. Of course, when creating the database, you can specify the log file size, such as 10M. But you don’t know what happens to the system after 10 meters. No errors, right? It should be a circular pattern, and when it reaches a critical point, it automatically covers the first place, and so on and so on.

But what about the already huge log files? If you don’t do something about it, it looks like it will continue to ferment until it fills up your hard drive.

Access to information. The book says (” SQL SERVER troubleshooting “, Publishing House of Electronics Industry), SQL SERVER log files have two senses of size, one is logical size, one is physical size. The physical size refers to the disk space occupied by log files, while the logical size refers to how many logs are actually stored. To reduce the size of database log files, you can shrink by removing inactive logical logs.

There are two cases:

1. The fault recovery mode of the database is simple (you can see the fault recovery mode in the properties – options of the database). You can directly shrink the database by using the following command:

DBCC SHRINKFILE(logical name of diary files, file size in MB after shrinking)

Such as:

DBCC SHRINKFILE(gcjs_log,1)

GO

After running, the log file will shrink to 1M.

2. If the database is in full or bulk recovery mode, truncate enough logical logs to mark them as inactive:

BACKUP LOG Database name WITH NO_LOG

GO

then

DBCC SHRINKFILE(logical name of diary files, file size in MB after shrinking)

GO

In this way, log files can be shrunk to a specified size.

Log files can be used for data recovery, and we are taught that shrinking log files should be based on frequent full database backups.