Post reply

stop responding to queries of specific database

  • April 26, 2021 at 7:27 am

    #3875733

    Hello

    I found a problem on my SQL Server instant, after 5-6 days the servers stop responding to queries of specific database and it won’t get fixe until I restart the service!

     

    I checked the event logs, it says:

    " SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes. "

    and I see this one a lot too:

    " SQL Server is terminating a system or background task Full text Host Controller Timer Task due to errors in starting up the task (setup state 1)."

    the connections are set to "0" that means unlimited, the server has 48 gigabytes of ram (SQL Server uses almost about 10 GB of it)

    I don't know what to do to solve this problem, what's your idea?

    what can I do about it?

     

  • April 26, 2021 at 4:40 pm

    #3875936

    Version & Edition of SQL? 2019/2017/2016? express/standard/enterprise?

    tried things like sp_whoisactive to see what’s going on?

    Are you hitting max worker threads.

  • April 28, 2021 at 6:32 am

    #3876700

    thanks.

    it's a 2019 enterprise edition

    the server has about 32 gb of ram , 8 cores of cpu , and server load is normal always .

    how can I see that I'm hitting the max worker threads or not ?

  • April 28, 2021 at 8:59 am

    #3876765

    Max memory / min memory settings? 32GB of RAM max memory should be around 25GB to SQL, if the value from below is 2147483647 you need to go and change your memory settings to 25600

    select * from sys.configurations where name = 'max server memory (MB)'

    Workers in use at the moment use below

    SELECT max_workers_count FROM sys.dm_os_sys_info

     

    For 8 cores you should have the config set to 0 or 576 (usually leave it 0 as you want the OS to manage it, verify using

    select * from sys.configurations where name = 'max worker threads'

  • April 28, 2021 at 9:57 am

    #3876782

    so based on the output , it's ok on this side

    what else you think could cause a problem ?

    Attachments:
    You must be logged in to view attached files.

  • April 28, 2021 at 10:33 am

    #3876798

    Memory configuration

    Poorly written queries

    Memory leaks

    To much happening on the server for it to respond

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply