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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-02-03 : 08:14:45
|
| Paresh writes "Hello . I am using SQL server 2000 my database mdf file is 223 mb and LDF file is 4.85 gb . I want to compress LDF file .It is growing rapidly how to decrease the growth of ldf file .Please Give me solution to compress the ldf file of my database.thank U" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-03 : 08:16:52
|
| 1. Truncate the log with the following:BACKUP LOG myDatabase WITH NO_LOG2. Use DBCC SHRINKFILE to shrink it:USE myDatabaseDBCC SHRINKFILE(2, 250) --this will shrink the log to 250 MBSee Books Online for more details. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-03 : 12:34:58
|
| And if you don't need FULL recovery model, switch it to SIMPLE if you aren't going to be performing backups of the transaction log.Tara |
|
|
|
HendersonToo
Starting Member
20 Posts |
Posted - 2004-02-04 : 12:33:32
|
| I have to recommend leaving your recovery model intact; and setting up at the minimum a 7-day rolling backup. It doesn't even have to backup to another server. I would recommend you store the backup files to another physical disk though. SQL Server has such a nice UI for setting up automated backups, its just too easy. You'll love being able to restore to that daily snapshot if your database gets corrupted or the disk crashes.In enterprise manager, explore management ->Database Maintanence Plans. They make it really easy. Enabling both a data file and transaction log file backup will by default shrink your transaction log on a regular basis. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-04 : 17:23:08
|
quote:
Originally posted by HendersonToo
I have to recommend leaving your recovery model intact
That's why I said if you don't need FULL recovery model. Some databases do not require the ability to restore to a point in time. Some can afford to lose all of the data since the last full backup. It just all depends on your requirements. FULL is not right for each situation. For most production systems though, FULL is what you need.Tara |
|
|
|
|
|
|
LDF file Compressing