SQL Server Performance

Transaction Logs

Discussion in 'Performance Tuning for DBAs' started by jameshuff, Jan 24, 2003.

  1. jameshuff New Member

    I am fairly new to SQL Server 2K, and need a question answered.

    We currently run SQL Server 2K with Full Transaction Logging, but we use a front end that mixes "DOS" and Windows utilities. (I know, it was like this when I started). Anyways, we use a full system backup nightly to backup our SQL Server to tape, as it is the only way to keep the DOS and SQL transactions in sync.

    One of our developers states that we can not disable the Transaction logging, as it will mess up the files and groups that they have.

    I am familiar with Oracle, and we changed the Rollback Segments on an 80,000 user - AIX database to enhance backups for restore capabilities, and never experienced problems.

    Two questions:

    Since we have a financial transaction tables I'll call 'Bill-tran' that we need to keep valid, is there a way to Export/Import the Bill-tran tables or have the Transaction Logs only process the commits to these tables/files?

    Second question: Is there a better way (other than migrating all the DOS stuff to Windows, (We are working on that)) of doing the backups.

    We currently have the databases split into 6 databases so we can backup the 'Bill-tran' seperately, but the administration is a nightmare. We are planning on merging all 6 databases into one, and I need a good backup plan for the 'Bill-tran' tables.

    Do I need to have Full Logging if we have to use the tape restore due to the 'DOS' records. Or can I pull just the 'Bill-tran' tables out of the transaction logs.

    Can someone tell me where I can find this anser?

    Thank you.

  2. tkelley New Member

    In response to your first question: Yes, you can restore your 'Bill-tran' table from the latest full backup and then apply the transaction logs from the time of your last backup. If you want a current copy of 'Bill-tran' to another database, you can export it on demand at the end of the work day. That help?

    Don't have a clue about anything DOS.

    T Kelley

  3. satya Moderator

    What is the recovery model setup for the database currently, you can choose SIMPLE recovery model which will truncate the Tlogs, but you should maintain regular full database backup. Refer to other recovery modlels from books online.

    Satya SKJ
  4. jameshuff New Member

    Thank you tkelley and satya:

    First answer to satya, Our current database model for recovery model is defined in the Maintenance plan out of Enterprise Manager as follows (again I am new at this, so hang in there):
    General Tab: All User Database
    Optimize Tab: Reorganize Data and Index Pages
    Change free space per page percentage to:10
    Remove unused space from Database Files
    Shrink when above 50MB
    Amount of free space after shrink: 10%
    Integrety Tab: Check Database Integrety - Include Indexes - Attempt to repair minor problems.
    Complete Backup Tab: Backup database as part of Maintenance Plan - Disk:
    Default backup directory
    Create subdirectory for each database
    Remove files older than 2 days
    Transaction Log Backup Tab: Backup Transaction Log as part of the Maintenance Plan
    Default backup directory
    Create subdirectory for each database
    Remove files older than 2 days
    Reporting Tab:
    Write report to Text File
    Delete text reports older than 1 week.
    Write history to the table
    Limit rows in the table to 1000.

    tkelley: Any idea where I can find information on the Import/Export functions, especially by query?
    The SQL Server 2000 Resource Kit and the SQL Server 2000 Reference Library were worthless on this topic.

    Thanks again.

  5. satya Moderator

    It assumes the recovery model is FULL and how you're dealing with backups to the tape. You can use DTS or BCP to import/export the data and also by query basis.

    Refer to books online for more information.

    Satya SKJ
  6. jameshuff New Member

    Thank you Satya.


  7. royv New Member

    If you have the ability to buy 3rd party software, SQL Lite Speed has been reviewed on this website and claims to have reduced size and speed of backup operations.

    "How do you expect to beat me when I am forever?"
  8. satya Moderator


    Still you need to confirm about handling backup to the tape, which will give bit insight to resolve the issue. If you're interested to depoly third party tools refer to Royv's tip which is reliable product with enhanced utilities.


    Satya SKJ
  9. jameshuff New Member

    Thanks again, I will look into SQL Lite Speed.


Share This Page