sp_attach_single_file_db

Question

sp_attach_single_file_db

zach_john

SSCertifiable

Points: 5922
More actions
May 14, 2003 at 11:45 am

#63292

Is there a workaround for this that allows me to drop my transaction log files I have 3 ldf on one database that I would like to reduce down to 1. Creating a new database and moving all the objects is not a solution as one colleague suggested is not a viable answer.
Many thanks
John Zacharkan

John Zacharkan

Viewing 12 posts - 1 through 11 (of 11 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.

But you can use DBCC Shrinkfile with 'emptyfile' option to allow the file to be dropped using the ALTER DATABASE statement.

zach_john SSCertifiable Points: 5922 More actions · Answer 2

Thanks for the quick reply. I tried the following

use EVMECASECU0z

go

dbcc shrinkfile (EVMECASECU010LOG71,emptyfile)

go

alter database EVMECASECU0Z remove file EVMECASECU010LOG71

I get:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ------ ----------- ----------- ----------- --------------

12 3 31969 31969 31968 31968

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Server: Msg 5042, Level 16, State 2, Line 1

The file 'EVMECASECU010LOG71' cannot be removed because it is not empty.

Any other thoughts?

John Zacharkan

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

quote:
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
12 3 31969 31969 31968 31968

Your log file does't seem to be empty. Try to backup log with truncate_only few times and run dbcc shrinkfile again.

zach_john SSCertifiable Points: 5922 More actions · Answer 4

I know and I can't get it to be empty either.

quote:
Your log file does't seem to be empty. Try to backup log with truncate_only few times and run dbcc shrinkfile again.

John Zacharkan

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

Allen Cui-55137

SSC Guru

Points: 51650

May 14, 2003 at 1:41 pm

#457169

Which version of SQL Server you run?

Andy Warren SSC Guru Points: 119902 More actions · Answer 6

Thats the right track, just keep going.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

zach_john SSCertifiable Points: 5922 More actions · Answer 7

SQL 7.0.0.1063.

I have the same problem with another database. This database was broken into 3 files the mdf id 100mb using 1.5mb, tlog1 10mb and tlog2 513mb combined using only 32mb. Since I couldn't get rid of the 2nd ldf file I created another databse and xfer every thing over. By the way it now takes up 3mb.

I can send a copy of the backup it takes less than 2mb if you want to try to play with it. I pretty much exhausted my knoweledge on making the 2nd log file disapear. I even tried the Andrew Zanevsky script to force shrink it wouldn't budge. Let me know if you want the backup.

Think its time to take up knitting or something besides database work.

quote:
Which version of SQL Server you run?

John Zacharkan

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 8

That is the version I expectd.

See KB http://support.microsoft.com/default.aspx?scid=kb;en-us;256650 for how to shrink log file in SQL Server 7.0.

Becker-213572 Old Hand Points: 394 More actions · Answer 9

I tried the backup log truncate and I ran this routine...still can't get rid of the log file. I guess I give up unless there is some other way to get rid of it.

When I was running the stored procedure, I set the target size at 0, but didn't change any other parameters on it, so I think I did it right.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 10

Are you also using SQL Server 7 as the original poster in this old post did? And can you post the exact steps you followed to shrink the file? Are you sure that there are no long-running transactions that have not yet committed?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Becker-213572 Old Hand Points: 394 More actions · Answer 11

I'm on SQL 7.0 and I stopped my DB, started it back up and ran this:

use powersuite

go

dbcc shrinkfile (newlog,emptyfile)

go

alter database powersuite remove file newlog

go

I get:

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Server: Msg 5042, Level 16, State 2, Line 1

The file 'newlog' cannot be removed because it is not empty.

Guess I could move everything over to a new DB and try that?