Database Management 101

Simplistic Database Management 101

October 20, 2017 — Brian Higgins

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).
Figure 1. Compressing Database - Database Management 101

 

  • We are asked to confirm the procedure by clicking the “Yes” button (Figure 2).
Figure 2. Compress Procedure Configuration - Database Management 101

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).
Figure 3. Shrinking Database - Database Management 101
  • 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.
Figure 4. Shrink Database GUI - Database Management 101

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!

We Wrote the Book

The Indispensible Guide to ArcGIS Online

Download It for Free

Brian Higgins

Brian Higgins is a Senior Consultant at the Utility & Telecommunications GIS consulting company SSP Innovations in Centennial, Colorado.  He is a certified Geographic Information Systems Professional (GISP) with 22 years of experience in the design and development of GIS systems for the management of municipal infrastructure.

What do you think?

Leave a comment, and share your thoughts

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>