November 9, 2002 at 11:35 am
Anyone have a way to check multiple server's free disk space in a mixed environment (7.0 & 2000) from one server?
November 9, 2002 at 4:15 pm
We use a management tool (Compaq Insight Manager) to do so.
You could also run a series of scripts on a regular basis which use the FileScriptingObject or a WMI provider and execute them against each server as well.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 10, 2002 at 9:17 am
Tried posting this yesterday but never got round to it. For a SQL Server solution use xp_fixeddrives. You will need linked servers setup or use OPENDATASOURCE or similar.
AS Brain days the other option for a more programmitic answer is to use the scripting object model.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 10, 2002 at 4:15 pm
SQL1_DEV is a linked server.
This works fine:
SELECT *
FROM
SQL1_DEV.Master.dbo.sysdatabases
This has an error:
EXEC SQL1_DEV.Master.dbo.xp_fixeddrives
"Server: Msg 7411, Level 16, State 1, Line 1
Server 'SQL1_DEV' is not configured for RPC."
Any ideas?
November 10, 2002 at 11:06 pm
I use product SALive to monitor all my servers. It's possible to check disk space, runnin services and mutch more. Neat an cheap tool.
http://www.woodstone.nu/salive/
Regards,
Henrik
November 11, 2002 at 7:28 am
I have a stored procedure on each of my servers that I run as a scheduled job. It raises an error when disk space runs over a certain percentage or size. When the error is raised sends a page and a netsend message.
CREATE procedure csp_logspace
as
declare @size int
declare @space int
declare @PercentUsed int
CREATE TABLE #logspace (
DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int
)
CREATE TABLE #Drives (
Drive char(1),
FreeSpace int
)
INSERT INTO #logspace
EXEC ('DBCC sqlperf( logspace)')
/*
process the data
*/
INSERT INTO #Drives
EXEC master..xp_fixeddrives
select @space = FreeSpace
from #Drives
where Drive = 'E'
select @size = cast(LogSize as int)
from #logspace
where DBName = 'CAPROD'
set @PercentUsed = cast((@size/@space*100) as int)
/*
Cleanup - drop the temp table
*/
drop table #logspace
drop table #Drives
if @PercentUsed > 60
RAISERROR (50001, 16, 1)
if @size > 6000
RAISERROR (50002, 16, 1)
GO
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
November 11, 2002 at 8:19 am
Those are great ideas. Thanks.
But I like where I am heading with creating one script that creates Linked Servers to all my boxes and returns the free drive space.
Any ideas with the below error?
"Server: Msg 7411, Level 16, State 1, Line 1
Server 'SQL1_DEV' is not configured for RPC."
November 11, 2002 at 8:53 am
Sounds like your linked servers aren't configured correctly/completely for remote procedure calls. Try one of these..
Setting the linked server options:
You can use the sp_serveroption stored procedure to set the options for the linked server.
Exec sp_serveroption
@server='linked server name',
@optname='option name' --some important options are give below:
@optvalue='option value'
The important option names are:
data access: Enables and disables a linked server for distributed queries.
Rpc: Enables remote procedure calls from the given server.
Rpc out: Enables rpc to the given server.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
November 11, 2002 at 2:26 pm
Works now.
Had to run:
USE master
EXEC sp_serveroption 'SQL_STG1', 'rpc', 'on'
USE master
EXEC sp_serveroption 'SQL_STG1', 'rpc out', 'on'
Thanks!!!
November 12, 2002 at 8:17 am
This is what I have come up with and its a pretty good solution. This script creates linked servers and runs an extented procedure to check drive space.
I then created a job and emailed the results of the script to me.
/* Script to check free drive space on all SQL Servers (both 7.0 and 2000) */
CREATE Procedure SPAdmin_DriveSpace
AS
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM SYSSERVERS WHERE SRVNAME = 'Server1_Link_Server_Admin_01')
BEGIN
exec sp_addlinkedserver
@server='Server1_Link_Server_Admin_01',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='Server1'
EXEC sp_serveroption 'Server1_Link_Server_Admin_01', 'rpc', 'on'
EXEC sp_serveroption 'Server1_Link_Server_Admin_01', 'rpc out', 'on'
END
IF NOT EXISTS (SELECT * FROM SYSSERVERS WHERE SRVNAME = 'Server2_Link_Server_Admin_01')
BEGIN
EXEC sp_addlinkedserver
@server='Server2_Link_Server_Admin_01',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='Server2'
EXEC sp_serveroption 'Server2_Link_Server_Admin_01', 'rpc', 'on'
EXEC sp_serveroption 'Server2_Link_Server_Admin_01', 'rpc out', 'on'
END
IF NOT EXISTS(SELECT * FROM SYSSERVERS WHERE SRVNAME = 'Server3_Link_Server_Admin_01')
BEGIN
EXEC sp_addlinkedserver
@server='Server3_Link_Server_Admin_01',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='Server3'
EXEC sp_serveroption 'Server3_Link_Server_Admin_01', 'rpc', 'on'
EXEC sp_serveroption 'Server3_Link_Server_Admin_01', 'rpc out', 'on'
END
--Check Free Space:
SELECT 'Server1 free drive space:'
EXEC Server1_Link_Server_Admin_01.master.dbo.xp_fixeddrives
SELECT 'Server2 free drive space:'
EXEC Server2_Link_Server_Admin_01.master.dbo.xp_fixeddrives
SELECT 'Server3 free drive space:'
EXEC Server3_Link_Server_Admin_01.master.dbo.xp_fixeddrives
GO
(3 server's checked here)
Edited by - bryan99y on 11/12/2002 08:21:27 AM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply