December 8, 2008 at 8:15 am
I attached several databases to some new SQL 2k5 SP2 instances this weekend.
I didn't have the LOG files, so I did FOR ATTACH_REBUILD_LOG in the commands.
Of the 4000 databases I attached, a small handeful didn't create the LOG file... according to the outputs of the CREATE DATABASE FOR ATTACH command the logs were created, but when I look for them, I can't find them.
Any tips on how to detach a database that has no log file, or to create a log file for an attached DB that is missing one? The database isn't listed as suspect... but doesn't let me do anything with it when I try to connect, etc.
December 8, 2008 at 8:21 am
Any errors in the error log when you try to use the DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2008 at 9:20 am
I'm not permitted in SSMS to use the DB... but the error I get when I try is a permissions type error
12/08/2008 00:00:47,spid58,Unknown,FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'S:\DorisLogs\xxxxx_DORISP_log.LDF'. Diagnose and correct the operating system error and retry the operation.
We came up with a work around to fix this... we simply take the databases offline, and then drop them. Reattach, delete the log file from the attach and let SQL create it during the attach. Change the recovery mode to FULL and go on with our business.
This whole mess is happening on a cluster... near as I can tell the few databases that are doing this (about 1 in 800) can sort of see their cluster resources from the other instances (we redistributed some test databases when we moved the rest to production). And while it can't actually access the log file, it sees it there, so the Rebuild command is ignored and the database is attached with a log file it can't access. AT least that's our guess as to what happened.
December 9, 2008 at 8:37 am
Hi,
This might help out
http://msdn.microsoft.com/en-us/library/ms174385.aspx
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1702647&SiteID=1
thnks
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
December 9, 2008 at 9:50 am
yes but sp_attach_single_file_db recreates the log file.
In this case the database was attached and no log file was created or accessible.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply