SQL Server Maintenance Plan?

03-07-2017 12:10 PM
New Contributor II

We've recently migrated to the ESRI platform and are still trying to get all the bugs worked out and everything running properly. When we initially set up our databases in SQL Server 2012, we did not define a maintenance plan. We are at a point now where we need to be maintaining and backing up our databases routinely and I was hoping to automate that process. 

I've done some reading on the web and it seems that we need to be performing some maintenance items along the lines of compressing the databases, rebuilding indexes, updating statistics, etc. SQL Server allows me to configure a maintenance plan that will perform these tasks, however, all of the examples show doing these tasks through ArcCatalog. My question is are these tasks better handled in ArcCatalog or in SQL Server? Also, if handled in SQL Server, are there any details/tricks/tips I need to be aware of? 

Below is a list of maintenance plan options in SQL Server 2012 for reference:

Check Database Integrity, Shrink Database, Reorganize Index, Rebuild Index, Update Statistics, Clean Up History, Execute SQL Server Agent Job, Back Up Database (Full), Back Up Database (Differential), Back Up Database (Transaction Log), Maintenance Cleanup Task

Thanks in advance!

0 Kudos
4 Replies
Esri Regular Contributor

Backups I'd handle via SQL server, but there are some solid python scripts out there to help with the geodatabase maintenance tasks you mention. Check this one out for instance.

New Contributor II

Thanks for that information. I'll take a look at those scripts.

0 Kudos
MVP Regular Contributor

While the Script is a good resource to check, you should read about the concepts to get a better idea. 

Example: The compress operation for the Enterprise Geodatabase is not the same as traditional Compress\Shrink (in case of SQL Server).

So my suggestion would be to read about these first, compare it with the SQL Server maintenance tasks and then decide how you want to get these done.

Here are some links for reference:

Geodatabase maintenance—Help | ArcGIS Desktop 

Geodatabase compression—Help | ArcGIS Desktop 

Update database statistics—Help | ArcGIS Desktop 

Rebuild system table indexes—Help | ArcGIS Desktop 

New Contributor II

Thank you Asrujit! I have looked at some of those links and will research further. 

0 Kudos