SQL Database Compression

SQL Database Compression :

Follow the following steps to compress the database:

Steps :

  1. Go to Start –> Programs –> Microsoft Server 2005 –>  SQL Server Management Studio; Click SQL Server Management Studio Connect to Server dialog box will appear (see Figure 1).
Figure 1

2.  Enter the password of the server in the Password field.

3. Click Connect. It will connect to the database server and Microsoft Server Management Studio window will refresh (see Figure 2).

Figure 2

4. Expand Databases subfolder (see Figure 3).

Figure 3

5. Select ‘WSS_Content_8888’ sub folder.

6. Click   .  A new query file will open.

7. Type the following query in the new query file. (see Figure 4).

Figure 4

8.  Click to execute the query (see Figure 5)

Figure 5

9. Once query is executed success fully, the following window will appear (see Figure 6):

Figure 6

10. The size of the ‘WSS_Content_8888’ before compression was shown below in (Figure 7):

Figure 7

11. The size of the ‘WSS_Content_8888’after Database compression will be as shown below in (Figure 8):

Figure 8

2nd Method In GUI :


  1. Go to Start –> Programs –> Microsoft Server 2005 –> SQL Server Management Studio, Click SQL Server Management Studio. Connect to Server dialog box will appear (see Figure 1).
Figure-1

2. Enter the   password of the server in the Password field.

3. Click Connect. It will connect to the database server and Microsoft Server Management Studio window      will refresh (see Figure 2).

Figure-2

4. Expand Databases sub folder (see Figure 3).

Figure-3

5. Select ‘WSS_Content_8888’ sub folder, right click –>properties  click on properties (shown in Figure 4)

Figure-4

6. In properties window  ( shown Figure 5), go to options , click options –> go to Recovery Model which by   default has value full ( shown in Figure 5)

Figure-5

7. Change the value in Recovery model to simple (as shown Figures 6 & 7), Click OK button.

Figure-6
Figure-7

8. Select ‘WSS_Content_8888’ –>right click  –> Tasks –> Shrink –> Files ( shown in Figure 8)

Figure-8

9. The following window will open ( see Figure 9)

Figure-9

10 . Click OK, it compresses database ‘WSS_Content_8888’ data (.mdf) file.

11. Click on the file type drop down menu (see Figure 10), and select LOG., Click OK button.

Figure-10

12 . It compresses LOG (.ldf) file. Final output shown below ( Figure 11):

Figure-11

13 . Once Database compression over, revert the Recovery model settings from Simple to Full (see Figure 12)

Figure-12


2 thoughts on “SQL Database Compression

  1. Your readers should have a look at SQL Storage Compress from Red Gate Software as well, this will provide streaming data compression for SQL Server data files, filegroups or entire databases

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s