Thursday, December 21, 2006

SQL 2005 full transaction log (Or how I almost cried)

Background:

SQL 2005 Express Edition with a full transaction log, the normal procedure that I would use for this (a development environment) would be to detach the DB, rename the transaction log to "trans_log.ldf.old" or similar, reattach the DB and go about my normal business.

Not so today, what happened was that the transaction log hit the limit with users still attached, tried to kill them off a-la SQL 2000 enterprise manager using the detach GUI and then hitting the clear users button without actually detaching the DB (sly dog).

This doesn't work in SQL 2005 (express edition at least) so I restarted the SQL2005EXPRESSservice and found that it had indeed detached the DB anyway.

What I had to do to resolve this:

Open up the "Attach Database" GUI window, select the transaction log in the lower pane and hit the remove button.
Hit the script button at the top
Go back to Management Studio and run the new script.
Bish-bosh your DB is now re-attached.

N.B. For some reason if you try to use the GUI window it barfs saying the transaction log is full, even if you have removed the transaction log .ldf from the database files...

1 comment:

selinitur said...

most likely, you have also heard about the way of fox dbf repairing