SQL Server 2000 Script:
====================
-- To get the total physical memory installed on SQL Server
CREATE TABLE #OS_Available_Memory (
ID [int]
,NAME [sysname]
,Physical_Memory_In_MB [int]
,Physical_Memory_In_Bytes [nvarchar](512))
INSERT #OS_Available_Memory
EXEC [master]..[xp_msver]
SELECT [Name], [Physical_Memory_In_MB], [Physical_Memory_In_Bytes]
FROM #OS_Available_Memory
WHERE NAME = 'PhysicalMemory'
GO
DROP TABLE #OS_Available_Memory
--To get the minimum and maximum size of memory configured for SQL Server
SELECT * FROM [master]..[sysconfigures]
WHERE [comment] IN ('Minimum size of server memory (MB)'
,'Maximum size of server memory (MB)')
SQL Server 2005 Script:
====================
-- To get the total physical memory installed on SQL Server
SELECT physical_memory_in_bytes / 1024 / 1024 AS [Physical_Memory_In_MB]
,virtual_memory_in_bytes / 1024 / 1024 AS [Virtual_Memory_In_MB]
FROM [master].[sys].[dm_os_sys_info]
--To get the minimum and maximum size of memory configured for SQL Server
SELECT [name] AS [Name]
,[configuration_id] AS [Number]
,[minimum] AS [Minimum]
,[maximum] AS [Maximum]
,[is_dynamic] AS [Dynamic]
,[is_advanced] AS [Advanced]
,[value] AS [ConfigValue]
,[value_in_use] AS [RunValue]
,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)'
,'Max server memory (MB)')
SQL Server 2008/200R2 and SQL Server 2012 Script:
======================================
-- To get the total physical memory installed on SQL Server
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]
--To get the minimum and maximum size of memory configured for SQL Server.
SELECT [name] AS [Name]
,[configuration_id] AS [Number]
,[minimum] AS [Minimum]
,[maximum] AS [Maximum]
,[is_dynamic] AS [Dynamic]
,[is_advanced] AS [Advanced]
,[value] AS [ConfigValue]
,[value_in_use] AS [RunValue]
,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)'
,'Max server memory (MB)')
- Thanks to author, saved mytime and others too.
====================
-- To get the total physical memory installed on SQL Server
CREATE TABLE #OS_Available_Memory (
ID [int]
,NAME [sysname]
,Physical_Memory_In_MB [int]
,Physical_Memory_In_Bytes [nvarchar](512))
INSERT #OS_Available_Memory
EXEC [master]..[xp_msver]
SELECT [Name], [Physical_Memory_In_MB], [Physical_Memory_In_Bytes]
FROM #OS_Available_Memory
WHERE NAME = 'PhysicalMemory'
GO
DROP TABLE #OS_Available_Memory
--To get the minimum and maximum size of memory configured for SQL Server
SELECT * FROM [master]..[sysconfigures]
WHERE [comment] IN ('Minimum size of server memory (MB)'
,'Maximum size of server memory (MB)')
SQL Server 2005 Script:
====================
-- To get the total physical memory installed on SQL Server
SELECT physical_memory_in_bytes / 1024 / 1024 AS [Physical_Memory_In_MB]
,virtual_memory_in_bytes / 1024 / 1024 AS [Virtual_Memory_In_MB]
FROM [master].[sys].[dm_os_sys_info]
--To get the minimum and maximum size of memory configured for SQL Server
SELECT [name] AS [Name]
,[configuration_id] AS [Number]
,[minimum] AS [Minimum]
,[maximum] AS [Maximum]
,[is_dynamic] AS [Dynamic]
,[is_advanced] AS [Advanced]
,[value] AS [ConfigValue]
,[value_in_use] AS [RunValue]
,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)'
,'Max server memory (MB)')
SQL Server 2008/200R2 and SQL Server 2012 Script:
======================================
-- To get the total physical memory installed on SQL Server
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]
--To get the minimum and maximum size of memory configured for SQL Server.
SELECT [name] AS [Name]
,[configuration_id] AS [Number]
,[minimum] AS [Minimum]
,[maximum] AS [Maximum]
,[is_dynamic] AS [Dynamic]
,[is_advanced] AS [Advanced]
,[value] AS [ConfigValue]
,[value_in_use] AS [RunValue]
,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)'
,'Max server memory (MB)')
- Thanks to author, saved mytime and others too.
No comments:
Post a Comment