Capacity planning script problem ....
-
Hi, with this script I want to do capacity planning on all my servers SQL 2000
From one SQL repository server, I want to monitor several servers : I create a linkedserver, retrieve information, store information in a table in the repository server (my problem
- see above in red color) and drop the linkedserver.Can you help me ?????
USE TEST
go
if not exists (select * from dbo.sysobjects where id = object_id(N'[LinkedInstanceTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[LinkedInstanceTable]
(
[Server_Name] VARCHAR(125),
[Instance_Name] VARCHAR(125),
[LinkedPsswd] VARCHAR(35)
)
INSERT INTO [TEST].[dbo].[LinkedInstanceTable]([Server_Name],[Instance_Name],[LinkedPsswd]) VALUES ('server1','instance1','password1')
INSERT INTO [TEST].[dbo].[LinkedInstanceTable]([Server_Name],[Instance_Name],[LinkedPsswd]) VALUES ('server2','instance2','password2')
INSERT INTO [TEST].[dbo].[LinkedInstanceTable]([Server_Name],[Instance_Name],[LinkedPsswd]) VALUES ('server3','instance3','password3')
-- select * from TEST.[dbo].[LinkedInstanceTable]
END
go
if not exists (select * from dbo.sysobjects where id = object_id(N'[CAPACITY_PLANNING]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[CAPACITY_PLANNING]
(
[Date] VARCHAR(50),
[Instance] VARCHAR(512),
[DB] VARCHAR(512),
[Logical_filename] VARCHAR(512),
[Size] int,
[Filename] VARCHAR(512),
[Groupname] VARCHAR(512)
)
USE master
GO
SET NOCOUNT ON
DECLARE @Inst VARCHAR (125),
@Srv VARCHAR(125),
@Lnk_Srv VARCHAR(125),
@SrvInst VARCHAR(125),
@psswd VARCHAR (15),
@mySQL VARCHAR (250),
@mySQL2 VARCHAR (250),
@Error VARCHAR (150),
@sql VARCHAR (250)
-- Set up cursor to load instances names
DECLARE myInstances CURSOR FOR SELECT Server_Name,Instance_Name,LinkedPsswd FROM TEST.dbo.LinkedInstanceTable
OPEN myInstances
FETCH NEXT FROM myInstances INTO @Srv, @Inst, @psswd
WHILE @@FETCH_STATUS = 0
BEGIN
set @Lnk_Srv = @Srv + '_' + @Inst
set @SrvInst = @Srv + '\' + @Inst
exec sp_addlinkedserver @server=@Lnk_Srv, @srvproduct='', @provider='SQLOLEDB', @datasrc=@SrvInst
IF @@Error <> 0
BEGIN
PRINT 'WARNING! sp_addlinkedserver error for instance: [' + @Lnk_Srv + '], please review'
END
ELSE
--PRINT 'Adding Linked Server for instance: [' + @Lnk_Srv + ']'
EXEC sp_addlinkedsrvlogin @Lnk_Srv, 'FALSE', 'sa', 'sa', @psswd
IF @@Error <> 0
BEGIN
PRINT 'WARNING! sp_addlinkedsrvlogin error for instance: [' + @Lnk_Srv + '], please review'
END
ELSE
PRINT 'Adding Linked Server Login for instance: [' + @Lnk_Srv + ']'
-- ------------------------------------------------------------------------------
-- Edit this section with the T_SQL query to be executed on all linked servers
-- ------------------------------------------------------------------------------
-- ******************************************************************************
exec sp_serveroption @Lnk_Srv, 'rpc', true
exec sp_serveroption @Lnk_Srv, 'rpc out', true
set @sql= @Lnk_Srv + '.MSDB.dbo.SP_MSFOREACHDB ''USE ? '
set @sql=@sql + 'select convert(varchar,getdate(),111), ''''' + @SrvInst + ''''', ''''?'''', [name],*8/1024 "SIZE",[filename] '
set @sql=@sql + 'from dbo.sysfiles '''
exec (@sql) -- How to store the result of this request in a table ?????????
-- ******************************************************************************
-- ------------------------------------------------------------------------------
IF @@Error <> 0
BEGIN
PRINT 'WARNING! CAN NOT EXECUTE REMOTE CODE ON INSTANCE: [' + @Lnk_Srv + '], PLEASE REVIEW'
END
ELSE
--PRINT 'Exeuted remote code on instance: [' + @Lnk_Srv + ']'
-- Drop the remote/linked server connection
PRINT 'Dropping Linked Server Instance: [' + @Lnk_Srv + ']'
EXEC sp_dropserver @Lnk_Srv, 'droplogins'
IF @@Error <> 0
BEGIN
PRINT 'WARNING! CAN NOT DROP CONNECTION FOR INSTANCE: [' + @Lnk_Srv + '] PLEASE REVIEW'
END
ELSE
PRINT 'Dropped connection on instance: [' + @Lnk_Srv + ']'
PRINT ' '
FETCH NEXT FROM myInstances INTO @Srv, @Inst, @psswd
END
CLOSE myInstances
DEALLOCATE myInstances
-- Drop work table. This will ensure better security as it contains you 'sa' passwords!
--drop table [TEST].[dbo].[LinkedInstanceTable]
IF @@Error <> 0
BEGIN
PRINT 'WARNING! CAN NOT DROP TABLE: MYCONNECT, PLEASE REVIEW'
END
ELSE
PRINT 'Dropped Table: MYCONNECT'
-
The SQL for storing the results in a table is fairly simple; however, the implementation has some potential pitfalls.
Replace
exec (@sql) -- How to store the result of this request in a table
With
insert into #results
exec(@sql)
Where #results is a table defined with the proper columns. The caveat here is that since you are saving the results from a call to a link server, SQL Server using MSDTC to coordinate the transaction. This will require that you are able to execute Distributed transactions between all the necessary servers, something that does not always work out of the box (a discussion of which is beyond the scope of the question asked).
Gordon
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
How about a scheduled job on each server that fills a local table once a day with the file sizes, then your aggregation server can just query those tables?
Another alternative is to use xp_fixeddrives to get the free space remaining on each drive, if certain drives are reserved for SQL files and you know the capactity you can easily determine space used.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply