Log file reduction
For log files, the Database Engine uses target_size to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. The target size for the entire log is then converted to the target size for each log file. DBCC SHRINKFILE attempts to compress each physical log file to its target size immediately.
However, if part of the logical log is located in virtual logs outside the target size, the Database Engine frees up as much space as possible and then issues an informational message.
The message describes what actions are required to move the logical output from the virtual logs at the end of the file. After completing the steps, DBCC SHRINKFILE can be used to free up the remaining space.
Because the log file can only be reduced to the edge of the virtual log file, it may not be possible to reduce the log file to a size smaller than the size of the virtual log file, even if it is not used . The size of the virtual log file is dynamically selected using the Database Engine when log files are created or expanded.
- Troubleshooting: the file is not compressed
If the shrink operation is performed without errors, but the file has not changed in size, make sure that the file has sufficient free space for deletion by performing one of the following operations:
Run the following query.
SELECT name, size / 128.0 - CAST (FILEPROPERTY (name, 'SpaceUsed') AS int) /128.0 AS AvailableSpaceInMB FROM sys.database_files;
Run the DBCC SQLPERF command to return the space used in the transaction log.
If there is not enough free space, the compression operation cannot reduce the file size.
This is usually a log file that is not compressed. This is usually the result of a log file that has not been truncated.
You can trim the log by installing the database recovery model in SIMPLE or backing up the log , and then run DBCC SHRINKFILE again.
Example:
Reduce the log file to the specified target size
The following example reduces the log file in the AdventureWorks database to 1 MB. In order for the DBCC SHRINKFILE command to compress the file, the file is first truncated, setting the database recovery model to SIMPLE.
Transact-sql
USE AdventureWorks2012;
GO
- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
INSTALL RECOVERY SIMPLE | GO
- Compression of the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
- Reset database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL,
GO
When you use DBCC SHRINKFILE (Logfile, size), it only truncates from the end of the log file as much as possible. When it reaches the highest virtual log that is still in use, it cannot shrink. This is described in SQL Server Books Online at:
http://technet.microsoft.com/en-us/library/ms189493.aspx
So, as soon as the top end of the magazine becomes clear, it can be reduced in size. Again, this will depend on how much of the log is still in use. The log can be cleared by backups, but backups will not clear incomplete transactions, so the log can remain in high-performance VLF even after repeated backups.
As for increasing and decreasing VLF, how large was the log file that was originally created, and what is the parameter for growing the log file? If he grows just a small amount, he will create more VLFs than anyone wants.
A common template for shortening a log file is CHECKPOINT, BACKUP, SHRINKFILE, CHECKPOINT, BACKUP, SHRINKFILE, etc., until you get the results. There are many reasons why a journal cannot be shrinkable, including very large rollbacks.
Switching from Simple to Full has a problem:
There are rules and exceptions. We will talk about long transactions in depth below.
But one caveat regarding the full recovery mode: if you simply switch to full recovery mode but never take the initial full backup, SQL Server will not honor your request in the Full Recovery model. Your transaction log will continue to work as it did in Simpleuntil when you switch to the full recovery model and perform the first full backup.
A complete recovery model without log backups is bad:
So what is the most common cause of uncontrolled magazine growth? Answer: Be in full recovery mode without any log backups.
This happens all the time for people.
Why such a common mistake?
Why is this happening all the time? As each new database gets the initial setup of the recovery model by looking at the model database.
The parameter of the original recovery model is always a full recovery model - until someone changes it. Thus, you can say that the "Default Recovery Model" is full. Many people are unaware of this and have their databases in the full recovery model without backing up logs and, therefore, the transaction log file is much larger than necessary. This is why it is important to change the default settings when they do not work for your organization and its needs)