Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Compacting a db via T-SQL

    Hello dbForumers,

    I was wondering if there is a way (MUST be) to instruct SQL server to compact a chosen database's files. I have a batch that runs every night who generate a huge amount to log lines that I get rid of with a backup log xxx with truncate_only, but still the logfile is several GB big afterwards with a lotsa empty space... I wanna get a clean small file everyday =)

    Thank you!

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Its not ideal to shrink the Transaction log every day, rather define a set of size by testing the activity on the database.

    During that batch overnight take before and after sizes for Tlog and set the higher level.

    Also maintain regular backups of Tlogs which will reduce the size of logical file and helps to fillup the Tlog quickly. If RECOVERY MODEL Is set to SIMPLE then ensure full database backups are carried in regular intervals.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    try DBCC SHRINKFILE

    USE UserDB
    GO
    DBCC SHRINKFILE (logical file name,Target_size)
    GO

    Target_size is how much free size is left over after you shrink
    if you leave this off, you will get the default.

    also
    you may have to switch the VLogs internally before you can shrink to a size that you desire...
    all of his should be done after a tlog backup.
    look up "Shrinking the Transaction Log" AND DBCC Shrinkfile in [BOL]

Posting Permissions

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