Query with specific Output

• Nita Reddy

  SSCarpal Tunnel

  Points: 4177

  More actions

  January 4, 2019 at 10:56 am

  #416602

  --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.

• Mike01

  SSChampion

  Points: 11393

  More actions

  January 4, 2019 at 12:51 pm

  #2018038

  This is triple posted.  Please use this link as that is where I posted a response

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

  Duplicate posts are this one and https://www.sqlservercentral.com/Forums/2015382/Need-Specific-result

  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/