The transaction log for database 'SID' is full.

[Former Member]

Hi Team,

We are performing system copy to sandbox. While importing i got few errors.

Could you please help us on this issue.

(IMP) INFO: ExeFastLoad failed with <2: BCP Commit failed:The transaction log for database 'SBX' is full. To find out why space in the log cannot be reused, see the lo>

(IMP) ERROR: ExeFastload: rc = 2

(DB) ERROR: DDL statement failed

(CREATE  INDEX [BSIS~1] ON [BSIS] ( [MANDT] , [BUKRS] , [BELNR] , [GJAHR] , [BUZEI]  )  WITH ( DATA_COMPRESSION = PAGE ) )

DbSlExecute: rc = 99

  (SQL error 9002)

  error message returned by DbSl:

The transaction log for database 'SBX' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

(DB) ERROR: DDL statement failed

(CREATE  INDEX [BSIS~Z01] ON [BSIS] ( [MANDT] , [BUKRS] , [HKONT] , [BUDAT]  )  WITH ( DATA_COMPRESSION = PAGE ) )

DbSlExecute: rc = 99

  (SQL error 9002)

  error message returned by DbSl:

The transaction log for database 'SBX' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

(DB) INFO: disconnected from DB

F:\usr\sap\SBX\SYS\exe\uc\NTAMD64\R3load.exe: job finished with 3 error(s)

F:\usr\sap\SBX\SYS\exe\uc\NTAMD64\R3load.exe: END OF LOG: 20150124000512

Thank you

Regards

Nani

[Matt_Fraser]

Hi Nani,

Shrinking the log isn't going to solve this problem. You need to create free space in the log. This can only be done by either truncating the log (via a transaction log backup, preferably) or by growing the log file (assuming you have enough disk space to do so). However, you are getting this problem during a CREATE INDEX operation, and you can backup the log, but not truncate it, until the CREATE INDEX is complete. BSIS is a pretty large table, too, so this will be a big operation.

You could consider putting the database into simple recovery mode and then restarting the R3LOAD. This will reduce the log usage during the load. Afterwards, put it back into full recovery mode and take a full backup.

You also could consider using database-dependent methods of doing the system copy, i.e. instead of using R3LOAD, just do a backup/restore. This is usually faster and simpler and less error-prone, and won't have this problem.

How large is your database, and how large is your transaction log?

Regards,

Matt

[Former Member]

Hi Matt,

Thanks for the response.

Our database is 350 GB & Log is 550 GB.

Here we have a doubt, while we took export for full DB it took 130GB,

While importing it was creating space issues.

We maintained 400 GB in SANDBOX for Database, here do i need to maintain the same LOG space in SANDBOX as maintained in production?

Regards

Nani

[Matt_Fraser]

That's a very large transaction log. It's not typical for it to be larger than the database, so this implies that at some point it was allowed to grow without being backed up and truncated. If you find that it's almost always empty (you should be able to see snapshots of this via DBACOCKPIT, but also with the SQL Server Management Studio's reports), then at some point you might consider shrinking it to a more reasonable size (say, 35 GB). It should go without saying that such an operation should not be done during business hours or times of heavy usage. Also, it's unrelated to your current question, so just an observation.

Since you are creating your sandbox copy via R3LOAD instead of backup/restore, the size of the sandbox transaction log will be dependent upon the operations performed by R3LOAD during the import. You could consider putting the new database in Simple recovery mode during the import to reduce how much logging occurs. Afterwards, it's up to you whether to leave it that way or switch it back to Full recovery.

The other question, however, is why you are choosing to use R3LOAD instead of backup/restore to do your system copy? Are you trying to reorganize the data files as part of this? If not, if you're just trying to make a copy "as-is" of your production system, it's usually preferable to use the backup/restore method. As I mentioned before, it's simpler, faster, and less error-prone. It also requires no downtime on the part of your source (production) system, as you just make a copy of the backup file and use that to 'restore' to the target (sandbox) system. However, it will create data and log files as exact duplicates of the source files, so that means that your sandbox system will have a 350 GB database and 550 GB transaction log (unless you shrink the log prior to taking the backup and using it). Of course, you could always shrink the log in the sandbox after you've finished the restore.

[former_member182657]

Hi Nani,

i got 1.5GB free space after shrinking.

Hope this space would be sufficient for your procedure but i doubt for this much only.

Here i would like to suggest SAP KBA's   421644 - SQL error 9002: The transaction log is full

363018 - File management for SQL Server

Hope this will help you.

Regards,

Gaurav

[Sriram2009]

Hi Nani

I think your MS Sql DB log full, you have to take the truncate log backup to reduce the log disk free space or increase the DB disk free space.

Kindly refer the SAP Note 1151572 - R3load uses DELETE for restart

BR

SS

[Former Member]

Hi Sriram,

I've seen my disk space for log file looks full.

In importing JOBS there are 3 failed, but the rest of them were running successfully.

Can i shrink the log file in the middle of import?

Regards

Nani

[Sriram2009]

Hi Nani

1. Yes, You can do the shrink the log db without performing the truncate log backup. On Sql MMC you can find the % of free space in log db.

2. Once the log db getting free-up, you can retry the system copy import  on same phase.

BR

SS

[Former Member]

Hi Sriram,

THanks a lot,

i got 1.5GB free space after shrinking.

But the importing is running normally. After the 3jobs got failed the rest of them were moving smoothly.

How do i need to solve the 3 failed jobs.

[Sriram2009]

Hi Nani

Have you go thru the SAP Note 1151572 with solution ?

BR

SS