Data and Analytics

You may find below error message in SQL Server Error Log typically adjutant to SQL Mail session start up messages. Error: 1073759778, Severity: 1, State: 0 Error: 1073759806, Severity: 1, State: 0 These are Severity: 1 error which means they are for information purpose only. These messages are logged because SQL Mail started with xp_startmail which is unnecessary in SQL 2000. SQL Mail automatically starts when you run xp_sendmail in SQL 2000. Below messages are from a SQL 2000 SP4 Error Log which for same reason we discussed here: Error: 1073759778, Severity: 1, State: 0 SQL Mail session started.. Using 'sqlmap70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_sendmail'. Error: 1073759806, Severity: 1, State: 0 Starting SQL Mail session.... Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'. Proposed corrective action: Short Term: No action needed . You can sa

Although each report’s subscription is configured as a SQL Agent job in the server hosting SSRS’s ReportServer database , it is little tricky to find the corresponding report subscription’s details from job name. Please use below codes (on the server where SSRS’s ReportServer database is hosted) to find details about report subscription including retrieving report names for SSRS subscription jobs, their last execution status, report’s path etc. SELECT       c . Name AS ReportName ,       c . Path AS Report_Path ,       rs . ScheduleID AS JOB_NAME       , s . [Description]       , s . LastStatus       , s . LastRunTime       , c . CreationDate       , c . ModifiedDate FROM       ReportServer .. [Catalog] c       JOIN ReportServer .. Subscriptions s ON c . ItemID = s . Report_OID       JOIN ReportServer .. ReportSchedule rs ON c . ItemID = rs . ReportID       AND rs . SubscriptionID = s . SubscriptionID Please Note:   1. This code is

Please use below code (TSQL Script) to find last backup history of a specific database. For SQL 2005/2008/2008R2: USE <DatabaseName> GO SELECT TOP 100 -- Change 100 to any number you need. a . database_name , b . physical_device_name , CAST ( CAST ( a . backup_size / 1000000 AS INT ) AS VARCHAR ( 14 )) + ' ' + 'MB' AS bkSize , CAST ( DATEDIFF ( second , a . backup_start_date , a . backup_finish_date ) AS VARCHAR ( 4 )) + ' ' + 'Seconds' TimeTaken , a . backup_start_date , CAST ( a . first_lsn AS VARCHAR ( 50 )) AS first_lsn , CAST ( a . last_lsn AS VARCHAR ( 50 )) AS last_lsn , CASE a . [type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType , a . server_name , a . recovery_model FROM msdb . dbo . backupset a INNER JOIN msdb . dbo . backupmediafamily b ON a . media_

Although there are many approaches to find this, as per me best approach is to run below code (SQL Script) on the SQL instance: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') Note: This code is tested on SQL 2008/R2, 2005 and 2000.

This is a related post from http://blog.consultdba.com/2012/06/finding-sql-servers-job-execution.html . If you need to find execution history of all SQL Server’s job but do not need to know how much time each step of a job took, then please use below code (TSQL Script): SELECT       sj . name , sja . run_requested_date , CONVERT ( VARCHAR ( 12 ), sja . stop_execution_date - sja . start_execution_date , 114 ) Duration FROM   msdb . dbo . sysjobactivity sja INNER JOIN msdb . dbo . sysjobs sj ON     sja . job_id = sj . job_id WHERE sja . run_requested_date IS NOT NULL ORDER BY sja . run_requested_date desc Note: This code is tested on SQL 2005/2008. It will not run on SQL 2000. Reference: http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx Note: You may modify where clause to limit this report to one or some specific job/s.  

Often we need to compare how long a job took to execute in past. While GUI is a good tool for finding this information, it may be inefficient if we need to retrieve data for multiple jobs and/or for multiple times. Below code (TSQL Script) will return Job Name, Execution date and execution duration for a specific job. Also please note that this report will generate one row for each step of the job . So for example if your job has 5 steps, you will get 5 steps for that particular execution of that job mentioning how much time each step’s execution has taken. select job_name , run_datetime , run_duration from (     select job_name , run_datetime ,         SUBSTRING ( run_duration , 1 , 2 ) + ':' + SUBSTRING ( run_duration , 3 , 2 ) + ':' +         SUBSTRING ( run_duration , 5 , 2 ) AS run_duration     from     (         select DISTINCT             j . name as job_name ,             run_datetime = CONVERT ( DATETIME , RTRI

Please use below code (tsql script) to find port number of a SQL Instance using TSQL. This may come handy when you need to find this information programmatically for many instances or if you are experiencing trouble to find port number using traditional ways (ie. Using configuration manager etc).    DECLARE @portNumber varchar ( 20 ), @key varchar ( 100 ) if charindex ( '\' , @@servername , 0 ) <> 0 begin            set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' + @@servicename + '\MSSQLServer\Supersocketnetlib\TCP' end else begin           set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP' end EXEC master .. xp_regread @rootkey = 'HKEY_LOCAL_MACHINE' , @key = @key , @value_name = 'Tcpport' , @value = @portNumber OUTPUT SELECT 'Server Name: ' + @@servername + ' Port Number:' + convert ( varchar ( 10 ), @portNumber ) Please note: