asked
Shrinking issue
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.
the process which i followed in shrinking the database is just by going to the tasks and selected the shrink database option and selected to shrink up to 10%. that is the process i followed nothing i did after that is this a correct way i did please suggest.i havent taken any backup of log nothing after shrinking.i saw to day the differential backup of that database takes only 2mb but today it has taken 5gb for delta. i am worried about it please guide me.
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.
no we dont have that thanks for suggestion i will create a job for index maintenance
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.
12 People are following this question.
