Debugging SQL Server

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716639

    Comments posted to this topic are about the item Debugging SQL Server

  • sanjarani

    SSC Veteran

    Points: 259

    As yet I haven't found a descent debugging tool for either SQL Server or ORACLE and I have been developing in both systems for some times.

    For SQL debugging I use print to display variable values.

    For SSIS I use script tasks to display variables.

    In SQL Server, there is a debugging tool similar to .NET where one can put a break point and step through the code. However I don't find it as powerful as that in .NET and as a developer I use the tried and tested method, print, that I have relied on for so many years.

    Look forward to suggestions from other readers.

  • Steve Robb

    Default port

    Points: 1401

    In SQL I use the Print statement if I need to check one value and if I need more, for example to see what it will insert or what it has deleted, I will insert into a temp table and check from there. I also insert a standard comment line above each one so I can search through a long procedure and remove or comment out the debug code when happy with it all.

  • Dervey

    SSC Enthusiast

    Points: 187

    With SQL Server I'm definitely a 'print'-er. Obviously if writing SQLs in an IDE for .NET then can do quite a bit of debugging in there too.

    For stored procedure debugging I find the RAISEERROR WITH NOWAIT statement useful too

  • edward_jeba

    SSC Veteran

    Points: 241

    This is a very good topic and look forward to learning other methods to debug SQL SPs.

    I am a "Print a comment" debugger and recently using Raise Error to do similar messages.

    For SSIS I use the Script Task with a message box.

  • call.copse

    SSCoach

    Points: 16922

    I'm still a PRINT and temp table guy I'm afraid :blush: feels quite stone age but you know where you stand.

    Debugging applications as a developer I tend to use the profiler on the dev system a lot to be sure of exactly what query is being passed, and go from there with the aforementioned techniques.

  • Todd Townley

    Ten Centuries

    Points: 1318

    PRINT and temp tables +1

  • evgeny.marmalstein

    Grasshopper

    Points: 13

    But. if log to temp table and rollback occurs, then you lost the log. And during rollbacks the most important logs.

  • shoestringdba

    SSCertifiable

    Points: 6206

    A combination of PRINT, logging to a table and even raiserror depending on what I'm investigating and where the whole project is in the process.

    Then there's always examining the execution plans. Grant did an excellent presentation on this at SQL Saturday in DC this past year and some of his tips have been very handy in unraveling exactly what your SQL is trying to do.

    ____________
    Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    evgeny.marmalstein (1/16/2015)


    But. if log to temp table and rollback occurs, then you lost the log. And during rollbacks the most important logs.

    Log to a table variable and then, after any rollbacks could have occured copy to temp table if necessary for querying, or just select from...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail Shaw

    SSC Guru

    Points: 1004446

    call.copse (1/16/2015)


    I'm still a PRINT and temp table guy I'm afraid :blush: feels quite stone age but you know where you stand.

    Stone age, but it works. I'm there too. Print, raiserror (where necessary), temp tables/table variables, stats IO/Time and execution plans

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Michael Meierruth

    SSChampion

    Points: 10051

    I'm using a general purpose logging table where any part of the application (backend or frontend) can log to. I'm curious to know what your table(s) look like. Naturally, the columns that are filled by the code that registers an event varies. It depends...

    create table myapp_eventlog

    (

    eventid int identity(1,1),

    process_name varchar(50),

    computer_name varchar(50),

    network_user varchar(50),

    app_user varchar(50),

    time_started datetime,

    time_ended datetime,

    elapsed_minutes decimal(10,2),

    event_info1 varchar(1000),

    event_info2 varchar(1000),

    event_info3 varchar(1000)

    )

  • tamie.mcdonald

    SSC Veteran

    Points: 227

    I will write values to a debug_table to debug processing within a trigger. ex. create debug_table(code varchar(20), desc varchar(254))

    For stored procs, for simple debugging I use a print.

    For more complex debugging of stored procs, I will convert the proc into a script and run it in sections. Plus I use select statements throughout to follow the logic.

    Example:

    select '1', @param1, @param2, @param3

    select '2', * from #temp_table

    select '3', col1, col2, col3 from table1 t1, table2 t2 where ...

  • Megistal

    SSCrazy Eights

    Points: 8787

    I was excited at first to read what others used (to learn new trick) but I feel we mainly all rely on the same ways to debug.

    When I had the need to use the SQL debugger, it's mainly because the underlying code "was the issue" (not set based but coded if it was a .Net application, line by line). In that case it helped a bit.

    Otherwise, almost all of the time it's print, temp / var tables, raiserror or sometimes profiler when too many SP / statements are involved.

    In short whatever I feel will get me to the problematic point the fastest possible.

    Printing stuff to debug feel it is the same strategy I used when I was learning to debug batch file under old DOS 5 & 6.xx way back in the past... (But SQL has a fancier GUI instead of the 80x25 DOS txt editor Yeah !)

  • Sqlraider

    SSCertifiable

    Points: 5038

    Print

    Temp tables

    Output with the Merge statement along with rollback

    Depends on the situation.

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply