no SQL Server background

[Former Member]

Hi,

I do not know SQL server. My experience is on Oracle. The BW server (3.0 with SQL 8.00 in Win 2000 server) of our System company have "transaction logs is full" error. (their BASIS is on Holiday and not contactable)

How can I clear the logs?

the error message is:

The log file for database 'BWL' is full. Back up the transaction log for the database to free up some log space..

thanks,

Roel

[Former Member · Accepted solution]

Hi,

check Note 363018 - File management for SQL Server

part E. Increasing and decreasing log files

regards,

kaushal

[Former Member · Accepted solution]

thanks for your reply. Where will I type this command? In the dos prompt? Sorry, I really do not know anything about SQL server.

[Former Member · Accepted solution]

Hi Kaushal,

I tried it using SQL Query Analyzer. I got this message after executing the command:

Cannot shrink log file 2 (BWLLOG1) because all logical log files are in use.

(1 row(s) affected)

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

[Former Member · Accepted solution]

Hi,

(BWLLOG1) because all logical log files are in use.

it's used by sap.

first you need to stop the SAP system.

then execute the command.

Note: you are not familiar with SQL server is it better to take backup of log file before executing any command

regards,

kaushal

Edited by: kaushal malavia on Apr 22, 2008 11:10 AM

[clas_hortien · Accepted solution]

Hi,

shrinking the log will not work, as the log is full (no free space).

You should perform a transaction log backup to a local disk. Keep the generated files until the basis guys are back from vacation, as they might need them. This is how you can make a backup to disk:

backup log BWL to disk='d:\backups\EmercencyLogBackup.bak'

Run this in the query analyzer. Once the command completes, the log will be freed. Repeat this step if necessary (use a different file).

Best regards

Clas

[Former Member · Accepted solution]

Hi Clas,

Thanks for your reply. I have this error when I tried the baclup log command:

Server: Msg 4208, Level 16, State 1, Line 1

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Server: Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

thanks,

Roel

[Former Member · Accepted solution]

Hi,

execute folloiwng

ALTER DATABASE [Database Name] SET RECOVERY FULL

then perform log backup.

or you can set recovery model full in sql server management studio

1. Go into the SQL Server Enterprise Manager, expand the Databases folder.

2. Highlight the databases required for backup, right click on them and select properties.

3. Go to the options tab and check that Full is selected in the Recovery section

mark helpful answers

regards,

kaushal

[clas_hortien · Accepted solution]

Hi,

if you are in simple mode and the log is full you have an open transaction that prevents that the log gets truncated (what will be done automatically when in simple mode). Check the oldest open transaction with:

use YourDatabase

dbcc opentran

go

Identify the SPID of this transaction and close it either via R/3 or with the kill command

kill FoundSpidNumber

go

Then run a

checkpoint

This then should truncate the log, once the rollback or commit of the open transaction is finished.

Regards

Clas

[Former Member · Accepted solution]

Thanks and poits to all.

Roel

[ken_halvorsen2]

The simplest thing to do might be to increase the Time lines that the Transaction Log backups are being run now (ours are run every 30 minutes in production), and \ or increase the size of the Transaction Log(s) if there is freespace on the drive.