Problem with Auto Shrink

Question

Problem with Auto Shrink

MANU-J.

SSC-Dedicated

Points: 31126
More actions
July 22, 2008 at 11:44 am

#70472

Hi,
I am facing one problem. Actually database AUTO SHRINK option was ON and I started the data deletion and import operation on the database.
Now database autoshrink started in background and now my data import process seems to be in hung state waiting for response from database.
What shall I do now as I can't kill shrink process as its an system process.
Manu

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try killing the data import. Is it blocked by the shrink? If so, the shrink will finish and then the import can continue.

You are going to switch the autoshrink off?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 2

You may check this article:

http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Gila,

Thanks for prompt reply. I have marked Auto shrink OFF now. Data import process is not blocked by this but its going very slow now. Already, 300 GB of data import has been done.

Would it be fine if I create one more data file big enough to leave ten percent of free space in database now(I think ten percent is the original percentage of free space threshold that is checked by auto shrink option).

Please advise.

Manu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If shrink is currently running, you will just have to wait for it to finish. Adding more files is not going to help if you have an ongoing shrink operation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Gila,

Many Thanks. I chked just now and found that Autoshrink is blocked by data import process.

What should I do now, I created one 150 GB data file too(but it won't help as you said).

Manu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Kill the data import, or wait for it to finish and to let the shrink complete. Depends how much time you have available and how easily/completely the import will roll back

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

Gila,

I am waiting for data import to finish on its own. I dont think sql server will kill it by itself. Will it?

Patience is the only weapon with me now.

Manu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

MANU (7/22/2008)
Gila,
I dont think sql server will kill it by itself. Will it?

Not unless it gets into a deadlock situation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 9

Gila,

Data import process completed its successful execution and then auto shrink succeeded too.

Thanks for all replies.

Manu

tosscrosby SSCoach Points: 18045 More actions · Answer 10

GilaMonster (7/22/2008)
Try killing the data import. Is it blocked by the shrink? If so, the shrink will finish and then the import can continue.
You are going to switch the autoshrink off?

Manu, Did you switch autoshrink to off as suggested, to prevent this from re-occurring??

-- You can't be late until you show up.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 11

Terry,

I turned it off when data import operation was going on(but I was already late by tht time).

Lesson learnt: Keep AUTO_SHRINK OFF.

Thanks for asking.

Manu