Sql server sudden memory issues

Question

0.00/5 (No votes)

See more:

i have an SQL Server on a Windows server 2012 R2 Standard (64) with 32 Gigabytes of RAM running a have a free 150 Gigs Space on 1 Tera HD. The storage is located in a different drive which has enough space , Logs are also located on a different drive with enough space and also backups in a separate drive

Maximum server Memory on the SQL Server is the default 2147483647 and Minimum Memory per Query is 1024.

This machine has been working very well with no issues. last week friday we started experiencing slowness on the server and the following is what i observed when i was troubleshooting

1) Memory for SQL Server (MSSQLSERVER) service is 28985.2 MB.TaskManager

2) Event Log has been giving me the following message

AppDomain 2 (SSISDB.dbo[runtime].1) is marked for unload due to memory pressure.

SQL Server has encountered 12 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf] in database id 1. The OS file handle is 0x000000000000081C. The offset of the latest long I/O is: 0x00000000410000

SQL Server has encountered 30 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\templog.ldf] in database id 2. The OS file handle is 0x0000000000000E70. The offset of the latest long I/O is: 0x00000000203000

When my users use my system everythings just times out. i used sp_who2 to check running process i found none.

Did anyone had this issue before ?

Thanks

What I have tried:

1) Reboot the sever
2) Restart the service
3) Optimize Query
4) Re-index Indexes
5) check if there are Processes Blocked (sp_who2)

Posted 5-Jul-17 5:21am

Vimalsoft(Pty) Ltd

Add a Solution

Comments

David_Wimbley 5-Jul-17 11:48am

Are you using any sql clr functions? Have you tried any of the items here:

https://dba.stackexchange.com/questions/116688/appdomain-2-mssqlsystemresource-dboruntime-1-is-marked-for-unload-due-to-mem

RickZeeland 5-Jul-17 13:05pm

Maybe it's time to say goodbye to the memory hog that SQL Server is and start using PostgreSQL !

Richard Deeming 6-Jul-17 10:05am

"I/O requests taking longer than 15 seconds to complete"

Sounds like your disks might be struggling, or you've got an overly aggressive real-time antivirus. Try using the Windows "Resource Monitor" to see what's hammering your disks, and how long the disk queue is.

I assume you've followed the recommendations and set up multiple TempDB data files?
Only One TempDB Data File - Brent Ozar Unlimited[^]

You might also want to try Brent's First Responder kit[^], to see if it can identify any other configuration issues. (You have to give him your email address, but I've not seen any spam from him since I signed up.)

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)