Forum Discussion

DanielE80's avatar
DanielE80
Copper Contributor
Jan 19, 2023

SQL Server 2019 Express memory leak after databases list query

I have installed instance of Microsoft SQL 2019 Server Express and ca 50 small databases registered on them for finantial application. Microsoft SQL Server is installed on Windows 11. A few days ago I haved problem with the Out of memory exeption on OS. SQL Server taked 10,2 GB RAM. After restart SQL was ok. After opened SSMS I clicked on databasses item on tree on refresh option and SQL Server reserved the same memory amount.


I tried set MAX MEMORY on 8GB but situation was repeta. When sql server take maximim of memory I can't connet to it. Connected applications are distonnected with the error comuniaction (if are connected).


The same situation is after refresh list of databasses without any another queries from applications.

After installation new instance of SQL 2019 server and move databasses to it situation are repeat.

 

I dont know what I can check another.

 

Have you any sugestions ?

4 Replies

  • MihailP's avatar
    MihailP
    Copper Contributor
    Jan 31, 2024

    Hi DanielE80,

    Firstly, I suggest reviewing the SQL Server error log. You can locate the path using the TSQL command:

     

    EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL

     


    SQL Server typically gathers DBCC MEMORYSTATUS results in cases of Out of Memory (OOM) errors. This would be a good starting point for your investigation.

     

    Secondly, I recommend collecting performance monitor data. You can use the script provided below. Remember to open the command prompt as an administrator.

    To create the performance log, use the following command:

     

    logman.exe create counter SQLServerPerf_mp -f bincirc -v mmddhhmm -max 1000 -c "\Processor(*)\*" "\LogicalDisk(*)\*" "\TCP\*" "\Memory\*" "\Network Interface(*)\*" "\PhysicalDisk(*)\*" "\Process(*)\*" "\Paging File\*" "\SqlServer:Wait Statistics(*)\*" "\SqlServer:SQL Statistics\*" "\SqlServer:Resource Pool Stats(*)\*" "\SqlServer:Memory Manager\*" "\SqlServer:Memory Node(*)\*" "\SqlServer:General Statistics\*" "\SqlServer:Databases(*)\*" "\SqlServer:Buffer Manager\*" "\SqlServer:Access Methods\*" "\SqlServer:Transactions\*" "\SqlServer:SQL Errors\*" "\ProcessorPerformance\*" "\SqlServer:Database Replica\*" "\SqlServer:Locks\*" -si 00:00:10

     

     

    To start the performance log:

     

    logman.exe start SQLServerPerf

     

     

    Wait for the issue to reoccur. Once it does, execute the following command to stop the performance log:

     

    logman.exe stop SQLServerPerf

     

     

    The default location for the logs is C:\perflogs.

     

    Lastly, it's important to verify that there are no third-party modules loaded in memory that could potentially be the root cause of a memory leak. You can do this by executing the following SQL query:

     

    SELECT * FROM sys.dm_os_loaded_modules WHERE company <> 'Microsoft Corporation'

     

     

    Please feel free to share data for analysis.

     

     

     

    • DanielE80's avatar
      DanielE80
      Copper Contributor
      Jan 23, 2023

      olafhelper 

       

      You're right. I don't know why SQL Express is using more RAM, above the limit.

      I don't know what SQL Server does when it starts.

      I open SSMS and on the Databases item in the tree and click Refresh or Expand and when the databases are visible in the tree, SQL starts some process in bacground and reserves RAM. When sql consumes ca. 10gb RAM approx. sqlsrv.exe is not stable. I can't connect to SQL Server or I have error that internal memory limit has problem.

      This situation started a few days ago. No installing new ones
      programs or updates. It is possible that Windwos 11 installed any update on its own.

      I upgraded SQL Server Express from 2019 to 2022 and the situation is the same.

      In my opinion after start SQL server process sqlsrv.exe shoult to have maximim 700 - 800 MB RAM consumed after refresh databases list.

      What this process is doing ?