Deletign a log file: Urgent

Question

Deletign a log file: Urgent

xgbnow

SSC Enthusiast

Points: 195
More actions
January 6, 2004 at 2:18 pm

#358789

Here is the situation. The Transaction log backup failed repeatedly over time the log began to consume the drive. This basically brought down the server because the transaction log was full. I added a second log file on a different drive and then performed a backup, truncated and shrunk the log file.
I now want to delete the second log file and return to the primary log file.
Is this is a simple task? Can anyone point me in the right direction?
Thanks in advance,
Larry

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

fromnaija SSCrazy Points: 2876 More actions · Answer 1

I don't know if there is a TSQL statement to do this but in EM right click on the database, properties, transaction log. Highlight the second log file and hit the delete command button.

Joseph

xgbnow SSC Enthusiast Points: 195 More actions · Answer 2

Thats what I was planning but, I did know if there would be any reprocusions. Such as losing data, updates etc. ?

Thanks.

fromnaija SSCrazy Points: 2876 More actions · Answer 3

I would suggest you do this after you run your backup. You could also issue the CHECKPOINT command before you perform the delete action. I don't think you run any risk of data loss.

Joseph

xgbnow SSC Enthusiast Points: 195 More actions · Answer 4

I appreciate your prompt replies and I think it is all good advice.

I think I had do a better job of proof reading my post, I am starting sound like an illiterate, skipping words and all.

That brings up another good point. In regards to having a second transaction log. I assume I should backup both log files. But, I am curious if after freeing up space on the primary log file if the SQL Server would revert back to the primary log file or maintain logging to the secondary file. The reason I am asking is how would I restore the database if need be? Do I restore the primary lof file then the secondary or from this point just the primary?

Thanks again,

Larry

? I thought there was a spell checker around here?

jgroseth SSC Eights! Points: 847 More actions · Answer 5

I use these 2 statements as two different steps in a job to back up the log and then truncate it. Works well.

Step 1

BACKUP LOG [MyDB] TO DISK= 'D:\Backup\MySqlServer\MyDB\MyDbLog.dat' WITH NOINIT , RETAINDAYS=2, NOUNLOAD , NAME = N'MyDB Translog Backup', NOSKIP , STATS = 10, NOFORMAT

Step 2

USE master

BACKUP LOG MyDB WITH NO_LOG

GO

USE MyDB dbcc shrinkfile('MyDB_log')

GO

Good luck,

Jeff

z28807 Valued Member Points: 52 More actions · Answer 6

Here are the steps we've take on production servers to delete a log file (but you'll probably want to try it in a test environment first):

DBCC SHRINKFILE (file_name,emptyfile)

GO

alter database dbname

remove file file_name

go

Make sure the existing log file has enough space to take on the data being emptied from the file being deleted.