SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Zora's SQL Tips

Hi! I am Zora Stalin, an IT geek and a passionate learner of technology. Besides my job as an Information Technology Analyst, I love searching and sharing new things that excite me help for others.

Four Ways to Find Free Space In SQL Server Database

Determine Free Space In SQL Server Database

In our previous section we have discussed about Using Stored Procedures in SQL Server For Better Performance and know various advantages of using them in SQL Server. Now, In this article we will discuss about how to check free space in sql server database. as we already know that the SQL Server is a relational database management system, developed by Microsoft that is used for storage and retrieval of data whenever required. Database on SQL Server contains one Master Data File (MDF) and may be associated with number of New Data File (NDF). Each MDF file is attached with a separate transaction Log Database File (LDF). The users of SQL Server often faces problem when the size of their SQL database increases as large database file may leads to corruption. Hence,proper management of the SQL database is very much required. One function of the database administrator is to keep track on free space available within the database and database files.

Why Determining Free Space Is Required in SQL Server database?

Even though the default Autogrow and Autoshrink settings provided by Microsoft can help users in managing the size of the database without user’s intervention, but manually managing the database files accordingly our requirements is better option. Keeping an eye on free space of the database and database files not only help in managing the database, but this information also helps in shrinking data files as needed if there is free space in the files. Let’s know how to find free space in database.

Different Ways to Determine Free Space in SQL Server database

In order to manage the SQL database and database filesin better way, we need to determine free space present within the database files.
First, we need to have a database and get the information about the files.

We can see the total size of the database and the current size of the data files but it does not tell us about the free space present in the database. In the output, we can see two files “Solivia” and “Solivia_log” and their size. One is the Master Data File and other is Log File of the database. Only these two files are present in the database and are displayed.
Some ways to find the free space will be discussed below:
1) Using sp_spaceused to check free space in SQL Server
First way to determine free space in SQL Server database is by using sp_spaceused.
USE Solivia
GO
sp_spaceused
GO
The command will give information about the total free space in the database but the free space of the transaction log file will not be properly defined. Output is shown below-

2) Using DBCC SQLPERF to check free space in SQL Server Database
The second option is by using DBCC SQLPERF (logspace) command.
USE Solivia
GO
DBCC SQLPERF(logspace)
GO
This command provides transaction log space usage statistics of not only one database but of the entire database of the SQL Server. LOGSPACE gives the current size of the transaction log and the percentage of log space used for every database. Output is shown.

3) Using DBCC SHRINKFILE to determine free space in SQL log file
Another way to find the free space is to use DBCC SHRINKFILE.
USE Solivia
GO
DBCC SHRINKFILE(Solivia)
DBCC SHRINKFILE(Solivia_log)
GO
The DBCC SHRINKFILE shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same file group, allowing the file to be removed from the database. This command can shrink a file to a size less than the size specified when it was created which resets the minimum file size to a new value.

Here is the detailed description of the output. The column that is helpful to us is Minimum Size, which tells us size we initially made the file when it was created. It also tells us information that could help in shrinking the files.
Description of the columns of the output:
Name of the columnsDescription
DBId Database Identification number of file the database engine tried to shrink.
Field File Identification number of file database engine tried to shrink
CurrentSize Number of 8-KB pages the file currently occupies
MinimumSize Number of 8-KB pages the file could occupy at minimum. It means minimum size/originally created size of file.
UsedPages Number of 8-KB pages currently used by the file.
EstimatedPages Number of 8-KB pages that the database engine estimates the file could be shrunk down to.
The drawback is that if there is free space in the file, it will shrink the file even if we use the command with a much higher value for the file size and clear SQL Server transaction log.
4) Using FILEPROPERTY to find free space in a database
One more option that we can use to determine free space is by using SpaceUsed property of the FILEPROPERTY function. We can calculate free space based on the current size and what is being used.
USE Solivia
GO
SELECTDB_NAME()AS DbName,
name ASFileName,
size/128.0 AS CurrentSizeMB,
size/128.0 -CAST(FILEPROPERTY(name,'SpaceUsed')ASINT)/128.0 AS FreeSpaceMB
FROMsys.database_files;
Below is the result of the above command:
Observation
As we have discussed several ways to determine free space in SQL Server database and database files, The FILEPROPERTY option gives the best result. Moreover, the Minimum Size value from DBCC SHRINKFILE also gave us good output. The users of SQL Server can use any of the ways that works best for their database. Another option for determining the free space in SQL Server database is to use a third party tool, which can be used to view log files detail.

Comments

Leave a comment on the original post [sqlserveroverview.blogspot.com, opens in a new window]

Loading comments...