on 08-07-2013 2:42 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)."
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
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.
Hi,
See Tony's comments above - as soon as you back up the log file you will be able to shrinlk it.
Regards,
Graham
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.