Monday, 20 February 2012

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

No comments:

Post a Comment