WITH Query definition not working

Question

WITH Query definition not working

One Orange Chip

Points: 29472

March 16, 2020 at 10:05 am

SELECT * 
FROM OPENQUERY (
[LinkedServer],
'
WITH Disk_Size (Servername,DiskMountPoint,fileSystemType,LogicalDrive,ToTalSizeGB,AvilableSizeGB,SpaceFree)
AS
(
SELECT DISTINCT @@Servername Servername,  
volume_mount_point [Disk Mount Point], 
file_system_type [File System Type], 
logical_volume_name as [Logical Drive Name], 
CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],  
CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
FROM sys.master_files 
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)
)
Select * from Disk_Size'

could you suggest anyone what is wrong that query.

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 1

Yes, I got it & working now. ) is missing.

But the above query retrieve datafile mount point only. it not fetched all available logical drive name.

handkot SSCertifiable Points: 5181 More actions · Answer 2

EXEC master..xp_fixeddrives

I Have Nine Lives You Have One Only
THINK!

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

this code EXEC master..xp_fixeddrives retrieve only available freespace. but total allocated size not retrieved

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

I don't have much time today but do a search for PowerShell and WMI for disk space.

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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 5

Maybe a permissions issue? I don't see why drives wouldn't show up for that query.

That code is extremely inefficient though, since it pulls drive info once for every file, instead of just once per distinct drive.

Here's sample code that will query for each drive letter only once:

    SELECT 
        GETDATE() AS date_captured,
        mf.drive, ovs.logical_volume_name AS drive_name,
        CAST(ovs.available_bytes / 1024.0 / 1024.0 / 1024.0 AS decimal(9, 1)) AS free_gb,
        CAST(ovs.total_bytes / 1024.0 / 1024.0 / 1024.0 AS decimal(9, 1)) AS total_gb,
        CAST(mf.data_pages / 128.0 / 1024.0 AS decimal(9, 1)) AS data_gb, 
        CAST(mf.log_pages / 128.0 / 1024.0 AS decimal(9, 1)) AS log_gb,
        mf.data_file_count, mf.log_file_count
    FROM (
        SELECT 
            LEFT(physical_name, 2) AS drive,
            SUM(CASE WHEN type_desc = 'LOG' THEN 0 ELSE 1 END) AS data_file_count,
            SUM(CASE WHEN type_desc = 'LOG' THEN 1 ELSE 0 END) AS log_file_count,
            SUM(CASE WHEN type_desc = 'LOG' THEN 0 ELSE size END) AS data_pages,
            SUM(CASE WHEN type_desc = 'LOG' THEN size ELSE 0 END) AS log_pages,
            MAX(database_id) AS database_id
        FROM sys.master_files mf1
        GROUP BY LEFT(mf1.physical_name, 2)
    ) AS mf
    CROSS APPLY (
        SELECT TOP (1) mf2.file_id
        FROM sys.master_files mf2
        WHERE LEFT(mf2.physical_name, 2) = mf.drive AND
            mf2.database_id = mf.database_id
    ) AS ca1
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, ca1.file_id) AS ovs

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Yeah, I checked that first query. some servers all available drive retrieved and some servers not retrieved. could you suggest me what kind of permission required for fetching all logical drives. all servers VMware virtual machine.

Thank you

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

SQL Galaxy wrote:

Yeah, I checked that first query. some servers all available drive retrieved and some servers not retrieved. could you suggest me what kind of permission required for fetching all logical drives. all servers VMware virtual machine.
Thank you

See the following post. It even finds thumb-drives and produces an emailed report for things that are "out of spec" as well as a "Removable Media Finder". If you're absolutely not allowed to use xp_CmdShell, you should still look because there's a whole lot of ancillary technique in it that you could use through a different "vehicle". WMI is the heavy worker bee here.

https://www.sqlservercentral.com/forums/topic/verify-ip-online-status-using-sql-server-query/page/2/#post-3728014

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