Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    London England
    Posts
    1

    Unanswered: Avoiding transaction logging - at least reducing it

    I have an application that uses work tables within an SP to generate a recordset output to the caller as the result of a search operation. The SP does not update the main database, and the results, although sizeable, are not saved.

    Clearly I do not need to log the updates to the work tables. They start out empty and the contents could be discarded.

    Does SQL Server recognise this and not log the updates to work tables? Is there any way I can indicate that a table is not significant and need not be logged at all?

    Would it help to put the work tables in a separate database?

    Maybe it would not make much difference anyway?

    Thanks in advance for any help

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Fortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.

    A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by fadace
    Fortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.

    A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !
    Huh? Well I'm glad you didn't become very specific about what you're talking about.

    Veritant:

    You say Search to return result set, then you say update to work table

    SELECTS are not logged

    Creating a result set on the fly, like

    SELECT * INTO #temp FROM...is not logged


    TRUNCATE TABLE or DROP TABLE is not logged..

    If you have some examples of what you're doing and need help, post it and we'll look at it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •