Query

Question

Query

Nita Reddy

SSCarpal Tunnel

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

#370045

--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 8 posts - 1 through 7 (of 7 total)

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

can you just sum the numbers up

select DatabaseName, sum(ActualSizeMB) as ActualSizeMB, sum(MaxRestrictedSizeMB) as MaxRestrictedSizeMB, sum(SpaceLeftMB) as SpaceLeftMB from ( 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 -- skip db files that have no max size --AND ([maxsize] - ) * 1.0 < 0.01 * 10 * [maxsize] -- find db files within percentage ) v group by DatabaseName ORDER BY 4

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/

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 2

THis is also including Log space as well, can we just get the sum of data file only?? and Log as seperate

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

Nita Reddy - Friday, January 4, 2019 10:53 AM
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.

You'll hate yourself in the morning when you run out of space after your report says you wouldn't. It's quite common for tables with multiple files to have one or more files grow at different rates and for one or more files to be set to different maximums. You really need to report by file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

Nita Reddy - Friday, January 4, 2019 10:53 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.

Also, stop using sysfiles. It's been deprecated since 2005 (IIRC) and requires the use of sp_MSForEachDB, which is undocumented, has its own problems, and causes your code to be much more complicated that necessary. Do a search for sys.master_files and see the joy there.

Finally, stop killing yourself with trying to convert pages to MB the way you did. Just divide the number of pages by 128 or 128.0 and Bob's your uncle.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

Here's an example of how short the code can be thanks to sys.master_files.
SELECT DatabaseName = DB_NAME(database_id) ,FileType = type_desc ,dbFileLogicalName = name ,dbFilePhysicalFilePath = physical_name ,ActualSizeMB = CONVERT(DECIMAL(9,1),size/128.0) ,MaxRestrictedSizeMB = max_size/128 ,SpaceLeftMB = (max_size-size)/128 ,PercentFree = CONVERT(DECIMAL(9,1),(max_size-size)*100.0/max_size) FROM sys.master_files WHERE max_size <> -1 --Skip DB files that are set to "Unrestricted File Growth" ORDER BY PercentFree ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63501 More actions · Answer 6

pietlinden

SSC Guru

Points: 63501

January 4, 2019 at 9:28 pm

#2018057

Oh wow... MUCH more readable!

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 7

pietlinden - Friday, January 4, 2019 9:28 PM
Oh wow... MUCH more readable!

And no Temp Table. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)