4

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

  • 2
    Could you post what's in your log file entirely, and as text instead of a screenshot? – Tom V Dec 22 '15 at 9:18
2

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 :

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 ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.