asked on

How can I solved Problem: Database could not recover thats put database in Suspect status

I have sql server 7 with 4 user databases. One of then almost every day has Suspect Status. Its strange 'cause this database only is used on weekends.

Checking sql server logs I've seen next error:

Starting up database 'SIACRespaldo'.
There is insufficient system memory to run this query.
Query Memory Manager: Grants=45 Waiting=0 Maximum=142188 Available=136428
Global Memory Objects: Resource=586 Locks=42 XDES=1...
Dynamic Memory Manager: Stolen=2930 OS=432 General=1003...
Procedure Cache: TotalProcs=7 TotalPages=61 InUsePages=61
Buffer Counts: Commited=177028 Target=193404 Hashed=162611...
Buffer Distribution: Stolen=3229 Free=11127 Procedures=61...
WARNING: Clearing procedure cache to free contiguous memory.
Database 'SIACRespaldo' (database ID 5) could not recover. Contact Technical S
Error: 3414, Severity: 21, State: 1
Opening file e:\MSSQL7\data\SAS_log.ldf.
Opening file e:\MSSQL7\data\sas.mdf.
Starting up database 'sas'.
There is insufficient system memory to run this query.
Query Memory Manager: Grants=45 Waiting=0 Maximum=142838 Available=137078
Global Memory Objects: Resource=586 Locks=42 XDES=1...
Dynamic Memory Manager: Stolen=2948 OS=432 General=1003...
Procedure Cache: TotalProcs=7 TotalPages=74 InUsePages=74
Buffer Counts: Commited=177028 Target=193528 Hashed=162761...
Buffer Distribution: Stolen=3234 Free=10958 Procedures=74...
WARNING: Clearing procedure cache to free contiguous memory.
Database 'sas' (database ID 9) could not recover. Contact Technical Support..
Error: 3414, Severity: 21, State: 1

I dont Know ow to solve this problem, any idea.

Steven_W

Have a quick look in books inline at the proc:

sp_resetstatus [ @DBName = ] 'database'

--

It does exactly what you asked.

Kevin Hill

What service pack level on the SQL 7? Sounds like a memory leak that was likely fixed in a service pack at some point...

Steven_W

Oops - realised that I mis-read your question... - You wanted to know the CUASE of the problem, not how to unmark it.

Sorry

memin

ASKER

Exactly Steven:

I want to know the CAUSE of the problem, not how to unmark it.

No problem.

From Kevin:

Answering your question

Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
      Apr 9 2002 14:18:16
      Copyright (c) 1988-2002 Microsoft Corporation
      Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

memin

ASKER

I want to Know both:

the CAUSE of the problem and HOW TO SOLVE IT.

ASKER CERTIFIED SOLUTION

pai_prasad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vadim Rapp

add more memory to the computer.

Sachin Parab

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:

Spilt: pai_prasad & Steven_W

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

sparab
EE Cleanup Volunteer