Can''t attach db - Transaction log full
-
I'm not a dba, and have very, very limited knowledge of administering sql server, so please bear with me.
When trying to attach a database, I get the following error:
"Attach database failed for Server xxx
...
The transaction log for database yyy is full. To find out why space in the log can't be reused, see the log_reuse_wait_desc column in sys.databases"
I can't open that table, but the properties of that column are more of a mystery than help to me.
I need to to fix this, so any help is appreciated. The information in any log file is not important to me. Getting the database attached is all I really need to do.
Thanks,
Lee...
-
Use DBCC shink the log file
-
Thanks, but now I get a message indicating the file cannot be found. I set the default location of log files to the folder where it is, and then tried adding the complete path to the sql command.
This is what I ran first:
USE
Master;
GO
DBCC
SHRINKFILE (hpx_log, 25);
GO
-
I just read this in BOL:
DBCC SHRINKFILE applies to the files in the current database.
If the db isn't even attached, can this command be used? Perhaps that's why I keep getting a the msg that it can't be found?
-
Nope, can't be used. It sounds like you don't have enough disk space for the files to be attached. Check that.
Can you afford data to possibly be lost? If so, search this site for attaching only a .mdf.
-SQLBill
-
Okay, will do. Thanks, Bill.
While I'm at it, would you or someone tell me the correct procedure for detaching a db? Bascially all I want to know is what the common gotchas are for a newbie when detaching a db. BOL aren't all that helpful in that regard.
I got a message in one of my attempts to reattach this db that it wasn't "shutdown properly." All I did was detach it like always, but I've never encountered this problem before, so I want to do it right the next time.
Thanks,
Lee...
-
How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
http://support.microsoft.com/kb/224071/en-us
MohammedU
Microsoft SQL Server MVP -
How did YOU detach it?
You can do it two ways.
1. Enterprise Manager. Right click on the database, select All Tasks, Detach Database.
2. Query Analyzer. Run:
USE Master
sp_detach_db 'dbname'
-SQLBill
-
I used the Management Studio with SQL Server 2005 to detach it, i.e.:
Right-click-myDB -> All tasks -> Detach.
That's all I did.
I don't understand why SQL Server disallows you to simply attach the database while giving you the option of using the old log file, or have it create a new one for you. The problem seems to be that the .mdf is designating a specific .ldf, but when it can't find it, it gives you no option whatsoever.
So, I had to grab a backup, then do all the changes I'd made once again. One day wasted.
This is one of the many reasons you do backups, I guess. Lee...
-
If you backup & restore database yyy it will automatically shrink the transaction log, or you can run this script which should work as well:
USE master
declare @dbname nvarchar(255)
set @dbname = 'NAME OF DATABASE'
backup log @dbname with truncate_only
DBCC SHRINKDATABASE (@dbname, 0)
-
if you have not fixed it yet... here are a couple of suggestions:
- you can delete the ldf file that is there. if you don't need it. or move it somewhere else. This will cause a new one to be created during the attach.
- you didn't mention if when you did the dettach if you were moving the mdf to another system or what. you said you copied in the mdf... try copying in both the mdf and the ldf that was associated with it.
-
Thanks for that advice, but the database isn't attached anymore, and SQL Server won't let me attach it for the very reason your advice addresses....a catch 22. But, I'll certainly put it to use in the future.
-
I tried both of those solutions, but SQL Server balked at each. I was trying to move the db, but it didn't work on the new server, and, to top it off, it wouldn't reattach to my server either, giving the msg I mentioned above.
I've got it fixed now, though, via a backup without the changes I'd made. Had to redo those.
Thanks for your time and efforts,
Lee...
-
Try the following...
1. Create a new db with the same name and and same .mdf and .ldf filenames as detached db.
2. Stop the sql service
3. Rename the .mdf and .ldf files of db created in step1 with suffix _old
4. Copy your db files which suppose to attach the files.
5. Start the sql service.
See what error shows up and what will be the status of new db.
MohammedU
Microsoft SQL Server MVP -
1. Use sp_attach_single_file_db (see syntax in Books Online in the SQL Server Star Menu) because it uses only the database file and creates a new transaction log
2. Check the log of Master database because the Attach process makes entries to Master database. Right-Click Master database in Enterprise Manager, Select properties, go to tab for log files, see the size and settings if the growth is allowed.
3. Check your Disk Space! it could be that you don't have disk space to attach to the location where you want it or Master database log is on the disk that does not have a disk space.
Regards,Yelena Varsha
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply