InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 18:02:13 GMT20http://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxDepends on the results of the checkDB, but the best thing would still be to restore from a clean backup. Emergency mode repair is a last resort for when there is no backup.Wed, 15 Aug 2012 01:42:29 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspx[quote][b]Scott D. Jacobson (8/2/2012)[/b][hr]1. EXEC sp_resetstatus 'DBName'GOALTER DATABASE DBName SET EMERGENCYDBCC checkdb('DBname')ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC CheckDB ('DBName')2. ALTER DATABASE DBName SET MULTI_USERIf you can't do that you're probably completely hosed. It's time to restore your last known good backup.[/quote]ScottJust curious ...If the db was actually 2005 or up, what your advice was going t be after running the DBCC ?Tue, 14 Aug 2012 18:29:09 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspx[quote][b]Scott D. Jacobson (8/6/2012)[/b][hr]Time to restore from last known good backup.[/quote]Exactly this.Fri, 10 Aug 2012 05:25:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxYou should restore your database from last good backup. Also check this article: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.aspThis 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: [url=https://www.google.com/#hl=en&output=search&sclient=psy-ab&q=sql+repair+software]https://www.google.com/#hl=en&output=search&sclient=psy-ab&q=sql+repair+software[/url]Wed, 08 Aug 2012 00:45:09 GMTprettsonshttp://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxOops, 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.Mon, 06 Aug 2012 12:33:25 GMTScott D. Jacobsonhttp://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxThanks for the reply.Before I try this, however, I don't think thatALTER DATABASE DBName SET EMERGENCYIs valid for 2000, from what I have read around the forums.Mon, 06 Aug 2012 12:30:56 GMTUsual (Suspect)http://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspx1. EXEC sp_resetstatus 'DBName'GOALTER DATABASE DBName SET EMERGENCYDBCC checkdb('DBname')ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC CheckDB ('DBName')2. ALTER DATABASE DBName SET MULTI_USERIf you can't do that you're probably completely hosed. It's time to restore your last known good backup.Thu, 02 Aug 2012 12:20:48 GMTScott D. Jacobsonhttp://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxI 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 1Thu, 02 Aug 2012 12:11:59 GMTUsual (Suspect)http://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxYou 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_ERRORMSGSThu, 02 Aug 2012 12:09:12 GMTScott D. Jacobsonhttp://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxThank you for the reply! Here is the result:[code="other"]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.[/code]Hopefully its good news.Thanks againThu, 02 Aug 2012 12:06:33 GMTUsual (Suspect)http://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxRun 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 [url=http://msdn.microsoft.com/en-us/library/ms176064.aspx]DBCC CHECKDB[/url] syntax.Thu, 02 Aug 2012 10:10:02 GMTScott D. Jacobsonhttp://www.sqlservercentral.com/Forums/Topic1339328-265-1.aspxGood 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:[code="plain"]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] [/code]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):[code="plain"]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 42012-08-01 14:53:46.82 spid3 Skipping startup of clean database id 62012-08-01 14:53:46.82 spid3 Skipping startup of clean database id 72012-08-01 14:53:46.82 spid3 Skipping startup of clean database id 82012-08-01 14:53:46.82 spid3 Skipping startup of clean database id 92012-08-01 14:53:46.82 spid3 Skipping startup of clean database id 102012-08-01 14:53:46.82 spid3 Skipping startup of clean database id 112012-08-01 14:53:46.82 spid3 Skipping startup of clean database id 122012-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 connections2012-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'.[/code]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:[code="plain"]- 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. [/code]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.[b]Now in making this post I discovered a BACKUP folder in the sql program folder.[/b] 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.Thu, 02 Aug 2012 10:03:50 GMTUsual (Suspect)