{"id":462,"date":"2009-09-13T20:39:19","date_gmt":"2009-09-13T14:39:19","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=462"},"modified":"2016-01-07T11:31:11","modified_gmt":"2016-01-07T05:31:11","slug":"how-much-transaction-log-is-left","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=462","title":{"rendered":"How much transaction log is left ?"},"content":{"rendered":"

What if a rouge transaction gets on your system and start using my all your transaction logs, don’t you want to be altered ?
\nOnce in a while I get certain ETL or applications that goes into a update, insert loop that just keeps running without committing causing the transaction logs to get full . The worst part of this is, genuine transactions and application gets affected as well and \u00c2\u00a0the database goes through a rollback. \u00c2\u00a0But sometimes there might be multiple mass update or delete application running and if the batch size is not configured properly, it might also fill up the log. \u00c2\u00a0In \u00c2\u00a0such cases I do not want to hunt down the developer and don’t want to him to take him to “Skull Island” for a “picnic” . \u00c2\u00a0 So I wrote this script to alert me when the Percentage of transaction log usage goes above 79%, so either I can track down the application that is doing this and take appropriate actions \u00c2\u00a0:<\/p>\n

1. Disconnect the rogue application and book a one way ticket to skull island \u00c2\u00a0or
\n2. \u00c2\u00a0Increase the secondary log and notify the developer to resize the batch size.<\/p>\n

Here is the script to download \u00c2\u00a0:log_usage.sh<\/a><\/p>\n

Below is the script for a quick review , but I suggest you download the script from the above link<\/p>\n

export PATH=$PATH:${HOME}\/sqllib\/bin\/ \r\n. ${HOME}\/sqllib\/db2profile \r\nDatetime=\"`date +%Y-%m-%d-%H:%M:%S`\" \r\nOutputDir=`echo \"\/home\/db2inst3\/report\"` \r\ndbas=\"dbas@tecshatwork.com\" \r\necho \"select 'MYDB log is greater than '||rtrim(char(int((float(total_log_used) \/\r\n float(total_log_used+total_log_available))*100)))||'%' \r\nfrom table(snapshot_database('',-1)) as sntable where int((float(total_log_used) \/\r\n float(total_log_used+total_log_available))*100) >79;\" > $OutputDir\/logspace_usage.sql<\/pre>\n
db2 connect to MYDB; \r\nif [ \"$?\" = \"0\" ] \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 then \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 db2 -txf $OutputDir\/logspace_usage.sql >> $OutputDir\/logspace.tmp 2> \/dev\/null \r\nelse \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0echo \"Error connecting to database -> MYDB at $Datetime\" >> $OutputDir\/getlogspace.err \r\nfi \r\nif [ $(cat $OutputDir\/logspace.tmp|wc -l) -gt 1 ] \r\n\u00c2\u00a0then \r\n\u00c2\u00a0 \u00c2\u00a0for emailaddr in $dbas \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0do \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 echo \"alert has been send for - > MYDB\" \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 mailx -s \"Space issue in MYDB - MYSRV\" $emailaddr < $OutputDir\/logspace.tmp \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0done \r\nelse \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0echo \"Yippeee its good for now !\" \r\n\u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0rm $OutputDir\/logspace.tmp \r\nfi<\/pre>\n
export PATH=$PATH:${HOME}\/sqllib\/bin\/<\/div>\n
. ${HOME}\/sqllib\/db2profile<\/div>\n
Datetime=”`date +%Y-%m-%d-%H:%M:%S`”<\/div>\n
OutputDir=`echo “\/home\/db2inst3\/report”`<\/div>\n
dbas=”raju@tecshatwork.com”<\/div>\n
echo “select ‘MYDB log is greater than ‘||rtrim(char(int((float(total_log_used) \/ float(total_log_used+total_log_available))*100)))||’%’ from table(snapshot_database(”,-1)) as sntable where int((float(total_log_used) \/ float(total_log_used+total_log_available))*100) >79;” > $OutputDir\/logspace_usage.sql<\/div>\n
db2 connect to siebelpa;<\/div>\n
if [ “$?” = “0” ]<\/div>\n
then<\/div>\n
db2 -txf $OutputDir\/logspace_usage.sql >> $OutputDir\/logspace.tmp 2> \/dev\/null<\/div>\n
else<\/div>\n
echo “Error connecting to database -> MYDB at $Datetime” >> $OutputDir\/getlogspace.err<\/div>\n
fi<\/div>\n
if [ $(cat $OutputDir\/logspace.tmp|wc -l) -gt 1 ]<\/div>\n
then<\/div>\n
for emailaddr in $dbas<\/div>\n
do<\/div>\n
echo “alert has been send for – > MYDB”<\/div>\n
mailx -s “Space issue in MYDB – MYSRV” $emailaddr < $OutputDir\/logspace.tmp<\/div>\n
done<\/div>\n
else<\/div>\n
echo “Yippeee its good for now !”<\/div>\n
rm $OutputDir\/logspace.tmp<\/div>\n
fi<\/div>\n
<\/pre>\n","protected":false},"excerpt":{"rendered":"
What if a rouge transaction gets on your system and start using my all your transaction logs, don’t you want to be altered ? Once in a while I get certain ETL or applications that goes into a update, insert loop that just keeps running without committing causing the transaction logs to get full . […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3],"tags":[],"class_list":["post-462","post","type-post","status-publish","format-standard","hentry","category-db2"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/462","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=462"}],"version-history":[{"count":3,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/462\/revisions"}],"predecessor-version":[{"id":1529,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/462\/revisions\/1529"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}