SQL Server Database in Suspect Mode!!!!

Hey Everyone,

What if you suddenly get login failiures to a database and when u check it says the database is in SUSPECT mode??

As per msdn, suspect mode is 

At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.

If you have a huge database which demands high up time, DBCC Checkdb to identify the root cause may not be the best solution. DBCC CheckDB can be time consuming.

--to check the status of checkdb

select dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
,percent_complete,blocking_session_id,* from
sys.dm_exec_requests where session_id=sessionID_goes_here
or blocking_session_id=sessionID_goes_here

--to identify any memory dumps created
SELECT top 2 *
FROM sys.dm_server_memory_dumps
ORDER BY creation_time DESC

If you have a smaller database you could follow below steps to recovery your database.

1. EXEC sp_resetstatus 'DBName'
2. ALTER DATABASE DBName SET EMERGENCY
3. DBCC checkdb('DBName')
4. If you get errors from checkdb=> ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5. DBCC CheckDB ('DBName ', REPAIR_ALLOW_DATA_LOSS)
6. ALTER DATABASE [DBName]  SET MULTI_USER

However, If your database is terabytes in size and unable to run dbcc checkdb, you could use dbcc checktable option on the suspicious tables to isolate any corruption.But the drawback is in EMERGANCY mode you are unable to execute DBCC Checktable ('tableName', REPAIR_ALLOW_DATA_LOSS).

We came across a simmilar situation recently.


Point in time recovery was useless since the corruption comes with the log backups, you need to identify the point before the corruption and restore until then to solve the issue which is not practical and can cause huge data loss.


Our approach which was suggested by microsoft was to rebuild the log file at the cost of losing data in the transaction log.

Simply you have to detach the database and attach it without the logfile. A new log file will be created and mapped to the database.

This way you can get the database online.

Remember to run a DBCC CHECKDB followed by the log rebuild and repair any inconsistancies.

Cheers

Dropping a user from All SQL server databases

I Know its a simple thing, It is None other than sp_MSforeachdb. That is all you need to execute.

Below is a sample code:

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC  sp_dropuser ''UserNameGoesHere'''

This will throw error messages for databases where user does not exist.

I was just too lazy to write the code to avoid the error since, it was doing the task.

Cheers.

Troubleshooting schedules after SSRS migration

You might come across situations where you can't create new schedules from your report manager.

Common symptoms are when you create new schedules it goes to an error page.

In order to trouble shoot, launch the report manager web page via your SSRS box. It throws the acutal error message.

One of the Common messages is listed below.

Reporting Services error: “RSInternalError: Invalid object name 'ReportServerTempDB.dbo.PersistedStream'."

Check the references to specified database in the error messages. If the report server does not have the database mentioned in the error message it is still refering to databases prior to migration.

In other words check if any of the stored procedures or triggers are reffering to a non-existant database. 

Alter the triggers and storedprocedures to match with the new report server temp database.

Your should be able to sucessfully create schedules then.

Cheers