Need Specific result

Question

Need Specific result

Nita Reddy

SSCarpal Tunnel

Points: 4177
More actions
January 4, 2019 at 10:57 am

#378929

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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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

Mike01 SSChampion Points: 11395 More actions · Answer 2

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/