Query Operating System

Is it possible to query the operating system for disk space, memory size,
and other values. Are these values kept in a system database?
Thanks,
nivekHi Nivek,
Yes - you can definitely use Transact-SQL to query these values. For
example, to query what drive letters are on your machine, and determine how
much space is available you can run this:
EXEC master..xp_fixeddrives
To figure out memory available and other performance counters, you can query
the master.dbo.sysperfinfo system table.
Best Regards,
Joseph Sack
www.JoeSack.com
Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
"nivek" wrote:
> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>|||You can also use xp_regread to query windows registry for a broader range of
values.
"Joseph Sack" <JosephSack@.discussions.microsoft.com> wrote in message
news:57A80ACA-ECE5-4D3E-879C-125B1057D773@.microsoft.com...
> Hi Nivek,
> Yes - you can definitely use Transact-SQL to query these values. For
> example, to query what drive letters are on your machine, and determine
> how
> much space is available you can run this:
> EXEC master..xp_fixeddrives
> To figure out memory available and other performance counters, you can
> query
> the master.dbo.sysperfinfo system table.
> Best Regards,
> Joseph Sack
> www.JoeSack.com
> Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
> "nivek" wrote:
>> Is it possible to query the operating system for disk space, memory size,
>> and other values. Are these values kept in a system database?
>> Thanks,
>> nivek
>>|||Am using this SP to get disk space of drives on the server... It might be
helpful to you also...
I have created a job which calls this SP in the following manner,
exec sp_diskalert 'my email addr', 100000
Where 100000 is the limit in MB's. You can set it to any size. This limit
gives me the list of drives which are below 100 gig.
SP script is pasted below.
HTH
GYK
**************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure sp_diskalert
@.RCPT VARCHAR(500),
@.LIMIT INT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
/* GENERATE THE MESSAGE */
IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@.RCPT) > 0 --CHECK THERE IS SOME
DATA AND A RECIPIENT
BEGIN
DECLARE @.MSG VARCHAR(400),
@.DLETTER VARCHAR(5),
@.DSPACE INT
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE < @.LIMIT
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) --PUT THE VARS
INTO A MSG
+ 'MB' + CHAR(13) + CHAR(10)
WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @.LIMIT AND DRVLETTER >
@.DLETTER) > 0
BEGIN --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1
WHERE DRVSPACE < @.LIMIT
AND DRVLETTER > @.DLETTER
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.MSG + @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) + 'MB'
+ CHAR(13) + CHAR(10)
END
/* SEND THE MESSAGE */
IF CHARINDEX('@.',@.RCPT) > 0 --THERE IS AN @. SYMBOL IN THE RECIPIENT - SEND
EMAIL
BEGIN
DECLARE @.EMAIL VARCHAR(600)
SET @.EMAIL = 'EXEC master.dbo.xp_sendmail
@.recipients = ''' + @.RCPT + ''',
@.message = ''' + @.MSG + ''',
@.subject = ''!! LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' !!'''
EXEC (@.EMAIL)
END
ELSE IF CHARINDEX('@.',@.RCPT) = 0 --THERE IS NO @. SYMBOL IN THE RECIPIENT -
NET SEND
BEGIN
--DETERMINE IF XP_CMDSHELL EXISTS
DECLARE @.FLAG BIT
SET @.FLAG = 1
IF NOT EXISTS(SELECT name FROM master..sysobjects WHERE name ='XP_CMDSHELL')
SET @.FLAG = 0
--IF NOT RECREATE IT
IF @.FLAG = 0
BEGIN
EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
PRINT 'ADDING XP_CMDSHELL'
END
--NET SEND MSG
DECLARE @.NETSEND VARCHAR(600)
SET @.MSG = 'ALERT - LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' : ' + @.MSG
SET @.NETSEND = 'xp_cmdshell ''net send "' + RTRIM(@.RCPT) + '" '
+ LEFT(RTRIM(REPLACE(@.MSG,CHAR(13) + CHAR(10),', ')),LEN(@.MSG)-2) + ''''
EXEC (@.NETSEND)
--DROP XP_CMDSHELL IF IT DIDN'T EXIST
IF @.FLAG = 0
BEGIN
EXEC sp_dropextendedproc 'xp_cmdshell'
PRINT 'DROPPING XP_CMDSHELL'
END
END
END
/* CLEANUP */
DROP TABLE #T1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
********************************************
"nivek" wrote:
> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>