As a DBA, one of the things I routinely need to check is how much space is available in my database data and log files. In this post of my ongoing SQL Scripts series, we will take a look at a script that gives us some insight into these files in our database.
Why Should I Check The Size Of My Database Files?
The size of your database’s data and log files may not be an accurate representation of the actual size of your data. For example, a log file may grow, and then get truncated, but the size of the log doesn’t shrink. There can be a big difference between the space the files physically consume on disk, and the amount of data actually in those files. Many DBAs like to initially create their data/log files large with a lot of free space and allow them to fill up over time as the data grows.
You also want to see if your database files are getting close to full, and try to head off any auto-growth events by manually increasing the size of your files before SQL Server is forced to do it on its own. I always try to keep an eye on this, so I can manually grow my files during a maintenance window instead of letting SQL Server do it during normal operating hours.
How Do I Find My Data And Log File Information?
The query below will check the built in sys.database_files DMV to return information about the data and log files associated with a given database. The DMV actually returns the size of the file in 8-KB pages, so my query does the calculations to convert that to megabytes and percentages, as well as also providing the current auto-growth settings for each file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
USE YourDatabaseName GO SELECT DB_NAME() AS [database_name], name AS [file_name], [type_desc], [physical_name], CONVERT(DECIMAL(10,2),SIZE/128.0) AS [current_size_MB], CONVERT(DECIMAL(10,2),CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS [space_used_MB], CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(SIZE/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [percent_used], CONVERT(DECIMAL(10,2),size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS [free_space_MB], CONVERT(DECIMAL(10,2),((SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)/(SIZE/128.0))*100) AS [percent_free], 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} -' ELSE '' END + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END AS [auto_growth] FROM sys.database_files; |
This post is part of an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration.