#!/bin/sh #++++++++++++++++ #.IDENTIFICATION tranlog #.LANGUAGE Bourne shell #.AUTHOR Sybase Administrator #.ENVIRONMENT #.KEYWORDS #.VERSION 1.0 16-Nov-1995 #.VERSION 2.0 12-Jun-1997: Dump all databases -- either to file or truncate #.VERSION 2.1 19-Dec-2006: NEWVIZ #.PURPOSE Save Transation Logs #.COMMENTS #---------------- # List of concerned databases. For other, just truncate transaction log. #db="master model sybsystemprocs archives ima metaviz" db="master model sybsystemprocs archives ima" sd=/home/sybase/logs tt=/tmp/SyTrLog.$$ e=`date +%Y.%m.%d` #### Dump completely the metaviz database; keep 7 copies rm=`(cd $sd ; ls -lt | fgrep metaviz.) | awk '{if(NR>7) print $NF}'` echo "....To remove: $rm" (cd $sd && pwd && ls -l | awk '{print " " $0}' && rm $rm) (echo 'dump database metaviz to "'$sd/metaviz.$e'"'; echo go) \ | $HOME/.isql | sed '/^Password/d' (cd $sd && pwd && ls -l ) # Keep, in file $tt.lis, the list of all databases # We issue the "helpdb" stored procedure which gives the # list of all databases. All these are then # dumped. (echo 'sp_helpdb'; echo 'go') | $HOME/.isql \ | sed '/^Password/d' \ | awk '/^ *[A-Za-z]/{if ($2 != "db_size") print $1}' \ | grep -v '^tempdb$' \ | sort > $tt.lis # Find out the list of non-dumped databases. echo $db | tr ' ' '\012' | sort \ | join -a2 -o 2.1 1.1 -e ==== - $tt.lis \ | awk '{printf("%-15s", $1); \ if ($2 == "====") printf(" ++++Transact Log Truncated");print""}' \ | tee $tt.st # Prepare an SQL script awk '\ BEGIN { sd = "'$sd/'"; e = "'.$e'" } \ { print "\nUse", $1; print "go" ; print "dbcc checktable(syslogs)\ngo"; \ print "sp_spaceused\ngo"; \ if (NF == 1) w = "to \"" sd $1 e "\" " ; \ else w = "with truncate_only"; \ print "dump transaction " $1, w "\ngo"; \ }' $tt.st > $tt.sql # Execute echo "`date +%Y.%j` ====Dump Transactions (`date`)" \ | tee -a $HOME/install/backup.log $HOME/.isql -e < $tt.sql | tee -a $HOME/install/backup.log | tee $tt.out # Prepare a Summary awk 'BEGIN{print "\n====Space Usage in various Databases===="; \ rule = "---" ; for(i=1; i<19; i++) rule = rule "----" ; print rule; \ printf("%-15s %8s %8s ", "Database", "Size", "Free"); \ printf("%10s(%%) %10s(%%) %10s(%%)\n", "Data", "Index", "Unused"); \ print rule } \ /^ *database_name / { getline;getline; printf("%-15s ", $1); s=$2; next}\ /^ *reserved / { getline;getline; printf("%8.1f %8.1f ", s, s-($1/1024.)) ; \ for (i=3; i<=7; i+=2) printf("%8.1f(%2d%%) ", $i/1024., (100.*$i)/$1) ; \ print "" } END { print rule} \ ' $tt.out rm $tt.* exit 0 ###################### Removed Code ###################################### for b in $db ; do (echo "USE $b"; echo "go"; echo "dbcc checktable(syslogs)"; echo "go"; \ echo sp_spaceused ; echo go ; \ echo "dump transaction $b to '$sd/$b.$e'"; echo "go") > $tt echo ""; echo "====Execute:"; cat $tt echo "`date +%Y.%j` ====Dump Transactions (`date`)" \ | tee -a $HOME/install/backup.log $HOME/.isql -e < $tt | tee -a $HOME/install/backup.log rm $tt done exit 0