Question on a large .LDF file

Ok...I'll let you know up front that I know pretty much nothing about SQL.

Here's my dilema. I've got a db being used on a server. It's got an MDF and an LDF. I've figured out that the latter is (I think) a log file while the former is the actual database.

The MDF is around 560MB and the LDF is around 1.9 GB. What I want to know is if there is any (fairly simple) way to compress/truncate/reduce the size of that 1.9 gig LDF file.

Any info anyone can offer would be appreciated.

Thanks!See: http://dbforums.com/showthread.php?threadid=561221|||DBA's solution is a good one-time fix and one that I have used before. For an ongoing solution, you will need to check a few things:

1. What's your business requirement? Up to the point of failure recovery, or something less than that?

2. What backup media options do you have available? Tape? Disk?

Depending on your answers to 1 and 2, you will need to create a backup strategy for the database. I suspect from your initial problem statement that you do not have database backups (aka dumps) enabled. I further suspect that the database is set for full transaction logging. See note below

See the SQL books on line, or just try walking through the Database Maintenance wizard.

To give you an idea, we have three strategies in our organization:

1. Transaction Logging. Where we backup the database and ship the log files over to another server for a "warm spare" capability. This is expensive and requires a fair knowledge of backups and SQL Administration.

2. Full and Transaction Log Backup to Disk. We have sufficient disk space on a separate partition, we do full backups daily and transaction log backups every hour. The backups are all stored to disk for 24 hours. In that time, they are copied to tape for archive retrieval. A variant is to back everything up directly to tape, but that is slow and may impact SQL performance.

3. Simple Recovery. A few of our databases (mostly development databases) do not need point-in-time recovery capability. These are set up to use Simple logging. This keeps the transaction log (.LDF file) nice and small and simplifies the backup plan (we back the full database once daily). But you can only restore the database to the last time it was fully backed up (meaning wwe could potentially lose 24 hours' worth of work).

Note: If I am wrong about the database backups not being enabled, then I humbly apologize. It's just that that's how I learned my lesson (the hard way). If you do have backup plans enabled, you might investigate shrinking the amount of time between transaction log backups.

HTH,

Hugh Scott

Originally posted by bprell
Ok...I'll let you know up front that I know pretty much nothing about SQL.

Here's my dilema. I've got a db being used on a server. It's got an MDF and an LDF. I've figured out that the latter is (I think) a log file while the former is the actual database.

The MDF is around 560MB and the LDF is around 1.9 GB. What I want to know is if there is any (fairly simple) way to compress/truncate/reduce the size of that 1.9 gig LDF file.

Any info anyone can offer would be appreciated.

Thanks!