This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(150.4, 17%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Hello,
We have Sql Server 2012 and we are running Ola's "DatabaseIntegrityCheck" job daily during off peak hours early in the morning, job runs fine but sometimes currently runs longer in our test server but production server no issue.
Server and Database settings are same in both Test and Production.
System going to be obsoleted so it's not updated with the latest patch.
We are having issue in both System databases and User Databases
This server had few databases but database are small and Total DBs size is less then 2 GB.
I checked TEMPDB and is not full.
Server having Disk Space also almost 95% Free space available.
When I checked WaitStat and it shows "RESOURCE_SEMAPHORE" for one of the user Database and Master database with command "DBCC TABLE CHECK" as stat shows 'SUSPENDED'.
I have standard MAX Memory setup to 80% out of 8 GB.
If it is going to be "obsoleted", I guess this just one more kick the butt to get that action going and upgrade the databases that needs to be upgraded.
Thanks Erland.
I am just trying to fix for future until we get rid off and making solution for any other system if it's happened.
Appreciate more suggestions.
First of all, I've converted your Answer to a comment. Answers are for answers to your question. So if you are asking a question, you would typically not be posting Answers. The one exception is if you find out the answer yourself.
Seeya gave a good reference for RESOURCE_SEMAPHORE. You could try rebooting the server. If the issue persists, throw more memory on it. 8GB is not a lot.
It is worth pointing out that one reasons DBCC CHECKDB all of a sudden starts to run a lot longer is if you actually have production, because it spends lots of time trying to piece things together.
Thanks Erland,
I just realized regarding comment v/s answer, thanks for pointing pout.
I have planned reboot this week any time and will see how it resolving the issue or not.
This is Test server and unfortunately Production server doesn't have nay issue but in test, it's happening on both User and System databases even though job running early morning, nothing else running during that time and also on the server doesn't have much activities.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi pdsqsql,
Please refer to this document: sys.dm_os_wait_stats (Transact-SQL)
You can run the sp_who command to get a list of all the current users, sessions and processes. You can then run the KILL command on any spid that is blocking others.
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Seeya,
Thank you very much for your detailed response.
I already checked SP_WHO2 and there are no SPID session is blocking.
I have also killed some inactive sessions.
Not sure is it Memory issue or something else?
I have also run some Memory related query:
Hi @pdsqsql ,
Some time has passed, and I noticed that some content seems to be missing from your reply. Have you solved your problem yet? If it is solved, you can select the answer Accept that is helpful to you. if not, please put the latest progress here.
Best regards,
Seeya
Seeya,
I restarted the server so will wait till next time if you issue comes back.
Thanks for all your help and everyone for your inputs.