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.

more ▼

asked Oct 09, 2012 at 08:53 AM in Default

avatar image

921 40 50 57

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.

Oct 09, 2012 at 09:12 AM sand143eep
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Oct 09, 2012 at 08:58 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 09, 2012 at 09:14 AM

avatar image

10.8k 37 57 51

no we dont have that thanks for suggestion i will create a job for index maintenance

Oct 09, 2012 at 09:48 AM sand143eep
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 09, 2012 at 12:59 PM

avatar image

ThomasRushton ♦♦
42.3k 20 57 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 09, 2012 at 08:53 AM

Seen: 856 times

Last Updated: Oct 09, 2012 at 12:59 PM

Copyright 2018 Redgate Software. Privacy Policy