CodeDocu.com

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
SQL Server: Compress a SQL Database with Shrink Database
 
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.
SQL Server: Compress a SQL Database with Shrink Database
 
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.
SQL Server: Compress a SQL Database with Shrink Database
 
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.
SQL Server: Compress a SQL Database with Shrink Database
 
Result after ShrinkFile
The file sizes are the same as with the Shrink Database
SQL Server: Compress a SQL Database with Shrink Database
 

Software Entwicklung Stuttgart NĂ¼rtingen
Suche Projekte C#, WPF, Windows App,ASP.Net, vb.Net, WinForms, SQL Server, Access, Excel