Help with SQL Script

  • EMtwo

    Hall of Fame

    Points: 3425

    I'll like to monitor my databases for the following:

    Size_in_MB, Free_In_MB, Free_In_MB, FreePercentage, Drive, DiskSpaceFree, DatabaseName, Filename, File_type, FileSize, SpaceFree, Physical_Name

    I have a couple of scripts that provide the results I want but just in the two scripts - I want to be able to either merge the results of the two tables or have a script that can have the about result put into a table.

    Below are the two scripts:

    --First Query returns each Disk Drive, its total size, amount of free space, and Percentage of free space

    SELECT distinct(volume_mount_point),

    total_bytes/1048576 as Size_in_MB,

    available_bytes/1048576 as Free_in_MB,

    (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage

    FROM sys.master_files AS f CROSS APPLY

    sys.dm_os_volume_stats(f.database_id, f.file_id)

    group by volume_mount_point, total_bytes/1048576,

    available_bytes/1048576 order by 1

    --Second Query returns each Disk Drive, Diskspacefree, Databasename, filename, filetype, filesize, spacefree, physicalname

    USE master

    GO

    CREATE TABLE #TMPFIXEDDRIVES ( DRIVE CHAR(1), MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES

    EXEC xp_FIXEDDRIVES

    CREATE TABLE #TMPSPACEUSED ( DBNAME VARCHAR(50), FILENME VARCHAR(50), SPACEUSED FLOAT)

    INSERT INTO #TMPSPACEUSED

    EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

    SELECT C.DRIVE,

    CASE

    WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'

    ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'

    END AS DISKSPACEFREE,

    A.NAME AS DATABASENAME,

    B.NAME AS FILENAME,

    CASE B.TYPE

    WHEN 0 THEN 'DATA'

    ELSE TYPE_DESC

    END AS FILETYPE,

    CASE

    WHEN (B.SIZE * 8 / 1024.0) > 1000

    THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'

    ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'

    END AS FILESIZE,

    CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,

    B.PHYSICAL_NAME

    FROM SYS.DATABASES A

    JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID

    JOIN #TMPFIXEDDRIVES C ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

    JOIN #TMPSPACEUSED D ON A.NAME = D.DBNAME AND B.NAME = D.FILENME

    ORDER BY DISKSPACEFREE,

    SPACEFREE DESC

    --DROP TABLE #TMPFIXEDDRIVES

    --DROP TABLE #TMPSPACEUSED

    If anyone has a script that I can use to perform my task will be most appreciated.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21453

    I think you hit the nail on the head when you used the word MERGE  - are you familiar with the command?

    MVDBA

  • Mr. Brian Gale

    SSC-Insane

    Points: 22506

    As a thought, what about creating a temporary table (or table variable), with all of the columns you want and populating that table by running both queries.  As both queries have at least one similar data point (the drive letter), run one query and then update the temp table with the other query where volume_mount_point like drive+':\'.

     

    Personally, I am not seeing a nice usage of "MERGE" in this case, but I might be thinking of thing wrong.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply