Reducing Allocated Space in LDF File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Reducing Allocated Space in LDF File

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-07 : 21:32:20

The shrink option shows me that I have a database with a log file of 100MB currentsize and 10MB used space.

How do I minimize the current size.

BOL says:
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.

I currently backup the database using the following commands

Full Backup:
BACKUP DATABASE [MyDB] TO DISK = 'c:\Full' WITH INIT , NAME = N'MyDB FullBackup', SKIP , STATS = 10, NOFORMAT

Diff Backup:
BACKUP DATABASE [MyDB] TO DISK = 'c:\Diff' WITH INIT, DIFFERENTIAL, NAME = N'MyDB DiffBackup', SKIP , STATS = 10, NOFORMAT

Log Backup:
BACKUP LOG [MyDB] TO DISK = 'c:\Log' WITH INIT, NAME = N'MyDB Log Backup', SKIP , STATS = 10, NOFORMAT

How do I get the transaction log file to reduce in size.

Do I run a truncate_only after my full backup or dbcc shrinkfile/shrinkdabase?? I'm confused.

What I want is once I have the data backed up to reduce disk space as much as possible.

Thanks in advance.

Here is something I tested.

Started out with a 10MB Database and ~0MB LDF File.
Next I ran a DTS which imports 500,000 rows from access.
Here is what DBCC SQLPERF(LOGSPACE) outputs

Database LogSize SpaceUsed% Status
MyDB 135.55469 51.75388 0

Next I ran DBCC SHRINKDATABASE MyDB and then DBCC SQLPERF(LOGSPACE)
this time I get

Database LogSize SpaceUsed% Status
MyDB .99 33.95 0

1.Does this mean I deleted the log entries?
2.Should I only do this after a full backup or can I do it after a log backup and still have all my data in case I need to recover?
3.What is the difference between using SHRINKDATABASE AND SHRINKFILE.

Edited by - ValterBorges on 11/08/2002 08:46:14

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-11-08 : 12:35:22

If disk space is a problem, then you should consider buying more disk space. You should not run the shrink command after each backup because that means that queries that need to expand the transaction log will suffer in performance. This is because while the query is running, SQL Server is having to expand the transaction log because it is too small. But if for some reason, the transaction log is really big, then consider shrinking the log.

You did not delete the log entries by running those commands. The log gets purged when a backup log command is run and not a shrink command. The SHRINKDATABASE command shrinks the database which includes both data and log files. The SHRINKFILE command gives you more control and allows you to shrink one file at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources