How to find when was SQL Server restarted ?

We can find the SQL Server restart time using various methods. Below are the methods which I used to find out when was the SQL Server restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

                          (or)

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1;

                          (or)
SELECT start_time FROM sys.traces WHERE is_default = 1

                          (or)
SELECT crdate FROM sysdatabases WHERE NAME = 'tempdb'

                           (or)

SELECT create_date FROM sys.databases WHERE NAME = 'tempdb'

How to find when was SQL Server restarted ?

We can find the SQL Server restart time using various methods. Below are the methods which I used to find out when was the SQL Server restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

                          (or)

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1;

                          (or)
SELECT start_time FROM sys.traces WHERE is_default = 1

                          (or)
SELECT crdate FROM sysdatabases WHERE NAME = 'tempdb'

                           (or)

SELECT create_date FROM sys.databases WHERE NAME = 'tempdb'

Script to estimate backup and restore completion time in SQL Server

I used to get frequent mails from the users stating about when would be the backup will complete etc.

For this I used the below script which will provide the details of backup with the estimated time.

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(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
 ,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
 ,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
 ,CONVERT(VARCHAR(1000), (
   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'
  )

Troubleshooting CXPACKET wait type in SQL Server

The CXPACKET term came from Class Exchange Packet, This can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using parallel plan.

You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

For more details please refer.

SQL Server Agent failing to start with the error “StartServiceCtrlDispatcher failed (error 6)”

SQL Server Agent failing tostart with the error “StartServiceCtrlDispatcher failed (error 6)” 

Log reader fails with “The process could not execute ‘sp_replcmds’ ” error

Log reader fails with “The process could not execute‘sp_replcmds’ ” error

How to export the Event Logs without opening MMC

Export the Event Logs without opening MMC

Application timing out due to excessive blocking on tempdb PFS

     Trouble shooting :  Application timing out due to excessive blocking on PFS