In this article, we will discuss the actual database file size and free space in the database file size with the help of monitoring script and shrink solutions. While creating a database initial size of the database files (Data File and Log File) can be defined by us, with the Autogrowth and MAXSIZE parameter. The initial size will be the same as the model database if by chance user forgot to define the initial size parameter while creating a database or do the same intentionally.
At the beginning, size of the database can be set up using the SIZE parameter and how much maximum space can be occupied by a database can be defined using the MAXSIZE parameter. How fast a database file can reach its maximum space can be resolved using the FILEGROWTH parameter. We are facilitated in a way we define the FILEGROWTH parameter. It leaves us with two options either by specific absolute value or by percentage, while in both cases we must define the value in MB format. There will be no boundary on the growth of a database file if we do not take care of defining the MAXSIZE parameter, as the default value is UNLIMITED.
SQL Server can not commit any transaction to disk storage in the situation where disk is full so it’s always good to define the MAXSIZE parameter beforehand to avoid such situation. UNLIMITED would fill up the disk storage but before that, it makes several spaces tinier than before. Even this has a very hazardous impact on the operating system as well if it doesn’t have enough space to execute its system programs. We also need to take into consideration the size of multiple databases before defining the MAXSIZE attribute.
Gigantic sized table data will be dispensed into multiple files in a larger platform in order to decrease the amount of the disk contention. To increase the I/O performance, SQL Server supports multiple file-group with secondary files, client data and index can be stored in the secondary file-group. The same tactics can be applied with the database log file in which the user can create multiple files with a single database and it is recommended to store log files on a different drive than the main data file (mdf).
The database file can increase in size without any boundary if the MAXSIZE parameter is not been configured by the user. It is highly suggested to construct an alert with a view to get rid of the chances of a full disk. When certain conditions exist we can report an issue or display it on health monitor using alert. There can be any issues and solutions with each issue to tackle it quickly. For example,
- Free space in Data file or Log file
- Heavy Transaction Log data file
- Increasing Log file Size and not shrinking due to heavy transaction stuck
Database log file size will be populating continuously till the next transaction backup happens if the database recovery model is not simple and Transaction backup or Log shipping is not set up. However, upon creating a Log backup, there is definitely going to exist a free space. For example, consider taking the log backup of the file with size 1024MB. What happens after this is that all logs will be removed inside the file and as a result of this there will be a plethora of space.
To shrink a file in SQL Server, we always use DBCC SHRINKFILE() command. This DBCC SHRINKFILE() command will release the free space for the input parameter.
|
1 |
DBCC SHRINKFILE([FileName / FileID],[EMPTYFILE / [nMB(Amount for Shrink), NOTRUNCATE / TRUNCATEONLY]]) |
The file will be shrunk by either file name or file id using the command above. The shrinking amount size will be considered as specified with the command in a unit of MB. Now what could be the Amount for Shrink in the DBCC SHRINKFILE()?
Let’s discuss with an example, In the above image, the AdventureWorks database has a one DATA file AdventureWorks2016CTP3_Data with space in the file is 500 MB larger and it also possesses free space of 208.8MB. Where Logfile AdventureWorks2016CTP3_Log is in existence with 600 MB with free space in the file is 362.9 MB. The below script can be put on to use by a user to get free space for the database files.
Get a list of database files with size for all databases in SQL Server:
- sys.master_files DMV returns the database files in detail with the current size of each file
- master_files system object will return details for each database of the SQL Server instance
|
1 2 3 4 5 6 |
SELECT DB_NAME(database_id) AS database_name, type_desc, name AS FileName, size/128.0 AS CurrentSizeMB FROM sys.master_files WHERE database_id > 6 AND type IN (0,1) |
Get a list of databases file with size and free space for a database in SQL Server:
- sys.database_files DMV returns the database file with the details
- sys.database_files is a system object which returns information for the selected database only
- If Free Space in the file is exceeding the limit (nMB)
- If the percentage of Free space is exceeding n% compare to total space
- If the current Size of a database file is exceeding the limit (nMB)
- When the disk is in close proximity of getting full then we might have to consider moving a file to a different disk
- If a database is in full recovery model and log shipping isn’t configured, make a database to the simple recovery model
- If a database is in full recovery model and log shipping isn’t configured take a Full backup and Transaction Log backup
- Change the Max Size parameter value for the database file, if it needs to be expanded
- Disable auto-growth parameter for the database file and limit with the (n)size as the priority of the database
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020
|
1 2 3 4 5 6 7 |
SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1); |
Now, free space for the file in the above query result set will be returned by the FreeSpaceMB column. 600 MB of space will be preoccupied with system disk into the file system for the AdventureWorks2016CTP3_Log file, However, 362 MB is free to compress it up to 238 MB (600 – 362 = 238).
The example taken above refers to a small database with a very lesser original database file size and free space in a file for the AdventureWorks. But, in real scenarios (i.e. on Production Servers), database size can be humongous with a heavy flow of database transactions. The log file will store the transaction log if your database is in full recovery model. Used space will be neither released nor flushed by log file until the next Log backup and database file size will grow up with the log.
When any transaction blocks or stuck any database transactions, database log file size can be rapidly increased and cross the expected output file size to handle this issue. The transaction lock problem can arise with Query performance and the same can happen with the client-side application also. When transactions are truncated and log backup is generated free space will be available. When any bulky Delete, DROP or Truncate operation is executed on the table, there can be a large amount of free space made available for data files. Free space can also be reclaimed by dropping a huge index also.
Query as above should be performed in each database to monitor the free space for all databases with the criteria and on the result set, space monitor criteria must be applied. Users can use sp_msforeachdb() procedure to execute monitor T-SQL queries in each database of the SQL Server.
T-SQL Query to get total space and free space for database files:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE TABLE #FileSize (dbName NVARCHAR(128), FileName NVARCHAR(128), type_desc NVARCHAR(128), CurrentSizeMB DECIMAL(10,2), FreeSpaceMB DECIMAL(10,2) ); INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB) exec sp_msforeachdb 'use [?]; SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1);'; SELECT * FROM #FileSize WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb') AND FreeSpaceMB > ?; DROP TABLE #FileSize; |
In the above query, for every individual database file size will be stored by a temporary table called #FileSize. Thereafter, Necessary filter or calculative logic will be applied by Database Administrator Alert with calculation logic can be set as below:
Action must be taken relative to the priority and level of the problem by DBA on the alert mail database.
Space can be reclaimed by making a move either:
Space alert can be configured on a health monitor with a mail. Nowadays we have many third-party monitors in the market to monitor database file size, where you can set the output of this query with the priority alert type. Users can attach this logic in Procedure as well with returning mail to the database monitoring team if any alert criteria succeed for the database file size.
Conclusion
We discussed how we can monitor the actual database file size and its free space with the help of monitoring script and shrink solutions in this article. If you have any questions, feel free to ask in the comments section below.
View all posts by Jignesh Raiyani