dbcc shrinkfile

I used dbcc shrinkfile to shrink transaction log, but it worked for only one day. When I checked the properties, transaction log was back to the size I started with. TL was 1586 MB and I set the target size to 1 MB. Any idea why it happened?...i believe the scope of the size specification is limited just to the run you executed, and as transactions build up it will grow, you may see that it is not using all of the space allocated (thru taskpad view) but what that means is that it had grown to the current size thru autogrow, to meet the needs of the log...

...two things to check, see how the tran log growth is specified (look at the databases attribute), and also look at how often the log is backed up. It could be that a batch job executes a large number of transactions causing it to grow to its current size and then releasing it once a backup has occured.

...this note assumes that the database is in "FULL" recovery mode and that tran log backups are occuring...

...given the previous statement it could be that if growth is a result of a large batch run that the log size is optimal and continued shrinking would only add overhead as it would have to repeatedly "autogrow"...

...hope this helps...sql

DBCC ShrinkDatabase on large DB

Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>

DBCC ShrinkDatabase on large DB

Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateo
nly')
go
dbcc shrinkfile('logical_ldf_name','truncateo
nly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>

DBCC ShrinkDatabase on large DB

Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian King
Have you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
sql

DBCC shrinkdatabase

Hi All
I am currently in a catch 22 situation. I need to shrink my database before
i can back it up ( as the time it takes to back up now has gone over the
time allocate to the backup process) .
Is it safe to run the DBCC SHRINKDATABASE (DBName,0,TRUNCATEONLY) command on
a production database ( 450GB with 9000 + connections ) ?
Any info with this regard will be highly appreciated.
Thanks
Elrond
The backup process only backs up the data...not the empty space in the MDF
file.
Shrink only removes the empty space, so I don't think it will gain you
anything.
Unless I am incrorect in the above, you may want to investigate some 3rd
party utilities that compress the backup as it is done, which saves time and
drive space.
Red gate makes SQL Backup ($295/server)
Quest sells SQL Litespeed (price based on version/processors, but higher
than red gate)
Both are good products.
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"news.microsoft.com" <someone@.microsoft.com> wrote in message
news:OxE4j8XCHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Hi All
> I am currently in a catch 22 situation. I need to shrink my database
> before i can back it up ( as the time it takes to back up now has gone
> over the time allocate to the backup process) .
> Is it safe to run the DBCC SHRINKDATABASE (DBName,0,TRUNCATEONLY) command
> on a production database ( 450GB with 9000 + connections ) ?
> Any info with this regard will be highly appreciated.
> Thanks
> Elrond
>
|||Kevin is right on the money. The time to backup a db is not affected by the
amount of free space only the data. I would use one of the 3rd party tools
to compress the backups on the fly. You may also want to look at using some
sort of hardware backups using the SAN or filegroup backups.
Andrew J. Kelly SQL MVP
"news.microsoft.com" <someone@.microsoft.com> wrote in message
news:OxE4j8XCHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Hi All
> I am currently in a catch 22 situation. I need to shrink my database
> before i can back it up ( as the time it takes to back up now has gone
> over the time allocate to the backup process) .
> Is it safe to run the DBCC SHRINKDATABASE (DBName,0,TRUNCATEONLY) command
> on a production database ( 450GB with 9000 + connections ) ?
> Any info with this regard will be highly appreciated.
> Thanks
> Elrond
>
sql

DBCC SHOWCONTIG shows no result

Hello
Trying to get some fragmentation info back on SQL2000 tables but no results
are returned. Is this a bug?
DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
DBCC SHOWCONTIG ('MsgAudit_CBOT')
DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
-- all above tables exist and have clustered index with millions of rows.
tks
all queries produce no result:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
-- cranfield, DBAAre you certain you really execute the queries? Press the "execute" button? Also, can you try using
OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> Hello
> Trying to get some fragmentation info back on SQL2000 tables but no results
> are returned. Is this a bug?
> DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> ALL_INDEXES, NO_INFOMSGS
> DBCC SHOWCONTIG ('MsgAudit_CBOT')
> DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> -- all above tables exist and have clustered index with millions of rows.
> tks
>
> all queries produce no result:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA|||I am certain query is executed. Have also tried osql - still no results, just
as below. My SQL2005 table returns results but NOT SQL2000
1> use eqmclog
2> go
1> dbcc showcontig('providersourceinfo')
2> go
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
1>
very strange this...
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Are you certain you really execute the queries? Press the "execute" button? Also, can you try using
> OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> > Hello
> >
> > Trying to get some fragmentation info back on SQL2000 tables but no results
> > are returned. Is this a bug?
> >
> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> > ALL_INDEXES, NO_INFOMSGS
> >
> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >
> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >
> > -- all above tables exist and have clustered index with millions of rows.
> >
> > tks
> >
> >
> > all queries produce no result:
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
>|||What happens if you omit the single quotes? As in DBCC SHOWCONTIG
(MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
2000 BOL, it does not indicate the use of single quotes around the object
name, but does in the SQL Server 2005 topics.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>I am certain query is executed. Have also tried osql - still no results,
>just
> as below. My SQL2005 table returns results but NOT SQL2000
> 1> use eqmclog
> 2> go
> 1> dbcc showcontig('providersourceinfo')
> 2> go
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> 1>
> very strange this...
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Are you certain you really execute the queries? Press the "execute"
>> button? Also, can you try using
>> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> exist.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> > Hello
>> >
>> > Trying to get some fragmentation info back on SQL2000 tables but no
>> > results
>> > are returned. Is this a bug?
>> >
>> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> > ALL_INDEXES, NO_INFOMSGS
>> >
>> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >
>> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >
>> > -- all above tables exist and have clustered index with millions of
>> > rows.
>> >
>> > tks
>> >
>> >
>> > all queries produce no result:
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA|||no, omitting quotes makes no difference. MS uses quotes themselves in their
BOL samples.
Just to make sure I have the correct tables:
select 'DBCC SHOWCONTIG ('+name+')'
from sysobjects
where type = 'u'
DBCC SHOWCONTIG (DataRecoveryLog)
DBCC SHOWCONTIG (ProviderSourceInfo)
DBCC SHOWCONTIG (MsgAudit_CBOT)
DBCC SHOWCONTIG (MsgAudit_KCBT)
DBCC SHOWCONTIG (MsgAudit_MGEX)
DBCC SHOWCONTIG (MsgAudit_WCE)
DBCC SHOWCONTIG (MsgAudit_JADE)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
-- cranfield, DBA
"Gail Erickson [MS]" wrote:
> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> 2000 BOL, it does not indicate the use of single quotes around the object
> name, but does in the SQL Server 2005 topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >I am certain query is executed. Have also tried osql - still no results,
> >just
> > as below. My SQL2005 table returns results but NOT SQL2000
> >
> > 1> use eqmclog
> > 2> go
> > 1> dbcc showcontig('providersourceinfo')
> > 2> go
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > 1>
> >
> > very strange this...
> > --
> > -- cranfield, DBA
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you certain you really execute the queries? Press the "execute"
> >> button? Also, can you try using
> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> exist.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> > Hello
> >> >
> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> > results
> >> > are returned. Is this a bug?
> >> >
> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> > ALL_INDEXES, NO_INFOMSGS
> >> >
> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >
> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >
> >> > -- all above tables exist and have clustered index with millions of
> >> > rows.
> >> >
> >> > tks
> >> >
> >> >
> >> > all queries produce no result:
> >> >
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> >
> >> >
> >> >
> >> > --
> >> > -- cranfield, DBA
> >>
>
>|||Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
TABLERESULTS, you get text back (use the correct tab in QA results).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> no, omitting quotes makes no difference. MS uses quotes themselves in their
> BOL samples.
> Just to make sure I have the correct tables:
> select 'DBCC SHOWCONTIG ('+name+')'
> from sysobjects
> where type = 'u'
> DBCC SHOWCONTIG (DataRecoveryLog)
> DBCC SHOWCONTIG (ProviderSourceInfo)
> DBCC SHOWCONTIG (MsgAudit_CBOT)
> DBCC SHOWCONTIG (MsgAudit_KCBT)
> DBCC SHOWCONTIG (MsgAudit_MGEX)
> DBCC SHOWCONTIG (MsgAudit_WCE)
> DBCC SHOWCONTIG (MsgAudit_JADE)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA
>
> "Gail Erickson [MS]" wrote:
>> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> 2000 BOL, it does not indicate the use of single quotes around the object
>> name, but does in the SQL Server 2005 topics.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >I am certain query is executed. Have also tried osql - still no results,
>> >just
>> > as below. My SQL2005 table returns results but NOT SQL2000
>> >
>> > 1> use eqmclog
>> > 2> go
>> > 1> dbcc showcontig('providersourceinfo')
>> > 2> go
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > 1>
>> >
>> > very strange this...
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Are you certain you really execute the queries? Press the "execute"
>> >> button? Also, can you try using
>> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> exist.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> > Hello
>> >> >
>> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> > results
>> >> > are returned. Is this a bug?
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >
>> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >
>> >> > -- all above tables exist and have clustered index with millions of
>> >> > rows.
>> >> >
>> >> > tks
>> >> >
>> >> >
>> >> > all queries produce no result:
>> >> >
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > -- cranfield, DBA
>> >>
>>|||Hi Tibor
I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
not return results for empty tables.
doh!
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
> TABLERESULTS, you get text back (use the correct tab in QA results).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> > no, omitting quotes makes no difference. MS uses quotes themselves in their
> > BOL samples.
> >
> > Just to make sure I have the correct tables:
> >
> > select 'DBCC SHOWCONTIG ('+name+')'
> > from sysobjects
> > where type = 'u'
> >
> > DBCC SHOWCONTIG (DataRecoveryLog)
> > DBCC SHOWCONTIG (ProviderSourceInfo)
> > DBCC SHOWCONTIG (MsgAudit_CBOT)
> > DBCC SHOWCONTIG (MsgAudit_KCBT)
> > DBCC SHOWCONTIG (MsgAudit_MGEX)
> > DBCC SHOWCONTIG (MsgAudit_WCE)
> > DBCC SHOWCONTIG (MsgAudit_JADE)
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> >> 2000 BOL, it does not indicate the use of single quotes around the object
> >> name, but does in the SQL Server 2005 topics.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no rights
> >> Download the latest version of Books Online from
> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >> >I am certain query is executed. Have also tried osql - still no results,
> >> >just
> >> > as below. My SQL2005 table returns results but NOT SQL2000
> >> >
> >> > 1> use eqmclog
> >> > 2> go
> >> > 1> dbcc showcontig('providersourceinfo')
> >> > 2> go
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> > 1>
> >> >
> >> > very strange this...
> >> > --
> >> > -- cranfield, DBA
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Are you certain you really execute the queries? Press the "execute"
> >> >> button? Also, can you try using
> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> >> exist.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> >> > Hello
> >> >> >
> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> >> > results
> >> >> > are returned. Is this a bug?
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> >> > ALL_INDEXES, NO_INFOMSGS
> >> >> >
> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >> >
> >> >> > -- all above tables exist and have clustered index with millions of
> >> >> > rows.
> >> >> >
> >> >> > tks
> >> >> >
> >> >> >
> >> >> > all queries produce no result:
> >> >> >
> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> >> > system administrator.
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > -- cranfield, DBA
> >> >>
> >>
> >>
> >>
>|||Ouch... That was not obvious! Thanks for posting back. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:FF387413-A310-4994-8A65-B118987CBF42@.microsoft.com...
> Hi Tibor
> I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
> not return results for empty tables.
> doh!
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
>> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
>> TABLERESULTS, you get text back (use the correct tab in QA results).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
>> > no, omitting quotes makes no difference. MS uses quotes themselves in their
>> > BOL samples.
>> >
>> > Just to make sure I have the correct tables:
>> >
>> > select 'DBCC SHOWCONTIG ('+name+')'
>> > from sysobjects
>> > where type = 'u'
>> >
>> > DBCC SHOWCONTIG (DataRecoveryLog)
>> > DBCC SHOWCONTIG (ProviderSourceInfo)
>> > DBCC SHOWCONTIG (MsgAudit_CBOT)
>> > DBCC SHOWCONTIG (MsgAudit_KCBT)
>> > DBCC SHOWCONTIG (MsgAudit_MGEX)
>> > DBCC SHOWCONTIG (MsgAudit_WCE)
>> > DBCC SHOWCONTIG (MsgAudit_JADE)
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Gail Erickson [MS]" wrote:
>> >
>> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> >> 2000 BOL, it does not indicate the use of single quotes around the object
>> >> name, but does in the SQL Server 2005 topics.
>> >>
>> >> --
>> >> Gail Erickson [MS]
>> >> SQL Server Documentation Team
>> >> This posting is provided "AS IS" with no warranties, and confers no rights
>> >> Download the latest version of Books Online from
>> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >> >I am certain query is executed. Have also tried osql - still no results,
>> >> >just
>> >> > as below. My SQL2005 table returns results but NOT SQL2000
>> >> >
>> >> > 1> use eqmclog
>> >> > 2> go
>> >> > 1> dbcc showcontig('providersourceinfo')
>> >> > 2> go
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> > 1>
>> >> >
>> >> > very strange this...
>> >> > --
>> >> > -- cranfield, DBA
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Are you certain you really execute the queries? Press the "execute"
>> >> >> button? Also, can you try using
>> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> >> exist.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> >> > Hello
>> >> >> >
>> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> >> > results
>> >> >> > are returned. Is this a bug?
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >> >
>> >> >> > -- all above tables exist and have clustered index with millions of
>> >> >> > rows.
>> >> >> >
>> >> >> > tks
>> >> >> >
>> >> >> >
>> >> >> > all queries produce no result:
>> >> >> >
>> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> >> > system administrator.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > -- cranfield, DBA
>> >> >>
>> >>
>> >>
>> >>

DBCC SHOWCONTIG & DB Performance

Hi,
If I run DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES on our DB I
get info back our indexes. As I understand it, an indexid = 0 is the index
representing the heap of a table with no clustered index, so we don't need t
o
worry about the scandensity or fragmentation of these for db performance.
Indexid's 1-254 are actual indexes & may need looking at if low scandensity
or high fragmentation.
What does an index with an indexid = 255 represent though, and do they have
an impact on performance?
TIASteve,
Indid = 255 is the entry for tables with text / image columns, for example,
if you create table t(colA text), then you will have two entries in table
sysindexes for this table idnid = 0 and indid = 255. I do not know if we
should defrag indid = 255.
AMB
"Steve" wrote:

> Hi,
> If I run DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES on our DB I
> get info back our indexes. As I understand it, an indexid = 0 is the index
> representing the heap of a table with no clustered index, so we don't need
to
> worry about the scandensity or fragmentation of these for db performance.
> Indexid's 1-254 are actual indexes & may need looking at if low scandensit
y
> or high fragmentation.
> What does an index with an indexid = 255 represent though, and do they hav
e
> an impact on performance?
> TIAsql

DBCC SHOWCONTIG - what's relevant?

It's my understanding that with DBCC SHOWCONTIG WITH FAST,
TABLERESULTS I'll
get back:
ObjectName
ObjectId
IndexName
IndexId
Pages
ExtentSwitches
ScanDensity
BestCount
ActualCount
LogicalFragmentation
My question is what's relevant for a heap? I've noticed that I'll get
back values for all the columns (the ones mentioned above plus Rows,
MinimumRecordSize, MaximumRecordSize, AverageRecordSize,
ForwardedRecords, Extents, AverageFreeBytes, AveragePageDensity, and
ExtentFragmentation) when it's a heap. Are any of these values "real"
for the heaps?
Also, I know that Logical Fragmentation isn't relevant for heaps- are
there any other metrics that aren't relevant? Are there any metrics
that aren't relevant for clustered tables? How about non-clustered
indexes on heap tables vs. ones on clustered tables?I think all the fields still have meaning, it is just there isn't much you
can do about bad numbers, short of creating/dropping a clustered index.
Books on line reports some of these fields as not relevant, but it is more
accurate to say that the value will always be the "best" possible value due
to the way heaps are scanned... Take logical fragmentation, it represents
the number of out of order pages.. When reading through the linked list of a
clustered index, an out of order page is where the logical order of pages in
the linked list does not matcht the physical order of the pages on disk...
When SQL reads a heap, it reads using the physical order on disk, so there
is no logical fragmentation. Scan density will be a good number for the same
reason. I would guess the best information would be relative to how full the
pages are, and how many forwarding pointers there are...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sleepy" <sleepysqlgirl@.yahoo.com> wrote in message
news:bf62573b.0411291644.47cef884@.posting.google.com...
> It's my understanding that with DBCC SHOWCONTIG WITH FAST,
> TABLERESULTS I'll
> get back:
> ObjectName
> ObjectId
> IndexName
> IndexId
> Pages
> ExtentSwitches
> ScanDensity
> BestCount
> ActualCount
> LogicalFragmentation
> My question is what's relevant for a heap? I've noticed that I'll get
> back values for all the columns (the ones mentioned above plus Rows,
> MinimumRecordSize, MaximumRecordSize, AverageRecordSize,
> ForwardedRecords, Extents, AverageFreeBytes, AveragePageDensity, and
> ExtentFragmentation) when it's a heap. Are any of these values "real"
> for the heaps?
> Also, I know that Logical Fragmentation isn't relevant for heaps- are
> there any other metrics that aren't relevant? Are there any metrics
> that aren't relevant for clustered tables? How about non-clustered
> indexes on heap tables vs. ones on clustered tables?|||I see what you're saying, but there's some wierdness here. With DBCC
SHOWCONTIG WITH FAST,TABLERESULTS I'll get back the columns I
mentioned before for clustered tables, but for heap tables I'll get
back numbers for the other columns as well- the ones that aren't
supposed to be returned. Here's an example from Northwind- I just
included results from two tables but it's the same query. (I've
retyped so it's easier to read):
ObjectName: OrderDetails
ObjectId: 325576198
IndexName: PK_Order_Details
IndexId: 1 <- A clustered table
Level: 0
Pages: 9
Rows: NULL <- not provided with FAST
MinimumRecordSize: NULL <- not provided with FAST
MaximumRecordSize: NULL <- not provided with FAST
AverageRecordSize: NULL <- not provided with FAST
ForwardedRecords: NULL <- not provided with FAST
Extents: 0 <- not provided with FAST
ExtentSwitches: 5
AverageFreeBytes: NULL <- not provided with FAST
AveragePageDensity: NULL <- not provided with FAST
ScanDensity: 33.33333333
BestCount: 2
ActualCount: 6
LogicalFragmentation: 11.11111069
ExtentFragmentation: NULL <- not provided with FAST
ObjectName: Region
ObjectId: 885578193
IndexName:
IndexId: 0 <- A heap table
Level: 0
Pages: 1
Rows: 4 <- not provided with FAST
MinimumRecordSize: 111 <- not provided with FAST
MaximumRecordSize: 111 <- not provided with FAST
AverageRecordSize: 111 <- not provided with FAST
ForwardedRecords: 0 <- not provided with FAST
Extents: 1 <- not provided with FAST
ExtentSwitches: 0
AverageFreeBytes: 7644 <- not provided with FAST
AveragePageDensity: 5.55967378616333 <- not provided with FAST
ScanDensity: 100
BestCount: 1
ActualCount: 1
LogicalFragmentation: 0
ExtentFragmentation: 0 <- not provided with FAST
Northwind isn't the best example, as on other tables I've gotten an
ExtentFragmentation value above 0 on heaps, but let's ignore that for
now. My real point is that on heap tables, it's returning values that
it shouldn't, by definition of WITH FAST. So... are these "real"
values for the heaps? Is this a SQL bug?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#QMjD4t1EHA.824@.TK2MSFTNGP11.phx.gbl>...
> I think all the fields still have meaning, it is just there isn't much you
> can do about bad numbers, short of creating/dropping a clustered index.
> Books on line reports some of these fields as not relevant, but it is more
> accurate to say that the value will always be the "best" possible value due
> to the way heaps are scanned... Take logical fragmentation, it represents
> the number of out of order pages.. When reading through the linked list of a
> clustered index, an out of order page is where the logical order of pages in
> the linked list does not matcht the physical order of the pages on disk...
> When SQL reads a heap, it reads using the physical order on disk, so there
> is no logical fragmentation. Scan density will be a good number for the same
> reason. I would guess the best information would be relative to how full the
> pages are, and how many forwarding pointers there are...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>|||As Wayne points out some things like the average Page density and Free Bytes
may be accurate. But the bottom line is that with a heap the rest is
meaningless or even useless for the most part. Without a clustered index
there is nothing you can do about fragmentation unless you BCP out all the
data (in some order), truncate the table and BCP it back in. And even then
you have absolutely no control over how it gets loaded back into the
database file(s). With very few exceptions, every table should have a
clustered index if for no other reason so you can control fragmentation. Of
coarse there are lots of others as well.
--
Andrew J. Kelly SQL MVP
"Sleepy" <sleepysqlgirl@.yahoo.com> wrote in message
news:bf62573b.0411301340.20190b5f@.posting.google.com...
>I see what you're saying, but there's some wierdness here. With DBCC
> SHOWCONTIG WITH FAST,TABLERESULTS I'll get back the columns I
> mentioned before for clustered tables, but for heap tables I'll get
> back numbers for the other columns as well- the ones that aren't
> supposed to be returned. Here's an example from Northwind- I just
> included results from two tables but it's the same query. (I've
> retyped so it's easier to read):
> ObjectName: OrderDetails
> ObjectId: 325576198
> IndexName: PK_Order_Details
> IndexId: 1 <- A clustered table
> Level: 0
> Pages: 9
> Rows: NULL <- not provided with FAST
> MinimumRecordSize: NULL <- not provided with FAST
> MaximumRecordSize: NULL <- not provided with FAST
> AverageRecordSize: NULL <- not provided with FAST
> ForwardedRecords: NULL <- not provided with FAST
> Extents: 0 <- not provided with FAST
> ExtentSwitches: 5
> AverageFreeBytes: NULL <- not provided with FAST
> AveragePageDensity: NULL <- not provided with FAST
> ScanDensity: 33.33333333
> BestCount: 2
> ActualCount: 6
> LogicalFragmentation: 11.11111069
> ExtentFragmentation: NULL <- not provided with FAST
> ObjectName: Region
> ObjectId: 885578193
> IndexName:
> IndexId: 0 <- A heap table
> Level: 0
> Pages: 1
> Rows: 4 <- not provided with FAST
> MinimumRecordSize: 111 <- not provided with FAST
> MaximumRecordSize: 111 <- not provided with FAST
> AverageRecordSize: 111 <- not provided with FAST
> ForwardedRecords: 0 <- not provided with FAST
> Extents: 1 <- not provided with FAST
> ExtentSwitches: 0
> AverageFreeBytes: 7644 <- not provided with FAST
> AveragePageDensity: 5.55967378616333 <- not provided with FAST
> ScanDensity: 100
> BestCount: 1
> ActualCount: 1
> LogicalFragmentation: 0
> ExtentFragmentation: 0 <- not provided with FAST
> Northwind isn't the best example, as on other tables I've gotten an
> ExtentFragmentation value above 0 on heaps, but let's ignore that for
> now. My real point is that on heap tables, it's returning values that
> it shouldn't, by definition of WITH FAST. So... are these "real"
> values for the heaps? Is this a SQL bug?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:<#QMjD4t1EHA.824@.TK2MSFTNGP11.phx.gbl>...
>> I think all the fields still have meaning, it is just there isn't much
>> you
>> can do about bad numbers, short of creating/dropping a clustered index.
>> Books on line reports some of these fields as not relevant, but it is
>> more
>> accurate to say that the value will always be the "best" possible value
>> due
>> to the way heaps are scanned... Take logical fragmentation, it represents
>> the number of out of order pages.. When reading through the linked list
>> of a
>> clustered index, an out of order page is where the logical order of pages
>> in
>> the linked list does not matcht the physical order of the pages on
>> disk...
>> When SQL reads a heap, it reads using the physical order on disk, so
>> there
>> is no logical fragmentation. Scan density will be a good number for the
>> same
>> reason. I would guess the best information would be relative to how full
>> the
>> pages are, and how many forwarding pointers there are...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org