File-Sizing

[SQL-Server-Team]

First published on TECHNET on Nov 06, 2010

One of the primary areas of responsibility for DBA's is maintenance, and one of the primary maintenance tasks is file-size management. First I'll present a list of the file-sizing tasks with short explanations for each one, then some background information, and then some T-SQL to give you the file sizes for all data and log files. Attached is a zip file that includes the T-SQL script and 4 policies you can use with Central Management Server if you're using SQL Server 2008 or later.

FILE-SIZE MANAGEMENT TASKS

1. Each file should be annually right-sized. Right-sized growth minimizes physical fragmentation and applies to all database files (i.e. mdf, ndf, and ldf). Set each file's size to have enough internal free space so that it won't need to grow for a long period of time, based on current data growth rates. Put another way, this will normally prevent autogrowth from occurring . A year's worth of free space is recommended to minimize the administrative time required. Use the script below to check file sizes on a regular basis and compare data growth over time and forecast file growth needs.


2. An alert should fire if a file's free space falls below 10%. If the data growth rate increases substantially, free space will decline more rapidly, possibly triggering autogrowth and filling the disk before the DBA would otherwise notice. You can use the free_space_percent column in the query below as a basis for an alert. Note that "free space" refers to the free space inside the file, not the free space of the disk that the file is on.


3. Autogrowth should be enabled for all files. If a file needs to grow and cannot, its database becomes read-only until it is allowed to grow.


4. Autogrowth should be set to 10% for all files. While there is no perfect growth amount for all files, 10% is recommended as the best value in most cases. Using a percentage provides a balance regardless of whether the file size is small or large, and setting it higher than 10% would increase the possibility that the disk will not have enough free space to grow as large as the higher value would require.


5. Maximum file size should be set for all files. Setting a maximum file size on every database file should be used to prevent a single file from filling a disk. (An exception would be when a disk is dedicated to a single file.)

NOTES

• 10%: Why isn't 10% the default autogrowth setting for data files? Because the model database settings haven't been updated since SQL Server 2000. The model database serves as the template for most new databases, and SQL Server 7 and 2000 had serious performance problems when growing data files by large amounts, which a percentage growth can cause. A database became unavailable while the file was increasing in size -- and the larger the growth, the longer the delay. However, the time delay due to file growth is insignificant if you're using SQL Server 2005 or later on Windows Server 2003 or later; which allows us to use a 10% growth option for emergency autogrowth.


• CMS: You can run the script below against multiple servers simultaneously by using SQL Server 2008's Central Management Server.


• PBM: The attached file includes a .sql file with this script and 4 policies that can be used in Policy Based Management. Remember, you can evaluate policies against multiple SQL Servers simultaneously by using Central Management Server.


• MDW: The Management Data Warehouse available as a feature in SQL Server 2008 and later gives you another great way to monitor file sizes, especially since it provides an automated method to monitor their growth over time. After you have MDW collecting data for awhile, open SQL Server Management Studio (SSMS), expand the server, expand the management node, right-click on the "Data Collection" node (not on the "Disk Usage" node under it), choose Reports/Management Data Warehouse/Disk Usage Summary.


• Disk Space: What about monitoring the free space for entire disks, rather than just files? That should be monitored in addition to monitoring free space in files, not instead of it. You can use xp_fixeddrives part of the script below to list free space in all the drives for an instance of SQL Server, and you can use that as the basis of alerts. If you have SCOM, you can also use that to generate alerts on low disk space.

T-SQL SCRIPT

The following code will give you the total free disk space on each drive and a complete list of all database and log files on an instance of SQL Server from version 2005 to 2008 R2. The base query was derived from a SQL Server 2000 query by Michael Valentine Jones. In the results of the script below, a maximum_file value of "Unlimited" means it's not limited by the file settings. Every database file still has a size limited by the version of SQL Server and the file system.

--Show free space for each drive for an instance of SQL Server
IF OBJECT_ID('tempdb..#Disks') IS NOT NULL DROP TABLE #Disks
CREATE TABLE #Disks (
[drive] [sysname] NOT NULL,
[free_space_mb] [int] NULL
) ON [Primary]

INSERT #Disks EXECUTE xp_fixeddrives

SELECT * FROM #Disks

DROP TABLE #Disks

--Show file data for all database files in an instance of SQL Server
IF OBJECT_ID('tempdb..#Files') IS NOT NULL DROP TABLE #Files
CREATE TABLE #Files (
[database_name] [sysname] NOT NULL,
[file_size_mb] [decimal](12, 2) NULL,
[space_used_mb] [decimal](12, 2) NULL,
[free_space_mb] [decimal](12, 2) NULL,
[free_space_percent] [decimal](12, 1) NULL,
[autogrowth] [varchar](3) NULL,
[growth_amount] [varchar](60) NULL,
[maximum_size] [varchar](60) NULL,
[file_control] [varchar](10) NULL,
[file_density] [varchar](6) NULL,
[file_id] [int] NULL,
[file_type] [nvarchar](60) NULL,
[file_state] [nvarchar](60) NULL,
[logical_file_name] [sysname] NOT NULL,
[physical_file_name] [nvarchar](260) NOT NULL,
) ON [Primary]

EXEC sp_msforeachdb
'USE [?] INSERT #Files SELECT database_name = ''?''
, file_size_mb = Convert(Decimal(12,2),Round(a.size/128.000,2))
, space_used_mb = Convert(Decimal(12,2),Round(FileProperty(a.name, ''SpaceUsed'')/128.000,2))
, free_space_mb = Convert(Decimal(12,2),Round((a.size - FileProperty(a.name, ''SpaceUsed''))/128.000,2))
, free_space_percent = Convert(Decimal(12,1),100*(a.size - FileProperty(a.name,''SpaceUsed''))/Convert(Decimal(12,2),a.size))
, autogrowth = Case When growth = 0 Then ''Off'' Else ''On'' End
, growth_amount = Case When is_percent_growth = 1 Then Convert(varchar(25), growth) + ''%'' Else Convert(varchar(25), Convert(Decimal(12,1), a.growth/128.0)) + '' MB'' End
, maximum_size = Case When a.max_size = 0 Then ''No Growth Allowed'' When a.max_size = -1 Or a.max_size = 268435456 Then ''Unlimited'' Else Convert(varchar(60), Convert(Decimal(12,2),Round(a.max_size/128.000,2))) + '' MB'' End
, file_control = Case When is_media_read_only = 1 Or is_read_only = 1 Then ''Read-Only'' Else ''Read-Write'' End
, file_density = Case When is_sparse = 1 Then ''Sparse'' Else ''Dense'' End
, a.file_id
, a.type_desc
, a.state_desc
, a.name
, a.physical_name
FROM [?].sys.database_files a'

SELECT file_type, total_file_size_mb = SUM(file_size_mb), total_space_used_mb = SUM(space_used_mb), total_free_space_mb = SUM(free_space_mb) FROM #Files GROUP BY file_type ORDER BY file_type DESC

SELECT * FROM #Files ORDER BY database_name, file_id

DROP TABLE #Files

File_Managment_v2.zip