Need Specific result

  • --My Query to get Space left in a data file.
    CREATE TABLE ##ALL_DB_Files (
    dbname SYSNAME,
    fileid smallint,
    groupid smallint,
    INT NOT NULL,
    [maxsize] INT NOT NULL,
    growth INT NOT NULL,
    status INT,
    perf INT,
    [name] SYSNAME NOT NULL,
    [filename] NVARCHAR(260) NOT NULL)

    -- loop over all databases and collect the information from sysfiles
    EXEC sp_MsForEachDB
    @command1='use [$];Insert into ##ALL_DB_Files select db_name(), * from sysfiles',
    @replacechar = '$'
    -- output the results
    SELECT 
    [dbname] AS DatabaseName,
    [name] AS dbFileLogicalName,
    [filename] AS dbFilePhysicalFilePath,
    ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS ActualSizeMB,
    ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) AS MaxRestrictedSizeMB,
    ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) - ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS SpaceLeftMB
    FROM ##ALL_DB_Files
    WHERE maxsize > -1 AND -- skip db files that have no max size 
    ([maxsize] - ) * 1.0 < 0.01 * 10 * [maxsize] -- find db files within percentage
    ORDER BY 6
    --DROP TABLE ##ALL_DB_Files

    Question: from above query I am getting Spaceleft for XYZ database which has 4 data file (XYZ.mdf, XYZ1.ndf, XYZ2.ndf), XYZ3.ndf) I need only 1 output from XYZ database which has Max spaceleft on data file.

  • Nita Reddy - Friday, January 4, 2019 10:57 AM

    --My Query to get Space left in a data file.
    CREATE TABLE ##ALL_DB_Files (
    dbname SYSNAME,
    fileid smallint,
    groupid smallint,
    INT NOT NULL,
    [maxsize] INT NOT NULL,
    growth INT NOT NULL,
    status INT,
    perf INT,
    [name] SYSNAME NOT NULL,
    [filename] NVARCHAR(260) NOT NULL)

    -- loop over all databases and collect the information from sysfiles
    EXEC sp_MsForEachDB
    @command1='use [$];Insert into ##ALL_DB_Files select db_name(), * from sysfiles',
    @replacechar = '$'
    -- output the results
    SELECT 
    [dbname] AS DatabaseName,
    [name] AS dbFileLogicalName,
    [filename] AS dbFilePhysicalFilePath,
    ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS ActualSizeMB,
    ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) AS MaxRestrictedSizeMB,
    ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) - ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS SpaceLeftMB
    FROM ##ALL_DB_Files
    WHERE maxsize > -1 AND -- skip db files that have no max size 
    ([maxsize] - ) * 1.0 < 0.01 * 10 * [maxsize] -- find db files within percentage
    ORDER BY 6
    --DROP TABLE ##ALL_DB_Files

    Question: from above query I am getting Spaceleft for XYZ database which has 4 data file (XYZ.mdf, XYZ1.ndf, XYZ2.ndf), XYZ3.ndf) I need only 1 output from XYZ database which has Max spaceleft on data file.

    Please do not double post your questions.
    Please post replies to first post here: https://www.sqlservercentral.com/Forums/2015381/Query-with-specific-Output

  • this was triple posted.  I responded to this one

    https://www.sqlservercentral.com/Forums/2015379/Query

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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