Can''t attach db - Transaction log full

Question

Post reply

Can''t attach db - Transaction log full

Lee Roper

Mr or Mrs. 500

Points: 585
More actions
December 6, 2006 at 11:11 am

#175179

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...

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

T Y MA SSC Veteran Points: 271 More actions · Answer 1

T Y MA

SSC Veteran

Points: 271

December 6, 2006 at 11:25 am

#676350

Use DBCC shink the log file

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 2

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

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 3

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?

SQLBill SSC Guru Points: 51440 More actions · Answer 4

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

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 5

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...

MohammedU SSCoach Points: 19644 More actions · Answer 6

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

SQLBill SSC Guru Points: 51440 More actions · Answer 7

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

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 8

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...

El Guapo Old Hand Points: 366 More actions · Answer 9

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)

Daiseyjane Newbie Points: 8 More actions · Answer 10

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.

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 11

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.

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 12

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...

MohammedU SSCoach Points: 19644 More actions · Answer 13

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 14

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