MS SQL transaction log full - archiving

[VolkerBarth]

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 Member]

Done Volker

[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/