Jobs Running Longer than Average Run Time

create procedure dbo.usp_Dba_JobsExceedingAvgRunTime

@ExceededBySeconds int,

@EmailRecipients varchar(128) = 'email@domain.com'

as

/*

Created By: Dulari Parikh

Purpose: Executed to stop jobs stuck in a hanged state. Can be used to simply report jobs running longer than average run time

usp_Dba_JobsExceedingAvgRunTime

@ExceededBySeconds = 3600

usp_Dba_JobsExceedingAvgRunTime

@ExceededBySeconds = 1

*/

declare @JobId uniqueidentifier

declare @JobName varchar(128)

if exists

(

select

ed.job_id AS 'JobId',

ed.name AS 'JobName',

ed.start_execution_date AS 'StartTime',

ja.stop_execution_date AS 'StopTime',

art.AvgRuntimeOnSucceed,

DATEDIFF(ss,ja.start_execution_date,GETDATE()) as CurrentRunTime

from

(

select j.name, j.job_id, MAX(start_execution_date) as start_execution_date

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id

group by j.name, j.job_id

) ed

join msdb.dbo.sysjobactivity ja on ed.start_execution_date = ja.start_execution_date and ed.job_id = ja.job_id

join

(

SELECT job_id,

AVG

((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)

+

STDEV

((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100) AS AvgRuntimeOnSucceed

FROM msdb.dbo.sysjobhistory

WHERE step_id = 0 AND run_status = 1

GROUP BY job_id

) art ON ed.job_id = art.job_id

WHERE

ja.stop_execution_date IS NULL

and DATEDIFF(ss,ja.start_execution_date,GETDATE()) > art.AvgRuntimeOnSucceed + @ExceededBySeconds -- Runs longer than @ExceededBySeconds over the average successful runtime

)

BEGIN

delete from JobsLongRunning

insert into JobsLongRunning

(

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

)

select

ed.job_id AS 'JobId',

ed.name AS 'JobName',

ed.start_execution_date AS 'JobStartTime',

art.AvgRuntimeOnSucceed,

DATEDIFF(ss,ja.start_execution_date,GETDATE()) as CurrentRunTime

from

(

select j.name, j.job_id, MAX(start_execution_date) as start_execution_date

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id

group by j.name, j.job_id

) ed

join msdb.dbo.sysjobactivity ja on ed.start_execution_date = ja.start_execution_date and ed.job_id = ja.job_id

join

(

SELECT job_id,

AVG

((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)

+

STDEV

((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100) AS AvgRuntimeOnSucceed

FROM msdb.dbo.sysjobhistory

WHERE step_id = 0 AND run_status = 1

GROUP BY job_id

) art ON ed.job_id = art.job_id

WHERE

ja.stop_execution_date IS NULL

and DATEDIFF(ss,ja.start_execution_date,GETDATE()) > art.AvgRuntimeOnSucceed + @ExceededBySeconds -- @ExceededBySeconds -- Runs longer than @ExceededBySeconds over the average successful runtime

--ORDER BY ja.start_execution_date DESC

select

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

from DBAdmin.dbo.JobsLongRunning

exec msdb..sp_send_dbmail

@recipients = @EmailRecipients,

@subject = 'ETL SQL Job Exceeded Average Run Time',

@query = 'select

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

from DBAdmin.dbo.JobsLongRunning'

create table #Jobs

(

JobId uniqueidentifier,

JobName varchar(128),

JobStartTime datetime,

AvgRuntimeOnSucceed int,

CurrentRunTime int

)

insert into #Jobs

(

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

)

select

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

from DBAdmin.dbo.JobsLongRunning

while exists(select * from #Jobs)

begin

select top 1 @JobName = JobName from #Jobs

exec msdb..sp_stop_job @job_name = @JobName

delete from #Jobs where JobName = @JobName

end

END

GO

Mail attachment allowed file size

EXECUTE msdb.dbo.sysmail_configure_sp
    'MaxFileSize', '2097152' ;

http://msdn.microsoft.com/en-us/library/ms186321.aspx

Progress of Completion

SELECT

      command,

      percent_complete,

      estimated_completion_time,

      r.estimated_completion_time / 60000 as estimated_completion_time_mins,

      cpu_time,

      total_elapsed_time,

      total_elapsed_time / 60000 as total_elapsed_time_mins,

      ST.TEXT

FROM sys.dm_exec_requests r

      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

WHERE command in( 'RESTORE DATABASE', 'BACKUP DATABASE')

Percentage of work completed for the following commands:

·         ALTER INDEX REORGANIZE

·         AUTO_SHRINK option with ALTER DATABASE

·         BACKUP DATABASE

·         DBCC CHECKDB

·         DBCC CHECKFILEGROUP

·         DBCC CHECKTABLE

·         DBCC INDEXDEFRAG

·         DBCC SHRINKDATABASE

·         DBCC SHRINKFILE

·         RECOVERY

·         RESTORE DATABASE,

·         ROLLBACK

·         TDE ENCRYPTION

Connect to SQL across domains using Windows authentication

To connect from desktop on DOMAIN1 to SQL instance hosted on DOMAIN2

runas /netonly /user:domain\username program.exe

Example:
runas /netonly /user:DOMAIN2\username "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

After being prompted for the password, the following screen comes up.

Enter the following:
SQL Instance: 12.12.123.123 (IP Address)
Authentication: Windows Authentication

Even though the DOMAIN1 domain account shows up greyed out and shows DOMAIN1\username on the left Object Browser, it will connect with DOMAIN2\username credentials as shows on the right.

The same method can be used for any program.


http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/runas.mspx?mfr=true


http://www.olegsych.com/2009/05/crossing-domain-boundaries-windows-authentication/


http://stackoverflow.com/questions/849149/connect-different-windows-user-in-sql-server-management-studio-2005-or-later

Database Space Usage by File, Filegroup & Database

/* Database space usage for instance */


IF OBJECT_ID('tempdb..#DatabaseFileSpace') IS NOT NULL DROP TABLE #DatabaseFileSpace ;
CREATE TABLE #DatabaseFileSpace
    (
      ID INT IDENTITY(1, 1),
      DatabaseName varchar(128),
      LogicalFileName varchar(128),
      FileGroupName nvarchar(128),
      PhysicalFileName varchar(128),
      FileSizeMB float,
      SpaceUsedMB float,
      FreeSpaceMB float
    ) ;
    
   
EXEC dbo.sp_MSforeachdb '
use ?
INSERT INTO #DatabaseFileSpace 
select
''?'' as DatabaseName
    ,a.name as LogicalFileName
,isnull(g.name, ''LOG'') as FileGroupName
    ,filename as PhysicalFileName
,convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
,convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
,convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from [?].sys.sysfiles a
left join [?].sys.filegroups g on a.groupid = g.data_space_id
'


select *
from #DatabaseFileSpace
order by DatabaseName, FileGroupName


select 
DatabaseName, 
FileGroupName,
SUM(FileSizeMB) as FileSizeMB, 
SUM(SpaceUsedMB) as SpaceUsedMB, 
SUM(FreeSpaceMB) as FreeSpaceMB
from #DatabaseFileSpace
group by DatabaseName, FileGroupName
order by DatabaseName, FileGroupName


select 
DatabaseName, 
SUM(FileSizeMB) as FileSizeMB, 
SUM(SpaceUsedMB) as SpaceUsedMB, 
SUM(FreeSpaceMB) as FreeSpaceMB
from #DatabaseFileSpace
group by DatabaseName
order by DatabaseName

Database Restore History

I have been frequently asked when the last time a DEV or QA database was refreshed from PROD.

select
    h.restore_date,
    h.destination_database_name,
    b.backup_finish_date,
    b.type,
    b.server_name
from msdb.dbo.RestoreHistory h
join msdb.dbo.BackupSet b on h.backup_set_id = b.backup_set_id
order by restore_date desc