Managing the size of the SQL database

[G.W.Green]

SQL databases are made up of two main files; the mdf file and ldf file.

The ldf file is a ‘Transaction Log’ file and records all the changes made to the database, no matter how small those changes are. As you can imagine, this can make the file quite large, which in turn increases the overall size of the database, and therefore affects the performance of Sellerdeck.

Common affects of a large ldf file include, poor startup time, failing snapshot export and import, poor navigation around the content tree and slow switching tabs.

To keep the ldf file from getting too large, it is important that the ‘Recovery Model’ of the database is set to ‘SIMPLE’ and not ‘FULL’. To check and if necessary change this, do the following:

1. Open SQL Server Management Studio
2. Expand Databases from the directory tree on the left
3. Locate your databases
4. Right click on the database (eg, Site1_Catalog) and choose Properties
5. Click on Options on the left
6. On the right you will see the Recovery Model.
7. Ensure this is set to SIMPLE and press ok
   
   

Now we can ‘Shrink’ the log file to free up space in the file. This should then stay at a smaller file size.

1. In SQL Server Management Studio, locate and right click on your database (eg Site1_Catalog)
2. Go to ‘Tasks | Shrink | Files’
3. Change the File Type to ‘Log’
4. Take a look at the ‘Available free space’. If this is a high percentage, ie 99%, then the database will shrink quite noticeably
5. Press OK
   
   
   
   
6. Do the same for the shipping databases