9

Each month we make an end of month snapshot of our production database. 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

16

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.

9

As mentioned in another answer, 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

You can alter the database back to READ_WRITE, make any changes needed, then set it to READ_ONLY any time you need to.

The log file is still required to maintain the ACID properties of the database.

2

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
-3

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.

UPDATE 2019-09-24: Yeah, I agree this is a really bad idea. I stopped doing this almost immediately. I have rebuilt all the indexes using a fill factor of 100. Shrunk only the .ldf files. And changed all the databases to be READ ONLY.

  • 6
    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
  • 3
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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