DBCC DBReindex follwed by DBCC ShrinkFile

We have a client who is running DBCC DBReindex (all tables) followed by DBCC
ShrinkFile on the log and data files with AutoShrink on their database
turned on.
We have already advised them to turn autoshrink off.
In our tests running a show contig after each operation the index
optimization is erased by running the ShrinkFile operation with many indexes
showing 100% fragmentation.
Does anyone know of documentation which reflects this circumstance?I haven't seen any documentation per se, saying that shrink causes fragmenta
tion. But if you read about what
shrink does and how it work, it is clear that you get fragmented data after
the shrink. I recommend below two
articles:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.cox.net> wrote in message news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl
..
> We have a client who is running DBCC DBReindex (all tables) followed by DB
CC
> ShrinkFile on the log and data files with AutoShrink on their database
> turned on.
> We have already advised them to turn autoshrink off.
> In our tests running a show contig after each operation the index
> optimization is erased by running the ShrinkFile operation with many index
es
> showing 100% fragmentation.
> Does anyone know of documentation which reflects this circumstance?
>
>|||It looks like shrinkdatabase does not have the same implications (this is
what the built in maintenance plan uses as viewed through profiler)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> I haven't seen any documentation per se, saying that shrink causes
fragmentation. But if you read about what
> shrink does and how it work, it is clear that you get fragmented data
after the shrink. I recommend below two
> articles:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gene Black" <geblack@.cox.net> wrote in message
news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
DBCC[vbcol=seagreen]
indexes[vbcol=seagreen]
>|||Shrinkdatabase does the same thing as shrinkfile, but it targets all the fil
es for the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.hotmail.com> wrote in message news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.
gbl...
> It looks like shrinkdatabase does not have the same implications (this is
> what the built in maintenance plan uses as viewed through profiler)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> fragmentation. But if you read about what
> after the shrink. I recommend below two
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> DBCC
> indexes
>|||Evidently it doesn't since the results of showcontig don't reflect
fragmentation after the dbcc shrinkdatabase
I did
1. sp_msforeachtable 'dbcc dbreindex(''?'')'
dbcc showcontig for all tables
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
2. DBCC shrinkfile (2,5) then showcontig
results:
ADBForecasts-2 98%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 98%
ADBTasks-3 0%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 98%
ADBWorkSheetTasks-3 92%
ADBWorkSheetTasks-4 96%
ADBWorkSheetTasks-5 96%
ADBWorkSheetTasks-19 98%
3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
4. dbcc shrinkdatabase then showcontig
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Shrinkdatabase does the same thing as shrinkfile, but it targets all the
files for the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gene Black" <geblack@.hotmail.com> wrote in message
news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
is[vbcol=seagreen]
in[vbcol=seagreen]
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx[vbcol=seagreen]
by[vbcol=seagreen]
database[vbcol=seagreen]
>|||Note:
this database had autoshrink off
the shinkdatbase command was
DBCC shrinkdatabase(N'AutoADB', 10, TRUNCATEONLY )
could it be the truncateonly option which is causing the difference?
"Gene Black" <geblack@.hotmail.com> wrote in message
news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> Evidently it doesn't since the results of showcontig don't reflect
> fragmentation after the dbcc shrinkdatabase
> I did
> 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> dbcc showcontig for all tables
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> 2. DBCC shrinkfile (2,5) then showcontig
> results:
> ADBForecasts-2 98%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 98%
> ADBTasks-3 0%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 98%
> ADBWorkSheetTasks-3 92%
> ADBWorkSheetTasks-4 96%
> ADBWorkSheetTasks-5 96%
> ADBWorkSheetTasks-19 98%
> 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
>
> 4. dbcc shrinkdatabase then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> files for the database.
> news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> is
wrote[vbcol=seagreen]
> in
data[vbcol=seagreen]
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
followed[vbcol=seagreen]
> by
> database
many[vbcol=seagreen]
circumstance?[vbcol=seagreen]
>|||It looks like the difference is the TruncateOnly option
TRUNCATEONLY
Causes any unused space in the files to be released to the operating system
and shrinks the file to the last allocated extent, reducing the file size
without moving any data. No attempt is made to relocate rows to unallocated
pages. target_size is ignored when TRUNCATEONLY is used.
"Gene Black" <geblack@.hotmail.com> wrote in message
news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> Evidently it doesn't since the results of showcontig don't reflect
> fragmentation after the dbcc shrinkdatabase
> I did
> 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> dbcc showcontig for all tables
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> 2. DBCC shrinkfile (2,5) then showcontig
> results:
> ADBForecasts-2 98%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 98%
> ADBTasks-3 0%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 98%
> ADBWorkSheetTasks-3 92%
> ADBWorkSheetTasks-4 96%
> ADBWorkSheetTasks-5 96%
> ADBWorkSheetTasks-19 98%
> 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
>
> 4. dbcc shrinkdatabase then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> files for the database.
> news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> is
wrote[vbcol=seagreen]
> in
data[vbcol=seagreen]
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
followed[vbcol=seagreen]
> by
> database
many[vbcol=seagreen]
circumstance?[vbcol=seagreen]
>|||> It looks like the difference is the Truncate option
Yep, as it only cuts of the files at the end, it doesn't move any data... :-
)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.hotmail.com> wrote in message news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.g
bl...
> It looks like the difference is the TruncateOnly option
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating syste
m
> and shrinks the file to the last allocated extent, reducing the file size
> without moving any data. No attempt is made to relocate rows to unallocate
d
> pages. target_size is ignored when TRUNCATEONLY is used.
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> in
> wrote
> data
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> followed
> many
> circumstance?
>|||Yes, truncate only does not move data. As Tibor pointed out Shrink Database
does exactly the same work as ShrinkFile with the exception that
ShrinkDatabase combines both the data and log file(s). And it does not give
you any control what so ever as to how much it shrinks one or the other.
That is why I always recommend using ShrinkFile. Srinking a file that was
just defragged will always refragment it to some degree. In most case a
great deal. DBA's who shrink their data or log files each night, especially
after a reindex should just be shot<g>.
Andrew J. Kelly SQL MVP
"Gene Black" <geblack@.hotmail.com> wrote in message
news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
> It looks like the difference is the TruncateOnly option
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
system
> and shrinks the file to the last allocated extent, reducing the file size
> without moving any data. No attempt is made to relocate rows to
unallocated
> pages. target_size is ignored when TRUNCATEONLY is used.
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> in
the[vbcol=seagreen]
(this[vbcol=seagreen]
> wrote
> data
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> followed
index[vbcol=seagreen]
> many
> circumstance?
>|||Thanks for the help guys, even though it took a little time for me to 'see
the light'
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGyaU7kgEHA.3676@.TK2MSFTNGP12.phx.gbl...
> Yes, truncate only does not move data. As Tibor pointed out Shrink
> Database
> does exactly the same work as ShrinkFile with the exception that
> ShrinkDatabase combines both the data and log file(s). And it does not
> give
> you any control what so ever as to how much it shrinks one or the other.
> That is why I always recommend using ShrinkFile. Srinking a file that was
> just defragged will always refragment it to some degree. In most case a
> great deal. DBA's who shrink their data or log files each night,
> especially
> after a reindex should just be shot<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
> system
> unallocated
> the
> (this
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> index
>