cancel
Showing results for 
Search instead for 
Did you mean: 

sap log drive full

Former Member
0 Kudos

Hi,

Can I follow the procedure below even if my sap log drive is full? I tried it the first time before when the log drive is full, I detach the database and got error that the detach was not successful since a drive is full.

http://4rapiddev.com/sql-server/delete-shrink-eliminate-transaction-log-ldf-file/

My issue is I am facing an sap log drive full and did a shrink but does not change the file space after. I tried thrice the shrinking, and the available free space in SQL becomes low every time I do a shrink and still  in 0%.

Please also see attached screenshots.

Can anyone help me resolve this issue?

Thank you!

Regards,

AJ

Accepted Solutions (0)

Answers (5)

Answers (5)

Sriram2009
Active Contributor
0 Kudos

Hi Almira

Follow the Steps

1. Stop the SAP system

2. Start the MS Sql DB backup

3. Once the DB backup task completed then Start the MS Sql Truncate log backup

4. Shrinking the DB once your log disk space is recovered then start the SAP system

Kindly follow the MS Sql db backup & transaction log shrinking steps links

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

SAP Business One Notes -625546  - Size of transaction log file is too big

Thanks

Sriram

Private_Member_12188
Active Participant
0 Kudos

You do not want to backup your log file with Truncate Only.  That doesn't back up the transaction log, it simply writes the transactions to the database without backing up the log.  This is a very bad habit to get into.  If you truncate your log file and need to do a point in time restore you can only restore to the last log backup before the log was truncated.

If you're going to take the time to backup your database log file, actually back it up. 

Was just validating, the truncate_only option has been deprecated as of SQL Server 2008+.

http://msdn.microsoft.com/en-us/library/ms186865.aspx

"The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see View or Change the Recovery Model of a Database (SQL Server)."

Former Member
0 Kudos

Hi,

Please execute below query step by step

In SQL 2008

use <DATA BASE>

BACKUP LOG <DATA BASE> TO DISK='NUL:'

sp_helpdb <DATA BASE>

dbcc shrinkfile ('<LOG DB NAME>',10)



In SQL 2005

use <DATA BASE>

backup log <DATA BASE> with truncate_only

sp_helpdb <DATA BASE>

dbcc shrinkfile ('<LOG DB NAME>',10)

Regards,

            Renjith Lakshmanan

Private_Member_12188
Active Participant
0 Kudos

Please see my previous post.  Backing up the log with truncate only or to DIsk='Nul' is a bad idea and it's basically against everyone's best practices. 


Why do you think Microsoft deprecated the truncate only back up option?

If you really do not care about having a point in time database restore and need to free up the log file space, the Microsoft recommended solution is to change the recovery model to simple then shrink the log file.

Former Member
0 Kudos

Hi,

See Tony's comments above - as soon as you back up the log file you will be able to shrinlk it.

Regards,

Graham

Private_Member_12188
Active Participant
0 Kudos

A couple of questions for you:

What version of SQL are you running?

Are you taking transaction log backups as a part of your database maintenance plan? 

Shrinking the log file will not do anything until you've freed up space inside of the log file.  Try taking a transaction log backup then try shrinking the file again.

Back Up a Transaction Log (SQL Server)

http://technet.microsoft.com/en-us/library/ms179478.aspx

bxiv
Active Contributor
0 Kudos

Do you have a page/swap file on that drive?  With 2008 R2 you have a check box that sets 1 page file across multiple drives; or the server administrators set up dedicated page files per drive.

My guess is that you don't have enough space to compress/shrink the logs.

Former Member
0 Kudos

Hi,

Can you please Check the below note.

1224089 - How to shrink a MSSQL Server 2005 / 2008 Database?

can you please try to give the shrink size to 500 MB and see. what happens.

Thanks

Rishi abrol