Take the 2-minute tour ×
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

share|improve this question
    
Is SQL Server the RDBMS? –  Thomas Stringer May 1 '12 at 14:06
3  
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 Stringer May 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 Gunny May 1 '12 at 15:13
add comment

4 Answers

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.

Consume the space, and don't delete your log files. You're asking for trouble with that.

See this article on Transaction Logs, in particular the "Log Mismanagement" portion.

share|improve this answer
    
Our production MS SQL 2005 database is re-created each night from file extracts off of another system. –  Cape Cod Gunny May 1 '12 at 14:20
    
That article is no longer freely viewable. –  Nurgle May 30 '13 at 18:38
    
@Nurgle I just tried the link and I see it no problem. You may need to be registered on the site to read the article, I'm not sure. But it works on my end. –  Thomas Stringer May 30 '13 at 18:40
add comment

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

USE master;
GO
ALTER DATABASE databasename SET READ_ONLY;
GO
share|improve this answer
    
Unfortunately I can't set the database to READ_ONLY. We may need to create a view to help support our reporting capabilities. –  Cape Cod Gunny May 1 '12 at 14:23
    
You can alter the database back make the changes and then set it to READ_ONLY any time you need to. –  jgardner04 May 1 '12 at 14:27
    
Okay, but what purpose does an .ldf file serve if there have been no updates? –  Cape Cod Gunny May 1 '12 at 14:31
3  
It is required to maintain the ACID properties of the database. Check out this presentation by Brad McGehee for some more info about Transaction logs. bit.ly/Kozbdr –  jgardner04 May 1 '12 at 14:34
add comment

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.

  1. Set your database to Simple recovery model. You can do this only if you don't want to roll back the transactions
  2. Instead of making MDF and LDF files, create a full database backup (.BAK) file. It will be smaller tnan MDF+LDF
share|improve this answer
add comment
up vote 1 down vote accepted

Here is the solution I came up with to shrink the LDF files.

  1. Detach Database
  2. Rename LDF file to *_old.ldf
  3. Attach Database
  4. Remove the refernece to the missing LDF

This recreates an LDF file that is 504K in size.

  1. Delete *_old.ldf
  2. Empty recycle bin

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.

share|improve this answer
1  
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
    
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? –  dezso Aug 17 '12 at 16:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.