Can't delete second log file in v7 - works in v200

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Can't delete second log file in v7 - works in v200

Author

Topic

dkitson
Starting Member

4 Posts

Posted - 2002-12-10 : 06:55:51

Hi,

I have a SQL Server 7 database that has two log files - circumstances have changed and I no longer need the second log file, and wish to delete it.

The problem is when I try to delete it, it says 'not empty'.

I've gone through the usual techniques to shrink and cycle the active virtual log file to be in the primary log file.....

DBCC shrinkfile (with 'EMPTYFILE' option), and 'backup log...with truncate_only' etc.....

'DBCC loginfo' shows that the second, unwanted log file is not active:

FileId FileSize StartOffset FSeqNo Status Parity CreateTime
----------- ---------------------- ---------------------- ----------- ----------- ------ ------------------------------------------------------
2 253952 8192 251 2 128 2002-12-07 16:07:07.467
2 253952 262144 250 0 128 2002-12-07 16:07:06.493
3 13041664 8192 249 0 128 2002-12-07 16:04:42.597

But then when I then try:

alter database rstest180 remove file RSTEST170_2_Log

I get:

Server: Msg 5042, Level 16, State 2, Line 1
The file 'RSTEST170_2_Log' cannot be removed because it is not empty.

How do I get the log file into an 'empty' state, so that I can delete it?

Just as an experiment, I restored a backup of the database to SQL Server 2000, and attempted the same thing - and it worked - the file was successfully deleted with:

alter database rstest180 remove file RSTEST170_2_Log

on SQL Server 2000.

Can anyone suggest how I can delete the file in SQL Server 7.0?

And is there a documented difference in behaviour between 7 and 2000 when dealing with the deletion of second log files?

Many thanks.

Douglas

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-10 : 07:27:27

Have you tried detach and attach?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dkitson
Starting Member

4 Posts

Posted - 2002-12-10 : 08:10:37

nr,

Yes, I've tried 'detach' and 'reattach' - but what did you have in mind specifically?

I've tried 'sp_attach_db' and 'sp_attach_single_file_db', with various combinations of 'filename' parameters. But they all fail - both procedures insist that the second (unwanted) log file is physically present.

For example, if I try 'sp_attach_db' specifying only the data file and the primary log file, having renamed the second log file, it still complains about the second log file not being there, even though it's not named in the command, as follows:

sp_attach_db rstest180, 'F:\MSSQL7\Data\rstest180.mdf',
'F:\MSSQL7\Data\rstest180_log.ldf'

gives:

Server: Msg 5105, Level 16, State 10, Line 1
Device activation error. The physical file name 'F:\MSSQL7\DATA\rstest180_log_1.ldf' may be incorrect.
Server: Msg 945, Level 14, State 1, Line 1
Database 'rstest180' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'rstest180'. CREATE DATABASE is aborted.

The file mentioned in the above error message is the second log file.

If the above command (with no mention of the second log file) is run with the second log file present, it restores it anyway, so I'm back where I started.

Douglas

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-10 : 09:07:15

>> but what did you have in mind specifically
Exactly what you tried.

Bit surprised the sp_attach_single_file_db doesn't work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources