How to repair a Suspect Database in SQL Server

By: Editor
March 25, 2010

Page: 1/2

Introduction

There can be a possibility that when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. During such scenarios, you will not be able to connect to the database. In this article we will go through the steps which you need to follow to recovery a database which is marked as SUSPECT. The steps mentioned in this article works on SQL Server 2005 and higher versions.

Some of the reasons why an SQL Server database can be marked as SUSPECT

1. Database could have been corrupted.
2. There is not enough space available for the SQL Server to recover the database during startup.
3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.
4. Database files are being held by operating system, third party backup software etc.
5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.

Due to a hardware failure one of our database namely BPO was marked SUSPECT when the SQL Server came back online. Already due to the hardware failure we had downtime for more than two hours and adding to that when the server came back online our mostly critical database was marked as SUSPECT.

Steps to Recover a Database Marked as SUSPECT

1. Execute the below mentioned TSQL code to identify all the databases which are marked as SUSPECT.
USE master
GO

SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO

 

2. Open the latest SQL Server Error Log and check for errors logged for the database which is marked as suspect. You can open SQL Server Error Log by expanding Management Node à SQL Server Error Logs. In my server I could find below mentioned entries in SQL Server Error Logs.
Sample Error Messages within SQL Server Error Log when database is marked as SUSPECT

Starting up database 'BPO'.
Error: 9003, Severity: 20, State: 9.

The log scan number (189624:16:2) passed to log scan in database 'BPO' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Error: 3414, Severity: 21, State: 1.

An error occurred during recovery, preventing the database 'BPO' (database ID 10) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

CHECKDB for database 'BPO' finished without errors on 2009-12-15 11:30:28.320 (local time). This is an informational message only; no user action is required.

3. When a database is in SUSPECT mode you will not be able to get connected to the database. Hence you need to bring the database first in EMERGENCY mode to repair the database. Execute the below mentioned TSQL code to bring the database in EMERGENCY mode.

USE master
GO 

ALTER DATABASE BPO SET EMERGENCY
GO

Once the database is in EMERGENCY mode you will be able to query the database.

4. Execute the DBCC CHECKDB command which will check the logical and physical integrity of all the objects within the specified database.

DBCC CHECKDB (BPO)
GO

5. Next step will be to bring the user database in SINGLE_USER mode by executing the below mentioned TSQL code.

ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

6. Once the database is in SINGLE_USER mode execute the below TSQL code to repair the database. When you repair your database using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command there can be some loss of data. Once the database is successfully repaired using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command then there is no way to go back to the previous state.

DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS)
GO

7. Finally, execute the below mentioned TSQL command to allow MULTI_USER access to the database.

ALTER DATABASE BPO SET MULTI_USER
GO

Conclusion

In this article you have seen how you can recover a database which is marked SUSPECT.

 Taken from : http://www.mytechmantra.com/LearnSQLServer/Repair_Suspect_Database_P1.html

How to Manually Repair Windows 7 Boot Loader Problems

If you’re having boot problems on your Windows PC, it’s often helpful to repair the MBR (Master Boot Record) to restore the Windows 7 boot loader—and you can do it easily from the Windows installation disc.
This is generally most useful if you’ve broken something and there’s a boot loader error, or if you have made the mistake of installing an older version of Windows on the same PC that already has Windows 7 which wipes out the boot loader.
Note: If your PC starts booting into Windows but fails, you should probably try using Safe Mode instead.

Boot From the Windows Install Disc

The first thing you’ll need to do is boot off the install disc, and then click through until you see the “Repair your computer” link in the lower left-hand corner.

You’ll need to choose the correct installation of Windows and then click the Next button.

And then you’ll get to the System Recovery Options screen, where you can get to the Command Prompt.

Repairing the Master Boot Record

If you want to restore the master boot record, you can simply type in the following command:

bootrec /fixmbr

You can also write a new boot sector onto the system partition with this command (which is often more useful):

bootrec /fixboot

And of course, if you just use bootrec /? you’ll be able to see all the options.

This is the same way that we fixed the “BOOTMGR is missing” error when trying to boot up Windows 7 or Vista.

Replacing the Windows XP Bootloader with Windows 7

If you’ve managed to install XP on the same PC that you already had Windows 7 on, you’ll noticed that you can’t boot into Windows 7 anymore. You can use this command to fix that and restore the Windows 7 bootloader:

bootsect /nt60 all

Depending on the partition that you’ve installed, you might need to substitute the drive letter instead of “all”.

Note: if you want to restore Windows XP back to the menu, you can open up a command prompt in Windows 7 and run this command:

bcdedit /create {ntldr} -d “Windows XP”

Using the Automated Startup Repair

Of course, all this command-line stuff is probably not necessary in most cases. You can usually just use the Startup Repair option from the Recovery menu.

 It’ll check for problems and probably fix them. If not, then you can always use the command prompt.

Taken from : http://www.howtogeek.com/howto/32523/how-to-manually-repair-windows-7-boot-loader-problems/

How To Remove Windows XP Genuine Advantage Notification

1. First – click Start then Run and type “regedit” without quotes and press Enter
2. Go to My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WindowsNT\CurrentVersion\Winlogon\Notify. In this location delete the folder “WgaLogon” and all content with name WgaLogon. If there is no WgaLogon folder continue with next steps.
3. Restart your computer. After restart WGA will not be active and you can remove it completely following the next steps:
4. Set your folders options to show hidden and protected Windows files – go to My Computer/Tools/Folder Options and then choose View. From the View tab choose “Show hidden files and folders” and uncheck “Hide protected operating system files” and click OK
5. Now, Go to folder Windows\System32\dllcache, find and delete “WgaTray.exe”. Also, check for “WgaTray.exe” in Windows\System32\, and if you find it delete it, and search for “Wga*.*” with Start-Search-Allfiles and folders and delete everything that starts with “Wga” (Wgatray.exe; Wgalogon.dll, Wgasetup; etc..)
6. Restart your computer again and live happy and free.

Taken from : http://www.detector-pro.com/2009/03/how-to-remove-windows-xp-genuine.htmlhttp://www.detector-pro.com/2009/03/how-to-remove-windows-xp-genuine.html

How To Move Filter Rule Position In Mikrotik

Sometimes if you create a new filter rule, the position will be at the bottom, and you must drag the rule to top. There is a quick way..

open new terminal, and type :
> Ip firewall filter moves 445 destination = 50

445 = rule you just created
50 = position desired rule

or if you make a rule with the terminal and the rule is to be positioned to a specific sequence of how to add "place-before = (rule number)"

example:
> Ip firewall filter add chain = forward dst-address-list = Server_Facebook_Friendster time = 08:00 to 12:00, mon action = drop place-before = 0