question

akmatey avatar image
akmatey asked

Good alternative to using sp_msforeachdb

I inherited the script below and struggling to run this against all databases across various instances. it works when I use sp_msforeachdb but it does not output the way I want it to and it also includes blank result sets for databases with no data which is not what I want which is part of the problem of using sp_msforeachdb...I feel it's my joins which I have tried different forms but still not outputing the way I want. Help!!

SELECT DatabaseName, [File Name], [Physical Name], [File - Total Size In MB], [File - Available Space In MB], [Filegroup Name], [Disk Mount Point], [Disk Total Size in GB], [Disk Available Size in GB], [Disk Free Space %], Growth, NextGrowthRequirementInMB, FileSpaceAfterGrowthMB FROM ( SELECT DB_NAME() AS DatabaseName, f.[file_id], f.name AS [File Name] , f.physical_name AS [Physical Name], CAST((f.size/128.0) AS DECIMAL(15,2)) AS [File - Total Size In MB], CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [File - Available Space In MB], fg.name AS [Filegroup Name], volume_mount_point [Disk Mount Point], CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Disk Total Size in GB], ---1GB = 1073741824 bytes CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Disk Available Size in GB], CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Disk Free Space %], f.Growth, CASE f.is_percent_growth WHEN 0 THEN f.Growth/128 WHEN 1 THEN f.growth/100.00*CAST((f.size/128.0) AS DECIMAL(15,2)) END AS NextGrowthRequirementInMB, CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) - (CASE f.is_percent_growth WHEN 0 THEN f.Growth/128 WHEN 1 THEN f.growth/100.00*CAST((f.size/128.0) AS DECIMAL(15,2)) END) AS FileSpaceAfterGrowthMB, f.is_percent_growth, file_system_type [File System Type] FROM sys.database_files AS f WITH (NOLOCK) INNER JOIN sys.master_files m on f.file_id = m.file_id LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id CROSS APPLY sys.dm_os_volume_stats(database_id, m.file_id) WHERE m.database_id = DB_ID() and m.database_id > 4) a

--where [Physical Name] like ''%%df''

--ORDER BY 1 desc

sql server
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WRBI avatar image WRBI commented ·

You say a couple of times that it's not outputting the way you want it to, but i don't see an example of how you want it outputting.

Also, if you stick the T-SQL into a the CODE tag in the editor, it will format nicely, which is easier to read.

0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered

What you need to do is to rewrite your query to insert the results of that SELECT into a temporary table, and then query that temp table to remove / show the lines you want.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

akmatey avatar image akmatey commented ·

Thanks that is exactly what I have done with some help.

1 Like 1 ·
akmatey avatar image
akmatey answered
I have inserted the updated code into the code tab as requested. The issue I am facing is not be ing able to output the script in order of instances because I am running it against several databases across multiple instances via CMS server. 
What I am after is to be able to sort the output based on instance/servername but what I get is various databases per grid which does not help with exporting to a spreadsheet.


SET NOCOUNT ON
declare @cmd1 nvarchar(max)
declare @text nvarchar(50)

set @text = @@SERVERNAME;
set @cmd1 =
'use ?
SELECT ''?''
DatabaseName, [File Name], [InstanceName], [Physical Name], [File - Total Size In MB], [File - Available Space In MB], [Filegroup Name], [Disk Mount Point], [Disk Total Size in GB], [Disk Available Size in GB], [Disk Free Space %], Growth, NextGrowthRequirementInMB, FileSpaceAfterGrowthMB FROM ( SELECT
       @@servername AS [InstanceName],
	   DB_NAME() AS DatabaseName,
       f.[file_id],
       f.name AS [File Name] ,
       f.physical_name AS [Physical Name],
       CAST((f.size/128.0) AS DECIMAL(15,2)) AS [File - Total Size In MB],
       CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int)/128.0 AS DECIMAL(15,2)) AS [File - Available Space In MB],
       fg.name AS [Filegroup Name],
       volume_mount_point [Disk Mount Point],
       CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Disk Total Size in GB], ---1GB = 1073741824 bytes
       CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Disk Available Size in GB], 
       CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Disk Free Space %],
	   f.Growth,
		CASE f.is_percent_growth 
			WHEN 0 THEN f.Growth/128
			WHEN 1 THEN f.growth/100.00*CAST((f.size/128.0) AS DECIMAL(15,2))
		END AS NextGrowthRequirementInMB,
		CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) - (CASE f.is_percent_growth 
			WHEN 0 THEN f.Growth/128
			WHEN 1 THEN f.growth/100.00*CAST((f.size/128.0) AS DECIMAL(15,2))
		END) AS FileSpaceAfterGrowthMB,
		f.is_percent_growth,
       file_system_type [File System Type] FROM sys.database_files AS f WITH (NOLOCK) INNER JOIN sys.master_files m on f.file_id = m.file_id LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id CROSS APPLY sys.dm_os_volume_stats(database_id, m.file_id) WHERE m.database_id = DB_ID()) a where DB_NAME() NOT IN(''master'',''model'',''msdb'',''tempdb'',''xxx'',''xxx'',''xxx'')
ORDER BY instancename desc'
	   exec sp_foreachdb @command = @cmd1
	   GO
	   SET NOCOUNT OFF
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.