DBCC shrinkfile

would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
while its running. Ive got a lot of extra space on some data files that I
want to shrink and was wondering if its safe to do it during our peak
hours... What does it do internally ? Any locking ,etc..Using SQL 2000Yes. It issues a lot of IO and takes short term X page locks. In internal
tests we've seen up to 20% drop in transaction throughput, depending on the
exact workload and hardware configuration. This is unavoidable due to the
operations shrink has to perform.
What proportion of the database size is free-space? Consider not doing the
shrink unless you're really desperate for the disk space or you *know* the
database size won't grow again. If you shrink, the odds are that the
database will have to grow again anyway. As always, depends on your exact
workload etc etc
It is always 'safe' to do a shrink.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uBcDfwnFEHA.2876@.TK2MSFTNGP09.phx.gbl...
> would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
> while its running. Ive got a lot of extra space on some data files that I
> want to shrink and was wondering if its safe to do it during our peak
> hours... What does it do internally ? Any locking ,etc..Using SQL 2000
>

DBCC INDEXDEFRAG causing blocking

I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
--
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>

DBCC INDEXDEFRAG causing blocking

I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>

DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
thorough job than INDEXDEFRAG.
Thanks in advance.
Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> thorough job than INDEXDEFRAG.
> Thanks in advance.
|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:

> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
>

DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
thorough job than INDEXDEFRAG.
Thanks in advance.Hi
read the following page
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:629A02AB-2F02-4210-9B57-463D111C47A8@.microsoft.com...
> We have a client who switched from using DBCC DBREINDEX to DBCC
> INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a
> more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:
> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
> >
> > We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> > because DBREINDEX caused some blocking issues. We suggested that DBCC
> > DBREINDEX does a more thorough job of handling fragmentation and that they
> > should really try to figure out what blocking is being caused instead of
> > switching to INDEXDEFRAG.
> > Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> > INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> > thorough job than INDEXDEFRAG.
> > Thanks in advance.
>

DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a mor
e
thorough job than INDEXDEFRAG.
Thanks in advance.Hi
read the following page
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:629A02AB-2F02-4210-9B57-463D111C47A8@.microsoft.com...
> We have a client who switched from using DBCC DBREINDEX to DBCC
> INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a
> more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRA
G
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a m
ore
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:

> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
>