RE: Shrink File?

From: Baisong Wei[MSFT] (v-baiwei_at_online.microsoft.com)
Date: 03/12/04

  • Next message: Hilary Cotter: "Re: Merge Agent error number 53 for replication" 
    Date: Fri, 12 Mar 2004 02:13:32 GMT

    Hi Joshua,

    Thank you for using the newsgroup. It my please to help you with your
    issue.

    As my understanding of you problem, you have a database with a data file
    (.MDF) small (28M) while the LDF huge (7G), you want to shrink the log
    file. right?

    Based on my experience, it is normal that your log file is much larger that
    the data file. For example, when your database is in a full recovery mode
    and you use bcp to insert many data into it. All the actions will be saved
    in the log file.

    There is some ways for you to shrink the log file size.

    You can truncate only nonactive portion of the transaction log. If your
    transaction log is very big and full, and you want to truncate transaction
    log, try the following:

    1. Make the full database backup.
    2. Set the Truncate Log On Checkpoint database option and then run
    CHECKPOINT command from the Query Analyzer.
    3. If the transaction log was not truncated, run the DUMP TRANSACTION
    command with NO_LOG parameter.
    4. If the log was truncated, you can decrease the size of the log file by
    using the DBCC SHRINKFILE statement.

    If the transaction log was not truncated, and the database have only one
    data file, detach the database by using the sp_detach_db stored procedure,
    then attach only the data file by using the sp_attach_single_file_db stored
    procedure. The transaction log will be recreated automatically with the
    small size. This new log file will be about 500KB.

    Please refer to some information in the SQL Server Books Online:
    'Recovery Mode', 'DUMP TRANSACTION', 'DBCC SHRINKFILE', 'SP_DETACH_DB',
    'SP_ATTATCH_SINGLE_FILE_DB'

    Hope this helps. If you still have questions, please feel free to post your
    message here and I am glad to help.

    Thanks.

    Sincerely Yours

    Baisong Wei
    Microsoft Online Support
    ----------------------------------------------------
    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
    Please reply to newsgroups only. Thanks.

  • Next message: Hilary Cotter: "Re: Merge Agent error number 53 for replication"

    Relevant Pages

    • Re: optimum log file size
      ... Fixing of Transaction log file based up on the amount of bulk transaction ... CUrrent log growth will be because of FULL recovery mode. ... If you do not require a point in time recovery or if your database ...
      (microsoft.public.sqlserver.clients)
    • RE: SPS 2003 transaction log question
      ... It is a SQL question, ... you truncate the sql transaction logs ... set the recovery model of the database to “Simple”. ... This truncates the transaction log. ...
      (microsoft.public.sharepoint.portalserver)
    • RE: Backup in maintenance plan
      ... I selected "Back up the transaction log of the database as part of the ... To ensure an efficient and effective backup and recovery strategy for your ... The transaction log should not be backed up if the truncate log on ...
      (microsoft.public.sqlserver.programming)
    • Transaction log problems
      ... just before you backup the ... database change the recovery model to ... Simple, shrink the log file then change it back to Full, ... >transaction log and it is causing us some problems. ...
      (microsoft.public.sqlserver.server)
    • Re: DB export/Import looses users - SQL7
      ... log file. ... the database before you delete the DB. ... choose "All Tasks" and then "Generate SQL Script". ... >>> transaction log space), all users are dropped, and have ...
      (microsoft.public.sqlserver.security)
    Loading