February 13, 2020 at 10:03 pm
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.
February 14, 2020 at 9:00 am
I think you hit the nail on the head when you used the word MERGE - are you familiar with the command?
MVDBA
February 14, 2020 at 8:15 pm
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.
EDIT - alternate solution would be to join the 2 queries. May make more sense to join them than to do a table variable...
EDIT 2 - Here is the SQL query I think you are looking for:
USE [master];
GO
-- declare temporary tables.
DECLARE @TMPFIXEDDRIVES TABLE
(
[DRIVE] CHAR(1)
, [MBFREE] INT
);
DECLARE @TMPSPACEUSED TABLE
(
[DBNAME] VARCHAR(50)
, [FILENME] VARCHAR(50)
, [SPACEUSED] FLOAT
);
-- populate temporary tables
INSERT INTO @TMPFIXEDDRIVES
EXEC [sys].[xp_fixeddrives];
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'
ENDAS [DISKSPACEFREE]
, [A].[name]AS [DATABASENAME]
, .[name]AS [FILENAME]
, CASE .[type]
WHEN 0
THEN 'DATA'
ELSE .[type_desc]
ENDAS [FILETYPE]
, CASE
WHEN (. * 8 / 1024.0) > 1000
THEN CAST(CAST(((. * 8 / 1024) / 1024.0) AS DECIMAL(18, 2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((. * 8 / 1024.0) AS DECIMAL(18, 2)) AS VARCHAR(20)) + ' MB'
ENDAS [FILESIZE]
, CAST((. * 8 / 1024.0) - ([D].[SPACEUSED] / 128.0) AS DECIMAL(15, 2)) AS [SPACEFREE]
, .[physical_name]
, [Q1].[Size_in_MB]
, [Q1].[Free_in_MB]
, [Q1].[FreePercentage]
FROM[sys].[databases] AS [A]
JOIN[sys].[master_files] AS
ON [A].[database_id] = .[database_id]
JOIN@TMPFIXEDDRIVES AS [C]
ON LEFT(.[physical_name], 1) = [C].[DRIVE]
JOIN@TMPSPACEUSED AS [D]
ON [A].[name] = [D].[DBNAME]
AND .[name] = [D].[FILENME]
JOIN
(
SELECTDISTINCT
[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
) AS [Q1]
ON [C].[DRIVE] = LEFT([Q1].[volume_mount_point], 1)
ORDER BY[DISKSPACEFREE]
, [SPACEFREE] DESC;
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply