Add files to filegroups and log for many dbs

Total article views: 26 | Views in the last 30 days: 1

 Rate this |   Join the discussion |  Briefcase

Purpose: This SP is used to add a logfile and a datafile to each file group in a database or all databases. This is handy when you have a lot of databases and you want to add a new big SAN-disk and stop db expansion on the old disks.
Consider: Avoid running any large updates when you use this SP. I freeze expansion of old databasefiles while adding another 10 MB extra for safety when I set max size (current size plus 10 MB), if someone is adding data anyway.
Performance: Intel 1000 MHz x 2, 2 GB RAM, StorageTek SAN- disks, 500 databases with a total of 220 GB. Added another SAN-disk with 290 GB. Freezing the old files for the 500 databases and adding a logfile, a file for each of the two filegroups took 25 minutes in daytime on a heavily used system.

Total article views: 26 | Views in the last 30 days: 1