x

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

sand143eep gravatar image

sand143eep
811 27 37 41

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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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

Cyborg gravatar image

Cyborg
10.6k 36 40 45

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

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1949
x50
x37

asked: Oct 09, 2012 at 08:53 AM

Seen: 675 times

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