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:
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.  Lock Blocks,  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:
[ Name] MultimaxTxnService
- EventID 106
[ Qualifiers] 0
[ SystemTime] 2012-07-21T01:37:46.000000000Z
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.