best way to shrink database file sizes

Posted on 2006-05-10
Last Modified: 2008-01-09
What is the best and safest way to shrink the mdf and ldf files of my databse? I use the following command:

DBCC SHRINKFILE(sfs_dat, truncateonly)
DBCC SHRINKFILE(sfs_log, truncateonly)

I have also used this command, but I think truncateonly is healthier. Is that correct?


I see some people recommend this command. Why the backup log command does that force sqlserver to do some cleanup internally or something?

DBCC SHRINKFILE(<TransactionLogName>, 1)
DBCC SHRINKFILE(<TransactionLogName>, 1)

and final question: Is the only difference between DBCC SHRINKFILE and DBCC SHRINKDATABSE, that DBCC SHRINKDATABASE does all files?
Question by:brokeMyLegBiking
    LVL 75

    Accepted Solution

    From BOL


    Migrates all data from the specified file to other files in the same filegroup. Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.


    Causes the freed file space to be retained in the files.

    When NOTRUNCATE is specified along with target_size, the space freed is not released to the operating system. The only effect of the DBCC SHRINKFILE is to relocate used pages from above the target_size line to the front of the file. When NOTRUNCATE is not specified, all freed file space is returned to the operating system.


    Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.

    > DBCC SHRINKFILE(<TransactionLogName>, 1)
    The above commands are used to reduce the database size.Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file
    LVL 42

    Assisted Solution

    if it is sqls server 2000 and up
    check db options - recovery mode: Full; Simple; BL (read BOL)
    if it is 'FULL' make sure you have regular trans log backup job - it will prevent
    the DB trans log bee too big and reduce shrinking time if you will need ever
    <Is the only difference between DBCC SHRINKFILE and DBCC SHRINKDATABSE, that DBCC SHRINKDATABASE does all files?

    DBCC SHRINKFILE -- does file by file
    Shrinks the size of the specified data file or log file for the related database.
    Shrinks the size of the data files in the specified database.
    <that DBCC SHRINKDATABASE does all files?
    yes, it does (read above)
    LVL 4

    Author Comment

    ok, thx

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now