0

We have a web application that, for government-regulated security reasons, requires each client to have their own database. As a result, we have a few thousand small databases that all have the exact same schema (each database is between 10MB and 50MB right now, but will never be larger than ~400MB)

We have a program that automatically scans through each database and upgrades the schema as there are changes to our master copy, which is working great. However, we do not have any scripted maintenance being run other than nightly backups.

Any suggestions on what else we should be running? Integrity checking, updating statistics, rebuilding indexes? No matter what we run, we'd have to make sure we can easily get a pass/fail from the script so that we can easily report the failures to investigate later the next morning (since we're not going to watch a script that is repeated thousands of times).

Beep beep
  • 1,833
  • 2
  • 18
  • 33
  • Try to get a little more into your database design, and its purpose and usage. There are no single golden rule for maintenance plans like this – Mathias R. Jessen Jan 12 '12 at 01:51
  • I'm not sure what you mean. We have ~250 tables in each database, 80 of which are transactional but grow relatively slowly (our largest database grows by ~200,000 rows per month, but most by ~2,000). Databases are accessed daily by clients via a web browser data entry application. – Beep beep Jan 12 '12 at 12:51

1 Answers1

2

Powershell is going to be your friend here. Off the cuff:

$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "your-server-name-here"
foreach ($db in $server.Databases) {
   foreach ($table in $db.Tables) {
      foreach ($index in $table.Indexes) {
         $index.Rebuild()
      }
   }
}

Would I use this script unmodified? No (for one, it just blindly rebuilds every table in every database on the instance whether it needs it or not). But it shows you how powerful this can be with very little code.

Ben Thul
  • 3,024
  • 17
  • 24
  • 1
    do you have any links to how we would handle logging and error reporting of sql server scripts with ps? I had no idea you could do this type of thing, very cool! – Beep beep Jan 12 '12 at 12:49
  • 1
    For catching errors, you're looking for "trap". Throw "trap powershell" into your favorite search engine and read the result that makes the most sense. As to logging, that can be as easy as redirecting the output to a log file. Or, if you want to log to SQL Server, check out PSX on codeplex (sqlpsx.codeplex.com). Good luck! – Ben Thul Jan 13 '12 at 15:56