Edmark Reading Program Software Level 1' title='Edmark Reading Program Software Level 1' />SQL Server SUSPECT databaseCheck out my Pluralsight online training course SQL Server Detecting and Correcting Database Corruption. This is a post Ive been trying to get to since I started blogging a couple of years ago how to re attach a detached SUSPECT database. This is a pretty common scenario I see on the forums a database goes SUSPECT so the DBA tries to detachattach, which fails. I wrote a demo for my corruption session at Tech. Ed this year that shows how to create a SUSPECT database with a hex editor, then detaches it and shows how to re attach and fix it. Edmark Reading Program Software Level 1' title='Edmark Reading Program Software Level 1' />Lesson Plans All Lessons QueTtiempo Hace All Authored by Rosalind Mathews. Subjects Foreign Language Grade 3 Grade 5 Description Students complete a. New WebBased Version is NOW AVAILABLE Click here for more information and to order. Features Management tools allow teachers to individualize the program. With over 300 Maker Exhibits, Talks, Workshops, Nerdy Derby Power Racing Maker Faire Orlando has Something for Everyone Buy Online and Save System definition, an assemblage or combination of things or parts forming a complex or unitary whole a mountain system a railroad system. How To Games In Pc. See more. How to reattach and repair a SUSPECT database using EMERGENCY mode. Its going to be a long blog post, but bear with me you never know when youll need to know how to recover from this. Creating a SUSPECT Database. First off Im going to create a simple database to use, called Demo. Suspect with a table and some random data. CREATE DATABASE Demo. Suspect. USE Demo. Suspect. CREATE TABLE Employees. CONTENTS CHAPTER ONE. INTRODUCING MARKETING. CHAPTER THREE MARKETING RESEARCH AN AID TO DECISION MAKING 53. Introduction 2. First. Name VARCHAR 2. Last. Name VARCHAR 2. Yearly. Bonus INT. INSERT INTO Employees VALUES Paul, Randal, 1. INSERT INTO Employees VALUES Kimberly, Tripp, 1. Now Ill perform an update in an explicit transaction and force it to be written out to disk with a CHECKPOINT. Ive accidentally deleted Kimberlys bonus Simulate an in flight transaction. Employees. Yearly. Bonus 0. Last. Name Tripp. Force the update to disk. Then in another window, Ill simulate a crash using. SHUTDOWN WITH NOWAIT. Now that SQL Server is shutdown, Im going to simulate an IO failure that corrupts the log file. Im going to use a hex editor to do this my editor of choice is the immensely popular and useful XVI3. Christian Maas. I opened the log file, filled the first section with zeroes, and then saved it again. See the screenshot below. As a small note of warning, this hex editor will truncate files that are over 2. GB. Used the Hx. D editor instead for larger files. When I start up SQL Server again, it will try to run recovery on the Demo. Suspect database and fail. This will put the database into the SUSPECT state. So I restarted SQL Server, lets try getting in to the Demo. Suspect database. USE Demo. Suspect. Msg 9. 45, Level 1. State 2, Line 1. Database Demo. Suspect cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Now lets check the database status. SELECT DATABASEPROPERTYEX NDemo. Suspect, NSTATUS AS NStatus. At this point, the correct procedure is to restore from backups. If there are no backups available, then the next best thing is to get the database into EMERGENCY mode and extract as much data as possible, or run EMERGENCY mode repair. However, Im going to try the detachattach route instead. Detaching the Database. On SQL Server 2. 00. SUSPECT database using spdetachdb, but on later versions SQL Server wont let you do this. EXEC spdetachdb NDemo. Suspect. Msg 3. 70. Level 1. 6, State 2, Line 1. Cannot detach a suspect or recovery pending database. It must be repaired or dropped. I was sopleased when I saw this change was made. Im going to have to set the database offline to release the NTFS locks on the files, copy the files to somewhere safe, then drop the database and delete the files. Its no longer possible to accidentally detach a SUSPECT database. Not allowed on 2. ALTER DATABASE Demo. Suspect SET OFFLINE. COPY THE FILES. Copy. DROP DATABASE Demo. Suspect. Now the Demo. Suspect is really detached from SQL Server, and now the fun starts, which is why Im sure many of you are reading this post. Re attaching a SUSPECT Database. Lets try the obvious spattachdb. EXEC spattachdb dbname NDemo. Suspect. filename. NC Program FilesMicrosoft SQL ServerMSSQL1. MSSQLSERVERMSSQLDATADemo. Suspect. mdf. filename. NC Program FilesMicrosoft SQL ServerMSSQL1. MSSQLSERVERMSSQLDATADemo. Suspectlog. ldf. Msg 5. Level 1. State 1. Line 1. The header for file C Program FilesMicrosoft SQL ServerMSSQL1. MSSQLSERVERMSSQLDATADemo. Suspectlog. ldf is not a valid database file header. The Page. Audit property is incorrect. Hmm. How about using the ATTACHREBUILDLOG option on CREATE DATABASEThat should create a new log file for me. CREATE DATABASE Demo. Suspect ON. NAME NDemo. Suspect. FILENAME NC Program FilesMicrosoft SQL ServerMSSQL1. MSSQLSERVERMSSQLDATADemo. Suspect. mdf. FOR ATTACHREBUILDLOG. Depending on the version of SQL Server youre using, youll see either. Msg 5. 17. 2, Level 1. State 1. 5, Line 1. The header for file C Program FilesMicrosoft SQL ServerMSSQL. MSSQLDataDemo. SuspectLOG. The Page. Audit property is incorrect. File activation failure. The physical file name C Program FilesMicrosoft SQL ServerMSSQL. MSSQLDataDemo. SuspectLOG. The log cannot be rebuilt because the database was not cleanly shut down. Msg 1. 81. 3, Level 1. State 2, Line 1. Could not open new database Demo. Suspect. CREATE DATABASE is aborted. File activation failure. The physical file name C Program FilesMicrosoft SQL ServerMSSQL1. MSSQLSERVERMSSQLDATADemo. Suspectlog. LDF may be incorrect. Msg 1. 81. 3, Level 1. State 2, Line 1. Could not open new database Demo. Suspect. CREATE DATABASE is aborted. Msg 5. 24. 3, Level 2. State 8, Line 1. An inconsistency was detected during an internal operation. Please contact technical support. Hmm. The database knows that there was an active transaction. Using the ATTACHREBUILDLOG command only works if the database was cleanly shut down and the log is missing. Even removing the damaged log file makes no difference. Basically the problem is that the database wasnt cleanly shutdown, which means that recovery HAS to run and complete before the database can be attached again. Given that our log file is corrupt, thats impossible. So, never detach a suspect database. The only way to get the database back into SQL Server is to use a hack. Im going to create a new dummy database with the exact same file layout as the detached database. Then Im going to set the dummy database offline, swap in the corrupt database files, and bring the database online again. If all goes well, the corrupt database will be attached again. The one major downside of this is that if the SQL Server instance doesnt have instant initialization enabled see How to tell if you have instant initialization enabled, then creating the dummy database could take a long time if the data files are very big. This means that your application is offline while the files are created and zerod out. Youll need to delete the existing files. Before doing this you want to make absolutely sure youve got multiple copies of the corrupt database files just in case. After deleting the files, I can create my dummy database and set it offline. CREATE DATABASE Demo. Suspect. Check the files are there. ALTER DATABASE Demo. Suspect SET OFFLINE. If you forget to delete the existing corrupt files first, youll get the following error. Msg 5. 17. 0, Level 1. State 1, Line 1. Cannot create file C Program FilesMicrosoft SQL ServerMSSQL1. MSSQLSERVERMSSQLDATADemo. Suspect. mdf because it already exists. Change the file path or the file name, and retry the operation. Msg 1. 80. 2, Level 1. State 4, Line 1. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Now Ill delete the file created for the dummy database, copy back in the corrupt database files, and bring the database online, checking its state. ALTER DATABASE Demo.