MS SQL transaction log full - archiving

[former_member183044]

Hi all,

Good day.

I 've 3 system landscape here. In my QAS system 4 data files and 1 transaction log is there. When i checked the space only few MB is remaining.

1.jpg

Among 4 data files, the size is about 150 gb and the transaction log is alone near to 200 gb.

Kindly tell me how to increase the disk size ? In this situation should i follow Archiving? If yes, please kindly help me in archiving as till now i didn't done the same.

Kindly help in the above matter.

Regards

Praveen

[VolkerBarth]

You have chosen the "SQL Anywhere" tag, and "SAP SQL Anywhere" is a particular DBMS - as you seem to ask about MS SQL Server, I would suggest that you re-tag the question accordingly so the fitting community members notice your question (in case it is SAP-related at all?)...

(And sorry, I don't know the answer.)

[former_member183044]

Done Volker

[Matt_Fraser]

Praveen,

S Sriram is not giving you the best advice. For one thing, it's not necessary to stop your SAP system for much of what you need to do to fix this, although as a QAS and not PRD system, that is at least more available to you as an option.

The bottom line is that for an ABAP system transaction logging is set to "full" by default (and this is the recommendation). You can see this in your SQL Server Management Studio: select your database, right click and choose Properties, then look on the Options page for the setting Recovery model.

You probably should do regular backups of your QAS system, even if you don't keep the backups very long. For instance, if you do something in testing that hoses the database and you want to restore it, this would be a lot quicker and easier than rebuilding it from scratch (or as a new system copy). So, your testers would thank you for that.

So, my recommendation is to take a full backup of the database, then do a transaction log backup. This will "empty" the transaction log, though it will not shrink it (it will stop the growth). However, it has to be empty before you can shrink it. Then you can shrink the file by again right-clicking the database and choosing Tasks -> Shrink -> Files. Choose File type = Log. For the shrink action, it sometimes takes a couple iterations of "Release unused space" and/or "Reorganize pages..." (in which you decide what your ideal target log size is). You may need to do one or two more transaction log backups in between these actions as well. Also, it could happen that you end up shrinking it much smaller than you intended. This is ok; if that happens, just go back to the database Properties dialog, choose the Files page, and set the new desired size of the log file.

After you've gotten the filesize down to where you want it, you can decide if you really don't want to do regular backups or not. If you aren't going to do backups, then you need to set the Recovery model (mentioned earlier) to Simple.

Cheers,
Matt

[luisdarui]

Hi,

Archiving is very particular option! SAP has some recommendations in this regard, see SAP Note 2388483 - How-To: Data Management for Technical Tables

Now, after archiving/deleting data, you can release disk space by shrinking the database, see SAP Note 1721843 - MSSQL: Post-steps after archiving, deleting or compression.

For your T-Log size, it seems too much higher for a 150GB DB!

If your T-Log keep growing indefinitely, you may have to check:

• How often you take transaction log backups;
• Long running active transactions prevent the T-log to be truncated during a backup operation;
• Auto-grow being active for T-Log file.

You may shrink your T-Log backup, but it would require it to be truncated, this operation will break the log chain.

I would recommend you to read this article for more information about the Transaction log:

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

[former_member197667]

Hi There,

The other way that you can deal with this situation of taking backup on to network drive is by MS SQL management studio.

All you need to do is to create new backup device. Check the below mentioned link.

https://msdn.microsoft.com/en-us/library/ms189109.aspx

You can find this device in SAP and you can create a backup job to this newly created backup device with immediate execution.

Kind regards,

Raghavendra

[former_member183044]

Hi all,

As of last reply i tried to taking the backup via SQL server. At that time i am facing the following error message

"cannot open backup device operating system error 3"

I am trying to take the backup to a network location which is already mapped. But the above message is coming again and again. But when i googled , i found that the network location which we have to take the backup also have to be mapped in SQL. For that i executed the following command.

exec xp_cmdshell 'net use X: (XXX.XXX.XXX.XX)\Backup'

go

But the output of the above command also showing that network name is not found. Kindly help me in the crucial situation

Awaiting your earliest reply

Regards

Praveen

[luisdarui]

Hi Praveen,

But this is another thread. In this case you have to work on your other thread https://answers.sap.com/questions/82414/cannot-open-backup-device-operating-system-error-3.html

[former_member183044]

Hi Matt,

Thanks for the reply.

If we are taking backups, should the size will decrease ? Then is there any need of shrinking again?

Regards

Praveen

,

Hi Matt,

Thanks for the reply. Kindly note the below points

1. //You probably should do regular backups of your QAS system, even if you don't keep the backups very long// - Till now we didnt take the backup of QAS (Quality system). In our environment, we are daily taking full backup and transaction backups.

2. //my recommendation is to take a full backup of the database, then do a transaction log backup. This will "empty" the transaction log, though it will not shrink it (it will stop the growth). However, it has to be empty before you can shrink it.// - Here u mentioned as first i should take the backup ( both full and transaction). If we taking backups regularly, then the size would decrease or not? If then , is there any need of shrinking?


Regards

Praveen

[former_member183044]

Hi Luis,

Thanks for the reply. For your information, i'd like to share some info.

//How often you take transaction log backups// -- As this is a QAS server, till now we didn't take the backup. Daily we are taking the backup of PRD server only( both full backup and transaction log)

//You may shrink your T-Log backup, but it would require it to be truncated, this operation will break the log chain//-- So as per the reply from Sriram, I'll do the shrinking process. But is it necessary to the truncating also? How to do the same. kindly tell

Regards

Praveen

[Sriram2009]

Hi Praveen.

During the backup operation you should not do any action in DB. better you can perform the full DB backup and then start the shrinking..

to my understanding it should have any issues.

Regards

,

Hi Praveen.

If we shrink the transaction log file and if any problem happens what will we do? At this point can we do the full backup ?

To my understanding it will not happen any thing, better you can start the full db backup and then do the DB log shrinking. During the backup operation your should not do any activity in the DB,

BR

SS

[former_member183044]

SS,

Ok. As per your advice, will do the full DB backup from SAP via DB13. After that will do the shrinking activity via MS SQL server . After that i'll check the size of the transaction log backup file which is now 200 gb.

//During the backup operation your should not do any activity in the DB//

I'll perform the backup operation of QAS in night. So at that time no one will access the QAs server. Is that enough?

Regards

Praveen

[Sriram2009]

Hi Praveen.

Its not required to restore the DB and Shrink will not affect the database. Kindly follow the steps as mention below. we have faced this issue so many time in our environment.

1. Stop the SAP instance.

2. Run the Transaction log backup.

3. Shrink the transaction log file. Transaction log file shrinking is to release the free disk space.

4. Once again check the over the free disk space in Transaction log & Ms Sql DB files.

5. Start the SAP instance.

BR

SS

[former_member183044]

SS,

Thanks for your reply. Will do the same in our environment and let you know the status.

Regards

Praveen

[former_member183044]

SS,

One more doubt? If we shrink the transaction log file and if any problem happens what will we do? At this point can we do the full backup ?

Regards

Praveen

[Sriram2009]

Hi Praveen.

1. Refer the link backup action before that stop the SAP Instance

https://www.mssqltips.com/sqlservertutorial/8/sql-server-transaction-log-

backups/

2. After completed the backup. shrink the DB log file from the following link

https://technet.microsoft.com/en-us/library/ms190757(v=sql.105).aspx

Regards

SS

[former_member183044]

SS,

Thanks for the links.

Can u pls explain me the concept of Shrink? After shrinking, whether we have to restore the database again? Whether shrinking will affect the live data in QAS?

Regards

Praveen

[former_member183044]

SS,

For increasing i have to put some HDD. As of now its not possible. Sriram, can you please tell the steps for clearing the transaction log backup from MS SQL MMC

Regards

Praveen

[Sriram2009]

Hi Praveen.

You have to increase the disk space or stop the SAP, & clear the transaction log backup from MS Sql MMC.

Regards

Sriram