Running SQL Server 2008R2 STD 64bit edition on Windows 2008 R2 64 bit edition.
The Windows server is a Hyper-V VM with its memory set as follows
When I run the following query;
SELECT
[physical_memory_in_bytes] AS [PhysMemBytes],
[physical_memory_in_use_kb] AS [PhysMemInUseKB],
[available_physical_memory_kb] AS [PhysMemAvailKB],
[locked_page_allocations_kb] AS [LPAllocKB],
[max_server_memory] AS [MaxSvrMem],
[min_server_memory] AS [MinSvrMem]
FROM
sys.dm_os_sys_info
CROSS JOIN
sys.dm_os_process_memory
CROSS JOIN
sys.dm_os_sys_memory
CROSS JOIN (
SELECT
[value_in_use] AS [max_server_memory]
FROM
sys.configurations
WHERE
[name] = 'max server memory (MB)') AS c
CROSS JOIN (
SELECT
[value_in_use] AS [min_server_memory]
FROM
sys.configurations
WHERE
[name] = 'min server memory (MB)') AS c2
It gives the output below;
Task Manager shows the same 1Gb in use for the sqlserver.exe process
I do not understand why SQL Server is not grabbing the minimum or maximum memory set in SQL server when the VM is configured with dynamic memory and able to grow to 16Gb?
EDIT
First lines of error log below
LogDate ProcessInfo Text
2018-06-17 06:18:21.660 Server Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)
Mar 26 2015 21:18:04
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
2018-06-17 06:18:21.680 Server (c) Microsoft Corporation.
2018-06-17 06:18:21.680 Server All rights reserved.
2018-06-17 06:18:21.680 Server Server process ID is 1684.
2018-06-17 06:18:21.680 Server System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
2018-06-17 06:18:21.680 Server Authentication mode is MIXED.
2018-06-17 06:18:21.680 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2018-06-17 06:18:21.690 Server This instance of SQL Server last reported using a process ID of 1504 at 17/06/2018 6:16:11 AM (local) 16/06/2018 8:16:11 PM (UTC). This is an informational message only; no user action is required.
2018-06-17 06:18:21.690 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2018-06-17 06:18:21.700 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2018-06-17 06:18:21.700 Server Detected 2 CPUs. This is an informational message; no user action is required.
2018-06-17 06:18:22.040 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2018-06-17 06:18:22.350 Server Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2018-06-17 06:18:22.550 spid7s Starting up database 'master'.
2018-06-17 06:18:22.710 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2018-06-17 06:18:22.910 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2018-06-17 06:18:23.000 spid7s SQL Trace ID 1 was started by login "sa".
2018-06-17 06:18:23.000 spid7s Starting up database 'mssqlsystemresource'.
2018-06-17 06:18:23.030 spid7s The resource database build version is 10.50.4042. This is an informational message only. No user action is required.
2018-06-17 06:18:23.370 spid10s Starting up database 'model'.
2018-06-17 06:18:23.370 spid7s Server name is 'DB01'. This is an informational message only. No user action is required.
2018-06-17 06:18:23.630 spid10s Clearing tempdb database.
2018-06-17 06:18:23.750 Server A self-generated certificate was successfully loaded for encryption.
2018-06-17 06:18:23.750 Server Server is listening on [ 'any' <ipv6> 1433].
2018-06-17 06:18:23.750 Server Server is listening on [ 'any' <ipv4> 1433].
2018-06-17 06:18:23.760 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2018-06-17 06:18:23.760 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2018-06-17 06:18:23.760 Server Server is listening on [ ::1 <ipv6> 1434].
2018-06-17 06:18:23.760 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-06-17 06:18:23.760 Server Dedicated admin connection support was established for listening locally on port 1434.
2018-06-17 06:18:23.800 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/DB01 ] for the SQL Server service.
2018-06-17 06:18:23.800 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/DB01:1433 ] for the SQL Server service.
2018-06-17 06:18:23.800 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2018-06-17 06:18:23.990 spid12s A new instance of the full-text filter daemon host process has been successfully started.
2018-06-17 06:18:24.050 spid12s Starting up database 'msdb'.
2018-06-17 06:18:24.050 spid18s Starting up database 'SQL_D'.
2018-06-17 06:18:24.050 spid16s Starting up database 'INVOICES'.
2018-06-17 06:18:24.050 spid14s Starting up database 'BAS'.
2018-06-17 06:18:24.050 spid17s Starting up database 'COLLEC'.
2018-06-17 06:18:24.250 spid10s Starting up database 'tempdb'.
2018-06-17 06:18:24.370 spid15s The Service Broker protocol transport is disabled or not configured.
2018-06-17 06:18:24.370 spid15s The Database Mirroring protocol transport is disabled or not configured.
2018-06-17 06:18:24.550 spid15s Service Broker manager has started.
2018-06-17 06:18:24.640 spid17s Recovery is writing a checkpoint in database 'COLLEC' (8). This is an informational message only. No user action is required.
2018-06-17 06:18:26.160 spid12s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2018-06-17 06:18:27.970 spid7s Recovery is complete. This is an informational message only. No user action is required.
2018-06-17 06:18:28.060 Logon Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection made using Windows authentication. [CLIENT: <local machine>]
2018-06-17 06:18:29.430 spid51 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:29.480 spid51 Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2018-06-17 06:18:29.640 Logon Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection made using Windows authentication. [CLIENT: <local machine>]
2018-06-17 06:18:29.930 spid51 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:30.000 spid51 Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2018-06-17 06:18:30.440 spid51 Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:31.240 spid51 Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2018-06-17 06:22:26.230 spid70 Configuration option 'blocked process threshold (s)' changed from 30 to 30. Run the RECONFIGURE statement to install.
2018-06-17 06:22:26.230 spid70 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2018-06-17 06:22:27.060 spid70 Attempting to load library 'odsole70.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:22:27.900 spid70 Using 'odsole70.dll' version '2009.100.1600' to execute extended stored procedure 'sp_OACreate'. This is an informational message only; no user action is required.
sp_readerrorlog
the top 100 lines should do. This will tell us about how SQL Server is configured and licensing.