none
SQL Server memory high usage.

    Question

  • Hi

    I Have a SQL 2008 R2 SP1 running on 64bits. The memory usage grows until no memory is available. We have Max memory set to 6 GB. The server has 24 GB of RAM. I understand that the limit is only for buffer pool, and there could be external component which uses more memory.

    Currently I have seen that there's a component called MEMORYCLERK_SQLGENERAL which uses more than 8 GB of memory. What is this component? How can I know which session is using the memory?

    Thanks in advance.

    Regards.

    Thursday, March 15, 2012 3:02 AM

Answers

  • Hello Frivas,

    It's very difficult to find out the session without taking SQL server memory dump.  So, please run this query and search for the stored procedure having xml:

    EXEC sp_MSforeachdb '

    SELECT ''?'', name, text

    FROM ?.dbo.syscomments c

    INNER JOIN ?.dbo.sysobjects o ON c.id = o.id

    WHERE CHARINDEX (''sp_xml_'', [text])>0'

    and try to find out the ones not having: sp_xml_removedocument in them. If you find the stored procedure without sp_xml_removedocument then please add it to free up the memory consumed by xml documents.

    HTH!


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Monday, March 19, 2012 4:33 PM

All replies

  • First of all  you can increase MAX memory value to 12/16GB easily... Please show the output of DBCC MEMORYSTATUS (interesting in CACHESTORE_SQLCP (Total) ,Procedure Cache ,Buffer Distribution)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 15, 2012 3:42 AM
  • Thanks for the answer.

    Yes I could change the memory to a greater MB but that will consume the 100% faster, in fact it were 10GB and we lowered to 6 GB.

    Memory Manager                           KB
    ---------------------------------------- -----------
    VM Reserved                              33321064
    VM Committed                             8126796
    Locked Pages Allocated                   6413952
    Reserved Memory                          1024
    Reserved Memory In Use                   0
    
    (5 row(s) affected)
    
    Memory node Id = 0                       KB
    ---------------------------------------- -----------
    VM Reserved                              7976192
    VM Committed                             7975832
    Locked Pages Allocated                   0
    MultiPage Allocator                      7340880
    SinglePage Allocator                     2190664
    
    (5 row(s) affected)
    
    Memory node Id = 1                       KB
    ---------------------------------------- -----------
    VM Reserved                              25341160
    VM Committed                             147376
    Locked Pages Allocated                   6413952
    MultiPage Allocator                      72592
    SinglePage Allocator                     2190664
    
    (5 row(s) affected)
    
    Memory node Id = 64                      KB
    ---------------------------------------- -----------
    VM Reserved                              2624
    VM Committed                             2552
    Locked Pages Allocated                   0
    MultiPage Allocator                      2472
    SinglePage Allocator                     2190664
    
    (5 row(s) affected)
    
    MEMORYCLERK_SQLGENERAL (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     6384
    MultiPage Allocator                      29616
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLGENERAL (node 1)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1277504
    MultiPage Allocator                      7332416
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLGENERAL (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1283888
    MultiPage Allocator                      7362032
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB
    ---------------------------------------- -----------
    VM Reserved                              25214976
    VM Committed                             32768
    Locked Pages Allocated                   6406528
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      1624
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLQUERYEXEC (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     520
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLOPTIMIZER (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     688
    MultiPage Allocator                      920
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLUTILITIES (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              840
    VM Committed                             840
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     144
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSTORENG (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              7424
    VM Committed                             0
    Locked Pages Allocated                   7424
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     4416
    MultiPage Allocator                      1056
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSTORENG (node 1)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1416
    MultiPage Allocator                      3696
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSTORENG (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                              7424
    VM Committed                             0
    Locked Pages Allocated                   7424
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     5832
    MultiPage Allocator                      4752
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLCONNECTIONPOOL (node 0)   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     640
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLCONNECTIONPOOL (node 1)   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     560
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLCONNECTIONPOOL (Total)    KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1200
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLCLR (node 0)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSERVICEBROKER (node 0)    KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     160
    MultiPage Allocator                      544
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLHTTP (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SNI (node 0)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     80
    MultiPage Allocator                      16
    
    (7 row(s) affected)
    
    MEMORYCLERK_SNI (node 1)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     88
    MultiPage Allocator                      16
    
    (7 row(s) affected)
    
    MEMORYCLERK_SNI (node 64)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      16
    
    (7 row(s) affected)
    
    MEMORYCLERK_SNI (Total)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     168
    MultiPage Allocator                      48
    
    (7 row(s) affected)
    
    MEMORYCLERK_FULLTEXT (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     24
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLXP (node 0)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_BHF (node 0)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     80
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_BHF (node 1)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     96
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_BHF (Total)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     176
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_XE_BUFFER (node 0)           KB
    ---------------------------------------- -----------
    VM Reserved                              2816
    VM Committed                             2816
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_XE_BUFFER (node 1)           KB
    ---------------------------------------- -----------
    VM Reserved                              1408
    VM Committed                             1408
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_XE_BUFFER (Total)            KB
    ---------------------------------------- -----------
    VM Reserved                              4224
    VM Committed                             4224
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_HOST (node 1)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     136
    MultiPage Allocator                      64
    
    (7 row(s) affected)
    
    MEMORYCLERK_SOSNODE (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     18224
    MultiPage Allocator                      13744
    
    (7 row(s) affected)
    
    MEMORYCLERK_SOSNODE (node 1)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     15832
    MultiPage Allocator                      3960
    
    (7 row(s) affected)
    
    MEMORYCLERK_SOSNODE (node 64)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      2392
    
    (7 row(s) affected)
    
    MEMORYCLERK_SOSNODE (Total)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     34064
    MultiPage Allocator                      20096
    
    (7 row(s) affected)
    
    MEMORYCLERK_FULLTEXT_SHMEM (node 0)      KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              288
    SM Committed                             288
    SinglePage Allocator                     0
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
    ---------------------------------------------- -----------
    VM Reserved                                    0
    VM Committed                                   0
    Locked Pages Allocated                         0
    SM Reserved                                    0
    SM Committed                                   0
    SinglePage Allocator                           48
    MultiPage Allocator                            0
    
    (7 row(s) affected)
    
    MEMORYCLERK_XE (node 0)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     432
    MultiPage Allocator                      168
    
    (7 row(s) affected)
    
    CACHESTORE_OBJCP (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     477600
    MultiPage Allocator                      20096
    
    (7 row(s) affected)
    
    CACHESTORE_SQLCP (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     11488
    MultiPage Allocator                      4040
    
    (7 row(s) affected)
    
    CACHESTORE_PHDR (node 0)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     69120
    MultiPage Allocator                      32
    
    (7 row(s) affected)
    
    CACHESTORE_XPROC (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     264
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_TEMPTABLES (node 0)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     560
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_NOTIF (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_VIEWDEFINITIONS (node 0)      KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_XMLDBTYPE (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_XMLDBELEMENT (node 0)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_XMLDBATTRIBUTE (node 0)       KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_STACKFRAMES (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      8
    
    (7 row(s) affected)
    
    CACHESTORE_STACKFRAMES (node 1)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      8
    
    (7 row(s) affected)
    
    CACHESTORE_STACKFRAMES (node 64)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      8
    
    (7 row(s) affected)
    
    CACHESTORE_STACKFRAMES (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      24
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERTBLACS (node 0)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     80
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERTBLACS (node 1)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     400
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERTBLACS (Total)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     480
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERKEK (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERDSH (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERRSB (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERREADONLY (node 0)       KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     96
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_BROKERTO (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_EVENTS (node 0)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_SYSTEMROWSET (node 0)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     3312
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_SYSTEMROWSET (node 1)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     816
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_SYSTEMROWSET (Total)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     4128
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_CONVPRI (node 0)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     64
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_CONVPRI (node 1)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     16
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_CONVPRI (Total)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     80
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    CACHESTORE_FULLTEXTSTOPLIST (node 0)     KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     32
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SCHEMAMGR (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     21008
    MultiPage Allocator                      192
    
    (7 row(s) affected)
    
    USERSTORE_DBMETADATA (node 0)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     32120
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_DBMETADATA (node 1)            KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2568
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_DBMETADATA (Total)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     34688
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_TOKENPERM (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1664
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_TOKENPERM (node 1)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     720
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_TOKENPERM (Total)              KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2384
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_OBJPERM (node 0)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     1800
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_OBJPERM (node 1)               KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     208
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_OBJPERM (Total)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     2008
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SXC (node 0)                   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     152
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SXC (node 1)                   KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     192
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SXC (node 64)                  KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     8
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    USERSTORE_SXC (Total)                    KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     352
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LBSS (node 0)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     96
    MultiPage Allocator                      480
    
    (7 row(s) affected)
    
    OBJECTSTORE_LBSS (node 1)                KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     96
    MultiPage Allocator                      480
    
    (7 row(s) affected)
    
    OBJECTSTORE_LBSS (Total)                 KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     192
    MultiPage Allocator                      960
    
    (7 row(s) affected)
    
    OBJECTSTORE_SNI_PACKET (node 0)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     3544
    MultiPage Allocator                      56
    
    (7 row(s) affected)
    
    OBJECTSTORE_SNI_PACKET (node 1)          KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     3040
    MultiPage Allocator                      56
    
    (7 row(s) affected)
    
    OBJECTSTORE_SNI_PACKET (node 64)         KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     0
    MultiPage Allocator                      56
    
    (7 row(s) affected)
    
    OBJECTSTORE_SNI_PACKET (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     6584
    MultiPage Allocator                      168
    
    (7 row(s) affected)
    
    OBJECTSTORE_SERVICE_BROKER (node 0)      KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     448
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LOCK_MANAGER (node 0)        KB
    ---------------------------------------- -----------
    VM Reserved                              32768
    VM Committed                             32768
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     216584
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LOCK_MANAGER (node 1)        KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     79320
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LOCK_MANAGER (node 64)       KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     24
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_LOCK_MANAGER (Total)         KB
    ---------------------------------------- -----------
    VM Reserved                              32768
    VM Committed                             32768
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    SinglePage Allocator                     295928
    MultiPage Allocator                      0
    
    (7 row(s) affected)
    
    OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB
    ------------------------------------------ -----------
    VM Reserved                                0
    VM Committed                               0
    Locked Pages Allocated                     0
    SM Reserved                                0
    SM Committed                               0
    SinglePage Allocator                       16
    MultiPage Allocator                        0
    
    (7 row(s) affected)
    
    Buffer Pool                              Value
    ---------------------------------------- -----------
    Committed                                768000
    Target                                   768000
    Database                                 284142
    Dirty                                    44629
    In IO                                    0
    Latched                                  1
    Free                                     210025
    Stolen                                   273833
    Reserved                                 0
    Visible                                  768000
    Stolen Potential                         455767
    Limiting Factor                          17
    Last OOM Factor                          0
    Last OS Error                            0
    Page Life Expectancy                     22640
    
    (15 row(s) affected)
    
    Process/System Counts                    Value
    ---------------------------------------- --------------------
    Available Physical Memory                7079747584
    Available Virtual Memory                 8759982362624
    Available Paging File                    10661367808
    Working Set                              9146646528
    Percent of Committed Memory in WS        100
    Page Faults                              42530874
    System physical memory high              1
    System physical memory low               0
    Process physical memory low              0
    Process virtual memory low               0
    
    (10 row(s) affected)
    
    Procedure Cache                          Value
    ---------------------------------------- -----------
    TotalProcs                               1676
    TotalPages                               72830
    InUsePages                               640
    
    (3 row(s) affected)
    
    Global Memory Objects                    Pages
    ---------------------------------------- -----------
    Resource                                 1349
    Locks                                    36991
    XDES                                     131
    SETLS                                    12
    SE Dataset Allocators                    24
    SubpDesc Allocators                      12
    SE SchemaManager                         1111
    SE Column Metadata Cache                 1277
    SQLCache                                 244
    Replication                              321
    ServerGlobal                             52
    XP Global                                2
    SortTables                               2
    
    (13 row(s) affected)
    
    Query Memory Objects (internal)          Value
    ---------------------------------------- -----------
    Grants                                   0
    Waiting                                  0
    Available                                282093
    Current Max                              282093
    Future Max                               282093
    Physical Max                             562278
    Next Request                             0
    Waiting For                              0
    Cost                                     0
    Timeout                                  0
    Wait Time                                0
    
    (11 row(s) affected)
    
    Small Query Memory Objects (internal)    Value
    ---------------------------------------- -----------
    Grants                                   0
    Waiting                                  0
    Available                                14847
    Current Max                              14847
    Future Max                               14847
    
    (5 row(s) affected)
    
    Query Memory Objects (default)           Value
    ---------------------------------------- -----------
    Grants                                   0
    Waiting                                  0
    Available                                405840
    Current Max                              405840
    Future Max                               405840
    Physical Max                             555765
    Next Request                             0
    Waiting For                              0
    Cost                                     0
    Timeout                                  0
    Wait Time                                0
    
    (11 row(s) affected)
    
    Small Query Memory Objects (default)     Value
    ---------------------------------------- -----------
    Grants                                   0
    Waiting                                  0
    Available                                21360
    Current Max                              21360
    Future Max                               21360
    
    (5 row(s) affected)
    
    Optimization Queue (internal)            Value
    ---------------------------------------- --------------------
    Overall Memory                           5042995200
    Target Memory                            2601304064
    Last Notification                        1
    Timeout                                  6
    Early Termination Factor                 5
    
    (5 row(s) affected)
    
    Small Gateway (internal)                 Value
    ---------------------------------------- -----------
    Configured Units                         48
    Available Units                          48
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         380000
    Threshold                                380000
    
    (6 row(s) affected)
    
    Medium Gateway (internal)                Value
    ---------------------------------------- -----------
    Configured Units                         12
    Available Units                          12
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         12
    Threshold                                -1
    
    (6 row(s) affected)
    
    Big Gateway (internal)                   Value
    ---------------------------------------- -----------
    Configured Units                         1
    Available Units                          1
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         8
    Threshold                                -1
    
    (6 row(s) affected)
    
    Optimization Queue (default)             Value
    ---------------------------------------- --------------------
    Overall Memory                           5042995200
    Target Memory                            3917905920
    Last Notification                        1
    Timeout                                  6
    Early Termination Factor                 5
    
    (5 row(s) affected)
    
    Small Gateway (default)                  Value
    ---------------------------------------- -----------
    Configured Units                         48
    Available Units                          48
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         380000
    Threshold                                380000
    
    (6 row(s) affected)
    
    Medium Gateway (default)                 Value
    ---------------------------------------- -----------
    Configured Units                         12
    Available Units                          12
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         12
    Threshold                                -1
    
    (6 row(s) affected)
    
    Big Gateway (default)                    Value
    ---------------------------------------- -----------
    Configured Units                         1
    Available Units                          1
    Acquires                                 0
    Waiters                                  0
    Threshold Factor                         8
    Threshold                                -1
    
    (6 row(s) affected)
    
    Memory Pool Manager                      Pages
    ---------------------------------------- -----------
    Reserved Current                         0
    Reserved Limit                           455771
    
    (2 row(s) affected)
    
    Memory Pool (internal)                   Pages
    ---------------------------------------- -----------
    Allocations                              2686
    Predicted                                2686
    Private Target                           0
    Private Limit                            0
    Total Target                             729600
    Total Limit                              729600
    OOM Count                                0
    
    (7 row(s) affected)
    
    MEMORYBROKER_FOR_CACHE (internal)        Pages
    ---------------------------------------- -----------
    Allocations                              1890
    Rate                                     0
    Target Allocations                       318636
    Future Allocations                       0
    Overall                                  615600
    Last Notification                        1
    
    (6 row(s) affected)
    
    MEMORYBROKER_FOR_STEAL (internal)        Pages
    ---------------------------------------- -----------
    Allocations                              796
    Rate                                     0
    Target Allocations                       317542
    Future Allocations                       0
    Overall                                  615600
    Last Notification                        1
    
    (6 row(s) affected)
    
    MEMORYBROKER_FOR_RESERVE (internal)      Pages
    ---------------------------------------- -----------
    Allocations                              0
    Rate                                     0
    Target Allocations                       316746
    Future Allocations                       0
    Overall                                  615600
    Last Notification                        1
    
    (6 row(s) affected)
    
    Memory Pool (default)                    Pages
    ---------------------------------------- -----------
    Allocations                              271143
    Predicted                                410168
    Private Target                           0
    Private Limit                            0
    Total Target                             729600
    Total Limit                              729600
    OOM Count                                0
    
    (7 row(s) affected)
    
    MEMORYBROKER_FOR_CACHE (default)         Pages
    ---------------------------------------- -----------
    Allocations                              109629
    Rate                                     84
    Target Allocations                       426459
    Future Allocations                       0
    Overall                                  615600
    Last Notification                        1
    
    (6 row(s) affected)
    
    MEMORYBROKER_FOR_STEAL (default)         Pages
    ---------------------------------------- -----------
    Allocations                              161514
    Rate                                     -1
    Target Allocations                       478260
    Future Allocations                       0
    Overall                                  615600
    Last Notification                        1
    
    (6 row(s) affected)
    
    MEMORYBROKER_FOR_RESERVE (default)       Pages
    ---------------------------------------- -----------
    Allocations                              0
    Rate                                     0
    Target Allocations                       455687
    Future Allocations                       138941
    Overall                                  615600
    Last Notification                        1
    
    (6 row(s) affected)
    
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    

    Thursday, March 15, 2012 5:05 AM
  • As you probably know SQL Server allocates all available memory  and releases it when the OS requires it. So in your case it should allocate 6GB MAX memory value

    BTW is it standard or enterprise edition? Have you added an sql server account to local group policy name Locked Pages in Memory?

    Looks like MEMORYCLERK_SQLGENERAL is trace event ,so try identified  the trace event that is generated too often (many hundreds of times / second) and remove it from your  machine, take a look at sys.traces table


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 15, 2012 6:00 AM
  • Adding my 2 cents:

    In this scenario, the issue is because of multipage allocator.

    MEMORYCLERK_SQLGENERAL (Total)           KB
    ---------------------------------------- -----------
    VM Reserved                             
    0
    VM Committed                            
    0
    Locked Pages Allocated                  
    0
    SM Reserved                             
    0
    SM Committed                            
    0
    SinglePage Allocator                    
    1283888
    MultiPage Allocator                     
    7362032  <== It's approx.7GB

    This space is being used from the OS and that's causing the major issue. In 64 bit system, this sometimes cause the problem because of the huge VAS available.  In your case :

    Memory Manager                           KB
    ---------------------------------------- -----------
    VM Reserved                             
    33321064
    VM Committed                            
    8126796 <== 8Gb is total memory being used currently
    Locked Pages Allocated                  
    6413952
    Reserved Memory                         
    1024
    Reserved Memory
    In Use                   0

    (5 row(s) affected)

    Memory node Id
    = 0                       KB
    ---------------------------------------- -----------
    VM Reserved                             
    7976192
    VM Committed                            
    7975832
    Locked Pages Allocated                  
    0
    MultiPage Allocator                     
    7340880  <== memory used from the OS.
    SinglePage Allocator                    
    2190664

    (5 row(s) affected)

    Memory node Id
    = 1                       KB
    ---------------------------------------- -----------
    VM Reserved                             
    25341160
    VM Committed                            
    147376
    Locked Pages Allocated                  
    6413952
    MultiPage Allocator                     
    72592 <== memory used from the OS.
    SinglePage Allocator                     2190664

    If we sum the multipage allocator: -  72592 +7340880  =  7413472  and out of this 7362032  is being used by SQLgeneral memory clerk. This memory clerk is also responsible for keeping xml and stuff. Please run the below mentioned query to find out the object using major chunk of memory. It will give us more clarity about the issue :

    SELECT SUM (pages_allocated_count * page_size_in_bytes)/1024 as 'KB Used', mo.type, mc.type

    FROM sys.dm_os_memory_objects mo

    join sys.dm_os_memory_clerks mc on mo.page_allocator_address=mc.page_allocator_address

    GROUP BY mo.type, mc.type, mc.type

    ORDER BY 1 DESC;


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Thursday, March 15, 2012 6:52 AM
  • As Harsh said, your SQL Server buffer pool memory is using the correct amount of RAM.  You have no control over the other SQL memory allocations.

    You need to look at the other programs running on the server.  Are you running another instance, SSIS, SSRS, something else on this server?

    Thursday, March 15, 2012 1:33 PM
  • I think you're right, XML seems to be the issue on this case, I'm suspecting connections are done, prepare the xml document, but never releases.

    We had to reboot the server last night and I was not able to capture or to run anything.

    One of the errors stated on the clients is the following:

                  Server: Msg 6624, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1

    XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.

    Does anybody know how to review which documents are in memory? and who's the owner of them?

    Thanks Uri and Harsh for looking at this, and everyone who contribute.

    Regards.

    Thursday, March 15, 2012 9:21 PM
  • This is the ouput right now.

    1399032    MEMOBJ_MSXML    MEMORYCLERK_SQLGENERAL
    65152    MEMOBJ_PARSE    CACHESTORE_PHDR
    38872    MEMOBJ_LOCKBLOCKS    OBJECTSTORE_LOCK_MANAGER
    32088    MEMOBJ_SECOLMETACACHE    USERSTORE_SCHEMAMGR
    29152    MEMOBJ_METADATADB    USERSTORE_DBMETADATA

    Regards.

    FR.

    Friday, March 16, 2012 4:47 PM
  • I suspect xml to be the cause of the issue and you need to look into your application for how the xml is being handled.


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Friday, March 16, 2012 5:13 PM
  • Yes I've done some tests on a test env. and run xmls until the memory depletes, however i cannot identify which session is getting the memory full. That's the issue i Have.

    Regards.

    FR.

    Friday, March 16, 2012 6:49 PM
  • Hello Frivas,

    It's very difficult to find out the session without taking SQL server memory dump.  So, please run this query and search for the stored procedure having xml:

    EXEC sp_MSforeachdb '

    SELECT ''?'', name, text

    FROM ?.dbo.syscomments c

    INNER JOIN ?.dbo.sysobjects o ON c.id = o.id

    WHERE CHARINDEX (''sp_xml_'', [text])>0'

    and try to find out the ones not having: sp_xml_removedocument in them. If you find the stored procedure without sp_xml_removedocument then please add it to free up the memory consumed by xml documents.

    HTH!


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Monday, March 19, 2012 4:33 PM
  • This was very helpful for me.  I was especially interested in sys.dm_os_memory_objects for type MEMOBJ_MSXML in SQL Server 2012.  With the new anypage allocator some of the memory-related DMVs have changed a little.  I used the slightly modified query below to get exactly what I needed for monitoring aggregate sp_xml_preparedocument use.

    Thanks!

    SELECT
       SUM (pages_in_bytes)/1024 AS 'KB Used',
       mo.type,
       mc.type
    FROM
       sys.dm_os_memory_objects AS mo
    JOIN
       sys.dm_os_memory_clerks AS mc
       ON mo.page_allocator_address = mc.page_allocator_address
    WHERE
       mo.type='MEMOBJ_MSXML'
    GROUP BY mo.type, mc.type
    ORDER BY 1 DESC;

    Wednesday, September 25, 2013 12:15 AM
  • This is the ouput right now.

    1399032    MEMOBJ_MSXML    MEMORYCLERK_SQLGENERAL
    65152    MEMOBJ_PARSE    CACHESTORE_PHDR
    38872    MEMOBJ_LOCKBLOCKS    OBJECTSTORE_LOCK_MANAGER
    32088    MEMOBJ_SECOLMETACACHE    USERSTORE_SCHEMAMGR
    29152    MEMOBJ_METADATADB    USERSTORE_DBMETADATA

    Regards.

    FR.

    so you know it is because of prepared document not remove. run the below query and identify the session

    which has open documents and fix it

    select * from sys.dm_exec_xml_handles(0)


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, September 25, 2013 12:14 PM