My Path in SEQUEL!!: Database Suspect Mode.

Never -ever rebuild the transaction log file without knowing proper knowledge because as we know

Rebuilding an log is the last option when there is no good backup in your hand (see how the backup

Plays an very important role here-hence good backup strategy can save time & can save real time business.)( will explain on other post about how to rebuild the log).

I have seen many cases in my working environment and worked for resolving the issues.

Let discuss possible ways the databases goes in to suspect mode.

1) Improper shutdown of SQL server services ->for ex : when users are on server and performing some operations against user DB’s(like DML,DDL,DCL ) or Jobs are running, suddenly the SQL server services get’s restarts it may be either Human error or SQL server itself(like SQL server shutdown unexpectedly it has done 1 times –for this we have multiple solutions to check how ever this out of topic and I will explain this on other post).

2) When users are performing bulk operations and the transaction log file grown rapidly how ever

The transaction log file do not have space on the drive to grow further ->the reason why Iam telling is that I have seen one year back on one of the servers where users are fired Large queries against the database and the transaction log file has grown very huge size almost it reached to 50GB ,all the VLF’s are active status and since drive was running out of space user’s start getting the transaction log file was full backup the tran…… how ever when I see during that time the transactions (I,e spids which users executed queries) were in rolled back status.

But you should know that the rollback is an single thread processes and also to get rollback completely the transaction log file should have space in it ,but in our case no space in the log file & in the disk, so transaction log file marked as corruption –so finally the Database reflected to suspect mode.

3)Mount points or Drives are missing where the databases are resides ->if you have created the database on mount points & those mount points are missing when you have fired the query

Select * from sysaltfiles (SQL 2000) or sys.sysaltfiles(2005).

4)Yes Drive space also plays an very important role. That’s the reason you should have proper capcity planning( you should have proper report against your Database growth based on daily ,weekly or monthly.).

5)I have seen another case where the database resides on one node(I,e active-active cluster)

I,e on one node one instance and another node has another instances- when you are doing failover activity during SQL patch or when you are supporting to OS team when they are performing OS hotfix/service packs.

When failover performing there is no problem when you are doing failback you may see database go suspect mode(here I said only as an example which I have seen and worked-there may be other reason).

>>I will explain in the other post what cause the issue and resolving the issue.

6) During SQL server services start up if any of the database files locked by another processes like

OS incremental backup or any scan(so that’s the reason we always exclude DB files from incremental backup or from Scan(but proper plan can help here.))

7)No proper backup strategy for t-log backups(full/bulk logged recovery model) for the databases.

Here it is very important you should have enough information about user’s transaction activity during peak/non –peak hours.

8) Hardware failures.

9) I/O subsystem errors.

10) OS corruption.

11) Do not continue running the uncommitted transactions forcefully.

12) You have added one additional file to the one file group and forcefully trying to remove

From GUI.

13) Renaming the Physical files.

14) Consistency errors in the Databases and not taken care during initial stage.

15)One interesting information in SQL server Enterprise Manager( sql server 2000).

We have one of the servers, usually during non business hours it does the Cold backup(using scripts I,e using XP_CMD)

I,e first it shutdowns the SQL server services and copies the physical files from one drive to another once done it will start the SQL server services.

So in our case every day one of the database was going to suspect mode after SQL server services starts->worked with OS team,Backup team to see any DB files will lock during the SQL server services starts or in between during cold backup –how ever we didn’t see any issues but

Database was going to suspect mode –we didn’t find any errors in SQL server logs but in the event logs was showing one of my colleague Id that sql server services started by his/her id.

But my colleague was not aware of that cold backup much, hence durning the cold backup it was in progress… he used to connect to SQL server via SQL server enterprise manager.

Below is the interesting part in SQL server 2000.

We know how to map other server using SQL server enterprise manager I,e

Connect to the SQL server enterprise Manager ->right click select new SQL server registration->type the server name ->click on OK.

But in our case when he was using to connect to SQL server Enterprise manager it has one option in SQL server registration as mentioned below

See above encircled one “when ever the user trying to connect SQL server using this option

Eventhough SQL server services are stopped it will start –see below how it is