Cannot delete transaction log
-
We have some databases where we have kept a copy at a certain date for government purposes. We re-sized transaction logs by doing the following
1. Detach db
2.Delete transaction log
3.Re-attach db
However we now have one database where we got the error 'cannot delete. the source is in use'. No-one is in the database as we have not made it available. We seem to have permission issues on this server, anyway, but I'm just checking that we are not going astray.
Madame Artois
-
Permissions issue may not result in a "source in use" alert, so check if there were any pending active connections by running sp_who commands.
Prasad Bhogadi
www.inforaise.com -
There are no active users (all ecid are 0). We have tried to delete other transaction logs on the same server (it only holds the government snapshots) and get the same error. I've run sp_who against these databases also have no active users.
Is it the permissions or can I try soemthing else?
Madame Artois
-
Why are you deleting the transaction log? This is really not a good thing to do and should only be done in emergencies.
If you don't need the transaction log for 'point-in-time' recoveries, then change the recovery mode to SIMPLE.
-SQLBill
-
We do need point-in time recovery in the new database. The original database is heavily used and the transaction log file large despite backups throughout the day. When we backup the database and restore it to a different location (for the government statistics database) we don't need this massive transaction log which is why we drop it after detachment then re-attach.
We tried to use dbcc_shrinkdatabase and dbcc_shrinkfile before; all that left us with was a scambled database. We had to repeat the whole performance.
Madame Artois
-
After you delete the transaction log, do you do a Full Backup? If not, then the transaction log backups are worthless to you. Deleting the .ldf
'breaks the restore chain'.
-SQLBill
-
Yes we do do a full backup. The whole process is to backup the live system to a device, copy that device to the new location, restore the database under another name. Then we detach the new database, drop the log and re-attach. It has worked countless times.
The new location now lies on a filer but so do other databases where we repeat the same process for other databases. The filer holds several servers. All of which are happy but this one.
The problem lies with trying to delete the transaction logs on this element of the filer which thinks it is the d drive of the original server.
Is there an alternative method of shrinking the transaction log without using dbcc shrinkdatabase
Madame Artois
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply