SQL server timeout after downgrade

Question

After a downgrade from SQL 2008 R2 Enterprise to SQL 2008 R2 Standard. SQL Server after some time starts lagging and CPU use is around 95%-100% and I can't log in in "MS SQL server management studio", another application gets time outs from SQL Server.

The log files don't have any error, but all files are filled with this text:

enter image description here

Log file:

https://drive.google.com/file/d/0B8niPQ9GLmG-ZHFuR3hqQUphMUU

• Tom V: Could you post what's in your log file entirely, and as text instead of a screenshot?

Answer 1

Based on your error log, you are already running on an unsupported version of sql server 2008R2. You are running on RTM build - Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64). You should Download SP3 (10.50.6000) then Security update which is the latest service pack.

Then scrolling through the error log ....

Warning: unable to allocate 'min server memory' of 10000MB.

This tells that you have min memory configured as 10GB !

The bottom line is you have misconfigured min and max memory and you should correct them..

Once you address the min and max memory, you should look into query plans with high memory grants that are stealing memory from the buffer pool and tune those queries.

Refer to :

• Suggested Max Memory Settings for SQL Server - this will give you a good start.
• How to reduce paging of buffer pool memory in the 64-bit version of SQL Server ?

Answer 2

The log seems to display an output of the DBCC MEMORYSTATUS, which is what happens when the engine runs out of memory. Did you configure the maximum amount of memory SQL Server can use ? To find out, you can run the following query :

SELECT *
FROM sys.configurations
WHERE name LIKE '%mem%'

For the row for which the column named "name" is valued to "max server memory (MB)", you should see a value different greater than zero.

If it is set to zero, you can easily configure it with the following code, assuming that you server has a couple GB or RAM and is dedicated to SQL Server :

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'max server memory (MB)', [your_server_RAM_amount_in_MB_minus_4GB]
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

This takes effect immediately, without the need to restart the SQL Server instance.

If it is not this, did you update all the statistics of all databases on this instance ?