SQL Server connectivity Issue

Hi All,

When we are  using  SQL Server our main aim is to keep  the server up time to 100 %.When your monitoring system shows that SQL is not reachable  and what is the first step you have to do ?

Please check this post on basic troubleshooting on SQL Connectivity issue 

Step 1 :
********

 Go to --SQL Configuration manager -->Check SQLserverice are running ,Agent is running .
If this is not running check with our team guys for any activity is in progress on that server .

Step 2:
******
If no activity is in progress then Go manually up the service /Agent

Step 3:
******
Go to Event Viewer and check for "System " Related info like with User name will be mentioned for rebooting this server

Step 4 :
*******
Check the server up time and statistics info in CMD prompt of your SQL server :

server  up time info :
************************
systeminfo | find  "System Up Time"

statistics info :
*************************

net stats srv | find "Statistics since"

Step 5 :
******

  Using Query analyzer also you can find the server rebooted time .

Query Ued  :
**********

select login_time from master.sys.sysprocesses where cmd=’LAZY WRITER’;

Of course, this just tells me when the SQL service was last started…

Using TempDB
**************
Note :: (TempDB will be newly created whenever your SQL server is restarted )

 1.SELECT create_date FROM sys.databases WHERE name = 'tempdb'
The above query works on SQL Server 2005 and above. For SQL Server 2000 the same query will vary a little bit.

 2.SELECT crdate FROM sysdatabases WHERE name='tempdb'

3.SELECT    [sqlserver_start_time] AS [LastStartupDate]
   FROM    [sys].[dm_os_sys_info]

Step 6 :
******

SQL Server Error Log
*******************

sp_readerrorlog 0,1


Step 7 :
*****

Using Sytem DMV :
****************

1. Using sys.dm_os_sys_info DMV

2. SELECT login_time FROM sys.dm_exec_sessions
WHERE session_id = 1;


Step 8 :
******

Start time of the Default Trace
*************************

select start_time from sys.traces
where is_default = 1


keep reading . . .

TROUBLESHOOTING BACKUP ISSUES IN SQL SERVER 2008

When one of my onsite Lead asked me to check the backup status and also he need the status of last months backup history . .I was using this following  backup queries to find the backup details .
Hope it will help you guys.

BACKUP HISTORY OF PARTICULAR DATE :
****************************************************

SELECT * FROM [msdb].[dbo].[backupset] where backup_start_date >'2012-02-01'

SELECT * FROM [msdb].[dbo].[backupset] where backup_start_date between '2012-01-01' and '2012-02-20'



BACKUP HISTORY  OF PARTICULAR DATABASE :
**********************************************************



DECLARE @DBNAME VARCHAR(50)

SET @DBNAME ='MASTER'

SELECT CEILING(((BACKUP_SIZE / 1024) /1024) /1024)  BKup_size_in_GB,
CEILING(((BACKUP_SIZE / 1024) /1024))  BKup_size_in_MB,[TYPE]BKup_type,backup_start_date
StartTime,backup_finish_date
from msdb..backupset
where database_name= @DBNAME
order by backup_start_date desc


I have given 'MASTER' database .You can use which ever database you like .

NOTE :: The value in bkup_type column indicates the type of backup performed. D indicates FULL backup, I indicates Differential and L indicates Log backup.


BACKUP HISTORY OF CURRENT DAY (TODAY's BACKUP)
*****************************************************************

DECLARE @dt as DATE
SET @dt = getdate()
SELECT *
FROM [msdb].[dbo].[backupset]
where backup_start_date>=(select @dt)


BACKUP HISTORY WITH BACKUP START &  END TIME 
******************************************************************

SELECT *
FROM [msdb].[dbo].[backupset]
where backup_start_date>=dateadd(day,datediff(day,0,getdate()),0)


TO CHECK WHICH  DATABASE MISSED FULL BACKUP
*****************************************************************

SELECT NAME FROM sys.databases
WHERE NAME != 'TEMPDB'
AND NAME NOT IN (    SELECT DISTINCT database_name FROM msdb..backupset
WHERE backup_start_date > DATEADD(DAY,-8,GETDATE())
AND  TYPE = 'D' )


After running this i got MSDB database as result which means that Full backup of MSDB was not performed .


When my Onsite lead asked me to check the status of backup completed . .I was using the following query to find the status of backup completed .

STATUS of BACKUP (percentage completed details)
**********************************************************************

SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins , (total_elapsed_time/1000)/60 Total_Elapsed_Time_Mins ,DB_NAME(Database_id) DBName ,* FROM sys.dm_exec_requests WHERE session_id=3576

STATUS OF BOTH BACKUP & RESTORE (percentage completed details)
***********************************************************************



SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')



***************Thanks*****************

Blocking in SQL server 2008

How to check who is blocking your SQL server

Basic Troubleshooting on  when you see a  transaction is running for long time and  how to find which is blocking your SQL server

Step 1:

USE Master

GO

EXEC sp_who2

GO

This is a System SP which will show the entire process running currently running in SQL server . It will  display the Users list, database name and CPU time of the current transaction running .

This show the current session is blocked by 54 SPID.

Step 2:

To find the current blocking session using DMV :

USE Master

GO

SELECT * 

FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0;

GO

 Step 3:

To find out what are the waiting sessions due to this blocking session :

USE Master

GO

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 

FROM sys.dm_os_waiting_tasks 

WHERE blocking_session_id <> 0

GO

 Step 4:

 

GUI Based findings of blocking sessions::

1.        

1               1.Activity monitor :

Through Activity monitor you can check which is blocking your SQL server .

 After clicking this you will  get an window showing the active sessions and blocked SP ID.

    2.Reports:

Reports is one of the best tool to increase SQL server  performance by finding which query is running for long time and blocking sessions .

One of my favourite  is Top queries by Avg. CPU time

Through reports we can check which session is blocked and you can find the RCA(Root cause analysis)

 Based on this trouble shooting you can KILL the SPID which is blocking your SQL server.

KILL 54;

Be care full when you kill a  transaction .Since the transaction would have completed 90% and killing this session is highly useless.

Based on the % completed and prior approval from the user KILL the transaction.