My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James Colbert
See if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>
|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James
|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James
|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James
