March 5, 2004 at 1:41 pm
Hi -
I have transferred data from tables on one server to tables in a second server. I keep receiving an error telling me that the log file is full and to backup the log file to create more space. I have done this and am still having the same error. I do not need the transaction log for backup and restore purpose right now. I tried to detatch the database and reattach only the mdf file with the sp_attach_single_file_db. I receive an error saying "The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL\data\WebPDM_Log.LDF' may be incorrect." If Books online says that it will create a new log file, why would I be getting an error about the ldf file? I detached the database using the following..
sp_detach_db @dbname = WebPDM,@skipchecks='false'
and tried attaching it using...
sp_attach_single_file_db @dbname = 'WebPDM',@physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\WebPDM_Data.MDF'
Any suggestions would be greatly appreciated!
Thanks -
Jane
March 5, 2004 at 3:06 pm
When you use the sp_attach_single_file_db you need to attach the .mdf file.
The error you got said .LDF,hence I presume you tried to add the wrong file.
Steven
March 5, 2004 at 3:26 pm
Jane,
Attaching a single file when your transaction log is available isn't generally a good idea. It is possible that there are still open transactions in the file. Go ahead and attach both files, then truncate the log with the following:
Backup Log WebPDM with truncate_only
Then set your recovery model to simple, and you should be set to go.
Remember to change recovery model back when you are done.
Steve
March 6, 2004 at 6:20 am
Just to add to Hoo's post, if you need to shrink the footprint, you can run the dbcc shrinkfile command after you truncate the log, see BOL for details. Doing the single file attach thing is not the best idea.
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply