-2
  • 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.

12

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.

10

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

10

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.

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

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.

2

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.

0

TL:DR the ldf file is required, because SQL documents everything (including allowing you to query) before it actually does anything.

SQL needs the ldf (log file) to support fault tolerance answer by StanleyJohns.

No matter how controlled your environment, there will be changes inside of your SQL database; failed logins, restarts from patching, plan updates with queries, many things create changes that you may not be aware of.

SQL is designed so if the power goes out during a change (no matter how small) it will be able to recover. To meet this it creates entries in the ldf file. If the system is in FULL recover, all off those entries are saved, if the system is in SIMPLE recovery, those entries are deleted when the change is completed or COMMITTED. To accomplish this, before any changes are made they are entered in the .ldf.

In the event of a power outage, everything in the .ldf that has not committed will be rolled back.

If ldf space is an issue read Why Does the Transaction Log Keep Growing or Run Out of Space?

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.