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.
  • I can make sure my app is in a consistent state.
  • I can rollback all the uncompleted transactions if any (just in case) it's ok
  • I can DETACH the database

What do I need the log file for after that?

I'm particularly talking about a "highly controlled" environment. So, the real question is: How can I explicitly force everything to be alright in order to avoid possible data loss? Does anyone here have experience doing this kind of operation?

UPDATE

The reason for this is that I'm not a fan of immense log files. The highly controlled environment is my PC running a single app in a single-user mode. I'm the developer of this app and I have complete control over the code changes. I'd prefer to delete rather than SHRINK so please do not suggest that I simply SHRINK the file.

UDATE 2 -- PRACTICE

I've had this process in production for more than half a year without any issue.

share|improve this question
4  
You just got whacked on SO: stackoverflow.com/questions/9413162/…. Consider explaining what you want to achieve and why. –  gbn Feb 23 '12 at 13:23
7  
Note to close voters - it may be wise to leave this here as an artifact of WHY you shouldn't do this. –  JNK Feb 23 '12 at 13:27
3  
Finally we know what's driving you :-). Then, by all means, please read about proper log file size management: Importance of proper transaction log size management, Manage the size of SQL Server Transaction Logs, Managing Transaction Logs in SQL Server... And continue only after you really understand the role. –  Marian Feb 23 '12 at 13:55
11  
Just to add to this, I have seen several cases on various SQL forums where someone deleted the log file (detach and delete or shutdown and delete) and could NOT in any way get their database back online afterwards. Complete 100% loss (no repair, no emergency mode, no fix). The log is not an optional part of the database, it's not an audit log or user log. It is an essential part of the database and what allows SQL to guarantee transactional consistency and durability. If you insist on deleting it, I suggest making sure you have an up to date résumé first. –  GilaMonster Feb 23 '12 at 14:53
3  
However rebuilding the log breaks the restore chain for the database (though I assume there is absolute no interest in point-in-time recovery for this app). Deleting the log may leave the database transactionally inconsistent (yes, even if the database was detached or taken offline first), in worst cases it can even leave the database structurally inconsistent. The log is what SQL uses to guarantee consistency and durability of transactions, if those aren't needed then SQL Server may not be the best tool, maybe a NoSQL solution instead –  GilaMonster Feb 23 '12 at 16:04

5 Answers 5

up vote 9 down vote accepted

Just to add to the existing answers.

The SQL Server 2008 Internals Book (pp 175-177) implies that detaching the database, deleting the log file and reattaching the mdf file ought to be quite safe as it says.

Detaching a database ensures that no incomplete transactions are in the database and that there are no dirty pages for this database in memory. If these conditions cannot be met, the detach operation fails ... one benefit of using the sp_detach_db procedure is that SQL Server records the fact that the database was shut down cleanly ... This can be a quick way to shrink a log file that has become much larger than you would like

However this does not seem to be the case. This blog post has a demo of this method leaving your database broken too.

share|improve this answer
    
Thanks for the post. I'm glad I'm not the only guy who don't follow MS's best practices blindly. Just went through the article you point to but didn't find that demo. Could you please point a finger at it. Thanks! –  InOrder Feb 23 '12 at 14:31
    
@InOrder - The code to reproduce is in the blog post. She fills the transaction log up to bursting then looks at detaching after first looking at shutting down and deleting. Search for the phrase "Same setup, but instead of shutting SQL down, I’ll detach the database." –  Martin Smith Feb 23 '12 at 14:35
3  
@InOrder - Having an .ldf as part of your db is not an MS best practice! If you really read that article you might have seen that too: "The transaction log is not an optional piece of the database. It’s not like an installation log or activity log where the entries are there just in case anyone’s interested. The transaction log is what SQL uses to ensure, at all times, that the data in the database is transactionally consistent. It’s what gives SQL databases the consistency and durability properties that are required from a relational database engine." –  Marian Feb 23 '12 at 14:39
    
@Martin Smith I think the author had to forcibly detach it. It's not the scenario I'm talking about. In my particular case, everything is a way smoother. it's not even full or whatever. I just don't want to drag it along.. –  InOrder Feb 23 '12 at 14:47
    
@InOrder - Well as long as you don't permanently delete the log before successful reattach without it then you should avoid disaster (I think!). But the rebuilt log will then need to auto grow again until it reaches whatever its stable size is once you start using the DB again and the log file can't use fast file initialisation. It always needs to zero out the file as it grows which takes time. –  Martin Smith Feb 23 '12 at 14:53

You can force a ROLLBACK though by switching the DB to RESTRICTED using WITH ROLLBACK IMMEDIATE. See ALTER DATABASE. You can't force COMMIT (as per your SO question)

Then...

  • If you delete the LDF (SQL Server shut down), your database comes back "suspect".
  • If you detach+delete, attach the MDF by itself, the LDF is re-created.

Note: for the millionth time, you need an LDF. In case you ask

share|improve this answer
4  
@MartinSmith: updated. OP now explicitly states "What do I need the log file for after that?" –  gbn Feb 23 '12 at 13:30

Surely you can delete your LDF file. You even may to choose to delete the sql server!

The only question - why do you need to do this?

If you drop the ldf's for just not to transfer them to the other server - copy data files, try to attach them and only if you succeed - delete old files, even with data files together.

You should not delete LDF files until you successfully raised up the DB on the side without LDF (and sql server surely recreates them). Or at least - tested this behavior.

share|improve this answer
    
I did test this behavior thousand times –  InOrder Feb 23 '12 at 14:34

After you dig yourself out of this hole, please, read this and learn from it. Logs are an essential part of how SQL Server works. You can't just get rid of them. But you can manage them appropriately. You're probably working off the default settings, which create all databases in Full Recovery mode, meaning, the logs are going to grow. Change this after you create the database. Other than that, @gbn up there has the answer.

share|improve this answer
    
What I mean is purging them. I understand it can't live without a log for a while :) –  InOrder Feb 23 '12 at 14:37
2  
More correctly, you can't live without a log at all. Might as well ask about removing brakes from a car. If you want to keep the log at a manageable size and don't care about point-in-time recovery in the case of a disaster, use Simple recovery model. doesn't mean it won't grow, does mean that you can mostly ignore that the log is there. Please read through this sqlservercentral.com/articles/64582 –  GilaMonster Feb 23 '12 at 14:55
    
@GilaMonster "What do I need the log file for after that?" –  InOrder Feb 23 '12 at 15:44
3  
Instead of worrying about how to get rid of the log, maybe consider learning how to manage the log properly? The app may be single user on your PC right now, but surely it's intended for wider release to users, and once that happens it's no longer a 'highly controlled environment' and the database's log will need to be properly managed. –  GilaMonster Feb 23 '12 at 16:07
2  
Cool, then here's something that one can't do - safely delete the transaction log without potential consequences. It's not 'overly scientific fluff, it's the integrity of your database. Detach and delete will be fine most of the time, except when it isn't. You asked for experienced DBAs to tell you how to deal with it. Lots of very experienced DBAs have told you that it's not safe to delete the log and have offered alternative suggestions. –  GilaMonster Feb 24 '12 at 12:18

To maintain ACID transactions in SQL server, the SQL server architecture relies on the use of data files and log files.

That is why you need the log file.

For the needs of your application maybe all you need is a flat file storage mechanism.

share|improve this answer

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.