December 3, 2014 at 4:17 pm
I am looking to execute this code over the linked server, i am OK to hardcode linked server name. Can someone tell me how to execute this on remote server in a elegant way?
USE master
GO
IF EXISTS ( SELECT *
FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ( 'U' )
AND o.id = OBJECT_ID(N'tempdb..#TempTable') )
DROP TABLE #TempTable
go
CREATE TABLE #TempTable
(
Dbname VARCHAR(1000) ,
LogicalFileName VARCHAR(500) ,
PhysicalFileLocation VARCHAR(1000) ,
type_desc VARCHAR(50) , -- Issue
GROWTHRATE_MB INT ,
CURRENT_FILE_SIZE_MB INT ,
MAX_FILE_SIZE_MB INT
)
DECLARE @sql VARCHAR(4000)
SET @sql = 'use [' + '?' + ']
INSERT INTO #TempTable
(
DBname,
LogicalFileName ,
PhysicalFileLocation ,
type_desc ,
GROWTHRATE_MB ,
CURRENT_FILE_SIZE_MB ,
MAX_FILE_SIZE_MB
)
SELECT
db_name(),
aa.name LogicalFileName ,
aa.physical_name PhysicalFileLocation ,
aa.type_desc ,
[GROWTHRATE_MB] = CONVERT(INT, ROUND(( aa.growth * 1.000 ) / 128.000,
0)) ,
[CURRENT_FILE_SIZE_MB] = CONVERT(INT, ROUND(( aa.size * 1.000 )
/ 128.000, 0)) ,
[MAX_FILE_SIZE_MB] = CONVERT(INT, ROUND(( CASE aa.max_size
WHEN -1 THEN NULL
ELSE aa.max_size
END * 1.000 ) / 128.000, 0))
--INTO #TempTable
FROM sys.database_files aa
Where aa.max_size IS NOT NULL'
INSERT INTO #TempTable
EXEC sp_MSForEachDB @sql
SELECT * ,
( MAX_FILE_SIZE_MB - CURRENT_FILE_SIZE_MB ) LeftSpaceToGrow_MB
FROM #TempTable
WHERE ( MAX_FILE_SIZE_MB - CURRENT_FILE_SIZE_MB ) < 1.1 * ( GROWTHRATE_MB )
December 4, 2014 at 4:06 am
Instead of using a linked server, what about using OPENQUERY?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2014 at 6:19 am
I don't know if this is applicable to what you're trying to accomplish, but SSMS has a feature that allows you to run a query in SSMS against multiple servers simultaneously. In your Registered Servers, you can right-click a group name and click New Query in the context menu. The tab that opens is connected to all the servers in that group, so anything you run will run against all of them. The server name is returned in each result set and they're all combined into one set. You have to be careful if you're querying different versions of SQL Server, as some of the result sets are different from version to version.
HTH
December 4, 2014 at 7:30 am
I want this to be scheduled so i can't use SSMS's feature .
December 4, 2014 at 7:46 am
yeah, if you want to explore multi-server management, you certainly can schedule them to run across multiple servers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2014 at 7:48 am
I just remember, I wrote an article on just this topic[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2014 at 10:39 am
December 4, 2014 at 10:53 am
curious_sqldba (12/4/2014)
My apologies, should have specified this. We already have a in-house alerting system, i just need the syntax for the query to execute on remote server
Central management isn't just an alerting system. It's for scheduling multiple servers and management of them. But, you already have the OPENQUERY link posted above too. One of these solutions should work for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply