Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,632,122 hits
  • Select GETDATE()

    June 2019
    M T W T F S S
    « Apr    
     12
    3456789
    10111213141516
    17181920212223
    24252627282930
  • Advertisements

Archive for the ‘SQL Server Agent’ Category

SSMS – FIX – Error – The log file is not using Unicode format

Posted by blakhani on December 11, 2014


While playing with SQL Server Agent Log setting, I changed some setting and found that I was not able to read SQLAgent Logs any more. Here is the error which I was getting in SSMS.

I followed my own blog and executed T-SQL to read the Agent log and got same error.

Msg 22004, Level 16, State 1, Line 13
The log file is not using Unicode format.

I looked around into the properties which I changed and one of them was “Write OEM error log”. As per MSDN documentation, this option writes the error log file as a non-Unicode file. This reduces the amount of disk space consumed by the log file. However, messages that include Unicode data may be more difficult to read when this option is enabled.

And that’s precisely the error message says – “it’s not in unicode”.

There are two ways to fix the error.

Method 1 (UI Based)

  • Connect to Object Explorer to SSMS > Expand “SQL Server Agent” >  Right Click on “Error Logs” and choose “Configure”
  • Over there uncheck the box “Write OEM error log”

Method 2 (T-SQL Based)

Execute below query

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @oem_errorlog=0
GO


Both of the methods have same effect. Once done, restart SQL Agent service so that now the file is written in unicode format. You should be able to view the log in the viewer now.

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in SQL Server, SQL Server Agent, SQL Server Management Studio, SSMS | Tagged: , , , , | Leave a Comment »

    Tips and Tricks : SQL Agent Job history data missing?

    Posted by blakhani on September 18, 2014


    I have many good friend who call me only when they have SQL Server related problem to solve. I really love you guys (in a manly kind of way!) for remembering me, at least during problem time.

    One fine day my DBA friend called me and said that they have a data missing problem. There is a job which is suppose to move the data from one table to another. Application team reported that job didn’t run on one specific time as per their data calculation. As per their logic they pull hourly data from Table1 to Table2. They have observed that 1 hour data is missing. When they looked into job history they don’t see any evidence of job history. Oldest record for this job is 2 hours back.

    My immediate question was – do you have many jobs running on the server? As expected, answer was Yes! They had may such data movement jobs and few of them run every 5 minutes. I asked him to run below command

    select count(*) from msdb.dbo.sysjobhistory

    and answer was 1000. And I said – that is THE problem! The SQL Agent Job system limits the total number of job history on per job basis as well as overall basis. This information is stored in the MSDB database. I asked him to open SQL Server Agent Properties and go to History page. Over there Modify the ‘Maximum job history log size (rows)’ and ‘Maximum job history rows per job’ to suit, or change how historical job data is deleted based on its age.

    I also told that this won’t give you back the history, but it’ll help with your future queries! He was happy with the answer.

    Later I did more research and found that this can also be done using below T-SQL command

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=1000, 
            @jobhistory_max_rows_per_job=100
    GO 

    If there is a need to purge the data manually using UI then we can use “Remove Agent History” checkbox and define the duration. The equivalent of that below

    EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2014-09-14T09:02:37'
    GO
    
    

    We need to provide the oldest date in T-SQL

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server Agent, Tips and Tricks | Tagged: , , , , | 7 Comments »