DBCC Shrinkfile

Hi,
I am plng to shrink a production database logfile from
14 GB to 3 GB. Does this slow down the application or does
it block users from accessing the database while i am
shrinking it ? It is an OLTP system. Also any idea how
much time shrinking a 14GB file would take ? Just
ballpark ..
TIA
MOHi MO,
Its best to update your stats on the DB and run your DBCCs first then do a
backup. The shrik should not take that long I would expect 15 min at the
outside. However, it will slow the system down and the more load from users
that is on the box the longer it will take. I would recommend shceduling
this for a slow time of the day I usually do this after 3am.
Hope that helps
John ...
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> Hi,
> I am plng to shrink a production database logfile from
> 14 GB to 3 GB. Does this slow down the application or does
> it block users from accessing the database while i am
> shrinking it ? It is an OLTP system. Also any idea how
> much time shrinking a 14GB file would take ? Just
> ballpark ..
> TIA
> MO|||Mo - there's no way to predict how long a shrink will take on a live system
as it could block waiting for a lock or spend time searching for free space
if the space usage within the database is fragmented. It also depends on how
much heap and text usage you have, the speed of your IO subsystem, the
concurrent workload (as John says below) and so on. I'd be very surprised to
see it take only 15 minutes. The best you can do is time it on a completely
quiescent system and then extrapolate.
Shrink is setup to be the deadlock victim in all cases so should not cause
deadlocks. It does not hold long-term locks so should not block although
because it does hold page locks while moving pages, and does a bunch of IO
you should expect some slowdown. On a test system setup with a TPCC
benchmark workload running at close to 100% cpu, I've seen a roughly 20%
drop in transaction throughput while shrink is running - YMMV.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John VanderVliet" <john.vandervliet@.sjrb.ca.NOSPAM> wrote in message
news:e6MPetW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi MO,
> Its best to update your stats on the DB and run your DBCCs first then do a
> backup. The shrik should not take that long I would expect 15 min at the
> outside. However, it will slow the system down and the more load from
users
> that is on the box the longer it will take. I would recommend shceduling
> this for a slow time of the day I usually do this after 3am.
> Hope that helps
> John ...
> "Mo" <anonymous@.discussions.microsoft.com> wrote in message
> news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> > Hi,
> > I am plng to shrink a production database logfile from
> > 14 GB to 3 GB. Does this slow down the application or does
> > it block users from accessing the database while i am
> > shrinking it ? It is an OLTP system. Also any idea how
> > much time shrinking a 14GB file would take ? Just
> > ballpark ..
> > TIA
> > MO
>

DBCC Shrinkfile

Hi,
I am plng to shrink a production database logfile from
14 GB to 3 GB. Does this slow down the application or does
it block users from accessing the database while i am
shrinking it ? It is an OLTP system. Also any idea how
much time shrinking a 14GB file would take ? Just
ballpark ..
TIA
MOHi MO,
Its best to update your stats on the DB and run your DBCCs first then do a
backup. The shrik should not take that long I would expect 15 min at the
outside. However, it will slow the system down and the more load from users
that is on the box the longer it will take. I would recommend shceduling
this for a slow time of the day I usually do this after 3am.
Hope that helps
John ...
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
> Hi,
> I am plng to shrink a production database logfile from
> 14 GB to 3 GB. Does this slow down the application or does
> it block users from accessing the database while i am
> shrinking it ? It is an OLTP system. Also any idea how
> much time shrinking a 14GB file would take ? Just
> ballpark ..
> TIA
> MO|||Mo - there's no way to predict how long a shrink will take on a live system
as it could block waiting for a lock or spend time searching for free space
if the space usage within the database is fragmented. It also depends on how
much heap and text usage you have, the speed of your IO subsystem, the
concurrent workload (as John says below) and so on. I'd be very surprised to
see it take only 15 minutes. The best you can do is time it on a completely
quiescent system and then extrapolate.
Shrink is setup to be the deadlock victim in all cases so should not cause
deadlocks. It does not hold long-term locks so should not block although
because it does hold page locks while moving pages, and does a bunch of IO
you should expect some slowdown. On a test system setup with a TPCC
benchmark workload running at close to 100% cpu, I've seen a roughly 20%
drop in transaction throughput while shrink is running - YMMV.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John VanderVliet" <john.vandervliet@.sjrb.ca.NOSPAM> wrote in message
news:e6MPetW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi MO,
> Its best to update your stats on the DB and run your DBCCs first then do a
> backup. The shrik should not take that long I would expect 15 min at the
> outside. However, it will slow the system down and the more load from
users
> that is on the box the longer it will take. I would recommend shceduling
> this for a slow time of the day I usually do this after 3am.
> Hope that helps
> John ...
> "Mo" <anonymous@.discussions.microsoft.com> wrote in message
> news:068901c3fd47$74bb8ea0$a601280a@.phx.gbl...
>sql

dbcc checkdatabase

I got a call from my manager because users were complaining about the server
being slow, and he said he found a lots of locks on the server.
I logged in and I see there is a process running DBCC CHECKDATABASE on
(DYNAMICS) the main Great Plains database.
I'm the DBA, there are many users that know the SA password, I don't think
any of them executed the command.
My question is: Anybody in this forum knows of a program that would execute
this command by itself?
Is there a reason for a program or the server to to do this by itself?
I have read the documentation and they advise to use it when the load is
light because it could impact disk I/O.
Thanks
Tim Bales
timbales@.bellsouth.netHave you checked to see if there is a scheduled job that might have run
this?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tim Bales" <timbales@.bellsouth.net> wrote in message
news:Oielg63YIHA.5612@.TK2MSFTNGP06.phx.gbl...
>I got a call from my manager because users were complaining about the
>server being slow, and he said he found a lots of locks on the server.
> I logged in and I see there is a process running DBCC CHECKDATABASE on
> (DYNAMICS) the main Great Plains database.
> I'm the DBA, there are many users that know the SA password, I don't think
> any of them executed the command.
> My question is: Anybody in this forum knows of a program that would
> execute this command by itself?
> Is there a reason for a program or the server to to do this by itself?
> I have read the documentation and they advise to use it when the load is
> light because it could impact disk I/O.
> Thanks
> Tim Bales
> timbales@.bellsouth.net
>|||Andrew is right. Great Plains creates a number of jobs for varying
purposes.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:epV4%23K4YIHA.4828@.TK2MSFTNGP05.phx.gbl...
> Have you checked to see if there is a scheduled job that might have run
> this?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tim Bales" <timbales@.bellsouth.net> wrote in message
> news:Oielg63YIHA.5612@.TK2MSFTNGP06.phx.gbl...
>>I got a call from my manager because users were complaining about the
>>server being slow, and he said he found a lots of locks on the server.
>> I logged in and I see there is a process running DBCC CHECKDATABASE on
>> (DYNAMICS) the main Great Plains database.
>> I'm the DBA, there are many users that know the SA password, I don't
>> think any of them executed the command.
>> My question is: Anybody in this forum knows of a program that would
>> execute this command by itself?
>> Is there a reason for a program or the server to to do this by itself?
>> I have read the documentation and they advise to use it when the load is
>> light because it could impact disk I/O.
>> Thanks
>> Tim Bales
>> timbales@.bellsouth.net
>|||I've just found GP to be a pain, in general. It opens a ton of
connections...it's slow, in general. Anyone else seeing this behavior?
Thanks.
Chris
"Tim Bales" wrote:
> I got a call from my manager because users were complaining about the server
> being slow, and he said he found a lots of locks on the server.
> I logged in and I see there is a process running DBCC CHECKDATABASE on
> (DYNAMICS) the main Great Plains database.
> I'm the DBA, there are many users that know the SA password, I don't think
> any of them executed the command.
> My question is: Anybody in this forum knows of a program that would execute
> this command by itself?
> Is there a reason for a program or the server to to do this by itself?
> I have read the documentation and they advise to use it when the load is
> light because it could impact disk I/O.
> Thanks
> Tim Bales
> timbales@.bellsouth.net
>
>