Archive

Archive for the ‘SQL Agent’ Category

SQL Server – Get values as DATETIME from sysjobhistory

July 22, 2013 Leave a comment

This is a follow-up post on SQL Server – Check SQL Agent Job History using T-SQL.

SQL Server Agent stores SQL jobs history in sysjobhistory. It has two different columns for date and time, Run_Date and Run_Time. Since this is not available as DATETIME we cannot filter based on certain criteria such as jobs that run in last 24 hours.

There is a system function available in msdb database which takes these two columns as input converts output to a DATETIME data type, dbo.agent_datetime. You can use this function as below:

SELECT        TOP 5

[JobName]     JOB.name,

       [StepName]    HIST.step_name,

              [RunDateTime] dbo.agent_datetime(HIST.run_date,HIST.run_time)

FROM          sysjobs JOB

INNER JOIN    sysjobhistory HIST ON HIST.job_id JOB.job_id

ORDERBY      HIST.run_dateHIST.run_time

Result Set:

JobName                  StepName                            RunDateTime

syspolicy_purge_history  Verify that automation is enabled.  2013-01-29 02:00:00.000

syspolicy_purge_history  Purge history.                      2013-01-29 02:00:00.000

syspolicy_purge_history  Erase Phantom System Health Records 2013-01-29 02:00:00.000

syspolicy_purge_history  (Job outcome)                       2013-01-29 02:00:00.000

syspolicy_purge_history  Verify that automation is enabled.  2013-01-30 02:00:00.000

 

(5 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – How to change SQL Server ERRORLOG location

July 8, 2013 3 comments

By default SQL Server ERRORLOG is stored in "C:\Program Files\Microsoft SQL Server\InstanceFolder\MSSQL\Log" folder. The ERRORLOG location is configured as a startup parameter for SQL Server Service.

image

To change the location of ERRORLOG you need to modify the startup parameter -e.

For example, if you need to move logs to C:\Logs\ then replace the startup parameter as below:

Step 1:

Replace "-eC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG"

to "-eC:\Logs\ERRORLOG"

Step 2:

Restart SQL Server Service to apply changes. The ERRORLOG will be created to "C:\Logs" after SQL Server is restarted.

 

To change location for SQL Server Agent Logs:

Location for SQL Server Agent Log can be changed using T-SQL or SQL Server Management Studio.

Method 1: To change SQL Server Agent Log location right click on "Error Logs" node and select "Configure"

image

Provide new location for Log file in "Error Log File" path:

image

Click on "OK" and restart SQL Server Agent to apply changes.

Method 2: To change log location using T-SQL

To change log location using T-SQL you can use below code:

USE [msdb]

GO

EXEC   msdb.dbo.sp_set_sqlagent_properties

       @errorlog_file = N'C:\Logs\SQLAGENT.OUT'

GO

Execute this code and restart SQL Server agent to apply changes.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Add Date/Time to output file of BCP / SQLCMD

April 9, 2013 1 comment

You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data. For generating dynamic file names you can use solution provided below. In the examples below I have appended date/time to exported files. You can modify the logic to suit your requirement.

Step 1: First let us create a stored procedure which will provide the data to be exported:

CREATE PROCEDURE ExportData

AS

SET NOCOUNT ON

SELECT 'Vishal', 'SqlAndMe'

GO

 

EXEC dbo.ExportData

GO

Result Set:

—— ——–

Vishal SqlAndMe

I have selected string here to keep things simple. You can specify any query in stored procedure which produces required data.

 

Step 2: Now, we will write the T-SQL code to export data returned from this stored procedure. Here we will use SQLCMD (you can also use BCP) to export data. We will execute SQLCMD using xp_cmdshell extended stored procedure.

DECLARE       @sqlCommand   VARCHAR(1000)

DECLARE       @filePath     VARCHAR(100)

DECLARE       @fileName     VARCHAR(100)

 

SET    @filePath = 'C:\Temp\'

 

SET    @fileName = 'MyFile_' +

       + CONVERT(VARCHAR, GETDATE(), 112) + '_' +

         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +

         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'

 

SET    @sqlCommand =

       'SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o "' +

       @filePath + @fileName +

       '" -h-1'

 

–Uncomment if you want to use BCP

–SET  @sqlCommand =

–     'bcp "EXEC ExportData" queryout "' +

–     @filePath + @fileName +

–     ' " -S (local) -T -d SqlAndMe -c'

 

–PRINT       @sqlCommand

 

EXEC   master..xp_cmdshell @sqlCommand

GO

The above code will create the required file as "MyFile_YYYYMMDD_HH_MM.txt".

You can verify the command generated by uncommenting the PRINT statement in the code above. Also, you can uncomment the fourth SET statement in case you want to use BCP command to export the data.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Cycle Error Logs for SQL Server and SQL Server Agent

February 8, 2013 1 comment

SQL Server error logs can fill up quickly, and when you are troubleshooting something you may need to go through huge log. However you can cycle the error log to manage the amount of log you need to go through. When you cycle error log the current log file is renamed from ERRORLOG to ERRORLOG.1, ERRORLOG.1 is renamed to ERRORLOG.2 and so on.

Restarting SQL Server will cycle the error logs. You can also use sp_cycle_errorlog to cycle error logs without restarting SQL Server.

Below is an example how to use it:

EXEC sp_Cycle_ErrorLog

GO

 

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

You can verify that the log has cycled using Management Studio or you can use xp_ReadErrorLog also.

 

Same effect can be achieved by using DBCC ErrorLog. In fact, sp_Cycle_ErrorLog calls DBCC ErrorLog to cycle error logs.

DBCC ErrorLog

GO

 

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

DBCC ErrorLog and sp_Cycle_ErrorLog only cycles SQL Server error logs. It does not cycle SQL Server Agent error logs. In order to achieve this you can restart SQL Server Agent or use another stored procedure sp_Cycle_Agent_ErrorLog.

USE   [msdb]

GO

 

EXEC  sp_Cycle_Agent_ErrorLog

GO

 

Result Set:

Command(s) completed successfully.

 

You can verify that the SQL Server Agent log has cycled using xp_ReadErrorLog.

* Use sp_helptext to see what is called from sp_Cycle_Agent_ErrorLog.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Check SQL Agent Job History using T-SQL

May 14, 2012 5 comments

To check SQL Server Agent Job History you can use the Log File Viewer from SQL Server Management Studio.

To Open Log File Viewer,

1. Expand Server Node > 2. Expand SQL Server Agent > 3. Expand Jobs > 4. Right click on the Job and 5. Select "View History" as shown in the screen shot below:

image

 

Alternatively, you can also use below T-SQL code to check Job History:

SELECT      [JobName]   = JOB.name,

            [Step]      = HIST.step_id,

            [StepName]  = HIST.step_name,

            [Message]   = HIST.message,

            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

            WHEN HIST.run_status = 1 THEN 'Succeeded'

            WHEN HIST.run_status = 2 THEN 'Retry'

            WHEN HIST.run_status = 3 THEN 'Canceled'

            END,

            [RunDate]   = HIST.run_date,

            [RunTime]   = HIST.run_time,

            [Duration]  = HIST.run_duration

FROM        sysjobs JOB

INNER JOIN  sysjobhistory HIST ON HIST.job_id = JOB.job_id

/* WHERE    JOB.name = 'Job1' */

ORDER BY    HIST.run_date, HIST.run_time

Output:

JobName  Step StepName      Message       Status     RunDate   RunTime   Duration

Job1     1    Step1         Executed a..  Succeeded  20120416  173935    10

Job1     0    (Job outcome) The job su..  Succeeded  20120416  173935    10

Job2     1    Step1         Executed a..  Succeeded  20120416  174037    10

Job2     0    (Job outcome) The job su..  Succeeded  20120416  174037    10

 

Check dbo.sysjobs and dbo.sysjobhistory on BOL for more information.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe

EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Follow

Get every new post delivered to your Inbox.

Join 263 other followers