Post
Ask Question

Learn to Shrink a Microsoft SQL Server Database Programatically

SQL

In this article, I’ll demonstrate what is shrink in SQL Server, the benefits and risks, and how to use C# and ADO.NET.

What is the shrink in SQL Server?

Shrink in Microsoft SQL Server is the process of lowering the size of a database file by reclaiming unneeded space. This applies to data files (.mdf,.ndf) and log files (.ldf).

This can be done programmatically using C# and ADO.NET. The operation requires lots of processing, and your database on production can slow down the queries while executing the command.

So, I use this command periodically after creating and deleting tables and storing data from extensive data processing to avoid extrapolating the quotes for the databases hosted on the web.

However, be aware of potential risks; this command should be executed at a good moment when a few users and services are using the database, and it’s recommended to have a little chat with a Database Administrator if you have one.

Why use shrink?

There are good reasons to use shrink periodically on your databases.

  1. Reclaim unused space: As data is lost or transferred, database files may accumulate much-unused space. Shrinking the database allows you to regain this space and reduce the size of your files on disk.
  2. Optimize disk usage: In circumstances with limited disk space, downsizing the database can assist in freeing up space for other purposes or reducing the overall footprint of the database.
  3. Backup and maintenance: Before performing a backup operation, a database may be shrunk to ensure that only relevant data is backed up, potentially lowering the size of backup files.

Benefits of shrinking

If your database host has a limit for database quotes, this is an excellent opportunity to avoid spending money on infrastructure.

  1. Reduced disk usage: By reclaiming unnecessary space, shrinking helps optimize available disk space, especially in contexts with limited storage.
  2. Improved manageability: Smaller database files are easier to manage, particularly during backup and restoration procedures. They can help reduce the time required to copy or move databases between servers.
  3. Cost efficiency: In contexts where storage costs are a factor, downsizing can result in cost savings by eliminating the requirement for new storage acquisitions.

Sample code to Shrink the SQL Server database

To conduct this action, utilize the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands.

SHRINKDATABASE performs the shrink at a high level, while SHRINKFILE optimizes the files and requires more privileges. It’s not recommended if you don’t know the potential risks, like slowing down the database.

In the sample below, you must call GetDatabaseNameFromConnection using the database string connection.

public static string GetDatabaseNameFromConnection(string connectionString)
{
    var builder = new SqlConnectionStringBuilder(connectionString);
    return builder.InitialCatalog;
}
public static bool ShrinkDb(string connectionString)
{
    var result = false;
    var shrinkCommand = $"DBCC SHRINKDATABASE ('{GetDatabaseNameFromConnection(connectionString)}')";
    using var oCnn = new SqlConnection(connectionString);
    using var command = new SqlCommand(shrinkCommand, oCnn);
    try
    {
        oCnn.Open();
        command.ExecuteNonQuery();
        result = true;
    }
    catch (Exception ex)
    {
        Console.WriteLine($"An error occurred while executing SqlServer Shrink on {GetDatabaseNameFromConnection(connectionString)}: {ex.Message}, {ex.StackTrace}");
    }
    finally
    {
        oCnn.Close();
    }
    return result;
}

Conclusion

While shrinking can be helpful in certain instances, there are also potential downsides.

Reducing a database’s size might result in increased data fragmentation, negatively influencing performance. The shrink operation may reorder pages within the file to save space.

Downsizing a database can be resource-intensive, which may affect the database’s performance while the operation is happening.

Shrinking should not be regarded as a routine maintenance task but rather something to be used sparingly when certain conditions require it, as I mentioned after huge updates.

Microsoft SQL Server shrinking is an effective tool for reclaiming unnecessary space and minimizing disk consumption. Still, it should be used cautiously due to the potential impact on performance and fragmentation.


Similar Articles