Results 1 to 9 of 9
  1. #1
    Join Date
    May 2002
    Posts
    73

    Unanswered: transactional log

    What if after truncation/backup action I shrink the file?
    What will happen after this in case of database recovery?

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Red face

    As a broad, sweeping generalization, "don't shrink the file. Don't bother."

    Databases are designed to be self-maintaining structures, and they are. Let them! They will recycle space on their own (although they don't shrink the size of the file .. an expensive operation on most filesystems), and they'll maintain their indexes on their own. No, they will never keep the file "as small as it could be," nor the indexes "as optimized as they could be," but they will keep their own house in order. Let them.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    May 2002
    Posts
    73

    transactional log

    So what you suggest?
    My database transactional log keeps getting full due high insert/update/delete activity over it.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by sundialsvcs
    As a broad, sweeping generalization, "don't shrink the file. Don't bother."
    In other words ... a broad sweeping generalization ... there is no need for DBA's.

    No, they will never keep the file "as small as it could be," nor the indexes "as optimized as they could be," but they will keep their own house in order. Let them.
    and dont pay any attention to the people shouting that the database has crashed.
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Re: transactional log

    Originally posted by jeremas
    So what you suggest?
    My database transactional log keeps getting full due high insert/update/delete activity over it.
    Code:
    sp_dboption YourDB, "trunc log on checkpoint", true

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "What if after truncation/backup action I shrink the file?
    What will happen after this in case of database recovery?"

    Nothing will happen. As a matter of fact, Microsoft recommends that you truncate the log prior to shrinking the file.

    What happens if you don't shrink files? Large transactions can cause the files to bloat significantly, and while this space may be reused by SQL server for later operations it won't be recovered automatically unless the autoshrink option is set. I'd strongly suspect that these large files (which may be several times the size of the actual data) increase the time required for data access and reduce the amount of space available on disk for other system processes such as disk-cacheing.

    But hey, that's not important is it?

    Sundial, isn't there a forum for making payrolls that you can join?

    blindman

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "sp_dboption YourDB, "trunc log on checkpoint", true"

    Do not do this unless you don't need point-in-time recovery. With this option you can only recover to your last full backup.

    blindman

  8. #8
    Join Date
    May 2002
    Posts
    73

    transactional log

    blindman, thanks for your help,
    could you explain with more details what I can achieve if I use sp_dboption my_db, trunc log on checkpoint, true?

    How I run it? Using SQL Query Analyzer?

    If I understood well, it could be an option for me as my db keeps the records for a day as maximum, having very high insert/delete activity...

    Regards,

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can achieve this by putting your database in what is known as "Simple Recovery Mode". If your database has high insert/delete activity then I would recommend against it. It means that if you backup your database at midnight and then your system crashes at 5 pm, you have lost an entire day's worth of transactions. Do you have a strategy for recreating them?

    In a normal recovery mode you backup the transaction log (maybe every hour or so) so that you can at least recover to that point, and unless your system gets really hosed you can also recover un-truncated transactions right up to the point in time that the crash occured.

    You can't do this if your database is in simple recovery mode. Simple recovery is normally used for development databases, and not on production systems.

    blindman

Posting Permissions

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