Tag: SQL Database Compression

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