October 6, 2016 at 4:07 am
Good Day guys,
Very excited , that I get a chance to actually be active by posting my very first question. I have been using this forum for quite a while now. Well , enough , here goes.
I want to retrieve database stats using the below command
EXEC sp_MSforeachdb '
USE ?
SELECT
CASE WHEN CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS LIKE ''D%'' THEN ''Dev''
WHEN CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS LIKE ''T%'' THEN ''Test''
WHEN CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS LIKE ''P%'' THEN ''Prod''
WHEN CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS LIKE ''PP%'' THEN ''Pre-Prod''
ELSE ''Uknown''END AS [Environment],
CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
Getdate() as Extract_Date,
CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), v.volume_mount_point) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.[Filename]) COLLATE Latin1_General_CI_AS [Filename],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000,2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],
CONVERT(Decimal(15,2), v.available_bytes/1048576.0) [Drive Free Space (MB)]
INTO dbo.dbstats
FROM sys.sysfiles f WITH (NOLOCK)
INNER JOIN sys.database_files df ON df.file_id = f.fileid
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'
I need to get this code running dynamically through all servers , using opendatasource as linked servers are not allowed. Has anybody went through this before?
SELECT * FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;user id=User;password=Password1'
)
The whole idea , is basically kicking off a dynamic procedure via opendatasource
Thanks,
Tate
October 6, 2016 at 4:35 am
Hi guys,
I think , this was my laziness to sort out the dynamic logic. I have managed to get the dynamic kind of working after testing it with a different method, but now I am getting this error below
The metadata could not be determined because statement 'exec(@precommand)' in procedure 'sp_MSforeachdb' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
If anyone has any light of how I can do it differently , please let me know
Thanks,
Tate
October 7, 2016 at 3:01 pm
I've setup something similar before, but I used OPENROWSET instead. I tried a few different things but I found if I was calling a stored procedure I had to use SET FMTONLY OFF and also store things in a temp table:
DECLARE @instance NVARCHAR(40), @ver smallint, @sqlcmd NVARCHAR(1000);
DECLARE cur_instance CURSOR FAST_FORWARD FOR
SELECT ic.instance, ic.[version]
FROM InstanceConfig ic
LEFT OUTER JOIN dbo.FileStats fs ON ic.instance = fs.instance AND fs.run_date = DateAdd(day, DateDiff(day, 0, GetDate()), 0)
WHERE ic.collect_file_stats = 1
AND fs.instance IS NULL;
OPEN cur_instance;
FETCH NEXT FROM cur_instance INTO @instance, @ver;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ver >= 2012
SET @sqlcmd = N'INSERT INTO dbo.FileStats SELECT * FROM OPENROWSET (''SQLNCLI'',''SERVER=' + @instance + N';Trusted_Connection=yes'',''msdb.dbo.sp_all_db_filespace
WITH RESULT SETS (([instance] varchar(40), [database] varchar(40), [run_date] date, [file_id] int, [file_name] varchar(60), [file_type] varchar(60), [drive] char(1), [size_mb] int, [used_mb] int, [available_mb] int, [growth] int, [is_percent_growth] bit, [state_desc] varchar(60)))'') fd';
ELSE
SET @sqlcmd = N'INSERT INTO dbo.FileStats SELECT * FROM OPENROWSET (''SQLNCLI'',''SERVER=' + @instance + N';Trusted_Connection=yes'',''SET FMTONLY OFF;
CREATE TABLE #FileStats([instance] varchar(40), [database] varchar(40), [run_date] date, [file_id] int, [file_name] varchar(60), [file_type] varchar(60), [drive] char(1), [size_mb] int, [used_mb] int, [available_mb] int, [growth] int, [is_percent_growth] bit, [state_desc] varchar(60));
EXECUTE sp_msforeachdb ''''USE [?]; INSERT INTO #FileStats SELECT CAST(SERVERPROPERTY(''''''''ServerName'''''''') AS varchar(40)), LEFT(DB_NAME(),40), CAST(DateDiff(day, 0, GETDATE()) AS DateTime), f.file_id, LEFT(f.name,60), f.type_desc, LEFT(f.physical_name,1), f.size/128, FILEPROPERTY(f.name, ''''''''SpaceUsed'''''''')/128, f.size/128 - FILEPROPERTY(f.name, ''''''''SpaceUsed'''''''')/128, CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END, f.is_percent_growth, f.state_desc FROM sys.database_files f'''';
SELECT * FROM #FileStats;DROP TABLE #FileStats'') fd';
EXEC sp_executesql @sqlcmd;
FETCH NEXT FROM cur_instance INTO @instance, @ver;
END
CLOSE cur_instance;
DEALLOCATE cur_instance;
October 12, 2016 at 1:38 am
Hi Chris,
Thanks for your response, much appreciated! I will be leveraging of some of your method, but tell me something. This proc 'msdb.dbo.sp_all_db_filespace' , did you manually create in on your msdb, but with the same logic as 'sp_msforeachdb' ?
Chris Harshman (10/7/2016)
I've setup something similar before, but I used OPENROWSET instead. I tried a few different things but I found if I was calling a stored procedure I had to use SET FMTONLY OFF and also store things in a temp table:
DECLARE @instance NVARCHAR(40), @ver smallint, @sqlcmd NVARCHAR(1000);
DECLARE cur_instance CURSOR FAST_FORWARD FOR
SELECT ic.instance, ic.[version]
FROM InstanceConfig ic
LEFT OUTER JOIN dbo.FileStats fs ON ic.instance = fs.instance AND fs.run_date = DateAdd(day, DateDiff(day, 0, GetDate()), 0)
WHERE ic.collect_file_stats = 1
AND fs.instance IS NULL;
OPEN cur_instance;
FETCH NEXT FROM cur_instance INTO @instance, @ver;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ver >= 2012
SET @sqlcmd = N'INSERT INTO dbo.FileStats SELECT * FROM OPENROWSET (''SQLNCLI'',''SERVER=' + @instance + N';Trusted_Connection=yes'',''msdb.dbo.sp_all_db_filespace
WITH RESULT SETS (([instance] varchar(40), [database] varchar(40), [run_date] date, [file_id] int, [file_name] varchar(60), [file_type] varchar(60), [drive] char(1), [size_mb] int, [used_mb] int, [available_mb] int, [growth] int, [is_percent_growth] bit, [state_desc] varchar(60)))'') fd';
ELSE
SET @sqlcmd = N'INSERT INTO dbo.FileStats SELECT * FROM OPENROWSET (''SQLNCLI'',''SERVER=' + @instance + N';Trusted_Connection=yes'',''SET FMTONLY OFF;
CREATE TABLE #FileStats([instance] varchar(40), [database] varchar(40), [run_date] date, [file_id] int, [file_name] varchar(60), [file_type] varchar(60), [drive] char(1), [size_mb] int, [used_mb] int, [available_mb] int, [growth] int, [is_percent_growth] bit, [state_desc] varchar(60));
EXECUTE sp_msforeachdb ''''USE [?]; INSERT INTO #FileStats SELECT CAST(SERVERPROPERTY(''''''''ServerName'''''''') AS varchar(40)), LEFT(DB_NAME(),40), CAST(DateDiff(day, 0, GETDATE()) AS DateTime), f.file_id, LEFT(f.name,60), f.type_desc, LEFT(f.physical_name,1), f.size/128, FILEPROPERTY(f.name, ''''''''SpaceUsed'''''''')/128, f.size/128 - FILEPROPERTY(f.name, ''''''''SpaceUsed'''''''')/128, CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END, f.is_percent_growth, f.state_desc FROM sys.database_files f'''';
SELECT * FROM #FileStats;DROP TABLE #FileStats'') fd';
EXEC sp_executesql @sqlcmd;
FETCH NEXT FROM cur_instance INTO @instance, @ver;
END
CLOSE cur_instance;
DEALLOCATE cur_instance;
Thanks,
Tate
October 12, 2016 at 6:51 am
I'm sorry, I forgot this was a custom stored proc. It's basically doing the sp_foreachdb loop outside of the remote procedure call:
CREATE PROCEDURE dbo.sp_all_db_filespace AS
BEGIN
CREATE TABLE #FileStats([instance] varchar(40), [database] varchar(40), [run_date] date, [file_id] int, [file_name] varchar(60), [file_type] varchar(60), [drive] char(1), [size_mb] int, [used_mb] int, [available_mb] int, [growth] int, [is_percent_grow
th] bit, [state_desc] varchar(60));
EXECUTE sp_msforeachdb 'USE [?]; INSERT INTO #FileStats SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(40)), LEFT(DB_NAME(),40), CAST(DateDiff(day, 0, GETDATE()) AS DateTime), f.file_id, LEFT(f.name,60), f.type_desc, LEFT(f.physical_name,1), f.
size/128, FILEPROPERTY(f.name, ''SpaceUsed'')/128, f.size/128 - FILEPROPERTY(f.name, ''SpaceUsed'')/128, CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END, f.is_percent_growth, f.state_desc FROM sys.database_files f';
SELECT * FROM #FileStats;
DROP TABLE #FileStats
END
October 12, 2016 at 7:05 am
Hi Chris,
You are a lifesaver , it works wonders now. Thanks for your assistance , this is highly appreciated
Chris Harshman (10/12/2016)
I'm sorry, I forgot this was a custom stored proc. It's basically doing the sp_foreachdb loop outside of the remote procedure call:
CREATE PROCEDURE dbo.sp_all_db_filespace ASBEGIN
CREATE TABLE #FileStats([instance] varchar(40), [database] varchar(40), [run_date] date, [file_id] int, [file_name] varchar(60), [file_type] varchar(60), [drive] char(1), [size_mb] int, [used_mb] int, [available_mb] int, [growth] int, [is_percent_grow
th] bit, [state_desc] varchar(60));
EXECUTE sp_msforeachdb 'USE [?]; INSERT INTO #FileStats SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(40)), LEFT(DB_NAME(),40), CAST(DateDiff(day, 0, GETDATE()) AS DateTime), f.file_id, LEFT(f.name,60), f.type_desc, LEFT(f.physical_name,1), f.
size/128, FILEPROPERTY(f.name, ''SpaceUsed'')/128, f.size/128 - FILEPROPERTY(f.name, ''SpaceUsed'')/128, CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END, f.is_percent_growth, f.state_desc FROM sys.database_files f';
SELECT * FROM #FileStats;
DROP TABLE #FileStats
END
Many thanks,
Tate
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply