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 |
|
cbrown83
Starting Member
2 Posts |
 Posted - 2008-11-21 : 09:28:00
|
| Hello,I'm trying to free up space on our server and noticed that I had a 50 gb LDF file. Under my database options, I had "Full" recovery model selected. This morning, I have changed it to "Simple" - We do nightly backups of the entire database. However, now I simply wish to get rid of this huge LDF file. Can I simply select it in the directory and delete it? Or is there a formal procedure I need to go through in order to eliminate it? Thank you. |
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-11-21 : 09:49:18
|
| You can't delete you transaction log file because it is necessary for your database to work. You should shrink your LDF-file.If it is a heavily used database, schedule a job where you use dbcc shrinkfile. |
 |
|
|
cbrown83
Starting Member
2 Posts |
Posted - 2008-11-21 : 10:07:23
|
| Carat,How much will the shrink actually decrease the size though? Is there anyway to return the LDF file to its pre-Full Recovery Model state? The database is only used during our business hours from 8am to 5pm.Thanks for your help. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-11-21 : 10:15:17
|
If you just run DBCC SHRINKFILE(2) it will shrink it as small as it can. Be aware that the log is a sequential stream of records. If active transactions are present the file cannot be shrunk to a point before those transactions. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-21 : 10:24:22
|
| 1)You should search as there are lots of post regarding this issue.2)You should never delete Log file as it can leave your database corrupt.3)In Full Recovery model, the key issue is to backup transaction log frequently like every 15-30 mins. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-11-21 : 10:44:05
|
quote:
3)In Full Recovery model, the key issue is to backup transaction log frequently like every 15-30 mins.
They don't have to be that frequent, but yes, you need to do periodic log backups if you're in full recovery mode. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-21 : 12:45:04
|
quote:
Originally posted by robvolk
quote:
3)In Full Recovery model, the key issue is to backup transaction log frequently like every 15-30 mins.
They don't have to be that frequent, but yes, you need to do periodic log backups if you're in full recovery mode.
Depends on how critical data is. Agreed ,it doesn't have to be like that frequent if transactions are less. |
|
|
|
|
|
|
|
|
LDF Files - How do I remove?