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

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

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

A: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.

Q. 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.

4. In my SQL Server 3 instance is there in that One instance is consuming more CPU, How to find?
A:
Right-click the instance name / reports / standard / top sessions

Before discussing how to do this, we should discuss these terms:

SPID is the SQL Server Process ID number and is assigned by SQL Server to each new connection. It starts with one and is globally unique. SPID 1 through 50 are reserved for system uses and are not used for any user connections.
KPID is the kernel-process ID. Under SQL Server for Windows this is the thread ID number, also known as "ID Thread," and is assigned by Windows when the thread is created. The Thread ID number is a system-wide identifier that uniquely identifies the thread. KPID is visible by querying the KPID column of master..sysprocesses. It is only filled in for spid numbers four and higher. You can also get KPID/ID Thread from Windows Perfmon using the "Thread" object and the "ID Thread" counter.

I am sure you are familiar with the SPID value, but have you ever noticed KPID? Probably not unless you queried the sysprocesses view directly. Let's look at example of how this works.
Example
Step 1
If we look at Task Manager on the server we can see the CPU usage. So the first step is to verify that the SQL Server process (sqlservr), and not some other process, is responsible for the excessive CPU use. Remember, the “sqlservr” process is made up of many different threads, each one generally (but not always) representing a specific user connection. In the screenshot below we see the sqlservr process is taking approximately 70 percent of CPU.

Now that we know SQL Server is the main culprit and responsible for the high CPU, the next step is to find out which SQL Server process is causing the high CPU.
Step 2
Now we need to find out what SQL Server thread is causing the high CPU. Once we have this ID Thread we can correlate that ID thread (KPID) to the SPID in SQL Server.
We will use Performance Monitor to get this info. Type perfmon in a Windows CMD prompt or launch from Control Panel.
Click on Add counters and select the "Thread" object in the drop down.

Select these counters at the same time:

% Processor Time
ID Thread
Thread State
Thread Wait Reason

In the right pane, you will see multiple instances from multiple applications, which are running on that server. Since we are looking for “sqlservr” select all of the instances that begin with “sqlservr” from the list box as shown below and click Add. You can add other counters as needed and will see below that I added a few additional counters.

Step 3
Press (Ctrl+R) or click on the view Report tab to change from graphical to report view as shown below. Here you should be able to find which ID Thread is eating up your CPU resources by using the scroll bar to see all instances.
Below we have identified which thread is causing the problem. Here you can see ID Thread 30 is taking 46.841% of the total CPU. You can also find the ID Thread which is 872. Now that we know the ID Thread, we can run a query in SSMS to find the actual query.

Step 4
Our last step is to correlate the Thread ID (KPID) identified in the last step to the SPID. To do this, run the following query in Query analyzer:
SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=872

Step 5
From the query above we can see SPID 71 is causing the issue. To find how many threads and open transactions this is running we can run this query.
SELECT spid, kpid, status, cpu, memusage, open_tran, dbid FROM sysprocesses WHERE spid=71

Step 6
To get the exact query that is running, we can run DBCC INPUTBUFFER using the SPID. The below output shows us that a backup job is causing our CPU issues on our server.

Q How to recover a Database in suspect mode?

A Step 1

1.EXEC sp_resetstatus 'DBname';

2) Step 2

1.ALTER DATABASE DBname SET EMERGENCY

2.DBCC checkdb('DBname')

3) Step 3

1.ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2.DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

4) Step 4

1.ALTER DATABASE DBname SET MULTI_USER

Q. How to find long running queries?

SELECT DISTINCT TOP 10

Q. How to transfer Logins?

With revlogin script or SSIS packages.

Q. SQL Server service does not start successfully because of a logon failure

This problem occurs because the password for the SQL Server startup service account or the SQL Server Agent startup service account is not correct. This problem can occur when the password for the account is changed but the password information was not updated for the SQL Server service or the SQL Server Agent service.

To solve this problem, type the correct password in the Microsoft SQL Server service account on the SQL Server host computer.

To correct the password in Microsoft Windows 2000 Server and Microsoft Windows XP:

1. Click Start, point to Settings, and then click Control Panel.

2. Double-click Administrative Tools, and then double-click Services.

3. Use one of the following steps based on your instance type:

o For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.

o For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.

o For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.

o For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.

4. Type the correct password in the Password and Confirm password textbox, and then click OK.

To correct the password in Microsoft Windows NT 4.0:

1. Click Start, point to Settings, and then click Control Panel.

2. Use one of the following steps based on your instance type:

o For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.

o For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.

o For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.

o For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.

3. Type the correct password in the Password and Confirm password textbox, and then click OK.

NOTE: Changing the password through the Services applet allows SQL Server to start successfully; however, if Microsoft Search (Full-Text Service) is installed, it requires a subsequent password change through SQL Enterprise Manager (SEM).

Note If you are using SQL Server 2005, see the "Changing passwords and user accounts" topic in SQL Server 2005 Books Online.

The following section is from SQL Server 2000 Books Online:

"After changing the SQL Server service account information in Control Panel, you must also change the SQL Server service account in SQL Server Enterprise Manager. This allows the service account information for Microsoft Search service to remain synchronized as well.

Important: Although the Microsoft Search service is always assigned to the local system account, the full-text search engine tracks the SQL Server service account in Windows. Full-text search and failover clustering are not available if Windows password changes are not reset using SQL Server Enterprise Manager."

Using SEM, right-click the Server, click Properties, and then click the Security tab. Under Startup service account, enter the appropriate account and password.

4. SQL Server Agent Service not started what is the possible reason?

Solution:

1. Click Start, click Programs, and then click SQL Server 2008.

2. Click Configuration Tools, and then click SQL Server Configuration Manager.

3. Expand SQL Server 2008 Services.

4. Locate the SQL Server Agent service. The SQL Server Agent service is named "SQL Server Agent" for default instances and is named "SQL Server Agent (Instance name)" for named instances.

5. Right-click SQL Server Agent, and then click Properties.

6. On the Log On tab, click to select the account check box. Specify a different account name and password.

7. In the Service status section, click Start, and then click OK.

Q. How to Assigning specific processors to SQL Server instances

Processor affinity should probably be tested carefully. But if you have pretty fast machines with lots of cores, I would think that your bottleneck would probably NOT end up being your processors, but your IO subsystem (ie: disks). However, mileage may vary. I've seem a LOT more systems bog down because of disk and memory that CPU. But it depends on your app so it is hard to tell. Also keep in mind you have to do the processor affinity on each SQL instance keeping in mind ALL instances on ALL other boxes, so on box 1 you might allocate cores 1-4 to instance 1, and 5-6 to instance 2, then when yo go to box 2 you would probably start at core 7, etc..

Short answer, I wouldn't do it. I would need a good reason to do it LATER. Be careful how many instances you have running. And let them use the resources they have, monitor for problems. If you have a problem instance you might just limit it..

Q. How to check port available.

Identify SQL Server TCP IP port being used

USE master

xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL, N'asc'

-----

sp_readerrorlog 1,1,'listening','server'

8. If SQL Server not working, in this situation, How to find Error Log path,

In SQL Server Configuration Manager, click SQL Server Services on the left side and then right click on SQL Server (MSSQLSEVER) and select Properties from the drop down as shown below. For a named instance, right click on the appropriate SQL Server (INSTANCENAME) service.

In SQL Server (MSSQLSERVER) Properties window click on the advanced tab and then expand the drop down next to Startup Parameters. The location of SQL Server Error Log file is mentioned next to the "-e" startup parameter as highlighted in the snippet below.

Identify SQL Server Error Log file used by SQL Server Database Engine Using Application Event Viewer

1. Click Start -> All Programs -> Administrative Tools -> Server Manager.

2. In Server Manager, expand Diagnostics, expand Event Viewer, expand Windows Logs and then select Application on the left side panel. In the right panel you need to filter for events with Event ID 17111 as shown in the below snippet. To set a filter right click on Application and select Filter Current Log.

3. To view the location of SQL Server Error Log file double click an event and you can see the event properties as shown below.

Q. How can Processors assign to SQL Server?

RC on Instance Properties > select Processors >

Q. SQL Server Agent Service is not working?

Change the service account to a system account such as "LocalSystem" or "NetworkService" (using SQL Computer Manager) and then restart Sql Agent.

Q. Difference between xp_readerrorlog & sp_readerrorlog

sp_readerrorlog is a stored procedure in the master database that checks that the user is part of the securityadmin group, then calls xp_readerrorlog, passing parameters if available. Any differences between the two procedures does not affect the output, which means the information returned is the same.

The parameters syntax has been changed in SQL Server 2012 but not documented yet.

sp_readerrorlog 0,1,’node’

xp_readerrorlog 0,1, ‘node’ -- Not supported

sp_readerrorlog 0,1, "node"

xp_readerrorlog 0,1,"node"

SQL DBA Interview Questions

Scroll Bar

Trouble Shooting