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

[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