Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Each month we make an end of month snapshot of our production databse. These month end snapshots are strictly for reporting purposes, there are no inserts, updates or deletes ever done on them. Each of these snapshots has an .MDF and .LDF file.

I want to delete the .LDF files and free up some space on the server. Are there any reasons why I need to keep the .LDF files?

Clarification:

Our production database is recreated each night from file extracts off of another system. We only report off of the production database... no updates are ever done.

Nightly Process:
From what I can tell...
Each night the databse tables are truncated
The tables are populated through a series of bulk insert statements
Indexes are rebuilt

So you DROP the old database, run CREATE DATABASE, create tables, and then populate it from file extracts? Or do you re-use the same database? What recovery model is the database in? If you could elaborate on the actual details surrounding "database is recreated each night", I think it will lead to more direct and targeted answers for your specific situation. The obvious is answer is YES, you need to keep .LDF files, but there may be simple ways to keep them small, if we have a little more information.
–
Aaron Bertrand♦May 1 '12 at 14:40

Sounds like you have one of the few implementations that could utilize the benefits of bulk logged recovery model.
–
Thomas StringerMay 1 '12 at 14:54

If the reporting database seems to be corrupt we restart the update database task that normally runs at night. We usually don't have to do this very often. Now I need to go read up on bulk logged recovery... thanks Shark.
–
Cape Cod GunnyMay 1 '12 at 15:13

4 Answers
4

You shouldn't delete the log file. If you are trying to reattach a data file without the log, SQL Server can technically recreate it, but there are a few potential issues, like if there were open transactions when the database was detached. In which case, you'd have total data loss.

As @Shark mentioned, you can't delete the log file. What you could do is set the database to READ_ONLY. With the database in READ_ONLY, no modifications are allowed and the log file will not grow. You could reduce the size of the log file to a minimal size and achieve your goal of a minimal footprint. To set the database in READ_ONLY run the following command

The fact is that you can create a database just using the mdf file. It's the sp_attach_single_file_db (Transact-SQL) command. Note that it will be removed in a future version of Microsoft SQL Server.
But, it's not smart to delete your LDF files. Shark is right 'You're asking for trouble with that.'
Another point of view - are your ldf files huge? If they are, you can do something about them.

Set your database to Simple recovery model. You can do this only if you don't want to roll back the transactions

Instead of making MDF and LDF files, create a full database backup (.BAK) file. It will be smaller tnan MDF+LDF

This has reclaimed a significant amount of disk space on the server. It works for us because all of these databases are static reporting databases ONLY. No Inserts, Updates or Deletes will ever be performed against these databases.

Wow, this is a really bad idea. What if the database doesn't detach successfully, or gets lost or corrupted somewhere between 1 and 3? You're SCREWED. You have ZERO copies of your database.
–
Aaron Bertrand♦Aug 17 '12 at 14:50

1

The proper way to do this would be: 1. Take a COPY_ONLY backup of the database. 2. Restore it on the reporting server. 3. Set recovery to simple and mark the restored copy as read-only. 4. Shrink the log file manually. Yes you need the space in the meantime, but you'll get to keep your job!
–
Aaron Bertrand♦Aug 17 '12 at 14:53

What amount of disk space are we talking about?
–
dezsoAug 17 '12 at 16:02