Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Suspect Mode Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:47 AM
Points: 4, Visits: 37
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
Posted Thursday, August 2, 2012 10:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:46 AM
Points: 360, Visits: 912
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
Posted Thursday, August 2, 2012 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:47 AM
Points: 4, Visits: 37
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
Posted Thursday, August 2, 2012 12:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:46 AM
Points: 360, Visits: 912
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
Posted Thursday, August 2, 2012 12:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:47 AM
Points: 4, Visits: 37
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
Posted Thursday, August 2, 2012 12:20 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:46 AM
Points: 360, Visits: 912
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
Posted Monday, August 6, 2012 12:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:47 AM
Points: 4, Visits: 37
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
Posted Monday, August 6, 2012 12:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:46 AM
Points: 360, Visits: 912
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
Posted Wednesday, August 8, 2012 12:45 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:45 PM
Points: 111, Visits: 1,212
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
Post #1341695
Posted Friday, August 10, 2012 5:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
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 »

Add to briefcase 12»»

Permissions Expand / Collapse