May 1, 2007 at 8:22 am
Iam caught up in a situation here..We are having some problems with one of our sql servers.It keeps going offline frequently and we have opened a case with MS to look into it.
My issue is when we restart the sql server service or reboot the box to bring the server back online..the ldf file of this database gets shifted to the default location instead of the actual location,[because of some drive problems]
My question is
1.how do I move this ldf file back to its old location...
2.Do I need to delete the log file in the old location before moving it there or is it fine to do this without deleting it.[will this create a problem]
Thanks
May 1, 2007 at 9:43 am
a db usually consists of a single xxx.MDF file for data and single xxx.LDF for tranlog
- with a corresponding entry in the master..sysdatabases table
the files don't "shift" [to another folder] unless you/ano actually do so
- maybe SQL could support shortcuts but I wouldn't recommend it
Perhaps you had actually moved the file but not educated sysdatabases about it !
- you need to do a detach, move, attach sequence like this
exec
sp_detach_db 'mydb'
rem in CMD shell
move C:\mydb.mdb D:
move C:\mydb_log.ldb D:\
exec
sp_attach_db 'mydb', 'D:\mydb.MDB', 'D:\mydb_log.LDB'
then SQL and filesystem are in harmony
May 3, 2007 at 1:44 am
check if the path is correct in master..sysaltfiles table. it where ur ldf file will be created.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
May 3, 2007 at 9:00 am
The two posters above are what I understand.
Please post an update if you figure something out as this would be a major bug to know about.
May 3, 2007 at 11:01 am
I would detach the database then re-attach with sp_attach_single_file_db . It does not really let you to specify a different directory for the log, but if you want your log in the same directory as your db file it will be fine.
Is the DB read-only? I found the following in BOL in regards to attaching:
" 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....." something similar can be going on in your case.
Regards,Yelena Varsha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply