Prev: access sql database from client machine
Next: Transactional Replication and Backups
From: Ken Ross on 25 Mar 2010 17:31
Does anyone know of a way that I could write a stored procedure to retrieve
information about the server host? What I'd like to be able to do is pull
back the total available RAM, Available Disk Space [on the volume where the
mdf/ldf are located], etc. This information would then be agregated into a
monitoring interface within our application.

Thanks!
From: Erland Sommarskog on 25 Mar 2010 19:04
Ken Ross (kross(a)horizonsoftware.com) writes:
> Does anyone know of a way that I could write a stored procedure to
> retrieve information about the server host? What I'd like to be able to
> do is pull back the total available RAM, Available Disk Space [on the
> volume where the mdf/ldf are located], etc. This information would then
> be agregated into a monitoring interface within our application. >

You can find available RAM in sys.dm_os_sys_info

You can retrieve available diskspace with xp_fixeddrives, but please be
aware of that this extended stored procedure is not documented.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Uri Dimant on 28 Mar 2010 03:10
Ken
------------------This query attempts to show some of the basic memory areas
in a SQL Server 2005 or 2008 installation.

--Memory columns of sys.dm_os_sys_info
SELECT
--Amount of physical memory on server
physical_memory_in_bytes
--Amount of physical memory available to the process in user mode
--Should be 2GB unless /3GB used
,virtual_memory_in_bytes
--Committed physical memory in buffer pool
--Does not include MemToLeave memory area
,bpool_committed AS 'Number of 8KB buffers in buffer pool'
, bpool_commit_target AS 'Number of 8KB buffers needed by the buffer pool'
,CASE
WHEN bpool_commit_target > bpool_committed THEN 'Extra memory needed
from OS for Buffer Pool'
WHEN bpool_commit_target < bpool_committed THEN 'Memory may be
released from Buffer Pool to OS'
END AS 'Status of Dynamic Memory'

, bpool_visible AS 'Number of 8KB Buffers in Buffer Pool that are directly
accessible in the processes VAS.'

/* When AWE is not used. When memory target reached, the value will be the
same as bpool_committed
When AWE is used on 32-bit. This value represents the size of the AWE
mapping window used to
access physical memory allocated by the buffer pool. Since the size of the
AWE mapping window
is bound by the process VAS the value of this column will be smaller than
the value of bpool_committed.
If the value of this column becomes too low, you may receive out of memory
errors.
*/
FROM sys.dm_os_sys_info


"Ken Ross" <kross(a)horizonsoftware.com> wrote in message
news:Xns9D46B24BD88CDkrosshorizonbosscom(a)207.46.248.16...
> Does anyone know of a way that I could write a stored procedure to
> retrieve
> information about the server host? What I'd like to be able to do is pull
> back the total available RAM, Available Disk Space [on the volume where
> the
> mdf/ldf are located], etc. This information would then be agregated into a
> monitoring interface within our application.
>
> Thanks!


From: Ken Ross on 29 Mar 2010 08:51
Thanks Uri!

From: Ken Ross on 29 Mar 2010 08:52
Thanks Erland!


 | 
Pages: 1
Prev: access sql database from client machine
Next: Transactional Replication and Backups