Hello All, yesterday i shrinked a database whose datafile size is 16GB and log file size is 8gb then i shrinked the log file to 89mb from 8gb. but today when i am monitoring the database size again i found the log file size is 6 gb it drastically increased in 24hrs.now i am bit worried as tommorow is it going to grow again please support how to tackle now.please suggest if i shrink again will the problem gets resolved. even i found the temp db size is also increased from 4mb to 4gb in 24 hrs only is this also related to the shrinking done.
I would expect the log file grew to accommodate a large transaction. Now that the transaction is complete the log file will be mostly empty but wont need to grow when a large transaction takes place again. Growing files on disk is a slow process and hurts performance so I would recommend leaving the log file at the size it is for a while and checking on whether it grows any further. In general terms repeatedly shrinking database and log files is not recommended.
Do you have any index maintenance job scheduled? Defragmenting the indexes generally makes your log file grow. If so, Plan log backup before and after the index defragment process, this truncates the log and can be reused.
Get a transaction log backup process scheduled. If the T-log growth is still a problem during the reindex job, then you could look at increasing the frequency of the T-log backups during the reindexing (implement a second schedule). The reason your DIFF backup is so huge after shrinking the database is that the DIFF backup contains all the data pages that have been altered since the last full backup. The database shrink would have moved a lot of data pages around, hence the large differential. Another good argument against regular shrink jobs.