Personal Situation

Last month, I received a call from a former client and friend. There was a panicked tone in her voice. Seems that there was a mapping deadline, and the GIS Department could not function and was basically “dead in the water”.

After some remote research to determine their problem(s), we learned that their database size was 10 gigabytes. That may not seem like a great deal, but if using SQL Server Express, it is major thing. As previously published, 10 gigabytes is the maximum database size for the free software. Since I was familiar with their system (only been a year since deployment), this amount seemed extremely excessive given the customer size and feature class quantity.

I asked the GIS Manager when was the last time they had done a compress on the database. Huh? Long story short……they had never conducted database maintenance (other than daily backups) in a year. After learning this, I was optimistic that we had found the “Silver Bullet”, and I am not talking about a Coors Light. Compressing the database removes unreferenced states from the version tree, which improves performance.

Database Management 101: Maintenance Procedures

First thing we did was connect to the database via ArcCatalog, and compress the database. The procedure is as follows:

• Create a connection (if necessary) and login to the desired database as an Administrator.
• Right-click on the connection, choose Administration from the context menu, and Compress Database (Figure 1).

• We are asked to confirm the procedure by clicking the “Yes” button (Figure 2).

The Compress procedure finishes in a few hours (because of the duration of time since it was last run). BUT…..it is only half of the procedure. Compressing will remove the rows in the database, but an Administrator must remove that space taken up via SQL Server Management Studio. Piece of cake. Conduct the following secondary procedure:

• Login to SQL Server Management Studio as an Administrator.
• Right-click on the desired database, choose “Tasks”, choose “Shrink”, and lastly “Database” from the context menus (Figure 3).

• The next GUI (Figure 4) summarizes the amount of free space one can expect to remove by running the procedure. Since this is just an example, the Available Free Space is low. Click the “OK” button to run.

Conclusion

Following the Shrink Procedure, the client’s resultant database size was one gigabyte (a removal of nine gigabytes of wasted space), which is significantly below the 10-gigabyte threshold. The procedure also brought them to a working status. For optimal database performance, these procedures should be run at a frequency dependent upon the amount of database use/edit.

Now that their problem was solved, and they are up and running again…….Time for the other Silver Bullet. Bottoms up!