Sql-server – Why is SQL Server only able to grab 1Gb of memory

memorysql-server-2008-r2

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

enter image description here

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;

enter image description here

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.

Best Answer

Are you aware that you have just 5.2 GB allocated to windows machine. Look at the below output and the column PhysicalMemBytes which you get from DMV sys.dm_os_sys_info is Total physical memory present on windows machine in bytes and this comes to approx 5.2 GB.

enter image description here

Out of this 5GB the windows OS and other components would be using 4GB and hence only 1 GB is left for the SQL Server. This is the reason SQL Server memory consumption cannot move beyond 1 GB. There is no point in keeping max server memory to 10GB and min server memory to 6 GB because RAM for SQL Server is not there. I would suggest add more RAM on the system.