Moving SQL Agent Log file "SQLAGENT.OUT" to a different location

Moving SQL Agent Log file "SQLAGENT.OUT" to a different location

 stored procedure for retrieving SQL Agent properties

sp_get_sqlagent_properties is and undocumented stored procedure to retrive the SQL Agent properties of a particular server. This stored procedure can be found in msdb database.

Usage:
EXEC msdb..sp_get_sqlagent_properties

-------------------------------------------------------------------------------

In one of my previous posts "Undocumented stored procedure for retrieving SQL Agent properties", I had explained how to retrieve the SQL Agent Properties.

In this post I will explain how to change the location of the SQL Agent Log file "SQLAGENT.OUT".

To find the current location of SQLAGENT.OUT file, execute the below SP and look at the value of the column "errorlog_file". This is location where SQLAGENT.OUT file is located.

?

1 2	EXEC msdb..sp_get_sqlagent_properties GO

Output:

Now, to change the location of SQLAGENT.OUT file, run the below command and re-start the SQL Server Agent Service and you are done.

?

1 2	EXEC msdb.dbo.sp_set_sqlagent_properties  @errorlog_file=N'<new path>\SQLAGENT.OUT'

GetJobSchedule


/*******************************************************************************

Name: GetJobSchedule (For SQL Server7.0&2000)

Author: Chetan


Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to
produce a resultset showing the jobs on a server plus their schedules
(if applicable) and the maximun duration of the job.

The UNION join is to cater for jobs that have been scheduled but not yet
run, as this information is stored in the 'active_start...' fields of the 
sysjobschedules table, whereas if the job has already run the schedule 


*******************************************************************************/


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]

Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch



/*
You then run the following query against sys.dm_exec_connections and see if that turns up anything 
useful based on the most recent SQL handle:

SELECT t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE session_id = 53

This returns:

FETCH API_CURSOR0000000000000004

Didn’t help.  

So what about other DMVs?  You eventually find a reference to the sys.dm_exec_cursors DMV and see it 
can tell you about open cursors, their properties and associated SQL handle.  But you're not sure the 
SQL Handle will be any help because it hasn't been helpful with the other DMVs:

*/
SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (125) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t



--A. Detecting old cursors
--This example returns information about cursors that have been open on the server
-- longer than the specified time of 36 hours.



SELECT creation_time, cursor_id, name, c.session_id, login_name 
FROM sys.dm_exec_cursors(0) AS c 
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id 

WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 48;

 This script email will be handy to get the database name, backup date, 
backup time and backup location, which will be useful to inform the storage admin or backup admin for 
the location that needs to be restored from backup disk or tape.



/*Use Cases:

--1. In a busy SQL Server where we have 100's of databases taken using scripts,
 maintenance plans, backup tape media etc to different locations and we don't know where the backup is located.



--2. Audit for previous backups. If backup is not taken in servers like QA, DEV, UAT etc.

--VIEW
--====

--Step 1: Create a view which is easy to manage.
*/

CREATE view [dbo].[Backup_Status]
 as SELECT sdb.name as Database_Name,
ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NO BACKUP') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 101), 'NOT APPLICABLE') as LastBackupDate,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 108), 'NOT APPLICABLE') as LastBackupTime,
bkpfmly.physical_device_name as File_Location
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bkpst with (readpast) ON bkpst.database_name = sdb.name AND bkpst.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupmediafamily bkpfmly with (readpast) ON bkpfmly.media_set_id = bkpst.media_set_id
GROUP BY sdb.Name,physical_device_name
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 1),20) AND CONVERT(nvarchar(10),(GETDATE()),20)

--Note: This script provides the backup history for the past 24 hours. If you want more days than just 24 hours change the HAVING section.
/*
EX:- 3 days
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 3),20) AND CONVERT(nvarchar(10),(GETDATE()),20)

In case you need the complete history of backup comment the HAVING section.

HTML Email:
===========
*/
go

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


SET @xml = CAST(( SELECT [Database_Name] AS 'td','',[DaysSinceLastBackup] AS 'td','',
       [LastBackupDate] AS 'td','', [LastBackupTime] AS 'td','',[File_Location] AS 'td',''
FROM  Backup_Status ORDER BY LastBackupTime asc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


SET @body ='<html><body><H3>Please find the values for Database Backup details</H3>
<table border = 1>
<tr>
<th bgcolor="#66CCFF">  Database_Name </th> <th bgcolor="#66CCFF">  DaysSinceLastBackup </th> <th bgcolor="#66CCFF">  LastBackupDate </th> <th bgcolor="#66CCFF">  LastBackupTime </th> <th bgcolor="#66CCFF">  File_Location </th></tr>'  


SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL DBA Team',
@body = @body,
@body_format ='HTML',
@recipients = 'chetan.kumar@driscolls.com',
@subject ='SQL Server Backup Status' ;

Script DB Level Permissions v2.1

-------------------------------------------------------------------------------------------------------------------------------------------------------------------/*

This script will script the role members for all roles on the database.

This is useful for scripting permissions in a development environment before refreshing

development with a copy of production.  This will allow us to easily ensure

development permissions are not lost during a prod to dev restoration. 

Author: S. Kusen

Updates:

05/14/2012: Incorporated a fix pointed out by aruopna for Schema-level permissions.

01/20/2010: Turned statements into a cursor and then using print statements to make it easier to 

copy/paste into a query window.

Added support for schema level permissions

Thanks to wsoranno@winona.edu and choffman for the recommendations.

*/

DECLARE 

    @sql VARCHAR(2048)

    ,@sort INT 

DECLARE tmp CURSOR FOR

/*********************************************/

/*********   DB CONTEXT STATEMENT    *********/

/*********************************************/

SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],

1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],

1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],

2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/

/*********     DB USER CREATION      *********/

/*********************************************/

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],

3 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],

4 AS [-- RESULT ORDER HOLDER --]

FROM sys.database_principals AS rm

WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

UNION

/*********************************************/

/*********    DB ROLE PERMISSIONS    *********/

/*********************************************/

SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],

5 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'EXEC sp_addrolemember @rolename ='

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],

6 AS [-- RESULT ORDER HOLDER --]

FROM sys.database_role_members AS rm

WHERE USER_NAME(rm.member_principal_id) IN (

--get user names on the database

SELECT [name]

FROM sys.database_principals

WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas

and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

 )

--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],

7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/

/*********  OBJECT LEVEL PERMISSIONS *********/

/*********************************************/

SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

8 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE 

WHEN perm.state <> 'W' THEN perm.state_desc 

ELSE 'GRANT'

END

+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects

+ CASE

WHEN cl.column_id IS NULL THEN SPACE(0)

ELSE '(' + QUOTENAME(cl.name) + ')'

 END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

+ CASE 

WHEN perm.state <> 'W' THEN SPACE(0)

ELSE SPACE(1) + 'WITH GRANT OPTION'

 END

AS [-- SQL STATEMENTS --],

9 AS [-- RESULT ORDER HOLDER --]

FROM

sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

--WHERE usr.name = @OldUser

--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],

10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/

/*********    DB LEVEL PERMISSIONS   *********/

/*********************************************/

SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

11 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE 

WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

ELSE 'GRANT'

END

+ SPACE(1) + perm.permission_name --CONNECT, etc

+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>

+ CASE 

WHEN perm.state <> 'W' THEN SPACE(0) 

ELSE SPACE(1) + 'WITH GRANT OPTION' 

 END

AS [-- SQL STATEMENTS --],

12 AS [-- RESULT ORDER HOLDER --]

FROM sys.database_permissions AS perm

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

--WHERE usr.name = @OldUser

WHERE [perm].[major_id] = 0

AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas

AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],

13 AS [-- RESULT ORDER HOLDER --]

UNION 

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

14 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE

WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

ELSE 'GRANT'

END

+ SPACE(1) + perm.permission_name --CONNECT, etc

+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

+ QUOTENAME(SCHEMA_NAME(major_id))

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default

+ CASE

WHEN perm.state <> 'W' THEN SPACE(0)

ELSE SPACE(1) + 'WITH GRANT OPTION'

END

AS [-- SQL STATEMENTS --],

15 AS [-- RESULT ORDER HOLDER --]

from sys.database_permissions AS perm

inner join sys.schemas s

on perm.major_id = s.schema_id

inner join sys.database_principals dbprin

on perm.grantee_principal_id = dbprin.principal_id

WHERE class = 3 --class 3 = schema

ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp

FETCH NEXT FROM tmp INTO @sql, @sort

WHILE @@FETCH_STATUS = 0

BEGIN

        PRINT @sql

        FETCH NEXT FROM tmp INTO @sql, @sort    

END

CLOSE tmp

DEALLOCATE tmp