grande dbcc shrinkfile confusion

Okay - here's what's happening. I have a weekly job which uses dbcc shrinkfi
le to shrink a 60GB database down to a 40GB database. Usually runs okay - ta
kes about 10 hours - but recently it has been failing with error 3140 after
running for the 10 hours. T
he DB shows as still being 60GB but when I start the job again it runs for a
couple seconds, shows success, and suddenly the DB is 40GB. Does anyone kno
w why this is happening? There is another job that shrinks all the DBs on th
at server using the MS SQL
shrink option and it runs toward the end of the dbcc shrinkfile job. It has
never interfered with it before but it's the only other thing happening. Cou
ld it be the problem?Kimmy
This may be a silly question but if the database grows to 60GB every week, w
hy do you keep shrinking it? It obviously needs to be 60gb. By constantly sh
rinking it all you are doing is creating a situation were you will get fragm
entation at both the operat
ing system and the database level.
Regardless, a shrinkfile should not take 10 hours. You are running something
else that stops it from running. A full maintenance plan maybe? Check what
is running at that time and if you really must run it, run it at another tim
e.
Rergards
John|||Hi,
My recommendation would be to do this DBCC command at a low-use time in any
case. I feel the other job might have interferred.
If you are very concerned about performance hits you could first try DBCC
SHRINKDATABASE (or SHRINKFILE) with TRUNCATEONLY.
This will only pick empty pages from the end of the file or database without
doing any reorganization.
This option will take only very less time.
Thanks
Hari
MCDBA
"kimmy" <anonymous@.discussions.microsoft.com> wrote in message
news:38E133F3-7B71-4610-AC92-4D5889B5E8CE@.microsoft.com...
> Okay - here's what's happening. I have a weekly job which uses dbcc
shrinkfile to shrink a 60GB database down to a 40GB database. Usually runs
okay - takes about 10 hours - but recently it has been failing with error
3140 after running for the 10 hours. The DB shows as still being 60GB but
when I start the job again it runs for a couple seconds, shows success, and
suddenly the DB is 40GB. Does anyone know why this is happening? There is
another job that shrinks all the DBs on that server using the MS SQL shrink
option and it runs toward the end of the dbcc shrinkfile job. It has never
interfered with it before but it's the only other thing happening. Could it
be the problem?|||There is no maintenance running at that time on that server, except the othe
r shrinks which do happen toward the end. How long is a shrink on a 60 GB da
tabase supposed to take? I have other large databases (between 12 and 20 GB)
that take at least a few h
ours to run so I am not disturbed by how long it takes, only that it runs fo
r the full time, appears to fail, but when run again immediately shrinks the
database by 20 GB and returns success. You're right it is silly to have to
shrink it every week by so
much, but the vendor application my customers use is so badly written that a
third of the database fills up with white space every week. If I don't keep
shrinking it, it we will be up over 100 GB within three weeks and no backup
s will run.|||Kimmy
I must say I don't know how long a shrink takes as I never shrink databases,
but I thought 10 hours sounded excessive. The white space you are talking a
bout is this empty pages cause by file fragmentation? You would be better of
f rebuilding your tables th
an shrinking the database. If the growth in your database is not caused by d
ata growth but high data change, there should be a better way to stabilise i
t.
I would think either dbcc dbreindex or create index with Drop_existing param
eter should be your way to go.
Regards
John|||I guess I need to change the way we are doing the shrink - Thanks everyone f
or your help and advice.sql