Another Suspect ?

Hi
My database went into suspect mode. I made some space on the drives after I
was unable to add additional log files
sp_add_log_file_recover_suspect_db nestledb_jp_coremart_v1, logfile2,
'D:\MSSQL\Data\db1_logfile2.ldf',
'100MB'
Server: Msg 9004, Level 23, State 1, Line 1
An error occurred while processing the log for database
'NestleDB_JP_CoreMart_V1'.
ALTER DATABASE nestledb_jp_coremart_v1 ADD LOG FILE(NAME = [logfile2],
FILENAME = 'D:\MSSQL\Data\db1_logfile2.ldf', SIZE = 100MB )
Connection Broken
So that didn't work so only way was to make space. So I made space on the
drive and ran the proc and restarted the lot. Nothing.
Thanks for helpHi,
If you have adequate space in hard disk Can you reset the status of the
database using sp_resetstatus system procedure and stop and start the SQL
server service and
see what happends. If the database is still marked suspect please check the
SQl server error log and post us the error.
Incase if the LDF is giving issues, you can start the database by emergency
mode. Update the status column of the suspect database in sysdatabases table
to 32768.
After that you can create a newe database and use DTS to transfer data and
other objects.
Thanks
Hari
SQL Server
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:3BAA266D-93F2-4268-B764-280409FD03E9@.microsoft.com...
> Hi
> My database went into suspect mode. I made some space on the drives after
> I
> was unable to add additional log files
> sp_add_log_file_recover_suspect_db nestledb_jp_coremart_v1, logfile2,
> 'D:\MSSQL\Data\db1_logfile2.ldf',
> '100MB'
> Server: Msg 9004, Level 23, State 1, Line 1
> An error occurred while processing the log for database
> 'NestleDB_JP_CoreMart_V1'.
> ALTER DATABASE nestledb_jp_coremart_v1 ADD LOG FILE(NAME = [logfile2],
> FILENAME = 'D:\MSSQL\Data\db1_logfile2.ldf', SIZE = 100MB )
> Connection Broken
> So that didn't work so only way was to make space. So I made space on the
> drive and ran the proc and restarted the lot. Nothing.
> Thanks for help
>|||The error log, seems there's a bit of a problem :)
I'll have to try this emergenvy startup.
Thanks for help so far
2005-04-01 16:16:24.21 server Microsoft SQL Server 2000 - 8.00.760
(Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2005-04-01 16:16:24.21 server Copyright (C) 1988-2002 Microsoft
Corporation.
2005-04-01 16:16:24.21 server All rights reserved.
2005-04-01 16:16:24.21 server Server Process ID is 1788.
2005-04-01 16:16:24.21 server Logging SQL Server messages in file
'e:\MSSQL\log\ERRORLOG'.
2005-04-01 16:16:24.22 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2005-04-01 16:16:24.30 server SQL Server configured for thread mode
processing.
2005-04-01 16:16:24.30 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2005-04-01 16:16:24.32 server Attempting to initialize Distributed
Transaction Coordinator.
2005-04-01 16:16:26.36 spid3 Starting up database 'master'.
2005-04-01 16:16:26.49 server Using 'SSNETLIB.DLL' version '8.0.760'.
2005-04-01 16:16:26.49 spid5 Starting up database 'model'.
2005-04-01 16:16:26.49 spid3 Server name is 'DEVINCI'.
2005-04-01 16:16:26.49 spid8 Starting up database 'msdb'.
2005-04-01 16:16:26.49 spid9 Starting up database 'pubs'.
2005-04-01 16:16:26.49 spid10 Starting up database 'Northwind'.
2005-04-01 16:16:26.49 spid11 Starting up database
'Nestle_JP_WorkspaceDB_V1'.
2005-04-01 16:16:26.49 spid13 Starting up database
'NestleDB_JP_WebMart_V1'.
2005-04-01 16:16:26.49 spid15 Starting up database
'NestleDB_JP_Update_V1_TN'.
2005-04-01 16:16:26.49 spid14 Starting up database
'NestleDB_JP_WebMart_V1_TN_V1'.
2005-04-01 16:16:26.49 spid16 Starting up database 'NestleDB_JP_Update_V1
'.
2005-04-01 16:16:26.49 spid17 Starting up database
'NestleDB_JP_CoreMart_V1'.
2005-04-01 16:16:26.49 spid12 Starting up database
'Nestle_JP_WorkspaceDB_V2'.
2005-04-01 16:16:26.55 spid12 Analysis of database
'Nestle_JP_WorkspaceDB_V2' (8) is 100% complete (approximately 0 more second
s)
2005-04-01 16:16:26.60 spid13 Analysis of database
'NestleDB_JP_WebMart_V1' (10) is 100% complete (approximately 0 more seconds
)
2005-04-01 16:16:26.60 spid16 Analysis of database
'NestleDB_JP_Update_V1' (17) is 100% complete (approximately 0 more seconds)
2005-04-01 16:16:26.61 spid5 Clearing tempdb database.
2005-04-01 16:16:26.63 spid11 Analysis of database
'Nestle_JP_WorkspaceDB_V1' (7) is 100% complete (approximately 0 more second
s)
2005-04-01 16:16:26.72 server SQL server listening on 192.33.20.218: 1433
.
2005-04-01 16:16:26.72 server SQL server listening on 192.168.234.235:
1433.
2005-04-01 16:16:26.72 server SQL server listening on 127.0.0.1: 1433.
2005-04-01 16:16:26.82 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2005-04-01 16:16:26.82 server SQL Server is ready for client connections
2005-04-01 16:16:27.03 spid5 Starting up database 'tempdb'.
2005-04-01 16:16:27.08 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
2005-04-01 16:16:29.47 spid15 Analysis of database
'NestleDB_JP_Update_V1_TN' (15) is 100% complete (approximately 0 more
seconds)
2005-04-01 16:16:32.13 spid17 Analysis of database
'NestleDB_JP_CoreMart_V1' (18) is 0% complete (approximately 56 more seconds
)
2005-04-01 16:16:33.46 spid17 Error: 9004, Severity: 23, State: 1
2005-04-01 16:16:33.46 spid17 An error occurred while processing the log
for database 'NestleDB_JP_CoreMart_V1'..
2005-04-01 16:16:33.47 spid17 Error: 3414, Severity: 21, State: 1
2005-04-01 16:16:33.47 spid17 Database 'NestleDB_JP_CoreMart_V1'
(database ID 18) could not recover. Contact Technical Support..
2005-04-01 16:16:33.49 spid3 Recovery complete.
2005-04-01 16:16:33.50 spid3 SQL global counter collection task is
created.
2005-04-01 16:16:34.10 spid51 Using 'xpsqlbot.dll' version '2000.80.194'
to execute extended stored procedure 'xp_qv'.
2005-04-01 16:16:36.36 spid1 Warning: unable to allocate 'min server
memory' of 3011MB.
"Hari Pra" wrote:

> Hi,
> If you have adequate space in hard disk Can you reset the status of the
> database using sp_resetstatus system procedure and stop and start the SQL
> server service and
> see what happends. If the database is still marked suspect please check th
e
> SQl server error log and post us the error.
> Incase if the LDF is giving issues, you can start the database by emergenc
y
> mode. Update the status column of the suspect database in sysdatabases tab
le
> to 32768.
> After that you can create a newe database and use DTS to transfer data and
> other objects.
>
> Thanks
> Hari
> SQL Server
> "Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
> news:3BAA266D-93F2-4268-B764-280409FD03E9@.microsoft.com...
>
>|||Mwuhahaha
I love SQL :P
So problem solved...
What was the problem ...
Some clever person set the transaction log size to a specific limit and "do
not grow automatically" thus even though I did make space it didn't help wit
h
the recovery since the file couldn't grow. I have no idea why I couldn't add
an additional log file though.
So as you said I changed it to emergency mode, fixed the automatically grow
log. Set status back to suspect and then did the restart and it recovered
correctly.
One thing that's not usefull is the fact that when you go into EM you can't
see filegroups and database setttings due to suspect mode. This is a pain
cause the moment I could see that the file didn't grow automatically I could
fix the problem.
Why do SQL disable the properties for viewing once it goes into suspect ?
How do these settings affect data ? assuming data integrity is the ONLY
reason why SQL change mode to suspect to start off with .
Thanks for help.
:)
"Mal" wrote:
> The error log, seems there's a bit of a problem :)
> I'll have to try this emergenvy startup.
> Thanks for help so far
> 2005-04-01 16:16:24.21 server Microsoft SQL Server 2000 - 8.00.760
> (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2005-04-01 16:16:24.21 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2005-04-01 16:16:24.21 server All rights reserved.
> 2005-04-01 16:16:24.21 server Server Process ID is 1788.
> 2005-04-01 16:16:24.21 server Logging SQL Server messages in file
> 'e:\MSSQL\log\ERRORLOG'.
> 2005-04-01 16:16:24.22 server SQL Server is starting at priority class
> 'normal'(4 CPUs detected).
> 2005-04-01 16:16:24.30 server SQL Server configured for thread mode
> processing.
> 2005-04-01 16:16:24.30 server Using dynamic lock allocation. [2500] Loc
k
> Blocks, [5000] Lock Owner Blocks.
> 2005-04-01 16:16:24.32 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2005-04-01 16:16:26.36 spid3 Starting up database 'master'.
> 2005-04-01 16:16:26.49 server Using 'SSNETLIB.DLL' version '8.0.760'.
> 2005-04-01 16:16:26.49 spid5 Starting up database 'model'.
> 2005-04-01 16:16:26.49 spid3 Server name is 'DEVINCI'.
> 2005-04-01 16:16:26.49 spid8 Starting up database 'msdb'.
> 2005-04-01 16:16:26.49 spid9 Starting up database 'pubs'.
> 2005-04-01 16:16:26.49 spid10 Starting up database 'Northwind'.
> 2005-04-01 16:16:26.49 spid11 Starting up database
> 'Nestle_JP_WorkspaceDB_V1'.
> 2005-04-01 16:16:26.49 spid13 Starting up database
> 'NestleDB_JP_WebMart_V1'.
> 2005-04-01 16:16:26.49 spid15 Starting up database
> 'NestleDB_JP_Update_V1_TN'.
> 2005-04-01 16:16:26.49 spid14 Starting up database
> 'NestleDB_JP_WebMart_V1_TN_V1'.
> 2005-04-01 16:16:26.49 spid16 Starting up database 'NestleDB_JP_Update_
V1'.
> 2005-04-01 16:16:26.49 spid17 Starting up database
> 'NestleDB_JP_CoreMart_V1'.
> 2005-04-01 16:16:26.49 spid12 Starting up database
> 'Nestle_JP_WorkspaceDB_V2'.
> 2005-04-01 16:16:26.55 spid12 Analysis of database
> 'Nestle_JP_WorkspaceDB_V2' (8) is 100% complete (approximately 0 more seco
nds)
> 2005-04-01 16:16:26.60 spid13 Analysis of database
> 'NestleDB_JP_WebMart_V1' (10) is 100% complete (approximately 0 more secon
ds)
> 2005-04-01 16:16:26.60 spid16 Analysis of database
> 'NestleDB_JP_Update_V1' (17) is 100% complete (approximately 0 more second
s)
> 2005-04-01 16:16:26.61 spid5 Clearing tempdb database.
> 2005-04-01 16:16:26.63 spid11 Analysis of database
> 'Nestle_JP_WorkspaceDB_V1' (7) is 100% complete (approximately 0 more seco
nds)
> 2005-04-01 16:16:26.72 server SQL server listening on 192.33.20.218: 14
33.
> 2005-04-01 16:16:26.72 server SQL server listening on 192.168.234.235:
> 1433.
> 2005-04-01 16:16:26.72 server SQL server listening on 127.0.0.1: 1433.
> 2005-04-01 16:16:26.82 server SQL server listening on TCP, Shared Memor
y,
> Named Pipes.
> 2005-04-01 16:16:26.82 server SQL Server is ready for client connection
s
> 2005-04-01 16:16:27.03 spid5 Starting up database 'tempdb'.
> 2005-04-01 16:16:27.08 spid5 Analysis of database 'tempdb' (2) is 100%
> complete (approximately 0 more seconds)
> 2005-04-01 16:16:29.47 spid15 Analysis of database
> 'NestleDB_JP_Update_V1_TN' (15) is 100% complete (approximately 0 more
> seconds)
> 2005-04-01 16:16:32.13 spid17 Analysis of database
> 'NestleDB_JP_CoreMart_V1' (18) is 0% complete (approximately 56 more secon
ds)
> 2005-04-01 16:16:33.46 spid17 Error: 9004, Severity: 23, State: 1
> 2005-04-01 16:16:33.46 spid17 An error occurred while processing the lo
g
> for database 'NestleDB_JP_CoreMart_V1'..
> 2005-04-01 16:16:33.47 spid17 Error: 3414, Severity: 21, State: 1
> 2005-04-01 16:16:33.47 spid17 Database 'NestleDB_JP_CoreMart_V1'
> (database ID 18) could not recover. Contact Technical Support..
> 2005-04-01 16:16:33.49 spid3 Recovery complete.
> 2005-04-01 16:16:33.50 spid3 SQL global counter collection task is
> created.
> 2005-04-01 16:16:34.10 spid51 Using 'xpsqlbot.dll' version '2000.80.194
'
> to execute extended stored procedure 'xp_qv'.
> 2005-04-01 16:16:36.36 spid1 Warning: unable to allocate 'min server
> memory' of 3011MB.
>
> "Hari Pra" wrote:
>