SQL7 - Do not need the Transaction Logs

[denacho]

I've searched for about 2 hours now trying to find a way that I can get rid of the Transaction Log file for a Test database that I have in SQL7. So far, most everyone has said it is not "really" possible to do this in SQL7 (or 2000 for that matter) and you don't actually want to in the event that you have to recover.

My point is I have a Test database that I constantly Restore from my Production database, so I don't really need the .ldf file. I'm not able to Restore right now because I don't have enough disk space with the .ldf inlcuded. Is there a way to get rid of this for an existing database? For example, recreating the database but without the .ldf file. Again, recovery is not an issue for this Test database.

Any help is appreciated.

 

[SQLBill]

I use SQL Server 2000. My understanding is that you can never 'get rid' of the .ldf. However, you can (at least in 2000) 'not use it'. Here's the directions for 2000, you'll have to figure out if they work for 7.

In Enterprise Manager, go to the database, right click, chose Properties. Go to the OPTIONS tab and there's a line for Recovery Mode. Set it to SIMPLE.

There will still be a .ldf but it doesn't do anything.

-SQLBill

 

[SQLBill]

I noticed I kinda missed your point.

Do you not have the room for your current .ldf or do you not have the room for ANY .ldf?

You could rename the .ldf so SQL Server can't find it (from mydb.ldf to newname.ldf for example). Then do a RESTORE.

If you constantly restore the database, maybe it would be better to deattach the database. Delete the .ldf file and then attach just the .mdf file (sp_attach_single_file_db).

However, both of those options will recreate the .ldf as an empty file with the default size.

-SQLBill

 

[denacho]

Thanks SQLBill.

To answer your last response, we didn't have room to Restore Prod to Test at all, so we were hoping to eliminate .ldf so we could "fit" in the space we had. I'll try your suggestion on detaching the db.

However, what we did find that was helpful:
(1) We do use SQL2000 on other servers and I was aware of the Simple Database Model. The closest concept in SQL7 is on the same Options tab, to check "Truncate Log on Checkpoint" (found that through a few postings here). We restored our Prod to another Practice db and then checked this option on the Practice db - because we don't want that checked on our Prod db.

(2) We had already shrunk the .mdf with dbccshrink, but it didn't shrink the .ldf - which Microsoft said it wouldn't. We did find a KB (256650) that instructed us how to shrink the .ldf and we were able to do that in Query Analyzer.

This seemed to solve our issue. We were then able to backup the Practice db (overwriting existing), and restore to our Test and it fit just fine. A bit of a length to go, but it worked! Thanks for your timely postings to our delima.