Attach DB Without Transaction Logs

Hi, there,

I have multiple transaction log files. I don't need the transaction logs and intended to remove them. I have tried detaching the database, rename the logs and trying to re-attach the database without the logs but to no avail. The error message mentioned something like file activation failed and SQL couldn't rebuild the transaction logs.

Is there a way to get rid of the transaction log files?

Thank you.

Regards,

Yong Hwee

Hi,

you can use sp_attach_single_file_db but it works only on databases that have a single log file.

When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.

...

OR by reading this thread attaching DB without .ldf file ?

CU

tosc

|||

Hi, Tosc,

Thank you for your immediate reply. It seems like there is no workaround for database with multiple log files.

Regards,

Yong Hwee

|||

There are lot of limitation with sp_attach_single_file_db. The database should not have morethan one LOG and Datafile and the database should be properly detached. So in your case this process will not do. You requirement is to remove the Secondary LOG File, if am correct. What you can do is, Remove the secondary log file first using

DBCC SHRINKFILE (,EMPTYFILE)
alter database test remove file .

Then you can trucate the primary log file to any size you want. Please take backup of the database before starting anything

Madhu

Refer : http://madhuottapalam.blogspot.com/search?q=Transaction+Log

Madhu

|||

In SQL Server 2005 you can you use CREATE DATABASE syntax to rebuild the log. The ATTACH_REBUILD_LOG syntax should work even if there are multiple log files.

|||

Hi, there,

Thank you for your reply. I managed to delete the log by first backup with option to truncate log after which I change the database to simple recovery. I can delete the logs this way.

Thank you.

Regards,

Yong Hwee

Assertion Errors, temdb guest account, and other errors

Last night, we had a sql server (2000, sp3) whose transaction logs get
backed up to a share have the log backup jobs fail. At the same time, a
database that is a replication publisher became unavailable. We were getting
the following errors in the sql server error log but this error was written
only once to the W2003 Application event log:
Error: 3624, Severity: 20, State: 1.
SQL Server Assertion: File: <sysscan.cpp>, line=565
Failed Assertion = 'xdes->GetDbId () == sDbid'.
I'm still researching this error.
The replicated database showed up in EM but there were no tables (not even
system tables). We tried to detach it to see if we could perhaps re-attach
the files and return it to availability but since it was being replicated,
it would not allow us to do so. We tried various ways of dropping the
publication but since the database was unavailable, we were unable to drop
it.
Since regardless of our resolution to bringing this database back on-line
(restore, etc.) was going to require that we get rid of it as it is, we
restarted the sql server service. The database showed up again and seemed to
be fine. However, this morning, after numerous reports from people unable to
see the databases on this server and errors related to invalid login in
tempdb, I discovered that the guest account was disabled. I granted access
to tempdb to the guest account and that resolved this problem.
We have also had a number of problems with the linked servers that were
previously functional to and from this server.
We recreated the replication of the database that had the problem last night
and the conflict tables are named aonflict% instead of conflict%. We're not
sure if this is related or not but the other problems certainly seem to be
related.
Everything seems to be functional now but I am faced with the task of
putting all of these pieces together in order to determine the cause and to
prevent it from happening again. Any advice on any of these possibly related
issues would be appreciated.It appears as if guest was disabled in model which would explain why it was
disabled in tempdb after restarting the sql server service. I'm not sure why
it was disabled in model.
We're calling MS PSS regarding the assertion errors.
It appears as if the funny conflict table names are due to the failure to
clean up the old publication and/or subscription.
"michelle" <michelle@.nospam.com> wrote in message
news:uUP169kdEHA.1604@.TK2MSFTNGP11.phx.gbl...
> Last night, we had a sql server (2000, sp3) whose transaction logs get
> backed up to a share have the log backup jobs fail. At the same time, a
> database that is a replication publisher became unavailable. We were
getting
> the following errors in the sql server error log but this error was
written
> only once to the W2003 Application event log:
> Error: 3624, Severity: 20, State: 1.
> SQL Server Assertion: File: <sysscan.cpp>, line=565
> Failed Assertion = 'xdes->GetDbId () == sDbid'.
> I'm still researching this error.
> The replicated database showed up in EM but there were no tables (not even
> system tables). We tried to detach it to see if we could perhaps re-attach
> the files and return it to availability but since it was being replicated,
> it would not allow us to do so. We tried various ways of dropping the
> publication but since the database was unavailable, we were unable to drop
> it.
> Since regardless of our resolution to bringing this database back on-line
> (restore, etc.) was going to require that we get rid of it as it is, we
> restarted the sql server service. The database showed up again and seemed
to
> be fine. However, this morning, after numerous reports from people unable
to
> see the databases on this server and errors related to invalid login in
> tempdb, I discovered that the guest account was disabled. I granted access
> to tempdb to the guest account and that resolved this problem.
> We have also had a number of problems with the linked servers that were
> previously functional to and from this server.
> We recreated the replication of the database that had the problem last
night
> and the conflict tables are named aonflict% instead of conflict%. We're
not
> sure if this is related or not but the other problems certainly seem to be
> related.
> Everything seems to be functional now but I am faced with the task of
> putting all of these pieces together in order to determine the cause and
to
> prevent it from happening again. Any advice on any of these possibly
related
> issues would be appreciated.
>