Friday, July 26, 2019

SQL Server Shrink

Problem 

  • SQL Server disk usage is very high. 
  • SQL Server Management Studio is very slow.

Investigation

Windows Event log:
  • Starting Up Database
  • Could Not Allocate Space for Object Name in Database ‘DB’ Because the ‘PRIMARY’ Filegroup is Full
  • CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.

Check

Size of database files (data and log) may be at maximum limit.

Size Limitation Database Size for Microsoft SQL Server versions
  • Microsoft SQL Server 2000 Desktop - 2 GB
  • Microsoft SQL Server 2005 Express edition has a database size limit to 4GB
  • Microsoft SQL Server 2008 Express edition has a database size limit to 4GB
  • Microsoft SQL Server 2008 R2 Express edition has a database size limit to 10GB
  • Microsoft SQL Server 2012 Express edition has a database size limit to 10GB
  • Microsoft SQL Server 2014 Express edition has a database size limit to 10GB
  • Microsoft SQL Server 2016 Express edition has a database size limit to 10GB

Solution

Shrink data file and log file.

Initial sizes:

  1. Connect SQL Server via SQL Management Studio.

  2. Find database in left pane, right click and select Tasks > Shrink > Files.


  3. Select "Log" under "File type" list. Select "Reorganize pages...". You can enter minimum MB in text box as shown next to it. I entered 1 and completed shrink.


  4. Now you may also want to shrink data file. Repeat 2nd and 3rd steps for "File type: Data".
  5. I entered 1000 and completed shrink.

File sizes after shrink:


0 comments: