Suspect Mode

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » Suspect Mode

12 posts, Page 1 of 2

1 2 »»

Suspect Mode

Rate Topic

Display Mode

Topic Options

Author

Message

Usual (Suspect)

Posted Thursday, August 02, 2012 10:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 2:50 PM
Points: 4, Visits: 35

Good morning,

It is a bit of a long story but i'm recently hired and have had a sql server that I did not know existed dropped into my lap because a third party software that uses the db would not let the user log in. I've learned that the reason is because one of the dbs is in suspect mode.

A little more background, It was installed by an outside agency that has neither the knowledge or the desire to repair it at all, their solution is to start from scratch. Prior to this incident, the ties with this company had been severed because of this type of workmanship. There have been no backups made in over a year because the tape drive attached didn't even have a driver installed so i'm hoping someone can help me out.

I must say that I was brought on board for networking and as/400 administration and know little about sql, so please forgive what is going to be obvious ignorance. The db is small and is running a security system for mag locks, door nodes, etc. The door nodes and locks operate on their last standing order, so right now the db being unavailable hasn't caused any problems for day to day use, which has afforded me time to lurk these forums and gain a little knowledge on the subject.

This is the error the user received when trying to start up their security program:

ODBC;Driver={SQL Server};DATABASE=multiMAX;SERVER=HP12265206811;Trusted_Connection=Yes;: 
Cannot open database requested in login 'multiMAX'. Login fails. 
State:37000,Native:4060,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]

From lurking I've learned that the first thing that always seems to be asked is what is in the error log. Well the error log is likely no help because the user restarted a bunch of times, so this is all that is in the 8 or so SQL error logs (multiMAX is the db in question):

2012-08-01 14:53:44.74 server    Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
	May  3 2005 23:18:38 
	Copyright (c) 1988-2003 Microsoft Corporation
	Desktop Engine on Windows NT 6.1 (Build 7601: Service Pack 1)

2012-08-01 14:53:44.74 server    Copyright (C) 1988-2002 Microsoft Corporation.
2012-08-01 14:53:44.74 server    All rights reserved.
2012-08-01 14:53:44.74 server    Server Process ID is 1724.
2012-08-01 14:53:44.74 server    Logging SQL Server messages in file 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2012-08-01 14:53:44.83 server    SQL Server is starting at priority class 'normal'(8 CPUs detected).
2012-08-01 14:53:46.06 server    SQL Server configured for thread mode processing.
2012-08-01 14:53:46.06 server    Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2012-08-01 14:53:46.15 spid3     Starting up database 'master'.
2012-08-01 14:53:46.82 spid5     Starting up database 'model'.
2012-08-01 14:53:46.82 server    Using 'SSNETLIB.DLL' version '8.0.2039'.
2012-08-01 14:53:46.82 spid3     Server name is 'HP12265206811'.
2012-08-01 14:53:46.82 spid3     Skipping startup of clean database id 4
2012-08-01 14:53:46.82 spid3     Skipping startup of clean database id 6
2012-08-01 14:53:46.82 spid3     Skipping startup of clean database id 7
2012-08-01 14:53:46.82 spid3     Skipping startup of clean database id 8
2012-08-01 14:53:46.82 spid3     Skipping startup of clean database id 9
2012-08-01 14:53:46.82 spid3     Skipping startup of clean database id 10
2012-08-01 14:53:46.82 spid3     Skipping startup of clean database id 11
2012-08-01 14:53:46.82 spid3     Skipping startup of clean database id 12
2012-08-01 14:53:46.82 spid3     Starting up database 'multiMAX'.
2012-08-01 14:53:46.84 server    SQL server listening on 192.168.100.21: 1433.
2012-08-01 14:53:46.84 server    SQL server listening on 127.0.0.1: 1433.
2012-08-01 14:53:46.90 spid5     Clearing tempdb database.
2012-08-01 14:53:46.99 spid3     Bypassing recovery for database 'multiMAX' because it is marked SUSPECT.
2012-08-01 14:53:47.10 server    SQL server listening on TCP, Shared Memory, Named Pipes.
2012-08-01 14:53:47.10 server    SQL Server is ready for client connections
2012-08-01 14:53:47.24 spid5     Starting up database 'tempdb'.
2012-08-01 14:53:47.28 spid3     Recovery complete.
2012-08-01 14:53:47.28 spid3     SQL global counter collection task is created.
2012-08-01 14:53:47.28 spid3     Launched startup procedure 'xp_g4StartServices'
2012-08-01 14:53:47.44 spid51    Using 'xplog70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_cmdshell'.
2012-08-01 14:53:48.98 logon     Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection: Trusted.
2012-08-01 14:53:48.98 spid52    Starting up database 'msdb'.
2012-08-01 14:53:49.65 spid52    Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'.
2012-08-01 14:53:52.75 logon     Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection: Trusted.
2012-08-01 14:53:53.31 logon     Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection: Trusted.
2012-08-01 14:53:54.03 spid51    Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_regread'.
2012-08-01 14:53:54.37 logon     Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection: Trusted.
2012-08-01 14:53:54.37 logon     Login failed for user 'NT AUTHORITY\SYSTEM'.

I may be wrong but I see nothing here besides a message saying the db is in suspect mode. But like I said i know little about sql someone here might see something I don't.

From here I checked the event viewer and see thousands of identical entries starting on 7/21/12:

- System 
  - Provider 
   [ Name]  MultimaxTxnService 
   - EventID 106 
   [ Qualifiers]  0 
   Level 3 
   Task 0 
   Keywords 0x80000000000000 
  - TimeCreated 
   [ SystemTime]  2012-07-21T01:37:46.000000000Z 
   EventRecordID 68340 
   Channel Application 
   Computer HP12265206811 
   Security 
- EventData 
   No more threads can be created in the system.

There is a plenty of free space on the drive. I'm not 100% sure this is related because mutliMAXTxn is another related db on this system that is not in suspect. But I posted this here anyways in case it helped.

According to the user, on 7/31 the user was viewing an IP camera on this system in a software application that does not use this db. The application locked the computer, the user powered down and back up and that's when they received the error message about login failed.

Now in making this post I discovered a BACKUP folder in the sql program folder. Not sure if this is an actual usable backup or not, the last modified date of the mutliMAX.bak is 07/31/12 at 2:00am, about 6 hours before the computer locked up. Again, I know next to nothing about sql so if it is usable I don't know the first thing about restoring it.

Thank you in advance for any help.

Post #1339328

Scott D. Jacobson

Posted Thursday, August 02, 2012 10:10 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:17 PM
Points: 335, Visits: 841

Run DBCC CHECKDB WITH ALL_ERRORMSGS.

What errors does it report?

*** Do NOT use the repair options until we know what the errors are and if they can be fixed ***

Documentation on DBCC CHECKDB syntax.

Post #1339336

Usual (Suspect)

Posted Thursday, August 02, 2012 12:06 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 2:50 PM
Points: 4, Visits: 35

Thank you for the reply! Here is the result:

DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 1292 rows in 22 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 100 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 4971 rows in 77 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2257 rows in 955 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 799 rows in 4 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 17 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 5511 rows in 27 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysdatabases'.
There are 12 rows in 1 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 8 rows in 1 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 13 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 3861 rows in 167 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 38 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 1 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 33 rows in 3 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 114 rows in 33 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 34 rows in 6 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 731 rows in 8 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object 'spt_fallback_usg'.
DBCC results for 'spt_provider_types'.
There are 25 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'spt_datatype_info'.
There are 36 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Hopefully its good news.

Thanks again

Post #1339415

Scott D. Jacobson

Posted Thursday, August 02, 2012 12:09 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:17 PM
Points: 335, Visits: 841

You ran DBCC CHECKDB on master. You need to run it on the database that is reporting as Suspect. Like so:

DBCC CHECKDB ('MyDBName') With ALL_ERRORMSGS

Post #1339417

Usual (Suspect)

Posted Thursday, August 02, 2012 12:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 2:50 PM
Points: 4, Visits: 35

I tried that but it cannot be run on a db that is in suspect mode, and gave me:
Error 926 Level 14 State 1 Line 1

Post #1339421

Scott D. Jacobson

Posted Thursday, August 02, 2012 12:20 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:17 PM
Points: 335, Visits: 841

1. EXEC sp_resetstatus 'DBName'

GO
ALTER DATABASE DBName SET EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DBName')

2. ALTER DATABASE DBName SET MULTI_USER

If you can't do that you're probably completely hosed. It's time to restore your last known good backup.

Post #1339430

Usual (Suspect)

Posted Monday, August 06, 2012 12:30 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 2:50 PM
Points: 4, Visits: 35

Thanks for the reply.

Before I try this, however, I don't think that

ALTER DATABASE DBName SET EMERGENCY

Is valid for 2000, from what I have read around the forums.

Post #1340824

Scott D. Jacobson

Posted Monday, August 06, 2012 12:33 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:17 PM
Points: 335, Visits: 841

Oops, my mistake for not seeing this was the 2000 forum (I was just browsing by active posts and didn't notice).

Time to restore from last known good backup.

Post #1340825

prettsons

Posted Wednesday, August 08, 2012 12:45 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 99, Visits: 1,031

You should restore your database from last good backup. Also check this article: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
This article is one of the best article which covers all recommended actions for corrupt or suspect databases.
If still you are unable to repair your database then you should try sql repair software. Search on Google about these software: https://www.google.com/#hl=en&output=search&sclient=psy-ab&q=sql+repair+software

SQL Database Recovery Expert Smile

Post #1341695

GilaMonster

Posted Friday, August 10, 2012 5:25 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 37,687, Visits: 29,946

Scott D. Jacobson (8/6/2012)

Time to restore from last known good backup.

Exactly this.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1343304

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions