SQL Server Performance Forum – Threads Archive

deleting a transaction log

Hey guys, I want to know the steps on how to properly delete an .ldf file.
I would like to know what db is associated to this .ldf file. Thanks. V1rt

The ‘Transaction Log’ tab in database properties window shows you the log file that the database is associated with. You need to detach the database to delet this log file. Though I don’t understand why do you want to delete this file.

Thanks. The reason I want to delete it is because, we are not going to use the that database in question anymore. You know what, before I delete it, can I just do a normal copy on that .ldf file first to another drive? Thanks.

Follow up, I found the transaction log tab in EM. How do I detach the database? Bear with me for now since I am new in MSSQL. Thanks again. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Hey sorry. I found the detach database <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Now, before I do it, how will I know if there are current users that are connected to this database, is there any sp that will show this to us? Thanks.

quote:Originally posted by v1rtu0s1ty …we are not going to use the that database in question anymore….

You don’t need the DB (and its log). DROP the DB. Everything else will follow suit. Or Is there a special reason why you would only want to keep the data files? Polite Advice: The route you are pursuing is bound to be disastrous at some point in future. If you are not sure of what you should do, just do backup (even on a disk location of your choice ) and delete the database. That way, you’ve got yourself covered. Nathan H.O.
Moderator
SQL-Server-Performance.com

sp_who (or sp_who2) can show you if any users are using this database. Also, when you try to detach it will give you a message if any users are using this datbase and gives you the option to clear these connections.

ok, what I will just do is: 1. backup those 2 files, .ldf and .mdf 2. drop dbname The reason I want to keep it is because, we might need the data in the future. So is the step now that I will be executing safer? Thanks also for the sp_who. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Since this db isn’t being used anymore, is it ok to detach the database and after 5 minutes attach it again so I can see how it works? Thanks.

I went to Windows Explorer and went to the files. I tried copying it and it told me, Sharing Violation. Looks like it is still open or in used by other application.

I just read in BOL about detach. I can use that. But now if I drop the db, how can I recreate it in the future say I want to restore it? Thanks.

Hey guys, I did it. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I have moved the huge transaction log to another drive. But still want to learn say if I delete it, but want to recreate the db in the future. Would like to know how it is done. Thanks guys.

I did it. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I have moved the huge transaction log to another drive. Why? Are you going to use this database anymore? If you don’t have the .mdf file, the .ldf file you does you zero good. You should have backed up the database, verified the backup, then deleted the database. You do still have the.mdf file don’t you? Was the big .ldf file the only reason you moved it? MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

You can use SP_ATTACH_SINGLE_FILE_DB in order to recreate fresh .LDF file for this .MDF file, but as referred by Nathan & Derrick keeping the good old backup will do the stuff rather than moving to another drive if the database is not used. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

If this were an experiment, it would still be wise to ironclad safe. PLEASE do a complete backup. Drop the database. Forget about it for a while. When you need it again (and you said you don’t), just restore the backup. Information about restoring databases can be found in the BOL. Everything else not said above will just elevate your medical bills if this is a production database. PLEASE. Nathan H.O.
Moderator
SQL-Server-Performance.com

]]>