Script to fetch All jobs with Schedule details

April 26, 2013 9:42 PM / Leave a Comment

Script:

USE msdb
Go

SELECT dbo.sysjobs.name AS ‘Job Name’,
‘Job Enabled’ = CASE dbo.sysjobs.enabled
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
END,
‘Frequency’ = CASE dbo.sysschedules.freq_type
WHEN 1 THEN ‘Once’
WHEN 4 THEN ‘Daily’
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly relative’
WHEN 64 THEN ‘When SQLServer Agent starts’
END,
‘Start Date’ = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + ‘/’ +
substring(convert(varchar(15),active_start_date),5,2) + ‘/’ +
substring(convert(varchar(15),active_start_date),7,2)
END,
‘Start Time’ = CASE len(active_start_time)
WHEN 1 THEN cast(’00:00:0′ + right(active_start_time,2) as char(8))
WHEN 2 THEN cast(’00:00:’ + right(active_start_time,2) as char(8))
WHEN 3 THEN cast(’00:0′
+ Left(right(active_start_time,3),1)
+’:’ + right(active_start_time,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(active_start_time,4),2)
+’:’ + right(active_start_time,2) as char (8))
WHEN 5 THEN cast(’0′
+ Left(right(active_start_time,5),1)
+’:’ + Left(right(active_start_time,4),2)
+’:’ + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+’:’ + Left(right(active_start_time,4),2)
+’:’ + right(active_start_time,2) as char (8))
END,
–    active_start_time as ‘Start Time’,
CASE len(run_duration)
WHEN 1 THEN cast(’00:00:0′
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast(’00:00:’
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast(’00:0′
+ Left(right(run_duration,3),1)
+’:’ + right(run_duration,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 5 THEN cast(’0′
+ Left(right(run_duration,5),1)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
END as ‘Max Duration’,
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN ‘Once’
ELSE cast(‘Every ‘
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ‘ ‘
+     CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN ‘Once’
WHEN 4 THEN ‘Minutes’
WHEN 8 THEN ‘Hours’
END as char(16))
END as ‘Subday Frequency’
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE next_run_time = 0

UNION

SELECT dbo.sysjobs.name AS ‘Job Name’,
‘Job Enabled’ = CASE dbo.sysjobs.enabled
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
END,
‘Frequency’ = CASE dbo.sysschedules.freq_type
WHEN 1 THEN ‘Once’
WHEN 4 THEN ‘Daily’
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly relative’
WHEN 64 THEN ‘When SQLServer Agent starts’
END,
‘Start Date’ = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + ‘/’ +
substring(convert(varchar(15),next_run_date),5,2) + ‘/’ +
substring(convert(varchar(15),next_run_date),7,2)
END,
‘Start Time’ = CASE len(next_run_time)
WHEN 1 THEN cast(’00:00:0′ + right(next_run_time,2) as char(8))
WHEN 2 THEN cast(’00:00:’ + right(next_run_time,2) as char(8))
WHEN 3 THEN cast(’00:0′
+ Left(right(next_run_time,3),1)
+’:’ + right(next_run_time,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(next_run_time,4),2)
+’:’ + right(next_run_time,2) as char (8))
WHEN 5 THEN cast(’0′ + Left(right(next_run_time,5),1)
+’:’ + Left(right(next_run_time,4),2)
+’:’ + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+’:’ + Left(right(next_run_time,4),2)
+’:’ + right(next_run_time,2) as char (8))
END,
–    next_run_time as ‘Start Time’,
CASE len(run_duration)
WHEN 1 THEN cast(’00:00:0′
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast(’00:00:’
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast(’00:0′
+ Left(right(run_duration,3),1)
+’:’ + right(run_duration,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 5 THEN cast(’0′
+ Left(right(run_duration,5),1)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
END as ‘Max Duration’,
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN ‘Once’
ELSE cast(‘Every ‘
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ‘ ‘
+     CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN ‘Once’
WHEN 4 THEN ‘Minutes’
WHEN 8 THEN ‘Hours’
END as char(16))
END as ‘Subday Frequency’
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE next_run_time <> 0

ORDER BY [Start Date],[Start Time]

How to delete duplicate values for a non Unique Value Column Tables

April 25, 2013 8:18 PM / Leave a Comment

Sometimes, We face a situation where we have to remove duplicate values from a table without deleting all the values.  There are two ways to acheieve this.

1. Traditional Method – Move all the DISTINCT  data from source(orginal table) to temp table.

Truncate the existing original table.

Move all the data from the temp table where you have copied all the distinct data.

(Dont forget to set the “SET IDENTITY INSERT ON”) to retain the key values if there is any identity column on the table.

 

2. This is quickest and simple method to remove the duplicate values from a table.

use Rowcount option to achieve this.

Example:

Create a table:

create table Deltest(col1 int, col2 int, col3 char(50))

Insert some duplicate values

insert into Deltest values (1, 1, ‘data value one’)
insert into Deltest values (1, 1, ‘data value one’)

insert into Deltest values (1, 1, ‘data value one’)
insert into Deltest values (1, 2, ‘data value two’)

Now find the total number of duplicate values on the table using count, group by functions

SELECT col1, col2, count(*)
FROM Deltest
GROUP BY col1, col2
HAVING count(*) > 1

Once you identify the total number of duplicate values.

Just calculate the values which has to be deleted as

n = no of same values exists on table

now eliminate one distinct value as:

n = (n -1)

**** total no of values – one distinct value

For ex:

if there are 2 same values on col1 and col2

1 1

1 1

1 1

Here duplicate values are

1 1

1 1

So (n-1) would be 3 -1 = 2

now set rowcount as 2

set rowcount 2

Now use the condition to delete the values as

set rowcount 2
delete from deltest
where col1=1 and col2=1

Now only 2 duplcate rows only will be deleted.

 

 

 

 

 

To collect all login details with detailed informations

March 13, 2013 5:44 AM / Leave a Comment

Query:

SELECT serverproperty(‘servername’) as ‘Server\Instance Name’,
p.name as ‘Login Name’, p.type_desc as ‘Type of Login’ , p.create_date as ‘Creation Date\Time’,
(case sl.sysadmin when 1 then ‘Yes’ else ‘No’ end) as ‘Is SysAdmin’,
(case p.is_disabled when 0 then ‘Yes’ else ‘No’ end) as ‘Is Enabled’
FROM sys.server_principals p
JOIN sys.server_permissions per ON p.principal_id = per.grantee_principal_id
join sys.syslogins sl on p.name = sl.loginname
where p.name not like ‘##MS%’ and p.name not like ‘public’

Total Space details, DB File details Consolidated Report

March 5, 2013 3:52 PM / Leave a Comment

if exists (select 1 from sysobjects where name like ‘files_total_space’)

begin

drop table files_total_space

end

go

if exists (select 1 from sysobjects where name like ‘drives_db_details’)

begin

drop table drives_db_details

end

go

DECLARE     @Drive TINYINT,

@SQL nVARCHAR(100)

SET   @Drive = 97

– Setup Staging Area

create  TABLE drives_db_details

(

Drive CHAR(1),

Info NVARCHAR(80)

)

Create table files_total_space

(

DBName nvarchar(255),

Logical_FileName nvarchar(255),

DB_Size float,

DB_File_Path nvarchar(500),

DB_File_Drive nvarchar(4),

Drive_Total_Space nvarchar(100),

Available_Free_Space nvarchar(100)

)

insert into files_total_space(DBName,Logical_FileName,DB_size, DB_File_Path )

select DB_NAME(dbid), name,(size * 8), [filename]  from master..sysaltfiles

where filename not like ‘%mssqlsystemresource%’

WHILE @Drive <= 122

BEGIN

SET   @SQL = ‘EXEC XP_CMDSHELL ”fsutil volume diskfree ‘ + CHAR(@Drive) + ‘:”’

INSERT      drives_db_details

(

Info

)

EXEC  (@SQL)

UPDATE      drives_db_details

SET   Drive = CHAR(@Drive)

WHERE Drive IS NULL

SET   @Drive = @Drive + 1

END

 

declare @drive_name nvarchar(3)

declare @total_space nvarchar(100)

declare @available_space nvarchar(100)

declare @exec_stmt nvarchar(1000)

declare drive_details_cur cursor for

SELECT            Drive,

str(SUM(CASE WHEN Info LIKE ‘Total # of bytes             : %’ THEN (((CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float)/1024)/1024)/1024) ELSE CAST(0 AS BIGINT) END), 5, 1) AS [Total Space in GB],

str(SUM(CASE WHEN Info LIKE ‘Total # of free bytes        : %’ THEN (((CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float)/1024)/1024)/1024)  ELSE CAST(0 AS BIGINT) END),5, 1) AS [Available Free Space in GB]

FROM        (

SELECT      Drive,

Info

FROM  drives_db_details

WHERE Info LIKE ‘Total # of %’

) AS d

GROUP BY    Drive

ORDER BY    Drive

open drive_details_cur

fetch drive_details_cur into @drive_name, @total_space, @available_space

while (@@fetch_status = 0  )

begin

update  files_total_space set DB_File_Drive = upper(@drive_name), Drive_Total_Space = @total_space, Available_Free_Space = @available_space

where SUBSTRING(DB_File_Path, 1,1) = @drive_name

fetch drive_details_cur into @drive_name, @total_space, @available_space

end

close drive_details_cur

deallocate drive_details_cur

–Final Output

select DBName as [Database Name], Logical_FileName as [Logical File Name],

cast(str((DB_Size/1024), 10, 2) as nvarchar(20)) + ‘ MB’ as [Database Size], DB_File_Path as [Physical File Path],

DB_File_Drive as [File Drive], Drive_Total_Space as [Total Space on Drive],

Available_Free_Space as [Space Available] from files_total_space

drop table files_total_space

drop table drives_db_details

Total drives, available space details on SQL DB Servers

February 14, 2013 4:29 AM / Leave a Comment

Script:

– Initialize Control Mechanism
DECLARE @Drive TINYINT,
@SQL VARCHAR(100)

SET @Drive = 97

– Setup Staging Area
create table #Drives
(
Drive CHAR(1),
Info VARCHAR(80)
)

WHILE @Drive <= 122
BEGIN
SET @SQL = ‘EXEC XP_CMDSHELL ”fsutil volume diskfree ‘ + CHAR(@Drive) + ‘:”’

INSERT into #drives
(
Info
)
EXEC (@SQL)

UPDATE #drives
SET Drive = CHAR(@Drive)
WHERE Drive IS NULL

SET @Drive = @Drive + 1
END

– Show the expected output
SELECT Drive,
cast((((SUM(CASE WHEN Info LIKE ‘Total # of bytes : %’ THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float) ELSE CAST(0 AS float) END)/1024)/1024)/1024)as real(6)) AS [Total Space in GB],
cast((((SUM(CASE WHEN Info LIKE ‘Total # of free bytes : %’ THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float) ELSE CAST(0 AS float) END)/1024)/1024)/1024)as real) AS [Free Space in GB],
cast((((SUM(CASE WHEN Info LIKE ‘Total # of avail free bytes : %’ THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), ”) AS float) ELSE CAST(0 AS float) END)/1024)/1024)/1024)as real) AS [Available Free Space in GB]
FROM (
SELECT Drive,
Info
FROM #drives
WHERE Info LIKE ‘Total # of %’
) AS d
GROUP BY Drive
ORDER BY Drive

drop table #drives

To Disable Windows Update restart Notifications

January 24, 2013 10:30 AM / Leave a Comment

To stop this service, open [Command Prompt] (Start>Run>cmd>Enter) and type the following command sc stop wuauserv. This will not prevent Automatic Updates from starting at the next boot. So don’t worry, you’ll keep getting Windows updates. Just don’t forget to restart eventually.

SQL 2005 Configuring SQL Job to send email notification if it failed

January 23, 2013 10:20 AM / Leave a Comment

This are the simple steps to enable it.
1) Run SQL Server Surface Area Configuration – > Surface Area Configuration for features ->
Select Database Mail – > Check Enable Database mail stored procedures.
2) Open SQL Management Studio – > Management – > Database mail – > Configure SMTP Settings.
3) Next, open the properties of the SQL Server Agent. On the Alert System page enable the mail profile. Restart the SQL Server Agent for the change to take effect.
4) Go to SQL Server Agent – > Operators – > Create New Operator
5) Go to SQL Job – > In the Notifications, Check email and select the Operator. You can either
select the option to send notification if job fails / succeeds/ completed.
Lastly, don’t forget to restart the SQL Agent.

Post navigation

← Older posts