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.