Error Dump 1

[DBAWinnipeg]

Good Morning everyone

I have a reindexing stored procedure that goes through... does a DBCC SHOWCONTIG for each table and then based off of criteria does a DBREINDEX.

Now it goes through and does all this but about 1/2 way through all the reindexes I get this error message in the log and SQL locks up


2004-08-24 08:08:03.32 spid1 LazyWriter: warning, no free buffers found.
2004-08-24 08:08:03.32 spid1 Buffer Distribution: Stolen=14271 Free=0 Procedures=0
Inram=192038 Dirty=20 Kept=0
I/O=0, Latched=2343, Other=0
2004-08-24 08:08:03.32 spid1 Buffer Counts: Commited=208672 Target=208672 Hashed=194401
InternalReservation=112 ExternalReservation=88 Min Free=1024
2004-08-24 08:08:03.32 spid1 Procedure Cache: TotalProcs=0 TotalPages=0 InUsePages=0
2004-08-24 08:08:03.32 spid1 Dynamic Memory Manager: Stolen=2132 OS Reserved=1648
OS Committed=1615
OS In Use=1604
Query Plan=27 Optimizer=0
General=3393
Utilities=8 Connection=82
2004-08-24 08:08:03.32 spid1 Global Memory Objects: Resource=3195 Locks=139
SQLCache=104 Replication=2
LockBytes=2 ServerGlobal=45
Xact=27


Now in monitoring this while it's running I can see that the CPUs are barely hit and the RAM has MORE then enough available.

Is there something else I'm missing that would cause this to happen?



Thanks in advance!!!

Colin in da 'Peg

 

[JayKusch]

Try splitting the job in two or three parts/interations. I have seen/read issue that too many DB and/or tables processes can cause this issue.

For example, if you have 100 tables in this DB, try running your SP on against the first 33 tables and see if the error is raised. The run it against tables 34-66 and then follow up w/ tables 67-100.

Let us know if this quiets the issue. The we can start looking for a work around for all the table to be processed in the DB.

Thanks

J. Kusch

 

[DBAWinnipeg]

I put a counter on the end of the temp table that stores the indexes to reindex and did a 1, 2, 3, 4 assignment.

Then did the reindexes in quarters

Worked like a charm


Thanks for the idea!!!


Gold Star for you!!!