//
you're reading...
SQL Server

Adding Files To A Database (Continued)

In a recent post I wrote about an adventure I had migrating several servers\databases to new EVA SAN storage and the incredible performance gains I saw. I also have a recent post with a script to add files to a database based on the number of processors. This post touches on both. On one of my newly migrated server I noticed some latency via the virtual file stats. Nothing horrible (800ms) in some cases, enough to make me want to tweak things a bit. So I thought I would try and spread the load across available volumes. Instead of having one big file I would split it up into 8 files (1 per processor) and then have the data evenly spread across all eight files. Below is what I came up with, it is a five step process:

  1. 1) Create a temporary file to hold the contents of the current primary file
  2. 2) Empty primary into the temporary file
  3. 3) Resize the primary data file and create additional database files
  4. 4) Empty the temporary file into the other db files
  5. 5) Remove the temporary database file

 

  1. /*– Add Physical Files to a Database and re-distribute the data
    — AUTHOR: L.GALLER
    — DESCRIPTION: This script will take the data from a single physical SQL data file
    –   and distribute evenly over n data files.

    — ITEMS TO CHANGE: (CHANGE TO MATCH YOUR ENVIRONMENT)
    — 1) "YourDB" TO THE ACTUAL DATABASE NAME
    — 2) "Z:\YourDB\Data\" TO THE APPROPROATE VOLUMES & DIRECTORIES
    — 3) 20gb SPREAD OUT OVER n FILES  (20GB\8 = 250000KB + Growth)
    — DISCLAIMER: THIS CODE IS PROVIDED WITHOUT WARRANTY AND CONFERS — NO RIGHTS.
    — TEST THIS ON NON-PRODUCTION SYSTEMS BEFORE ATTEMPTING TO RUN — ON PRODUCTION.

    */
    — Step 1: ADD TEMPORARY DB FILE TO HOLD DATA FOR RE-DISTRIBUTION
    USE [master]
    GO
    ALTER DATABASE [YourDB] ADD FILE (
       NAME = ‘YourDB_TEMP’,
       FILENAME = ‘U:\YourDB_TEMP.ndf’,
       SIZE = 20GB,
       FILEGROWTH = 100MB
    );
    GO

    — Step 2: EMPTY THE PRIMARY DB INTO THIS NEW TEMP FILE
    USE [YourDB]
    GO
    DBCC SHRINKFILE (‘YourDB_Data’,EMPTYFILE);
    GO
    — NOTE: IGNORE ANY ‘CANNOT MOVE ALL CONTENTS OF FILE…’ ERROR MESSAGES. THEY’RE EXPECTED.

    — Step3: RESIZE THE PRIMARY DB FILE, AND ADD n MORE DATA FILES
    — generally add 1 file per cpu
    USE [master]
    GO
    ALTER DATABASE [YourDB] MODIFY FILE (
       NAME = ‘YourDB_Data’,
       SIZE = 300000KB
    );
    GO
    ALTER DATABASE [YourDB] ADD FILE (
       NAME = ‘YourDB_Data2’,
       FILENAME = ‘W:\YourDB_Data2.ndf’ ,
       SIZE = 300000KB ,
       FILEGROWTH = 100MB
    );
    GO
    ALTER DATABASE [YourDB] ADD FILE (
       NAME = ‘YourDB_Data3’,
       FILENAME = ‘U:\YourDB_Data3.ndf’ ,
       SIZE = 300000KB ,
       FILEGROWTH = 100MB
    );
    GO
    ALTER DATABASE [YourDB] ADD FILE (
       NAME = ‘YourDB_Data4’,
       FILENAME = ‘W:\YourDB_Data4.ndf’ ,
       SIZE = 300000KB ,
       FILEGROWTH = 100MB
    );
    GO
    ALTER DATABASE [YourDB] MODIFY FILE (
       NAME = ‘YourDB_Data5’,
       FILENAME = ‘U:\YourDB_Data5.ndf’ ,
       SIZE = 300000KB ,
       FILEGROWTH = 100MB
       );
    GO
    ALTER DATABASE [YourDB] ADD FILE (
       NAME = ‘YourDB_Data6’,
       FILENAME = ‘W:\YourDB_Data6.ndf’ ,
       SIZE = 300000KB ,
       FILEGROWTH = 100MB
    );
    GO
    ALTER DATABASE [YourDB] ADD FILE (
       NAME = ‘YourDB_Data7’,
       FILENAME = ‘U:\YourDB_Data7.ndf’ ,
       SIZE = 300000KB ,
       FILEGROWTH = 100MB
    );
    GO
    ALTER DATABASE [YourDB] ADD FILE (
       NAME = ‘YourDB_Data8’,
       FILENAME = ‘W:\YourDB_Data8.ndf’ ,
       SIZE = 300000KB ,
       FILEGROWTH = 100MB
    );
    GO
    — Step 4: EMPTY THE TEMP FILE INTO THE OTHER DB FILES EVENLY
    USE [YourDB]
    GO
    DBCC SHRINKFILE (‘YourDB_Temp’,EMPTYFILE);
    GO

    — Step 5: REMOVE THE EMPTY TEMPFILE
    USE [YourDB]
    GO
    ALTER DATABASE [YourDB]  REMOVE FILE [YourDB_Temp]
    GO

    As with any script you retrieve off the internet, use with caution and review for appropriateness  in your environment. 

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: