You are here

Simplistic Database Management 101

database management 101Personal 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!

Author Information

  • 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.

    See all items created by this author >

    Connect with me on:

Category Tags:

Brian HigginsDataGIS

Comments

Hi Brian, thanks for the advice!
I am coordinating maintenance with our DBA currently, and wondering how often I should have them perform "shrink". They have heard that too often can cause negative results. I am creating a nightly reconcile/compress/rebuild-index/analyze-datasets python script. It needs to run after hours and disconnect all users.
ESRI does not recommend performing a shrink each time I compress. Do you think a weekly shrink would be appropriate?

Hi, Joe.  Thanks for your question/comment.

I agree with Esri.  It should be noted that in the case study provided, it had been a year since a shrink was conducted.  Although I do recommend periodic maintenance on the database, I don't think this is something that needs to be conducted daily.  Weekly at best, and probably more like monthly.   It depends upon the frequency/quantity of edits in the given duration.  

It should also be noted that prior to conducting the procedure, SQL Server will inform as to the amount of space gain.  Consider using that to gauge the frequency required.

Hope this helps.

Brian

Add new comment