mibuso.com
Home› SQL General
Options

SQL Report locks NAV Table

gob_Behnkegob_Behnke Member Posts: 14
edited 2010-10-07 in SQL General
Hi,

actually I have a customer that has problem with a sql report (SQL Server 2008) that locks a table in MS Dynamics NAV 2009.

Although the report is quite complex, it only reads records from the database, there is no write process (tested it with SQL Profiler),
thus I don't know why the table gets locked.

Is there a way to solve this issue.


Any help is greatly appreciated

Kind regards

Answers

  • Options
    bbrownbbrown Member Posts: 3,268
    The default isolation level is "Read Committed" which will issue shared locks.
    There are no bugs - only undocumented features.
  • Options
    bbrownbbrown Member Posts: 3,268
    I read this to understand that you are talking about a report using a SQL tool external to NAV. Correct?
    There are no bugs - only undocumented features.
  • Options
    gob_Behnkegob_Behnke Member Posts: 14
    Thanks for you fast answer.

    Yes, that's correct.

    The tool I use is Microsoft SQL Server Reporting Services.
    It's part of Microsoft SQL Server, so I think it is a quite common tool.

    How can I change the default isolation level via SQL?
  • Options
    bbrownbbrown Member Posts: 3,268
    You might consider turning on Read Committed Snapshot Isolation for the database. But be sure you have TempDB on a different disk (with enough space) from the NAV databasea and log files. Also have 1 tempDB file for each processor core (up to 8 ).
    There are no bugs - only undocumented features.
  • Options
    gob_Behnkegob_Behnke Member Posts: 14
    Isn't there a way to change the isolation level?

    I can't change anything about the target database.
  • Options
    bbrownbbrown Member Posts: 3,268
    You can change the level for a given session using the "SET TRANSACTION ISOLATION LEVEL" T-SQL command. But I suggest you read the definition of each level before deciding on one to use. Each has its pros and cons.

    Rsther then doing this, I'd suggest taking a look at redesigning the report to reduce the lock granularity and durations
    There are no bugs - only undocumented features.
  • Options
    gob_Behnkegob_Behnke Member Posts: 14
    Thanks, I guess I can't change the report itself (without great effort), but
    the "SET TRANSACTION ISOLATION LEVEL" - Command solved the issue.

    As far as I understood the command, the only disadvantage are dirty reads,
    but this is no problem.

    Thanks a lot.
Sign In or Register to comment.