FAQ:- Troubleshooting SQL Server start up problems.

My SQL Server service is not started. What may be the possible scenarios?
Possible Scenarios

* Logon Failure
            * Problem with service account.
* 3417
            * Files are not present in the respective path or there are no  
               permissions on target folder where the files are not present.
* 17113
            * Master files are moved to different location, but not mentioned in       
               startup parameters.

* Service cannot be started in timely fashion
                        * Insufficient resources, try to stop some other instances and
                            start again.

How to find error?
            1. Using windows event log
                        * start --> run --> eventvwr
                        * System
                        * In the right side check for the errors
            2. Using SQL Server ErrorLog file
                        * Go to respective instance LOG folder and open ErrorLog in
                            notepad and check for the errors.

FAQ:- SQL Server database was gone into suspect mode. How to troubleshoot the problem?

Generally SQL Server database may go into suspect mode due to various reasons. Major reasons are
  • If the files of a database are damaged or corrupted due to disk related issues.
  • Restoration process was failed unexpectedly.
  • If there is disk I/O error for data or T.Log files.
  • If there are more than 1000 pages are corrupted in a database.
Scenario 1: If the data file was damaged (17204).

* Take T.Log backup
* Restore last Full backup and if there is any differential backup.
* Restore T.Log backup
* Database comes online

Scenario 2 : If the T.Log file was damaged (17207)
* Take any user defined db for example : MyDB
* Check the current location of files
sp_helpdb  MyDB
* Stop server
* Move the T.Log file into different folder
* Start server --> DB goes into suspect mode
  select databasepropertyex('mydb','status')

* Steps to Recover
--step1: Make the db into single user
Alter database mydb set Single_User

--step2: Set the db into emergency mode
Alter database mydb set Emergency

--step3: Run checkdb with required repair level

--step4: Set the db into multi user mode
Alter database mydb set Multi_User

FAQ:- If the Transaction Log file was full (9002). How to handle the scenario?


* If there are active transactions in the log file
* If there are long running queries
* In database mirroring if mirror server fails
* In replication if distributor fails
* If there is no disk space to grow the T.Log file


1. Check why log file cannot be reused using the following query.

select name, log_reuse_wait_desc from sys.databases where name='dbname'

2. Depending on 2nd column value we have to troubleshoot by using the following solutions.

• Backing up the log (In case of above scenario).
• Freeing disk space so that the log can automatically    grow.
• Moving the log file to a disk drive with sufficient     space.
• Increasing the size of a log file.
• Adding a log file on a different disk.
* Troubleshoot replication or mirroring issue

Sample scenario

We can check why the transaction log is full as follows.

We can take log backup to truncate the log file in the above scenario.

Scenario 2

Here transaction log file is growing due to replication failed. We have to troubleshoot replication issue.

Scenario: Troubleshooting Snapshot generation failure due to inactive publisher.

2015-06-05 14:26:50.11 Microsoft (R) SQL Server Snapshot Agent
2015-06-05 14:26:50.11 [Assembly Version =, File Version = 11.0.3000.0 ((SQL11_PCU_Main).121019-1325 )]
2015-06-05 14:26:50.11 Copyright (c) 2008 Microsoft Corporation.
2015-06-05 14:26:50.11 The timestamps prepended to the output lines are expressed in terms of UTC time.
2015-06-05 14:26:50.11 User-specified agent parameter values:
2015-06-05 14:26:50.11 --------------------------------------
2015-06-05 14:26:50.11 -Publisher RIFA-PC\TEST
2015-06-05 14:26:50.11 -PublisherDB Northwind
2015-06-05 14:26:50.11 -Publication NorthMP
2015-06-05 14:26:50.11 -ReplicationType 2
2015-06-05 14:26:50.11 -Distributor RIFA-PC\STANDBY
2015-06-05 14:26:50.11 -DistributorSecurityMode 1
2015-06-05 14:26:50.11 -XJOBID 0x66AA2AAE487FCC44AF6DA747A8386441
2015-06-05 14:26:50.11 --------------------------------------
2015-06-05 14:26:50.11 Connecting to Distributor 'RIFA-PC\STANDBY'
2015-06-05 14:26:50.29 The replication agent had encountered an exception.
2015-06-05 14:26:50.29 Source: Replication
2015-06-05 14:26:50.29 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentException
2015-06-05 14:26:50.29 Exception Message: The snapshot could not be generated because the publisher is inactive.
2015-06-05 14:26:50.29 Message Code: 54057
2015-06-05 14:26:50.29

--step1: Check the status by running command in distributor
--Active column set to 0
EXEC sp_helpdistpublisher
--step2: Run the command in distributor
sp_changedistpublisher 'RIFA-PC\TEST', 'active', 'true'
--After this check Active=1
--step3: Run Snapshot Agent

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent. * Error Logs are main source for troubleshooting SQL Server...

SQL Server DBA Training

SQL Server DBA Training
SQL Server DBA