Managing transaction logs
Transaction managing in simple mode :
here we will test log managing in both simple mode and full mode .
i have created two database one full one simple mode
added table on it
then i will run trsnaction but i will not commit the transaction
you can see the qurey completed but i have not commit the transaction now let’s check the log status
we can use the below qurey to check log status
select name , recovery_model_desc ,log_reuse_wait_desc from sys.databases
in simple mode there is already lost of transaction done
bit log reuse is set to nothing
you can see log reuse is set to active because i have not committed the transaction
let’s see the status of size of the log
dbcc sqlperf(logspace)
you can see log space by parentage the usage is about 97% which means log will get full soon and then it grow again , this problem because it might cause the disk space to fill or you set the log space to certain space let’s say 200mb then this will cause the SQL to crash.
let’s commit our transaction and check the status of log
the output indicate that the log is waiting on nothing , because its simple mode it will truncate the transaction to log
let’s see the log space status
the log space usage is only 8% usage put the log size has increased, when you truncate the log its doesn’t shrink the log file .
transaction manging in full mode :
make sure you have full backup of the databse
log backup we will not be taking unless you have full backup
we did same thing before and run transaction without commit
let’s see what log is wating for
here is saying active transaction ,
let’s check the log space usage
you can see the log usage is 99%
let’s commit our transaction and check the status
of log wait
its waiting for log backup we will take it then check the status
the log space usage is 86 percentage
let’s see what will happen when we take log back
the log is waiting on nothing
and log space is back to 8 % usage