Trouble Shooting SQL Server

Published on October 21 2015 by LakshmiSaahul,Dhana Royal

1. Unable to start SQL Server, what is the reason, how to find issue?

Check Physical Error Log path : Open Configuration Manager > RC properties of Instance >Advanced check Startup Parameter

2. Log file is corrupted (there is no backups, how u will get database?

SELECT name, state, state_desc FROM sys.databases WHERE name='ABCD'

Output for state_desc was RECOVERY_PENDING.

I instructed him to set database to the emergency mode.

ALTER DATABASE ABCD SET EMERGENCY

Then, set the database to single use mode and started the rollback.

ALTER DATABASE ABCD SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Then rebuilt the log by ,

DBCC CHECKDB('ABCD',REPAIR_ALLOW_DATA_LOSS)

Then executed the initial statement and verified that database is ONLINE.

Below execution is to make that every use can log into the database.

ALTER DATABASE ABCD SET MULTI_USER

With this, users were able to login to the database without any issues.

3. How to rebuild Log File?

A:For SQL Server 2005 and 2008:

Here are the steps:

In the management studio query window.

1. Get the DBid for the database to which you are going to rebuild the log.

Select * from sys.sysdatabases

2. Pass the dbid and get logical name and the physical name from the sysaltfiles. In my case I have randomly choosen ‘8’.

select name,filename from sys.sysaltfiles where dbid=’8′

3. To rebuild we need to put the database in emergency.

alter database mydatabase set emergency

4. To rebuild we need to put the database in single user so that no one access it.

alter database mydatabase set single_user

5. stop sql server and remove the old file (the location of the old file is give by filename output of the query in step 2)and keep it separately.

6. Once the old file is backup in different location, give the same name, filename returned by step 2 in the below command and run it.

alter database mydatabase rebuild log on

(Name=mydatabase_log,filename=’mydatabase_log.ldf’)

7. Finally bring the database online.

alter database mydatabase set online

mydatabase is the name of the database for which you are rebuilding the log.