Troubleshooting

FAQ:- Troubleshooting SQL Server start up problems.

Scenario

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)
Practice:
* 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
DBCC CheckDB ('mydb', REPAIR_ALLOW_DATA_LOSS)

--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?

Scenarios

* 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

Troubleshooting

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.

Error:

Message

2015-06-05 14:26:50.11 Microsoft (R) SQL Server Snapshot Agent

2015-06-05 14:26:50.11 [Assembly Version = 11.0.0.0, 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