How to Manage your SQL Server Log and Database File Size Growth

How to Manage your SQL Server Log and Database File Size Growth

Managing the Database and log file size growth can be a difficult task sometimes. When things go out of hand, DBA tend to use inefficient methods such as “Database Shrink” or “Shrink Log Files.” Database shrink can hurt your SQL Server performance. Shrinking Log files will take away your option to do “Point in Time Restores.” So what are the best practices? Read on to know how to manage your SQL Server log and database file size growth.

The default setting for SQL Server is set as “In Percent” File Growth option for database files. This is not recommended. You need to plan from the beginning to avoid size issues in the future. When the “In Percent” option on any database file, the file size is probably going to be very big. This causes performance degradation when new data pages are allocated on the fly during the “file growth phase.” Use “In Megabytes” option for File Growth. This option is also included in the SQL Server 2016 Setup.

It is recommended the practice to keep the initial file size to 1024 MB for data files (MDF, NDF*) and 256 MB for log files (LDF). Log files are transactionally heavy and need low latency disk drivers to perform optimally. Shrinking Log files is not a good idea. This is because they are essential for point-in-time recovery in case of a disaster.

A Blog Post on MSDN talks about how to check the next “Auto Growth Size” for both Data and Log Files in SQL Server. See Below:

--auto growth percentage for data and log files
select DB_NAME(files.database_id) database_name, files.name logical_name, 
CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],
[next_auto_growth_size (MB)] = case is_percent_growth
    when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)
    when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)
end,
is_read_only = case is_read_only 
    when 1 then 'Yes'
    when 0 then 'No'
end,    
is_percent_growth = case is_percent_growth 
    when 1 then 'Yes'
    when 0 then 'No'
end, 
physical_name
from sys.master_files files
where files.type in (0,1)
and files.growth != 0

Database Shrink for Databases in Always On Availability Groups:

Shrinking Databases and Log Files are not recommended on a regular basis. Only in exceptional cases, a one-time activity is recommended. Shrinking your Database files will free up unused space and release the space to the file system. This can get ugly, and your indexes can be fragmented as a result of this. Instead, the recommended approach is to set the auto-growth settings to a fixed size, or change the default 10% auto-growth settings to bytes and look at your projected numbers.

For LDF Files (SQL Log Files): Taking Regular Transaction Log Backups from the Native Backup program build into SQL Server Management Studio should help to manage the size of the T-Logs. Setting up a retention policy on the Backups (For Example 2 Weeks ) would help you to control the size of the Log Files.

In case if you are using Symantec Backup Exec Software follow this article that talks about Truncating SQL Server Log Files. This is particularly recommended for SQL Databases running in Full Recovery Model.

Shrinking Databases running in Availability Groups:

Like I mentioned before, Shrinking Databases and Log-files is not a good option to maintain the Log File size. However, As a Onetime process, you can change the default backup priority options to primary SQL Server. Take at least one a full and one transaction log backup on the Primary Server. Perform a Shrink on the log files of all databases on the primary replica. This will I turn truncate the empty the log drives on all the secondary availability replicas. Alternatively, you can take out the Database from the Availability Group perform the maintenance tasks and again add them back to the AG. You need to check the Index Fragmentation and Rebuild or Reorganize Indexes if necessary.

Database Shrink Process Takes too Much time:

In case if the Database store LOB Data using LOB Data types like (varchar(max), nvarchar(max), XML, varbinary(max)) and Legacy LOB types (text, ntext, image), they can be stored in-row or off-row (i.e. as part of the data or index record, or in a tree of text records on altogether different pages).

Kindly note that shrinking these Datatypes will take much longer time than usual.

I hope this post was helpful, If you have questions, drop me a comment below.