Wednesday, August 8, 2007

Enumerate Disks & Disk Free Space On SQL Server

    Ever cameup with a situation wherein you want to create a databaes on the fly and to decide dynamically where to place your data and log files, yes I faced it and cameup with the following solution.

    You have one stored procedure and one function that can help, first xp_fixeddrives and second one is a system function fn_servershareddrives. xp_fixeddrives procedure outputs the list of drives with free space available on each of the drives, but does not tell you which is shared(SAN drive) and which is local. This is important because you cannot create a database on a local drive when SQL Server is running in clustered environment. And function fn_servershareddrives() does not list the free space available and also does not report local drives so combine the results of both and list every thing and pick what you want and create the database, based on isClusterd() server property output. Here is what I did.


      CREATE TABLE #DriveSpace (
              Drive char(1),
              MBFree int

      -- get availabe drives and free space on each of them
      INSERT INTO #DriveSpace EXEC xp_fixeddrives

      SELECT * INTO #DriveUnion FROM
              SELECT  A.DriveName COLLATE Latin1_General_CI_AS 
              [Drives Available], #DriveSpace.MBFree, 'X'[Shared] 
              FROM    ::fn_servershareddrives() A
                      LEFT JOIN #DriveSpace on
                      A.DriveName COLLATE Latin1_General_CI_AS =

              SELECT  Drive COLLATE Latin1_General_CI_AS [Drives Available], 
                      MBFree, '-'[Shared] 
              FROM    #DriveSpace 
              WHERE   Drive NOT IN (SELECT DriveName COLLATE Latin1_General_CI_AS 
                              FROM ::fn_servershareddrives())
      ) A

       -- if clustered environment eliminate local drives from the list.
       IF SERVERPROPERTY('isClustered')=1
              DELETE FROM #DriveUnion where Shared='-'
      SELECT * FROM #DriveUnion

      DROP TABLE #DriveSpace
      DROP TABLE #DriveUnion

    Sample Output

    'X' indicates shared drive, where as '-' indicates local drive. 
    Drives Available MBFree      Shared
    ---------------- ----------- ------
    C                50469       -
    D                36053       -
    E                267942      X
    F                39384       X
    G                3657        X
    H                179584      X

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.