The Impact Of Auto-Growth Settings For Log Files

Jamie Wick has started a series on log growth and starts by looking at auto-growth settings:

For the data file, the impact can be illustrated in the following chain of events:

  1. A new 1MB data file is created that contains no information. (ie. a 1MB data file containing 0MB of data)
  2. Data is written to the data until it reaches the file size. (ie. the 1MB data file now contains 1MB of data)
  3. The SQL server suspends normal operations to the database while the data file is grown by 1MB. (ie. the data file is now 2MB and contains 1MB of data) If Instant File Initialization (IFI) is enabled, the file is expanded and database operations resume. If IFI is not enabled, the expanded part of the data file must be zeroed before db operations resume, resulting in an additional delay.
  4.  Once the data file has been grown successfully, the server resumes normal database processing. At this point the server loops back to Step 2.

The server will continue this run-pause-run-pause processing until the data file reaches its Maxsize, or the disk becomes full. If the disk that the data file resides on has other files on it (ie. the C drive, or a disk that is shared by several databases), there will be other disk write events happening between the data file growth events. This may cause the data file expansion segments to be non-contiguous, increasing the file fragmentation and further decreasing the database performance.

This is all to answer the question, “What’s the problem with missing a few log backups?”

Related Posts

Notifications when Admins Connect to a SQL Server

Jon Shaulis builds a logon trigger to notify when sysadmins log into his systems: I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for […]

Read More

Memory Defaults in SQL Server 2019

Randolph West looks at a new settings tab in the SQL Server 2019 installation: In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias. SQL Server 2019 is still in preview as I write this, but […]

Read More

Categories

July 2018
MTWTFSS
« Jun Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031