Database become suspect..

Hello,
One of my production database become suspect. Here is the messages taken from event veiwer-> Application Log. This is all I can share with you. Please help.
Regards
Nurullah
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA "19013 :
SQL server listening on TCP, Shared Memory, Named Pipes.
"
--
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 17126 :
SQL Server is ready for client connections =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'. =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=3D2610 Failed Assertion =3D 'spaceNeeded <=3D spaceContig && spaceNeeded <=3D space_usable'.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "18052 :
Error: 3624, Severity: 20, State: 1.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '1618d4000000100' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <scanrid.cpp>, line=3D321 Failed Assertion =3D 'm_len !=3D 0'.
"
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '=01' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:34 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3063 Failed Assertion =3D '(m_lastLSN =3D=3D NullLSN) || (m_lastLSN > m_curLSN)'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3282 Failed Assertion =3D 'm_lastLSN =3D=3D NullLSN || startLSN < m_lastLSN'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9004, Severity: 23, State: 7
An error occurred while processing the log for database 'eBill_Main'. "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9001, Severity: 21, State: 1
The log for database 'eBill_Main' is not available. "
--An assertion is when a piece of SQL Server's C Code goes west.
This line interested me though
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=2610
Failed Assertion = 'spaceNeeded <= spaceContig &&
spaceNeeded <= space_usable'.
Do you have enough disk space?
What version SQL Server and SP are you running ?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Database become inaccessible

Hi,
log file of my database become inaccessible after some time.
After restarting mssqlserver, few hours it is working fine and then it happe
n again.
In the serverlog I can see:
2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The proce
ss cannot access the file because it is being used by another process.) duri
ng the creation/opening of physical device C:\Program Files\Microsoft SQL Se
rver\MSSQL\Data\Replication
DB_log.LDF.
2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\
Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virt
ual device number (VDN) 2.
2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for
database ReplicationDB.
2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for
file C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
regards
janCheck eventlog etc for OS and HW errors. Also, check if you have autoclose o
n, that can result in
the file being closed that the file is used by some other program (anti-viru
s, defrag etc).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Jan Zatko" <jan.zatko@.frequentis.com> wrote in message
news:855E0933-1BE8-4B7F-94D1-BA214CBADDB7@.microsoft.com...

quote:

---

> Hi,
> log file of my database become inaccessible after some time.
> After restarting mssqlserver, few hours it is working fine and then it hap
pen again.
> In the serverlog I can see:
> 2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot acce
ss the

---

file because it is being used by another process.) during the creation/openi
ng of physical device
C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.

quote:

---

> 2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program[/color
]

---

Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2.[QUO
TE]
> 2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database[/colo
r][/QUOTE]
ReplicationDB.

quote:

---

> 2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Progra
m

---

Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.

quote:

---

> regards
> jan

---

Database become inaccessible

Hi
log file of my database become inaccessible after some time.
After restarting mssqlserver, few hours it is working fine and then it happen again
In the serverlog I can see
2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF
2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2
2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database ReplicationDB.
2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
regard
janCheck eventlog etc for OS and HW errors. Also, check if you have autoclose on, that can result in
the file being closed that the file is used by some other program (anti-virus, defrag etc).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jan Zatko" <jan.zatko@.frequentis.com> wrote in message
news:855E0933-1BE8-4B7F-94D1-BA214CBADDB7@.microsoft.com...
> Hi,
> log file of my database become inaccessible after some time.
> After restarting mssqlserver, few hours it is working fine and then it happen again.
> In the serverlog I can see:
> 2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot access the
file because it is being used by another process.) during the creation/opening of physical device
C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
> 2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program
Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2.
> 2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database
ReplicationDB.
> 2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Program
Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
> regards
> jansql

Database Backup in 0.5 B and Transaction log backup in GBs

SQL gurus,
Simple question:
Our database is 470MB and transaction log has become 18GB! (Clustered). I
see local Fixed drives in both the SQL boxes from 'System Information' and
disk manager where data is being stored.
We are backing up database once in 24 hours and transaction logs every two
hours during business hours and full transaction log at night.
Simple Question is if we are backing full transaction log at night then
isn't supposed to make the transaction log to "zero" bytes or say clear the
transaction log automatically? After all when transaction logs have been
backup then why the transaction log continue to grow'
If the database is crashed what would be the sequence to recover:
Restore database in restore mode then restore first transaction log backup
taken after full backup of database and then restore the next backup taken
of transaction log and so on. (to apply in sequence starting from the first
one backup of transaction log taken).
Now question is :
Situation #1
1) if database is crashed and we still have transaction logs then can we
restore transaction logs (not backup of transaction logs) at the end of
procedure to restore as given above'
Situation #2
2) database is crashed and we do not have transaction logs then we can
restore as given in the procedure above except that in the last stage we
keep "no restore mode"'
Thanks
MeiNarendra
All of these issues are described on BOL very well.
Please refer to BOL.
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically? After all when transaction logs have been
> backup then why the transaction log continue to grow'
> If the database is crashed what would be the sequence to recover:
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
> Thanks
> Mei
>|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically?
Backing up the log does truncate the log( making space available for
re-use), but it does not shrink the log. Long running transactions can cause
the log to grow larger than you intended. After backing up the log, you may
use dbcc shrinkfile on the log file to reduce its physical size.
After all when transaction logs have been
> backup then why the transaction log continue to grow'
The only explanation I can think of is long running transactions...
> If the database is crashed what would be the sequence to recover:
>
set the db to dbo use only and kick off all users
backup the log with truncate_only
Restore the last whole data backup
restore each transaction log in the proper sequence.
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
No, restore the log backups
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
Yes, you may restore the whole database backup and allow recovery to run.
The database will be available, but you will have lost all of the data which
was in the transaction logs which you did not restore.
> Thanks
> Mei
>