Problem with create a new log file in a database in simple recovery mode with device is full

Question

Problem with create a new log file in a database in simple recovery mode with device is full

Jose Julio Mota Duarte

Old Hand

Points: 392
More actions
March 28, 2010 at 2:15 am

#139050

Hello
I have a problem in SQL SERVER 2005, the space in the device where is logfile of this database is full
Message
The transaction log for database 'Reporter' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
The database was in simple recovery mode
I see the field log_reuse_wait_desc is ACTIVE_BACKUP_OR_RESTORE in master..sys.databases
I try to create a new log file in another device with a lot of space
USE [master]
GO
ALTER DATABASE [Reporter] ADD LOG FILE ( NAME = N'Reporter_log_2', FILENAME = N'P:Reporter_log_2.ldf' , SIZE = 3072KB , FILEGROWTH = 10240KB )
GO
but i can't give me this error:
Msg 5105, Level 16, State 2, Line 1
A file activation error occurred. The physical file name 'P:Reporter_log_2.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 5009, Level 16, State 8, Line 1
One or more files listed in the statement could not be found or could not be initialized.
Someone can give me a help
best regards
José Júlio Duarte

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

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

Jose Julio Mota Duarte (3/28/2010)
I see the field log_reuse_wait_desc is ACTIVE_BACKUP_OR_RESTORE in master..sys.databases

The log cannot be cleared until the concurrent backup operation completes.

See http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-16-Concurrent-log-and-full-backups.aspx

A file activation error occurred. The physical file name 'P:Reporter_log_2.ldf' may be incorrect.

Most likely SQL Server cannot 'see' drive P. Check the account SQL Server is running under has access. If the SQL Server is in a cluster, remember also that P must be a clustered resource. The path would probably benefit from a backslash too.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jose Julio Mota Duarte Old Hand Points: 392 More actions · Answer 2

Thanks Paul

The account SQL Server is running under has access to the device in SQL Server is in a cluster, and device P is a clustered resource and the SQL Server 'see' this device.

Best regards

Jose Julio Mota Duarte Old Hand Points: 392 More actions · Answer 3

Hello Paul

Put in a path a backslash and resolve the error

Msg 5105, Level 16, State 2, Line 1

A file activation error occurred. The physical file name 'P:Reporter_log_2.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.

Msg 5009, Level 16, State 8, Line 1

One or more files listed in the statement could not be found or could not be initialized.

USE [master]

GO

ALTER DATABASE [Reporter] ADD LOG FILE ( NAME = N'Reporter_log_2', FILENAME = N'P:\Reporter_log_2.ldf' , SIZE = 3072KB , FILEGROWTH = 10240KB )

GO

but create another Error:

Msg 3023, Level 16, State 2, Line 1

Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

Best regards

Paul White SSC Guru Points: 150468 More actions · Answer 4

Jose Julio Mota Duarte (3/28/2010)
Msg 3023, Level 16, State 2, Line 1
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

That's right. You cannot add a file to a database while it is being backed up. Wait for the backup to finish, then add the new log file.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

There's a backup, restore or similar operation running on that database already. Also the source of the log reuse message.

Can you query sys.dm_exec_requests and see what's running on that database?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 6

Here is the query to do that:

SELECT EXR.session_id,

EXR.blocking_session_id,

EXR.command,

database_name = DB_NAME(EXR.database_id),

EXR.percent_complete,

EXR.estimated_completion_time,

EXR.wait_type,

EXR.wait_time,

EXR.wait_resource

FROM sys.dm_exec_requests EXR

WHERE EXR.command IN (N'BACKUP DATABASE', N'BACKUP LOG');

Notice you will be able to see an estimate of how far the process has progressed, and estimated time to completion.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jose Julio Mota Duarte Old Hand Points: 392 More actions · Answer 7

Hello Paul

thanks

I Can join the new logfiles and resolve the problem.

Best regards

Paul White SSC Guru Points: 150468 More actions · Answer 8

Thanks for the feedback.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi