Freelance Project Requests Software Development in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net

SQL Server: Shrink Database
Task: Shrink or compress the database
Over time, databases are getting bigger and bigger. This is also due to the fact that data that is deleted is only internally blown out.
Therefore, it is often recommended to compress the database from time to time.
How much does it cost?
Shrink Database is sufficient in the normal case.
A subsequent shrink files does not bring any improvement.
Initial situation:
The connected and active SQL Server database Rue25.mdf has a size of 72 MB at runtime and a log file of 427 MB

If you create a regular backup, the database with 72 MB is saved as a .bak file.
This corresponds to the current, active .mdf database without log entries.

A look at the database properties under the Microsoft SQL Server Management Studio shows a Database Size of 488 MB.
Here the running database plus the log file is added together.

Shrink Database
The function: Compression of the database is divided into two subfunctions.
  • Shrink Database
    Shrink Database compresses the entire database and all files.
  • Shrink Files
    With Shrink Files you can compress individual files if the database is divided into several files.
    The database can be optimized as a logical internal structure and the files themselves can be reorganized.
    Dialog: Shrink Database
    Shrink Database compresses all files.
    The choice is whether you have the option: Reorganize files before releasing unused space.
    The size of a database is reduced by collectively shrinking the database files, releasing unused space.
    To shrink individual database files, use Shrink files.
    After that, the .ldf has shrunk from 488 MB to 1 MB.
    The mdf file has remained almost the same.
    Shrink Database Files
    Is there an additional effect when you compress the files additionally?
    Conclusion: no.
    Dialog: Shrink File
    You can choose between the options:
  • Release unused space
  • Reorganize pages before releasing unused space

Both options have no effect on 1-file databases.

Result after ShrinkFile
The file sizes are the same as with the Shrink Database

SQL Server 2014: Can not connect to SQLEXPRESS
SQL Server: Compress a SQL Database with Shrink Database
SQL Server: Anmelden mit .\Sqlexpress
Fehlermeldung: beim Anfügen einer Datenbank
SQL Server : Tabellen Änderungen speichern ohne Nicht-Zulässig-Meldung von Entwurfsänderungen
Anleitung: Kopieren einer Datenbank als neue Datenbank


Contact for Jobs, Project Requests: