Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooing Skills

Tips and Tricks : Restore Using TRY…CATCH might fail with error

Posted by blakhani on July 22, 2014


Few days back one of my friend called me with an interesting problem. She told that when she does a regular restore of a database backup file, it works but when she does using TRY/CATCH block, it fails. The very first thing which I asked her– What is the error message? She sent me email with below message. I have changed some names to save the innocents.

<EMAIL>

Command which fails.

BEGIN TRY  
    RESTORE DATABASE [TestBackup] 
    FROM  DISK = N'C:\Temp\TestBackup.bak' 
    WITH  FILE = 1,  
    MOVE N'TestBackup' TO N'C:\Temp\TestBackup.mdf',  
    MOVE N'TestBackup_log' TO N'C:\Temp\TestBackup_log.LDF',  
    NOUNLOAD,  
    STATS = 5; 
END TRY 
BEGIN CATCH  
             THROW; 
END CATCH  

Here is the output

52 percent processed.

100 percent processed.

Processed 240 pages for database 'TestBackup', file 'TestBackup' on file 1.

Processed 2 pages for database 'TestBackup', file 'TestBackup_log' on file 1.

Converting database 'TestBackup' from version 611 to the current version 706.

Database 'TestBackup' running the upgrade step from version 611 to version 621.

Database 'TestBackup' running the upgrade step from version 621 to version 622.

Database 'TestBackup' running the upgrade step from version 622 to version 625.

Database 'TestBackup' running the upgrade step from version 625 to version 626.

Database 'TestBackup' running the upgrade step from version 626 to version 627.

Database 'TestBackup' running the upgrade step from version 627 to version 628.

Database 'TestBackup' running the upgrade step from version 628 to version 629.

Msg 102, Level 15, State 1, Procedure usp_logError, Line 11

Incorrect syntax near ‘200001’.

Msg 3013, Level 16, State 1, Line 11

RESTORE DATABASE is terminating abnormally

Command which works

RESTORE DATABASE [TestBackup] 
FROM  DISK = N'C:\Temp\TestBackup.bak' 
WITH  FILE = 1,  
MOVE N'TestBackup' TO N'C:\Temp\TestBackup.mdf',  
MOVE N'TestBackup_log' TO N'C:\Temp\TestBackup_log.LDF',  
NOUNLOAD,  
STATS = 5;

There is NO difference in restore command. Only difference is that it is wrapped in TRY block. If any exception raised, it would be caught and thrown.

<EMAIL>

Since I see version upgrade messages I asked if backup was taken on lower version and restore is performed on higher version? She informed that this backup is taken on SQL Server 2005 and she wants to restore on SQL Server 2012. I also asked her to share ERRORLOG and here is the snippet

2014-07-17 17:43:41.070 spid56 Error: 928, Severity: 20, State: 1.

2014-07-17 17:43:41.070 spid56 During upgrade, database raised exception 102, severity 25, state 1, address 00007FFCE2DC3E04. Use the exception number to determine the cause.


Exception 102 is “Incorrect Syntax” error. If we read the message in restore output carefully, we can see that there is a procedure usp_logError which seems to be a problem. I asked her to look at the code of usp_logError and search for 200001 and she found below:

raiserror 200001 'Debit and Credit Mismatch'

If we execute above piece of code in SQL 2005, it works and it’s valid whereas, it gives syntax error in SQL 2012. I looked into documentation and found below at http://msdn.microsoft.com/en-us/library/ms144262.aspx (Discontinued Database Engine Functionality in SQL Server 2014)

Transact-SQL

RAISERROR in the format RAISERROR integer 'string' is discontinued.

Rewrite the statement using the current RAISERROR(…) syntax.

 

I asked her to change the syntax and modify the stored procedure before taking backup. Once done, the restore with and without TRY CATCH worked like a charm.

You can easily reproduce this error by creating below database and procedure in SQL 2005.

Create database TestBackup 
go 
use TestBackup 
go 
Create Procedure FailureProc 
as 
begin 
RAISERROR 20001 'ERROR RAISED' 
end 

Restoring this backup in SQL 2012 using TRY CATCH would fail.

Tip: Whenever I see any “Error: 928, Severity: 20, State: 1.” I always look for the exception code. In this case it was 102. To convert code to text, we can use sys.messages catalog view (check image below) . While searching further, I was able to find below variations.

2014-07-17 17:43:41.070 spid56 Error: 928, Severity: 20, State: 1.

2014-07-17 17:43:41.070 spid56 During upgrade, database raised exception 156, severity 25, state 1, address 00007FFCE2DC3E04. Use the exception number to determine the cause.


image

Error 156 - Incorrect syntax near the keyword '%.*ls'.

There might be many exceptions raised even without try…catch, you need to read the message and look for explanation of exception code.

You might ask, What should be done before upgrade to avoid such situations? You should run upgrade advisor. http://msdn.microsoft.com/en-us/library/ee210467.aspx (SQL Server 2014 Upgrade Advisor). There are versions available for SQL 2012 and earlier as well. They mostly catch all errors which might come after upgrade. Interestingly, RAISERROR problem is not caught by upgrade advisor.

http://connect.microsoft.com/SQLServer/feedback/details/708167/somewhat-incompatible-t-sql-on-sql-server-2012-rc0

http://connect.microsoft.com/SQLServer/feedback/details/694484/denali-ctp3-upgrade-advisor-misses-deprecated-raiserror-syntax

You can vote up if you think it would help.

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

    Script : Find Currently executing Queries, Blocking, Waits, Statement, Procedure, CPU

    Posted by blakhani on July 17, 2014


    This  is one of my all time favorite script which I use most of the time in troubleshooting performance issues on live systems. This helps in findings below:

    • What is the performance bottleneck?
    • Is there any blocking? If yes, who is the blocker?
    • What are the queries which are executing currently?
    • What is the name of the stored procedure running currently?
    • Which statement in stored procedure is getting executed right now?
    • Who is consuming CPU right now? What are the high CPU queries?
    • Who is doing lots of IO right now?

    Column heading is self-explanatory. You need to adjust ORDER BY clause based on your need. In below query, I am showing top CPU consumers at the top.

    SELECT s.session_id
        ,r.STATUS
        ,r.blocking_session_id 'blocked by'
        ,r.wait_type
        ,wait_resource
        ,r.wait_time / (1000.0) 'Wait Time (in Sec)'
        ,r.cpu_time
        ,r.logical_reads
        ,r.reads
        ,r.writes
        ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
        ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
                (
                    CASE r.statement_end_offset
                        WHEN - 1
                            THEN Datalength(st.TEXT)
                        ELSE r.statement_end_offset
                        END - r.statement_start_offset
                    ) / 2
                ) + 1) AS statement_text
        ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + 
    Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text ,r.command ,s.login_name ,s.host_name ,s.program_name ,s.host_process_id ,s.last_request_end_time ,s.login_time ,r.open_transaction_count FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id

    Here is the partial output

    image

    Note that since it shows currently executing request, it won’t show the request which are not doing anything like sleeping connection or awaiting command.

    Hope this helps

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

    Tips and Tricks : Delete Complete History and Settings of Management Studio

    Posted by blakhani on July 15, 2014


    How many times you have browsed internet and cleared complete history of Internet Explorer (or any other browser)? I have done it many times and due to various reasons. Few days back one of my friend asked that how can same thing be achieved in SQL Server 2014 Management Studio? His goal was to delete all the saved setting of management studio for his login. This would include:

    • Server List and Login credentials saved on login screen.
    • History of reports launched – (Read Pinal’s blogs)
    • Settings change in Object Explorer Details like sorting and ordering of columns.
    • All setting which are saved under Tools > Options in menu bar.

    (This does NOT effect registered severs list)

    This all information is saved under file SQLStudio.bin.

    Below is the file in SQL 2012 Management Studio. (Go to start>run> and paste below path)

    %appdata%\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin

    Below is the file in SQL 2014 Management Studio.

    %appdata%\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin

    image

    In earlier version of SQL Server (before SQL 2012), the path was different. Refer Jonathan’s blog

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server Management Studio, SSMS | Tagged: , , , , , | Leave a Comment »

     
    Follow

    Get every new post delivered to your Inbox.

    Join 764 other followers