URGENT: Memory issue

• Peter Gadsby

  SSCrazy

  Points: 2718

  More actions

  March 5, 2007 at 3:46 am

  #369713

  HI all,

  I have a live server that is running Windows 2003 Enteprise edition with 17Gb of memory. It is running SQL Server 2000 Enteprise with AWE switched on.

  Recently one of our Windows administrators changed the SQL Server login from adminstrator to another user. and restarted SQL Server. The user didn't have the lock page memory permissions. This meant that AWE could not be enabled. I changed the permissions and restart SQL Server all seemed well....

  Now around 02:00 in the morning our DTS packages that perform the ETL for the data warehouse is failing with the following error message:-

  Step Error Source: Microsoft OLE DB Provider for SQL Server

  Step Error Description1:Extract Dimensions) SubStep 'DTSStep_DTSExecuteSQLTask_4' failed with the following error:

  There is insufficient system memory to run this query.

  After restarting the job, all seems to be fine!!!

  I have had a look in the SQL Server log, and there seem to be a lot of messages about memory.

  2007-03-04 02:24:14.85 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:14.85 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=140 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:14.85 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:14.85 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:14.85 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:14.85 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:14.86 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:14.96 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:14.96 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=140 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:14.96 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:14.96 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:14.96 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:14.96 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:14.97 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.07 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.07 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=140 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.07 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.07 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.07 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.07 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.08 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.18 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.18 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=140 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.18 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.18 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.18 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.18 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.19 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.29 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.29 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=140 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.29 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.29 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.29 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.29 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.30 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.40 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.40 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=140 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.40 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.40 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.40 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.40 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.41 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.50 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.50 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.50 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.50 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.50 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.50 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.54 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.61 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.61 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.61 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.61 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.61 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.61 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.65 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.72 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.72 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.72 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.72 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.72 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.72 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.75 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.83 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.83 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.83 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.83 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.83 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.83 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.86 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:15.94 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:15.94 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:15.94 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:15.94 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:15.94 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:15.94 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:15.97 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.05 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:16.05 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:16.05 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.05 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.05 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.05 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:16.08 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.16 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:16.16 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:16.16 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.16 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.16 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.16 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:16.19 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.27 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:16.27 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:16.27 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.27 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.27 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.27 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:16.30 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.38 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:16.38 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1024

  2007-03-04 02:24:16.38 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.38 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.38 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.38 spid66    Query Memory Manager:  Grants=2 Waiting=1 Maximum=67453 Available=0

  2007-03-04 02:24:16.41 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.47 spid51    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.50 spid66    Buffer Distribution:  Stolen=189960 Free=1283 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=157, Other=1908721

  2007-03-04 02:24:16.50 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33429 Min Free=1024

  2007-03-04 02:24:16.50 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.50 spid66    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.50 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.50 spid66    Query Memory Manager:  Grants=3 Waiting=0 Maximum=67509 Available=0

  2007-03-04 02:24:16.58 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.61 spid19    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.61 spid51    Buffer Distribution:  Stolen=189960 Free=1283 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=158, Other=1908720

  2007-03-04 02:24:16.61 spid51    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33429 Min Free=1016

  2007-03-04 02:24:16.61 spid51    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.61 spid51    Dynamic Memory Manager:  Stolen=156017 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=225 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.61 spid51    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.61 spid51    Query Memory Manager:  Grants=3 Waiting=0 Maximum=67509 Available=0

  2007-03-04 02:24:16.71 spid66    Buffer Distribution:  Stolen=189954 Free=1289 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=158, Other=1908720

  2007-03-04 02:24:16.71 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1016

  2007-03-04 02:24:16.71 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.71 spid66    Dynamic Memory Manager:  Stolen=156012 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=220 Optimizer=149672

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.71 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.71 spid66    Query Memory Manager:  Grants=2 Waiting=0 Maximum=67509 Available=56

  2007-03-04 02:24:16.72 spid19    Buffer Distribution:  Stolen=189957 Free=1286 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=158, Other=1908720

  2007-03-04 02:24:16.72 spid19    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1016

  2007-03-04 02:24:16.72 spid19    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.72 spid19    Dynamic Memory Manager:  Stolen=156016 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=220 Optimizer=149676

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.72 spid19    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.72 spid19    Query Memory Manager:  Grants=2 Waiting=0 Maximum=67509 Available=56

  2007-03-04 02:24:16.75 spid19    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.75 spid66    BPool::Map: no remappable address found.

  2007-03-04 02:24:16.86 spid19    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=158, Other=1908720

  2007-03-04 02:24:16.86 spid19    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1016

  2007-03-04 02:24:16.86 spid19    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.86 spid19    Dynamic Memory Manager:  Stolen=156016 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=220 Optimizer=149676

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.86 spid19    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.86 spid19    Query Memory Manager:  Grants=2 Waiting=0 Maximum=67509 Available=56

  2007-03-04 02:24:16.86 spid66    Buffer Distribution:  Stolen=189959 Free=1284 Procedures=139

    Inram=3788 Dirty=58094 Kept=0

    I/O=0, Latched=158, Other=1908720

  2007-03-04 02:24:16.86 spid66    Buffer Counts:  Commited=2162142 Target=2162142 Hashed=1970760

    InternalReservation=297 ExternalReservation=33373 Min Free=1016

  2007-03-04 02:24:16.86 spid66    Procedure Cache:  TotalProcs=1 TotalPages=139 InUsePages=139

  2007-03-04 02:24:16.86 spid66    Dynamic Memory Manager:  Stolen=156016 OS Reserved=1584

    OS Committed=1563

    OS In Use=1514

    Query Plan=220 Optimizer=149676

    General=4819

    Utilities=2311 Connection=156

  2007-03-04 02:24:16.86 spid66    Global Memory Objects:  Resource=1672 Locks=148

    SQLCache=98 Replication=2

    LockBytes=2 ServerGlobal=44

    Xact=70

  2007-03-04 02:24:16.88 spid66    Query Memory Manager:  Grants=2 Waiting=0 Maximum=67509 Available=56

  Any help much appreciated.

  Thanks

  Pete

   

  Peter Gadsby
  Business Intelligence Consultant
  www.hgconsult.co.uk

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  March 5, 2007 at 5:41 am

  #693059

  first off if this is a production system I'd either sack the windows admin who made the change or remove builtin admins access to the sql server ( although that won't fully solve the problem so the first option seems best )

  SQL Service accounts must be changed through enterprise manager, not services/service accounts as the new account won't pick up the proper permissions. So I suggst you take that route and you need to restart the sql service at least to be sure - check the sql error log to make sure all is ok.

  so . memory - well dts runs out of process so fights for that bottom 2gb of ram - awe takes 1 gb out of that ( as I understand it ) so I assume you don't have the /3gb switch enabled - if so get rid of it. Don't allocate more than 15gb to sql server with awe - make sure you have issues a sql command to set max memory with awe, don't use EM use the t-sql. Don't bother with min memory as awe disables dynamic memory management ( completely )

  You'll probably have lower memory issues, i have a server with similar issues, watch your out of process programs, dts, sqlmaint, linked servers, open rowset and so on. Check out the -g startup option ( you can place this through EM ) to attempt to set contiguous lower memory.

  If in doubt get Ken Henderson's internals guide to sql server, it'll explain some of this.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• Peter Gadsby

  SSCrazy

  Points: 2718

  More actions

  March 5, 2007 at 6:20 am

  #693065

  Cool thanks for that Colin, I had the same thoughts about 15Gb of memory... I have also read that upgrading to SP4 then applying a hotfix ( KB899761&nbsp may also solve the problem, have you had any issues with SP4 and AWE?

   

  Pete

  Peter Gadsby
  Business Intelligence Consultant
  www.hgconsult.co.uk

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  March 5, 2007 at 9:19 am

  #693146

  well I never did sp4 without the awe patch, which shows as version 2040, but i've mostly rolled up to 2187 of late, so no I never had any awe issues, but you do need the fix. I have a couple of servers with 24gb ram and previously have used up to 32gb of ram without any issues. ( well awe issues anyway )

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• Peter Gadsby

  SSCrazy

  Points: 2718

  More actions

  March 5, 2007 at 9:25 am

  #693153

  What about SP4 itself, any servers with issues? I have a jittery client that are worried about regression should we have problems with SP4....Especially as Microsoft say you can't rollback.

  Peter Gadsby
  Business Intelligence Consultant
  www.hgconsult.co.uk

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  March 5, 2007 at 10:04 am

  #693165

  sp4 has some problems ( so I'm told ) which are resolved with the 2187 rollup. You must apply the awe patch if you apply sp4. I personally haven't seen any obvious issues. sp4 introduces some new messages which may appear to indicate your server has problems, when it hasn't, reporting is just better.

  difficult to advise really, deploy to test system first ?

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• Peter Gadsby

  SSCrazy

  Points: 2718

  More actions

  March 5, 2007 at 10:11 am

  #693167

  I have already applied SP4 and the patch to development but this server doesn't have the same configuration, for example only 4Gb of memory and not on the SAN but on local disks. Got some interesting messages from it when running the ETL, which I Assume is what you are talking about when mentioning the messages....

  SQL Server has encountered 27 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [D:\SQL2000Data\BusObjDataSys\Data\tempdb.mdf] in database [tempdb] (2).  The OS file handle is 0x00000530.  The offset of the latest long IO is: 0x0000006e7d6000

   

  Pete

   

  Peter Gadsby
  Business Intelligence Consultant
  www.hgconsult.co.uk