DB recovery

Since my client's log file is going extremely large, I
DETACH the database,
delete the .LDF file, and ATTACH the database from the
original file. This
method worked in the past, but this time when I tried to
ATTACH the
database, it prompts 'ERROR: 1813, unable to create
database' something like
that (I tried to translate it since it is a Chinese SQL
server). It seems
the original .MDF file has corrupted. Now, I have with me
is the original
..MDF file, what can I do?
TonyYou shouldn't have deleted the log file in the first place. When SQL Server starts it performs
recovery for each database, where it reads through the log and synchronizes the modifications in the
log to the database. Imagine what happen if the log isn't there!
In some situations, where there is not recovery to perform, SQL Server can happily create a new log
file for you, but how would you know that this is what will happen if you delete the log file? You
can't.
Deleting the log file is an extremely unsafe method to re-claim HD space.
I suggest that you restore from the latest clean backup. That is the only way to get a consistent
database back. If that isn't an option, let MS help you though this situation as they might have
tools/commands to save what can be saved.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tony Lam" <anonymous@.discussions.microsoft.com> wrote in message
news:006b01c3ba1c$e534a8b0$a001280a@.phx.gbl...
> Since my client's log file is going extremely large, I
> DETACH the database,
> delete the .LDF file, and ATTACH the database from the
> original file. This
> method worked in the past, but this time when I tried to
> ATTACH the
> database, it prompts 'ERROR: 1813, unable to create
> database' something like
> that (I tried to translate it since it is a Chinese SQL
> server). It seems
> the original .MDF file has corrupted. Now, I have with me
> is the original
> ..MDF file, what can I do?
> Tony
>|||sorry to contridict you Tibor (I bow to your superior knowledge), but
detaching the database (in situations where there is only ONE logfile)
causes it to be shutdown cleanly meaning that the current logfile is
not needed for the reattach operation. I agree with you that deleting
it is perhaps best advised against until a successful reattach (simply
rename it).
Kalen mentions this tip in her Inside SQL 2K book (Chpt 5,Other
Database Considerations).
Let me know if the current thinking has changed on doing this (either
by MS or the SQL professionals).
Br,
Mark Broadbent
mcdba , mcse+i
=============|||Mark,
> sorry to contridict you Tibor
No problem, one of the best way to learn things IMO...
> (I bow to your superior knowledge),
LOL... :-)
> but
> detaching the database (in situations where there is only ONE logfile)
> causes it to be shutdown cleanly meaning that the current logfile is
> not needed for the reattach operation.
Ahh, I didn't read the OP that close. IIRC, the doc's states that you can do this if you have only
one log file *and* only one data file. And then use sp_attach_single_file_db. If this is what Tony
did, then SQL Server didn't behave as per the documentation. I agree with that. :-)
Personally, I still don't feel comfortable doing this unless I have very good backup etc to fallback
on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Broadbent" <no-spam-please_mark.broadbent@.virgin.net> wrote in message
news:Oo0LjdmuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> sorry to contridict you Tibor (I bow to your superior knowledge), but
> detaching the database (in situations where there is only ONE logfile)
> causes it to be shutdown cleanly meaning that the current logfile is
> not needed for the reattach operation. I agree with you that deleting
> it is perhaps best advised against until a successful reattach (simply
> rename it).
> Kalen mentions this tip in her Inside SQL 2K book (Chpt 5,Other
> Database Considerations).
> Let me know if the current thinking has changed on doing this (either
> by MS or the SQL professionals).
>
> --
> Br,
> Mark Broadbent
> mcdba , mcse+i
> =============|||Full Backup always a good idea @.:-)
Amen to that!
--
Br,
Mark Broadbent
mcdba , mcse+i
=============