Freelance Project Requests info@CodeDocu.de 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?
 
Conclusion:
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.
     
     
    Result:
    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
    or
  • Reorganize pages before releasing unused space

Selected.
Both options have no effect on 1-file databases.

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

 
Mobile
»
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: raimund.popp@microsoft-programmierer.de