I have loaded disk drive information by computername in a SQL DB. I am
interested in Freespace. When the data is loaded into DB its in (K). How can
I display the freespace in MB?
Query:
select SystemName,Win32_DiskDrive_Model,Win32_L
ogicalDisk_FreeSpace from
SystemInfo
The result is the following:
SystemName Drive Model FreeSpace
posdtdc368900 ST320410A 18982992896
I want to display FreeSpace in MB. I know that taking freeSpace and dividing
by 1048576 will give me MB. Just do not know how divide with TSQL.
The next step once converted to mb is to add a clause to get any drives less
than 500mb.select SystemName,Win32_DiskDrive_Model,Win32_L
ogicalDisk_FreeSpace/1048576
AS [Free Space (MB)]
from SystemInfo
Jacco Schalkwijk
SQL Server MVP
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:OYpv7T6aFHA.1660@.tk2msftngp13.phx.gbl...
>I have loaded disk drive information by computername in a SQL DB. I am
>interested in Freespace. When the data is loaded into DB its in (K). How
>can I display the freespace in MB?
>
> Query:
> select SystemName,Win32_DiskDrive_Model,Win32_L
ogicalDisk_FreeSpace from
> SystemInfo
>
> The result is the following:
> SystemName Drive Model FreeSpace
> posdtdc368900 ST320410A 18982992896
>
> I want to display FreeSpace in MB. I know that taking freeSpace and
> dividing by 1048576 will give me MB. Just do not know how divide with
> TSQL.
>
> The next step once converted to mb is to add a clause to get any drives
> less than 500mb.
>
>|||Try this
Select SystemName,Win32_DiskDrive_Model,Win32_L
ogicalDisk_FreeSpace/1048576
AS [Free Space (MB)]
From SystemInfo
Where (Win32_LogicalDisk_FreeSpace/1048576) < 500
"Big D" <BigDaddy@.newsgroup.nospam> escribi en el mensaje
news:OYpv7T6aFHA.1660@.tk2msftngp13.phx.gbl...
>I have loaded disk drive information by computername in a SQL DB. I am
>interested in Freespace. When the data is loaded into DB its in (K). How
>can I display the freespace in MB?
>
> Query:
> select SystemName,Win32_DiskDrive_Model,Win32_L
ogicalDisk_FreeSpace from
> SystemInfo
>
> The result is the following:
> SystemName Drive Model FreeSpace
> posdtdc368900 ST320410A 18982992896
>
> I want to display FreeSpace in MB. I know that taking freeSpace and
> dividing by 1048576 will give me MB. Just do not know how divide with
> TSQL.
>
> The next step once converted to mb is to add a clause to get any drives
> less than 500mb.
>
>
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Wednesday, March 28, 2012
Friday, March 9, 2012
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,
nivek
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
>
>
|||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...[vbcol=seagreen]
> 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:
|||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
>
>
and other values. Are these values kept in a system database?
Thanks,
nivek
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
>
>
|||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...[vbcol=seagreen]
> 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:
|||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
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
>
>
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
>
>
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
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
>
>
Monday, February 20, 2012
Query Memory?
I had posted a msg in another thread asking about forcing MSSQL to use
query memory vs disk I/O due to long fields.
query memory vs disk I/O due to long fields.
Is there any way for me to benchmark what SQL is using in Memory vs.
allocated memory.
We are running SQL 2000 Standard, not Enterprise, so it can only
address the 2GB.Here is a link to the other posting...
http://groups.google.com/group/comp...659ba61722dc3ea
Subscribe to:
Posts (Atom)