Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,436,001 hits
  • Select GETDATE()

    July 2018
    M T W T F S S
    « Apr    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  
  • Advertisements

Posts Tagged ‘State 1’

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 »

    Troubleshooting : Msg 3201 – Cannot open backup device .. Operating system error 3(The system cannot find the path specified.)

    Posted by blakhani on March 13, 2014


    Data recovery is a time consuming and expensive process. Taking regular backups can save a lot of time as well as make sure that the data can be restored in the case of disaster. There could be many situation where backup of the database taken in SQL Server might fail with 3201 error. The text of error id as below (you can use sys.messages catalog view to get it)

    Cannot open backup device ‘%ls’. Operating system error %ls.

    Same message is applicable for restore as well, it’s all about unable to open device. If we notice %ls is placeholder which would be inserted when message is raised. Some sample errors are as below.

    Message # 1

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device ‘E:\Backup\SQLServerHelp.bak’. Operating system error 3(The system cannot find the path specified.).

    Cause: Backup folder not created on E Drive.

    Message # 2

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device ‘C:\SQLServerHelp.bak’. Operating system error 5(Access is denied.).

    Cause: SQL Server Service account is not having permission on root of C Drive.

    Message # 3

    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device ‘\\NetworkShare\Backup\SQLServerHelp.BAK’. Operating system error 53(error not found).

    This one is interesting because it doesn’t tell the exact message for OS error 53. Sometime this could happen with any other OS error as well. Refer my earlier post here where I showed how to get text for an operating system error number. 53 = The network path was not found.

    Cause: Network Path: \\NetworkShare\Backup was incorrect.

    OK. Let me stop here.. I didn’t plan to discuss all the possible errors but this blog is to present one interesting scenario which helped me uncovering an operating system concept.

    Let’s create a database using below script.

    Create Database [SQL Server Help ]

    Once database is created I have created maintenance plan to take full backup of the database.

    image

    When I executed above maintenance plan, it failed with error:

    image

    TITLE: Execute Maintenance Plan

    ——————————

    Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.

    ——————————

    ADDITIONAL INFORMATION:

    Job ‘SQLServerHelp.Subplan_1’ failed. (SqlManagerUI)

    ——————————

    BUTTONS:

    OK

    ——————————

    If we look at the folder, the folder got created but backup failed. Looked into ERRORLOG and found below

    2014-03-13 06:20:31.66 spid65      Error: 18204, Severity: 16, State: 1.

    2014-03-13 06:20:31.66 spid65      BackupDiskFile::CreateMedia: Backup device ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL Server Help \SQL Server Help _backup_2014_03_13_062031_6415729.bak’ failed to create. Operating system error 3(The system cannot find the path specified.).

    2014-03-13 06:20:31.66 Backup      Error: 3041, Severity: 16, State: 1.

    2014-03-13 06:20:31.66 Backup      BACKUP failed to complete the command BACKUP DATABASE SQL Server Help . Check the backup application log for detailed messages.

    when I looked at folder structure, I found that there is a folder “SQL Server Help” got created under “E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup” but still backup was failing. I ran profiler to see the command being fired.

    BACKUP DATABASE [SQL Server Help ] 
    TO  DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL Server Help \SQL Server Help _backup_2014_03_13_062920_6305771.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'SQL Server Help _backup_2014_03_13_062920_6305771', 
    SKIP, REWIND, NOUNLOAD,  STATS = 10
    

     

    When I executed the command, it failed with below error.

    Msg 3201, Level 16, State 1, Line 1

    Cannot open backup device ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL Server Help \SQL Server Help _backup_2014_03_13_062920_6305771.bak’. Operating system error 3(The system cannot find the path specified.).

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.


    Again, something wrong with the path where I am taking backup. Do you see any problem in above message. Look closer.. there is a space at the end in the folder name where backup is attempted. “SQL Server Help “. If you try to create a folder with the whitespace at the end, operating system would trim the space at the end. This is documented here

    The root cause of my problem was name of the database where I have added an space at the end intentionally. Go back and have a look at create database statement which I have used. I also participated in this discussion where same problem was posted long back.

    Hope you have learned something new!

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