Which instance of a process is using lots of CPU on the database server

Running this script will tell you which instance of a process is using lots of CPU on the database server. You can use the loginame or hostname columns to identify the person.

 

SELECT TOP 25

       spid

       ,blocked

       ,convert(varchar(10),db_name(dbid)) as DBName

       ,cpu

       ,datediff(second,login_time, getdate()) as Secs

       ,convert(float, cpu / datediff(second,login_time, getdate())) as PScore

       ,convert(varchar(16), hostname) as Host

       ,convert(varchar(50), program_name) as Program

       ,convert(varchar(20), loginame) as Login

FROM master..sysprocesses

WHERE datediff(second,login_time, getdate()) > 0 and spid > 50

ORDER BY pscore desc 

index rebuild estimated time

 To rebuild a index, the below query will use full to estimate the approximate time(Not accurate but it will rough estimated time either +\- 30%).

use dbname

go

SELECT    IndexName = name,

IndexSizeGB = CEILING(1.0 * dpages / 128 / 1024),

ETA_Min = CEILING(1.0 * dpages / 128 / 1024) * 1.5,

LogSpaceGBRequired = CEILING(1.0 * dpages / 128 / 1024) * 2,

DataSpaceGBRequired = CEILING(1.0 * dpages / 128 / 1024)

FROM sysindexes

WHERE NAME LIKE ('indexname')

--OR NAME LIKE ('indexname')--If you want use multiple indexes use OR condition

Find Restore date and time with History

 Find the restore date and time.

SELECT @@servername as Destination_server_Name,[rs].[destination_database_name],

[rs].[restore_date],

[bs].[backup_start_date],

[bs].[backup_finish_date],

[bs].[server_name] as Source_Server_Name,

[bs].[database_name] as [source_database_name],

[bmf].[physical_device_name] as [backup_file_used_for_restore],

[rs].[user_name] as User_Name

FROM msdb..restorehistory rs

INNER JOIN msdb..backupset bs

ON [rs].[backup_set_id] = [bs].[backup_set_id]

INNER JOIN msdb..backupmediafamily bmf

ON [bs].[media_set_id] = [bmf].[media_set_id]

ORDER BY [rs].[restore_date] DESC