Transaction log Full

Hi there is a log maintenance in MS SQL,, you do it once a week or depending on the disk space remaining on your drive,, the problem of adding disk is that it will eventually become full always,, there is a
proper way to do it,, it is not in the SAP notes,, I took it from the Microsoft Team..
first you have to backup it,, then do a SQL shrinking,, there is a  proper procedure for these.

You can actually limit the size of the log but it is not recommended for recovery purposes,, it is better to shrink every other day than to have no recovery log for your backup,, anyway thats what we must do as

Hi Peter,
Thank you for your help, about the transaction log issue, actually my
user have one program required very huge process, everytime they run that
program, my server transaction log will become full and the system will
hang. If they run that program for 2 to 3 times a week then I have to
shrink the database for 2 to 3 times also. Is it possible to stop the
growing of transaction log when the program is running ?

Hi Peter,
Let say the current data capacity for certain drive 200GB already
used. And later I delete some client from SAP system, after delete I found
that the used space still same and never reduce. How to do in order can
view the reducing of space after client delete ?
shrink database also or ..................!!!

I never use shrink data before, any requirement or procedure need to follow
and any effect before shrink ?

Thank You.

Hi Lim,
Pankaj is right,, but your log is only 11G consumed.. because my log
grows to 60G in 2 weeks.. and then I have to shrink,, procedure for

Hi Pankaj,
Thanks for your reply, may I know how to shrink the transaction log
only ? My log currently still increase when my user run some big program.
Can I stop the log increase ?

Thank You.

Hi Kwang,
 
U need to first truncate the log file but before that keep a backup of the file.
 

Then use the DBCC_shrinkdb sp of sql server to shrink the database. Hope it will help.
 
Regards,e page

Thanks for your reply again, the current space for Data1 around 40GB,
Data2 44GB and Data3 is 42GB. For Transsaction log is around 11GB. Now what
OI want to do is to shrink the transaction log first, whether transaction
log can reduce by using shrink ? I found one option in SQL 2000 in
propertise of database which is auto shrink option, may I know what is this
option ? If I tick, any effect for the database & log ?

Another thing is according to you if I delete one client we can`t see the
reducing of the space ? so do you have any solution for reducing the space
(visible) ?

I hope you can give me some guides so that I can solve my problem for logs
always increase.

Thank You.

Regards,

You know the procedure for database reorganizaiton?

Regards

Thanks for reply again, may I know where can I get that file.

Hi Lim,
yeah you can actually shrink twice until its really small,, have a
great BASIS career.. I hope that the file I sent you helped you out..

I really thanks to both of you. Just Now I had tried to shrink the database log by using DBCC shrinkfile and I had success to shrink the log, but how to know that how many space that need to shrink, is it possible
shrink below than the space required or the minimum space already protected.

For example,
Let said the current log is 10GB, but after run some program, suddenly the log increase to 15GB. After that, I backup the log and continue the shrink the log space back to 10GB or can shrink below than 10GB. Is it
correct that what I`m doing like this ?

I found the utility on the SQL for database reorganization is very inefficient. I used it 2 years ago it took more than 48 hours to re-organize our production database. I think the utility provided by the database is much better such as DB2 , Oracle, Sequel. We run our R3 on IBM iSeries, I used its utility & it only toke 25 minutes to reorganized the entire database.

Actually database reorganization I saw it before in SQL, but I never use before. If you know how to use may be you can give me some guides or steps to use it. But to run that process not sure whether have any effect
any problem to the server or data. May you can let me know.

Hi, according to you, the database reorganisation was not so efficient in
SQL 2000. So any other way can use for shrink the database or re-organize
back the database? What way you use to re-arrange the database data ?
currently you still using SQL 2000 database ?

Thank You for your information.

Did u try ontape -c at command promt?
 

Thanks for your help, for transaction log backup actually I already have scheduled the backup everyday. Another question if let say I delete one of the client , how can I view back the database space already reduce.
As current, even I delete the client, the DB space still maintain the same, never change.

Thank You



This is really not the fault of the database.If you transaction log is
filling up its very very clear that something is written in the transaction
logs and i guess that something that is written in the transaction log are
the transactions that are done by the mentioned SAP programme.

Here its clear that the Z-prog or whichever programme for whatever
transaction that is used seems to be generating the logs.Please get the
application tuned rather than first getting into the database.The
application porogramme is writing the transaction log hence leading to it
getting full.

You can shrink the datafiles regularly its not very complicated but does
cause a performance loss,it recommended to get back the space after a
client deletion.YOu can also run DBCC checkDB for the database consistency
check.The transaction logs need to be backed up regularly so that the
transaction log drive gets emptied.If you backup the transaction log then i
dont think there should be any problem.YOu can always run as many
applications as many times you need JUST BACKUP THE TRANSACTION LOG.

ok if you havn`t gone through it I can give you procedures to do it
properly,, basically shrinking is the key to successfully save and maintain
the production system,, I have some few questions before we can start the
event,, what is the size of your DATA (DATA1, DATA2, DATA3)
and your log (LOG1). because basically the data is just 20Gmin in a
small medium corporation. and logs usually runs at 40G or more.. Deleting
client will not reduce the size,,

something about it before you even run out of space and affect your current
production system,, there are logs also that are related to TMS and needs
to be maintained these are two diffrent logs in SAP that are critical and
needs to be maintained properly,, just a reminder..
can trace where and what log is consuming the space,, thanks.. I hope we
can solve this issue of you..

This is really not the fault of the database.If you transaction log is filling up its very very clear that something is written in the transaction logs and i guess that something that is written in the transaction  log are the transactions that are done by the mentioned SAP programme.

Here its clear that the Z-prog or whichever programme for whatever transaction that is used seems to be generating the logs.Please get the application tuned rather than first getting into the database.The application porogramme is writing the transaction log hence leading to it getting full.

You can shrink the datafiles regularly its not very complicated but does cause a performance loss,it recommended to get back the space after a client deletion.YOu can also run DBCC checkDB for the database consistency check.The transaction logs need to be backed up regularly so that the transaction log drive gets emptied.If you backup the transaction log then i dont think there should be any problem.YOu can always run as many applications as many times you need JUST BACKUP THE TRANSACTION LOG.

You said to have deleted some of clients.
Have u performed reorganisation process this could get

some more space for u.