No announcement yet.

How to shrink MS SQL database from MS SQL Server 2005?


Unconfigured Ad Widget

  • Filter
  • Time
  • Show
Clear All
new posts

  • How to shrink MS SQL database from MS SQL Server 2005?

    One may need to follow below mentioned steps to shrink the MS SQL Server 2005 Database:

    1. Open SQL Server Management Studio.
    2. Select the Server Type as Database Engine.
    3. Select the Server Name:,1533
    4. Select the Authentication as: SQL Server Authentication.
    5. Enter the Username and Password.
    6. Click on Connect Button.
    7. Expand Databases from the left hand side.
    8. Right Click on your database.
    9. Select Task as shown in below figure.

    10. Click on Shrink.
    11. Click on Files.

    12. Select the option “Reorganize pages before releasing unused space”.
    13. Enter the value (e.g. 2 M in Shrink File to.

    14. Click on Ok button.

  • #2
    Re: How to shrink MS SQL database from MS SQL Server 2005?

    Thanks your information.
    My openion As a rough guideline your database's transaction log size should be about 25% of the size of the combined total of your database's data files. If the log is significantly larger, and is causing you problems, then you can always backup the log more regularly to keep its size down if you wish.

    The log is not truncated when you perform either a full or differential backup. To see this for yourself run PerfMon and add the following counter, choosing one of your databases to monitor:

    SQL Server - Log -> Log Space Used (%)

    Watch the counter and perform a full database backup - you should see no drop in the counter's value. Perform a log backup and the counter's value should drop.

    Confusingly in SQL Server 2005 if you create a new database then perform a full backup then the log appears to be truncated for the first backup only, however if you take a log backup before the first full backup then subsequent full backups do not truncate the log.

    Shrinking the log file every day is not necessarily a good thing as the constant growth and shrinkage of the file can cause fragmentation on disk. It is generally better to set the log (or shrink it) to a realistic size and then perform more frequent log backups to manage the space usage within the log file. Backing-up the log more frequently also means that you will lose less data in the event of a disaster.