Data Flow Task Problem: Data File size

My datafile is at 80GB. The data used in the datafile is about 70GB and
growing, with approximately 4,000,000 records in a table that contains a
Text field. I have a weekly purge that deletes about 500,000 records. (Disk
space is at a premium.) When I delete the 500,000 records in the table, the
data used in the data file does not reduce, but remains the same and keeps
on growing. I need to keep the datafile comfortably below 80GB.
My understanding is that shrinking the data file is not the best thing to
do, but if I have to, then I will. So essentially, to keep the data file
comfortably below 80GB I would:
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
Would the above 2 steps be the means of keeping my datafile comfortably
below 80GB?
--
Message posted via http://www.sqlmonster.comYou can turn off auto-grow option and lock your
datafile size on 80Gb. Make sure you have enough space
for your transaction log though in order to be able to
delete records weekly.
Shrink is really not good operation especially on large DBs.
Regards.
"Robert Richards via SQLMonster.com" wrote:
> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records. (Disk
> space is at a premium.) When I delete the 500,000 records in the table, the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.sqlmonster.com
>|||That does not sound like a solution, turning off auto grow. The reason why
is that at the current rate, the data file is growing and will eventually
fill up the 80GB. The purge is not reducing space in the data file.
Probably due to a high water mark. Therefore, even though I am keeping the
number of records at 4,000,000 or below, the unused space never gets
recovered, thus the datafile keeps growing even though the number of
records remains approximately the same.
I understand that ShrinkFile is not the best option, but what other option
is there?
--
Message posted via http://www.sqlmonster.com|||Hi,
What you are performing is absolutely perfect. Do a weekly purge or move the
old data into a history database.
Since you are going to store the data back into the same file you may not
need to shrink the file because the space you
purged will be utilized for the new data which is coming in.
What is the main reason you need to keep 80GB as maximum for your database?
There are many databases in the globe with more than Terabytes
and running smoothly.
Thanks
Hari
Sql Server MVP
"Robert Richards via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:0be9557414954ceca658ef750e5bb45e@.SQLMonster.com...
> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records.
> (Disk
> space is at a premium.) When I delete the 500,000 records in the table,
> the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.sqlmonster.com|||I do not believe I am being understood. When I delete the 500,000, it is
not to an archive table. These 500,000 records are gone.
The 80GB max is due to available disk space.
When I delete records, I need to reclaim the unused space. Are the two
steps below a way of doing that?
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
Is there a better way to reclaim unused space?
--
Message posted via http://www.sqlmonster.com|||What you are seeing is mainly due to the fact you have text or image
columns. Even though you delete rows it may not always free up all the
space it previously used for the text columns. If you have a clustered
index on the table you can reindex the table and possibly free up some space
that may have been due to fragmentation of the non-text columns. But in
2000 there is nothing you can do to clean up text space usage short of
exporting all the data, truncating the table and importing it back in. SQL
2005 will allow you to reorganize blobs. Your real solution is to get more
disk space so you can deal with it better.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:0be9557414954ceca658ef750e5bb45e@.SQLMonster.com...
> My datafile is at 80GB. The data used in the datafile is about 70GB and
> growing, with approximately 4,000,000 records in a table that contains a
> Text field. I have a weekly purge that deletes about 500,000 records.
> (Disk
> space is at a premium.) When I delete the 500,000 records in the table,
> the
> data used in the data file does not reduce, but remains the same and keeps
> on growing. I need to keep the datafile comfortably below 80GB.
> My understanding is that shrinking the data file is not the best thing to
> do, but if I have to, then I will. So essentially, to keep the data file
> comfortably below 80GB I would:
> 1. Perform the weekly purge (deleting approximately 500,000 records).
> 2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
> Would the above 2 steps be the means of keeping my datafile comfortably
> below 80GB?
> --
> Message posted via http://www.sqlmonster.com|||Why then does the "data used" decrease on the data file when I delete
records from the same table in my Test environment, but when I delete
records from the table in production the "data used" does not decrease?
--
Message posted via http://www.sqlmonster.com|||How are you determining the "data used"? Are you using sp_spaceused? Have
you tried running DBCC UPDATE USAGE or specifying the UpdateUasge option?
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ce78b9bd5c5447658b8a0e2badaf82cb@.SQLMonster.com...
> Why then does the "data used" decrease on the data file when I delete
> records from the same table in my Test environment, but when I delete
> records from the table in production the "data used" does not decrease?
> --
> Message posted via http://www.sqlmonster.com|||Also, the TRUCATE ONLY option will not Free Up the space becaue truncate
will only remove from the end of the file. You need to MOVE all the data to
the head of the file before shrinking. You can only do that if you use the
DBCC SHRINKFILE(MyDB_Data, TargetSize).
Reindexing the clustered index will most likely cause the file to grow even
more because the entire table is basically copied to a new when as it
reindexes. However, if you reindex, that will defragment by the index
order. Then if you shrink the file, it will move the data pages in
defragmented order, but could take quite some time, especially on a 80 GB
database.
You should consider creating 1 to 1 relationships for the LOB data and their
associated base tables. Then you could create VIEWs to replace the original
table definitions to minimize code impact. Then put all of the LOB data in
a seperate table(s). Those tables could then be created in seperate files
on seperate FileGroups. The shrinking and reordering just those files
should minimize the durations of those operations.
If you want to garauntee what you are looking at, run sp_spaceused
@.UPDATEUSAGE = 'true'.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecOFGg9ZFHA.3168@.TK2MSFTNGP10.phx.gbl...
How are you determining the "data used"? Are you using sp_spaceused? Have
you tried running DBCC UPDATE USAGE or specifying the UpdateUasge option?
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ce78b9bd5c5447658b8a0e2badaf82cb@.SQLMonster.com...
> Why then does the "data used" decrease on the data file when I delete
> records from the same table in my Test environment, but when I delete
> records from the table in production the "data used" does not decrease?
> --
> Message posted via http://www.sqlmonster.com|||I am still not clear how or why my data continues to increase. What you say
I cannot quite comprehend given my current data set. For instance:
5/21/2005
Records in table: 4,091,571
Data used within data file: 64,546.3 MB
6/5/2005
Records in table: 3,679,559
Data used within data file: 71,828.5 MB
So even though I have 412,012 less records in the table (the only other
tables are small, static lookup tables) the data within the data file has
grown 7,282.2 MB.
I guess I just do not understand the text column well enough, to understand
why the substantial growth, despite the significantly less records in the
table.
Please help, as I am going to have to explain this to my supervisor in such
a way to either justify more disk space, or resolve the growth issue
despite less records. Thanks for your patience.
--
Message posted via http://www.sqlmonster.com|||Hi
Have a look at DBCC CLEANTABLE in BOL.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ce3ad79a66384035a99a9f369793c430@.SQLMonster.com...
>I am still not clear how or why my data continues to increase. What you say
> I cannot quite comprehend given my current data set. For instance:
> 5/21/2005
> Records in table: 4,091,571
> Data used within data file: 64,546.3 MB
> 6/5/2005
> Records in table: 3,679,559
> Data used within data file: 71,828.5 MB
> So even though I have 412,012 less records in the table (the only other
> tables are small, static lookup tables) the data within the data file has
> grown 7,282.2 MB.
> I guess I just do not understand the text column well enough, to
> understand
> why the substantial growth, despite the significantly less records in the
> table.
> Please help, as I am going to have to explain this to my supervisor in
> such
> a way to either justify more disk space, or resolve the growth issue
> despite less records. Thanks for your patience.
> --
> Message posted via http://www.sqlmonster.com|||An have you re-built your clustered index on the table?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVPWDahaFHA.2768@.tk2msftngp13.phx.gbl...
> Hi
> Have a look at DBCC CLEANTABLE in BOL.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> message news:ce3ad79a66384035a99a9f369793c430@.SQLMonster.com...
>>I am still not clear how or why my data continues to increase. What you
>>say
>> I cannot quite comprehend given my current data set. For instance:
>> 5/21/2005
>> Records in table: 4,091,571
>> Data used within data file: 64,546.3 MB
>> 6/5/2005
>> Records in table: 3,679,559
>> Data used within data file: 71,828.5 MB
>> So even though I have 412,012 less records in the table (the only other
>> tables are small, static lookup tables) the data within the data file has
>> grown 7,282.2 MB.
>> I guess I just do not understand the text column well enough, to
>> understand
>> why the substantial growth, despite the significantly less records in the
>> table.
>> Please help, as I am going to have to explain this to my supervisor in
>> such
>> a way to either justify more disk space, or resolve the growth issue
>> despite less records. Thanks for your patience.
>> --
>> Message posted via http://www.sqlmonster.com
>|||I am not sure if this is helpful but I have included the results of running
"dbcc showcontig" on the table:
DBCC SHOWCONTIG scanning [Table Name] table...
Table: [Table Name] (2009058193); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 42259
- Extents Scanned.......................: 5292
- Extent Switches.......................: 5291
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.83% [5283:5292]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.13%
- Avg. Bytes Free per Page................: 781.0
- Avg. Page Density (full)................: 90.35%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The following is from running dbcc checktable (run two days ago):
DBCC results for [Table Name].
There are 3880797 rows in 44707 pages for object [Table Name].
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Lastly the following is from running dbcc checkalloc against the database:
DBCC results for [Database Name].
***************************************************************
Table sysobjects Object ID 1.
Index ID 1. FirstIAM (1:10). Root (1:11). Dpages 3.
Index ID 1. 5 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:25). Root (1:15). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:33). Root (1:31). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysindexes Object ID 2.
Index ID 1. FirstIAM (1:13). Root (1:14). Dpages 4.
Index ID 1. 6 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (1:27). Root (1:64). Dpages 0.
Index ID 255. 37 pages used in 6 dedicated extents.
Total number of extents is 6.
***************************************************************
Table syscolumns Object ID 3.
Index ID 1. FirstIAM (1:26). Root (1:17). Dpages 12.
Index ID 1. 14 pages used in 1 dedicated extents.
Index ID 2. FirstIAM (1:35). Root (1:34). Dpages 5.
Index ID 2. 7 pages used in 0 dedicated extents.
Total number of extents is 1.
***************************************************************
Table systypes Object ID 4.
Index ID 1. FirstIAM (1:29). Root (1:30). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:37). Root (1:36). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syscomments Object ID 6.
Index ID 1. FirstIAM (1:52). Root (1:51). Dpages 24.
Index ID 1. 27 pages used in 3 dedicated extents.
Total number of extents is 3.
***************************************************************
Table sysfiles1 Object ID 8.
Index ID 0. FirstIAM (1:12). Root (1:32). Dpages 1.
Index ID 0. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syspermissions Object ID 9.
Index ID 1. FirstIAM (1:72). Root (1:63). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysusers Object ID 10.
Index ID 1. FirstIAM (1:39). Root (1:38). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:42). Root (1:41). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:44). Root (1:43). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysproperties Object ID 11.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysdepends Object ID 12.
Index ID 1. FirstIAM (1:55). Root (1:54). Dpages 3.
Index ID 1. 5 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:58). Root (1:57). Dpages 2.
Index ID 2. 4 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysreferences Object ID 14.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 2. 0 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 3. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextcatalogs Object ID 19.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 2. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextnotify Object ID 24.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfilegroups Object ID 96.
Index ID 1. FirstIAM (1:47). Root (1:46). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:50). Root (1:49). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table dtproperties Object ID 517576882.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 255. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table BTSError Object ID 1061578820.
Index ID 1. FirstIAM (1:1076526). Root (1:1145807). Dpages 18042.
Index ID 1. 18102 pages used in 2261 dedicated extents.
Index ID 4. FirstIAM (1:1076527). Root (1:1157874). Dpages 1933.
Index ID 4. 1957 pages used in 243 dedicated extents.
Index ID 5. FirstIAM (1:1159153). Root (1:1159579). Dpages 2242.
Index ID 5. 2274 pages used in 283 dedicated extents.
Index ID 255. FirstIAM (1:355450). Root (1:355449). Dpages 0.
Index ID 255. 88231 pages used in 11036 dedicated extents.
Total number of extents is 13823.
***************************************************************
Table DriveSpace Object ID 1333579789.
Index ID 1. FirstIAM (1:1159157). Root (1:1159158). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:273386). Root (1:1159159). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 5. FirstIAM (1:273388). Root (1:273387). Dpages 1.
Index ID 5. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table DBSize Object ID 1509580416.
Index ID 1. FirstIAM (1:1076523). Root (1:1076524). Dpages 5.
Index ID 1. 7 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:1159155). Root (1:1076525). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table MessageCount Object ID 1669580986.
Index ID 1. FirstIAM (1:355452). Root (1:355448). Dpages 2.
Index ID 1. 4 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:411009). Root (1:411008). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:411012). Root (1:411010). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table IFSMessageTypes Object ID 1993058136.
Index ID 1. FirstIAM (1:618). Root (1:619). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table IFSMessages Object ID 2009058193.
Index ID 1. FirstIAM (1:432792). Root (1:9362263). Dpages 44700.
Index ID 1. 44780 pages used in 5597 dedicated extents.
Index ID 6. FirstIAM (1:432793). Root (1:9404245). Dpages 10083.
Index ID 6. 10119 pages used in 1266 dedicated extents.
Index ID 255. FirstIAM (1:268). Root (1:267). Dpages 0.
Index ID 255. 9062113 pages used in 1133279 dedicated extents.
Total number of extents is 1140142.
***************************************************************
Table IFSUpdateError Object ID 2025058250.
Index ID 1. FirstIAM (1:116). Root (1:1168777). Dpages 800.
Index ID 1. 821 pages used in 102 dedicated extents.
Index ID 255. FirstIAM (1:113). Root (1:112). Dpages 0.
Index ID 255. 487 pages used in 93 dedicated extents.
Total number of extents is 195.
***************************************************************
Processed 46 entries in sysindexes for database ID 7.
File 1. Number of extents = 1154232, used pages = 9229133, reserved pages =9233856.
File 1 (number of mixed extents = 40, mixed pages = 320).
Object ID 1, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
Object ID 1, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 1, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
Object ID 2, Index ID 0, data extents 0, pages 6, mixed extent pages 6.
Object ID 2, Index ID 255, index extents 6, pages 37, mixed extent
pages 22.
Object ID 3, Index ID 0, data extents 1, pages 14, mixed extent pages 9.
Object ID 3, Index ID 2, index extents 0, pages 7, mixed extent pages 7.
Object ID 4, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 4, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 6, Index ID 0, data extents 3, pages 27, mixed extent pages
10.
Object ID 8, Index ID 0, data extents 0, pages 2, mixed extent pages 2.
Object ID 9, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 2, index extents 0, pages 2, mixed extent pages
2.
Object ID 10, Index ID 3, index extents 0, pages 2, mixed extent pages
2.
Object ID 12, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
Object ID 12, Index ID 2, index extents 0, pages 4, mixed extent pages
4.
Object ID 96, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 96, Index ID 2, index extents 0, pages 2, mixed extent pages
2.
Object ID 99, Index ID 0, data extents 22, pages 91, mixed extent pages
1.
Object ID 1061578820, Index ID 0, data extents 2261, pages 18102, mixed
extent pages 26.
Object ID 1061578820, Index ID 4, index extents 243, pages 1957, mixed
extent pages 24.
Object ID 1061578820, Index ID 5, index extents 283, pages 2274, mixed
extent pages 26.
Object ID 1061578820, Index ID 255, index extents 11036, pages 88231,
mixed extent pages 28.
Object ID 1333579789, Index ID 0, data extents 0, pages 3, mixed extent
pages 3.
Object ID 1333579789, Index ID 2, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1333579789, Index ID 5, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1509580416, Index ID 0, data extents 0, pages 7, mixed extent
pages 7.
Object ID 1509580416, Index ID 3, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1669580986, Index ID 0, data extents 0, pages 4, mixed extent
pages 4.
Object ID 1669580986, Index ID 2, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1669580986, Index ID 3, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1993058136, Index ID 0, data extents 0, pages 3, mixed extent
pages 3.
Object ID 2009058193, Index ID 0, data extents 5597, pages 44780, mixed
extent pages 10.
Object ID 2009058193, Index ID 6, index extents 1266, pages 10119,
mixed extent pages 10.
Object ID 2009058193, Index ID 255, index extents 1133279, pages
9062113, mixed extent pages 28.
Object ID 2025058250, Index ID 0, data extents 102, pages 821, mixed
extent pages 26.
Object ID 2025058250, Index ID 255, index extents 93, pages 487, mixed
extent pages 20.
Total number of extents = 1154232, used pages = 9229133, reserved pages =9233856 in this database.
(number of mixed extents = 40, mixed pages = 320) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database
[Database Name].
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
Message posted via http://www.sqlmonster.com|||Well, certainly the bulk of your data resides in the LOB for the following
object:
Object ID 2009058193, Index ID 255, index extents 1133279, pages
9062113, mixed extent pages 28.
Another problem I see is the lack of usage for Clustered Indexes. Every
table should be defined with a Clustered Index. How to choose the
attributes for these indexes is a whole other discussion, and argument among
my peers here; however, the need for them can not be overstated, especially
when using LOBs.
I would certainly try to place one on Object ID 2009058193 to see if it
improves the storage.
Sincerely,
Anthony Thomas
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4a0b6d430d914267a3283d041ca13783@.SQLMonster.com...
I am not sure if this is helpful but I have included the results of running
"dbcc showcontig" on the table:
DBCC SHOWCONTIG scanning [Table Name] table...
Table: [Table Name] (2009058193); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 42259
- Extents Scanned.......................: 5292
- Extent Switches.......................: 5291
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.83% [5283:5292]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.13%
- Avg. Bytes Free per Page................: 781.0
- Avg. Page Density (full)................: 90.35%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The following is from running dbcc checktable (run two days ago):
DBCC results for [Table Name].
There are 3880797 rows in 44707 pages for object [Table Name].
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Lastly the following is from running dbcc checkalloc against the database:
DBCC results for [Database Name].
***************************************************************
Table sysobjects Object ID 1.
Index ID 1. FirstIAM (1:10). Root (1:11). Dpages 3.
Index ID 1. 5 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:25). Root (1:15). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:33). Root (1:31). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysindexes Object ID 2.
Index ID 1. FirstIAM (1:13). Root (1:14). Dpages 4.
Index ID 1. 6 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (1:27). Root (1:64). Dpages 0.
Index ID 255. 37 pages used in 6 dedicated extents.
Total number of extents is 6.
***************************************************************
Table syscolumns Object ID 3.
Index ID 1. FirstIAM (1:26). Root (1:17). Dpages 12.
Index ID 1. 14 pages used in 1 dedicated extents.
Index ID 2. FirstIAM (1:35). Root (1:34). Dpages 5.
Index ID 2. 7 pages used in 0 dedicated extents.
Total number of extents is 1.
***************************************************************
Table systypes Object ID 4.
Index ID 1. FirstIAM (1:29). Root (1:30). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:37). Root (1:36). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syscomments Object ID 6.
Index ID 1. FirstIAM (1:52). Root (1:51). Dpages 24.
Index ID 1. 27 pages used in 3 dedicated extents.
Total number of extents is 3.
***************************************************************
Table sysfiles1 Object ID 8.
Index ID 0. FirstIAM (1:12). Root (1:32). Dpages 1.
Index ID 0. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syspermissions Object ID 9.
Index ID 1. FirstIAM (1:72). Root (1:63). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysusers Object ID 10.
Index ID 1. FirstIAM (1:39). Root (1:38). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:42). Root (1:41). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:44). Root (1:43). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysproperties Object ID 11.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysdepends Object ID 12.
Index ID 1. FirstIAM (1:55). Root (1:54). Dpages 3.
Index ID 1. 5 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:58). Root (1:57). Dpages 2.
Index ID 2. 4 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysreferences Object ID 14.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 2. 0 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 3. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextcatalogs Object ID 19.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 2. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextnotify Object ID 24.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfilegroups Object ID 96.
Index ID 1. FirstIAM (1:47). Root (1:46). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:50). Root (1:49). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table dtproperties Object ID 517576882.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 255. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table BTSError Object ID 1061578820.
Index ID 1. FirstIAM (1:1076526). Root (1:1145807). Dpages 18042.
Index ID 1. 18102 pages used in 2261 dedicated extents.
Index ID 4. FirstIAM (1:1076527). Root (1:1157874). Dpages 1933.
Index ID 4. 1957 pages used in 243 dedicated extents.
Index ID 5. FirstIAM (1:1159153). Root (1:1159579). Dpages 2242.
Index ID 5. 2274 pages used in 283 dedicated extents.
Index ID 255. FirstIAM (1:355450). Root (1:355449). Dpages 0.
Index ID 255. 88231 pages used in 11036 dedicated extents.
Total number of extents is 13823.
***************************************************************
Table DriveSpace Object ID 1333579789.
Index ID 1. FirstIAM (1:1159157). Root (1:1159158). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:273386). Root (1:1159159). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 5. FirstIAM (1:273388). Root (1:273387). Dpages 1.
Index ID 5. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table DBSize Object ID 1509580416.
Index ID 1. FirstIAM (1:1076523). Root (1:1076524). Dpages 5.
Index ID 1. 7 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:1159155). Root (1:1076525). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table MessageCount Object ID 1669580986.
Index ID 1. FirstIAM (1:355452). Root (1:355448). Dpages 2.
Index ID 1. 4 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:411009). Root (1:411008). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:411012). Root (1:411010). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table IFSMessageTypes Object ID 1993058136.
Index ID 1. FirstIAM (1:618). Root (1:619). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table IFSMessages Object ID 2009058193.
Index ID 1. FirstIAM (1:432792). Root (1:9362263). Dpages 44700.
Index ID 1. 44780 pages used in 5597 dedicated extents.
Index ID 6. FirstIAM (1:432793). Root (1:9404245). Dpages 10083.
Index ID 6. 10119 pages used in 1266 dedicated extents.
Index ID 255. FirstIAM (1:268). Root (1:267). Dpages 0.
Index ID 255. 9062113 pages used in 1133279 dedicated extents.
Total number of extents is 1140142.
***************************************************************
Table IFSUpdateError Object ID 2025058250.
Index ID 1. FirstIAM (1:116). Root (1:1168777). Dpages 800.
Index ID 1. 821 pages used in 102 dedicated extents.
Index ID 255. FirstIAM (1:113). Root (1:112). Dpages 0.
Index ID 255. 487 pages used in 93 dedicated extents.
Total number of extents is 195.
***************************************************************
Processed 46 entries in sysindexes for database ID 7.
File 1. Number of extents = 1154232, used pages = 9229133, reserved pages =9233856.
File 1 (number of mixed extents = 40, mixed pages = 320).
Object ID 1, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
Object ID 1, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 1, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
Object ID 2, Index ID 0, data extents 0, pages 6, mixed extent pages 6.
Object ID 2, Index ID 255, index extents 6, pages 37, mixed extent
pages 22.
Object ID 3, Index ID 0, data extents 1, pages 14, mixed extent pages 9.
Object ID 3, Index ID 2, index extents 0, pages 7, mixed extent pages 7.
Object ID 4, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 4, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 6, Index ID 0, data extents 3, pages 27, mixed extent pages
10.
Object ID 8, Index ID 0, data extents 0, pages 2, mixed extent pages 2.
Object ID 9, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 2, index extents 0, pages 2, mixed extent pages
2.
Object ID 10, Index ID 3, index extents 0, pages 2, mixed extent pages
2.
Object ID 12, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
Object ID 12, Index ID 2, index extents 0, pages 4, mixed extent pages
4.
Object ID 96, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 96, Index ID 2, index extents 0, pages 2, mixed extent pages
2.
Object ID 99, Index ID 0, data extents 22, pages 91, mixed extent pages
1.
Object ID 1061578820, Index ID 0, data extents 2261, pages 18102, mixed
extent pages 26.
Object ID 1061578820, Index ID 4, index extents 243, pages 1957, mixed
extent pages 24.
Object ID 1061578820, Index ID 5, index extents 283, pages 2274, mixed
extent pages 26.
Object ID 1061578820, Index ID 255, index extents 11036, pages 88231,
mixed extent pages 28.
Object ID 1333579789, Index ID 0, data extents 0, pages 3, mixed extent
pages 3.
Object ID 1333579789, Index ID 2, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1333579789, Index ID 5, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1509580416, Index ID 0, data extents 0, pages 7, mixed extent
pages 7.
Object ID 1509580416, Index ID 3, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1669580986, Index ID 0, data extents 0, pages 4, mixed extent
pages 4.
Object ID 1669580986, Index ID 2, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1669580986, Index ID 3, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1993058136, Index ID 0, data extents 0, pages 3, mixed extent
pages 3.
Object ID 2009058193, Index ID 0, data extents 5597, pages 44780, mixed
extent pages 10.
Object ID 2009058193, Index ID 6, index extents 1266, pages 10119,
mixed extent pages 10.
Object ID 2009058193, Index ID 255, index extents 1133279, pages
9062113, mixed extent pages 28.
Object ID 2025058250, Index ID 0, data extents 102, pages 821, mixed
extent pages 26.
Object ID 2025058250, Index ID 255, index extents 93, pages 487, mixed
extent pages 20.
Total number of extents = 1154232, used pages = 9229133, reserved pages =9233856 in this database.
(number of mixed extents = 40, mixed pages = 320) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database
[Database Name].
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
Message posted via http://www.sqlmonster.com|||I have a weekly job that executes the following:
dbcc dbreindex(N'[dbo].[Table Name]', N'', 0, sorted_data_reorg)
Does that rebuild the index?
--
Message posted via http://www.sqlmonster.com|||I checked out dbcc cleantable. It appears to only to have an affect should
by text column be dropped, as per what I read on BOL. Do you know if it
applies to deleting rows from a table that contains a Text column?
--
Message posted via http://www.sqlmonster.com|||This table has 8 rows, with a primary key as a clustered index.
How can I know what objectid refers to?
--
Message posted via http://www.sqlmonster.com|||This is a multi-part message in MIME format.
--050503020302070404020007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Not sure I understand the question. The DBCC CHECKTABLE statement you
ran said you have 3,880,797 rows in your table (not 8). To get an
object name from an object ID, change to the DB in question (in Query
Analyzer) and run
select object_name(2009058193)
where 2009058193 is the object ID. But you already know the table name,
so why the question? From the DBCC CHECKALLOC you posted:
***************************************************************
Table IFSMessages Object ID 2009058193.
Index ID 1. FirstIAM (1:432792). Root (1:9362263). Dpages 44700.
Index ID 1. 44780 pages used in 5597 dedicated extents.
Index ID 6. FirstIAM (1:432793). Root (1:9404245). Dpages 10083.
Index ID 6. 10119 pages used in 1266 dedicated extents.
Index ID 255. FirstIAM (1:268). Root (1:267). Dpages 0.
Index ID 255. 9062113 pages used in 1133279 dedicated extents.
Total number of extents is 1140142.
***************************************************************
you can see "index" 255 has the majority of the extents. That index
number refers to the text or image data associated with the table. So
you can see 5597 extents (0.5% of the space consumed by the table) are
attributable to the clustered index (i.e. this is the actual table data
excluding the LOBs). 1266 extents (0.1% of the space consumed by the
table) are attributable to the single nonclustered index on that table
(indid 6) and the remaining 1133279 extents (99.4% of the total space)
is due to the LOB data in the table (i.e. indid 255). Since an extent
is 64K (eight 8K pages) that means about 69.2G of the total data in your
database is attributable to your LOB data in the IFSMessages table.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Robert Richards via SQLMonster.com wrote:
>This table has 8 rows, with a primary key as a clustered index.
>How can I know what objectid refers to?
>
>
--050503020302070404020007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Not sure I understand the question. The DBCC CHECKTABLE statement
you ran said you have 3,880,797 rows in your table (not 8). To get an
object name from an object ID, change to the DB in question (in Query
Analyzer) and run 
</tt>
<blockquote><tt>select object_name(</tt><tt>2009058193</tt><tt>) 
</tt></blockquote>
<tt>where 2009058193 is the object ID. But you already know the table
name, so why the question? From the DBCC CHECKALLOC you posted: 
</tt>
<blockquote>
<pre wrap="">***************************************************************
Table IFSMessages Object ID 2009058193.
Index ID 1. FirstIAM (1:432792). Root (1:9362263). Dpages 44700.
Index ID 1. 44780 pages used in 5597 dedicated extents.
Index ID 6. FirstIAM (1:432793). Root (1:9404245). Dpages 10083.
Index ID 6. 10119 pages used in 1266 dedicated extents.
Index ID 255. FirstIAM (1:268). Root (1:267). Dpages 0.
Index ID 255. 9062113 pages used in 1133279 dedicated extents.
Total number of extents is 1140142.
***************************************************************
</pre>
</blockquote>
<tt>you can see "index" 255 has the majority of the extents. That
index number refers to the text or image data associated with the
table. So you can see 5597 extents (0.5% of the space consumed by the
table) are attributable to the clustered index (i.e. this is the actual
table data excluding the LOBs). 1266 extents (0.1% of the space
consumed by the table) are attributable to the single nonclustered
index on that table (indid 6) and the remaining 1133279 extents (99.4%
of the total space) is due to the LOB data in the table (i.e. indid
255). Since an extent is 64K (eight 8K pages) that means about 69.2G
of the total data in your database is attributable to your LOB data in
the IFSMessages table.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Robert Richards via SQLMonster.com wrote:
<blockquote cite="mid3a0844e9a3c54080a0700d4b1565d3a7@.SQLMonster.com"
type="cite">
<pre wrap="">This table has 8 rows, with a primary key as a clustered index.
How can I know what objectid refers to?
</pre>
</blockquote>
</body>
</html>
--050503020302070404020007--|||This is a multi-part message in MIME format.
--020103030708070402090004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
The DBCC SHOWCONTIG you did is only on the clustered index of the table
(index ID 1).
It will most likely take a long time to run (because it'll have to scan
about 70GB worth of pages/extents) but is it possible to get a DBCC
SHOWCONTIG on index 255 and post it in this thread to see the
fragmentation of the text data in that table? WARNING: It'd be best to
do this out of hours because it will probably take several hours to run
and will hold shared locks on the entire table while it's running, which
will cause blocking issues with data modification statements to that table.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Robert Richards via SQLMonster.com wrote:
>I am not sure if this is helpful but I have included the results of running
>"dbcc showcontig" on the table:
>DBCC SHOWCONTIG scanning [Table Name] table...
>Table: [Table Name] (2009058193); index ID: 1, database ID: 7
>TABLE level scan performed.
>- Pages Scanned........................: 42259
>- Extents Scanned.......................: 5292
>- Extent Switches.......................: 5291
>- Avg. Pages per Extent..................: 8.0
>- Scan Density [Best Count:Actual Count]......: 99.83% [5283:5292]
>- Logical Scan Fragmentation ..............: 0.00%
>- Extent Scan Fragmentation ...............: 0.13%
>- Avg. Bytes Free per Page................: 781.0
>- Avg. Page Density (full)................: 90.35%
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>The following is from running dbcc checktable (run two days ago):
>DBCC results for [Table Name].
>There are 3880797 rows in 44707 pages for object [Table Name].
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>Lastly the following is from running dbcc checkalloc against the database:
>DBCC results for [Database Name].
>***************************************************************
>Table sysobjects Object ID 1.
>Index ID 1. FirstIAM (1:10). Root (1:11). Dpages 3.
>Index ID 1. 5 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (1:25). Root (1:15). Dpages 1.
>Index ID 2. 2 pages used in 0 dedicated extents.
>Index ID 3. FirstIAM (1:33). Root (1:31). Dpages 1.
>Index ID 3. 2 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table sysindexes Object ID 2.
>Index ID 1. FirstIAM (1:13). Root (1:14). Dpages 4.
>Index ID 1. 6 pages used in 0 dedicated extents.
>Index ID 255. FirstIAM (1:27). Root (1:64). Dpages 0.
>Index ID 255. 37 pages used in 6 dedicated extents.
>Total number of extents is 6.
>***************************************************************
>Table syscolumns Object ID 3.
>Index ID 1. FirstIAM (1:26). Root (1:17). Dpages 12.
>Index ID 1. 14 pages used in 1 dedicated extents.
>Index ID 2. FirstIAM (1:35). Root (1:34). Dpages 5.
>Index ID 2. 7 pages used in 0 dedicated extents.
>Total number of extents is 1.
>***************************************************************
>Table systypes Object ID 4.
>Index ID 1. FirstIAM (1:29). Root (1:30). Dpages 1.
>Index ID 1. 3 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (1:37). Root (1:36). Dpages 1.
>Index ID 2. 2 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table syscomments Object ID 6.
>Index ID 1. FirstIAM (1:52). Root (1:51). Dpages 24.
>Index ID 1. 27 pages used in 3 dedicated extents.
>Total number of extents is 3.
>***************************************************************
>Table sysfiles1 Object ID 8.
>Index ID 0. FirstIAM (1:12). Root (1:32). Dpages 1.
>Index ID 0. 2 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table syspermissions Object ID 9.
>Index ID 1. FirstIAM (1:72). Root (1:63). Dpages 1.
>Index ID 1. 3 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table sysusers Object ID 10.
>Index ID 1. FirstIAM (1:39). Root (1:38). Dpages 1.
>Index ID 1. 3 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (1:42). Root (1:41). Dpages 1.
>Index ID 2. 2 pages used in 0 dedicated extents.
>Index ID 3. FirstIAM (1:44). Root (1:43). Dpages 1.
>Index ID 3. 2 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table sysproperties Object ID 11.
>Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 1. 0 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table sysdepends Object ID 12.
>Index ID 1. FirstIAM (1:55). Root (1:54). Dpages 3.
>Index ID 1. 5 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (1:58). Root (1:57). Dpages 2.
>Index ID 2. 4 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table sysreferences Object ID 14.
>Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 1. 0 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 2. 0 pages used in 0 dedicated extents.
>Index ID 3. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 3. 0 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table sysfulltextcatalogs Object ID 19.
>Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 1. 0 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 2. 0 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table sysfulltextnotify Object ID 24.
>Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 1. 0 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table sysfilegroups Object ID 96.
>Index ID 1. FirstIAM (1:47). Root (1:46). Dpages 1.
>Index ID 1. 3 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (1:50). Root (1:49). Dpages 1.
>Index ID 2. 2 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table dtproperties Object ID 517576882.
>Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 1. 0 pages used in 0 dedicated extents.
>Index ID 255. FirstIAM (0:0). Root (0:0). Dpages 0.
>Index ID 255. 0 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table BTSError Object ID 1061578820.
>Index ID 1. FirstIAM (1:1076526). Root (1:1145807). Dpages 18042.
>Index ID 1. 18102 pages used in 2261 dedicated extents.
>Index ID 4. FirstIAM (1:1076527). Root (1:1157874). Dpages 1933.
>Index ID 4. 1957 pages used in 243 dedicated extents.
>Index ID 5. FirstIAM (1:1159153). Root (1:1159579). Dpages 2242.
>Index ID 5. 2274 pages used in 283 dedicated extents.
>Index ID 255. FirstIAM (1:355450). Root (1:355449). Dpages 0.
>Index ID 255. 88231 pages used in 11036 dedicated extents.
>Total number of extents is 13823.
>***************************************************************
>Table DriveSpace Object ID 1333579789.
>Index ID 1. FirstIAM (1:1159157). Root (1:1159158). Dpages 1.
>Index ID 1. 3 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (1:273386). Root (1:1159159). Dpages 1.
>Index ID 2. 2 pages used in 0 dedicated extents.
>Index ID 5. FirstIAM (1:273388). Root (1:273387). Dpages 1.
>Index ID 5. 2 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table DBSize Object ID 1509580416.
>Index ID 1. FirstIAM (1:1076523). Root (1:1076524). Dpages 5.
>Index ID 1. 7 pages used in 0 dedicated extents.
>Index ID 3. FirstIAM (1:1159155). Root (1:1076525). Dpages 1.
>Index ID 3. 2 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table MessageCount Object ID 1669580986.
>Index ID 1. FirstIAM (1:355452). Root (1:355448). Dpages 2.
>Index ID 1. 4 pages used in 0 dedicated extents.
>Index ID 2. FirstIAM (1:411009). Root (1:411008). Dpages 1.
>Index ID 2. 2 pages used in 0 dedicated extents.
>Index ID 3. FirstIAM (1:411012). Root (1:411010). Dpages 1.
>Index ID 3. 2 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table IFSMessageTypes Object ID 1993058136.
>Index ID 1. FirstIAM (1:618). Root (1:619). Dpages 1.
>Index ID 1. 3 pages used in 0 dedicated extents.
>Total number of extents is 0.
>***************************************************************
>Table IFSMessages Object ID 2009058193.
>Index ID 1. FirstIAM (1:432792). Root (1:9362263). Dpages 44700.
>Index ID 1. 44780 pages used in 5597 dedicated extents.
>Index ID 6. FirstIAM (1:432793). Root (1:9404245). Dpages 10083.
>Index ID 6. 10119 pages used in 1266 dedicated extents.
>Index ID 255. FirstIAM (1:268). Root (1:267). Dpages 0.
>Index ID 255. 9062113 pages used in 1133279 dedicated extents.
>Total number of extents is 1140142.
>***************************************************************
>Table IFSUpdateError Object ID 2025058250.
>Index ID 1. FirstIAM (1:116). Root (1:1168777). Dpages 800.
>Index ID 1. 821 pages used in 102 dedicated extents.
>Index ID 255. FirstIAM (1:113). Root (1:112). Dpages 0.
>Index ID 255. 487 pages used in 93 dedicated extents.
>Total number of extents is 195.
>***************************************************************
>Processed 46 entries in sysindexes for database ID 7.
>File 1. Number of extents = 1154232, used pages = 9229133, reserved pages =>9233856.
> File 1 (number of mixed extents = 40, mixed pages = 320).
> Object ID 1, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
> Object ID 1, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
> Object ID 1, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
> Object ID 2, Index ID 0, data extents 0, pages 6, mixed extent pages 6.
> Object ID 2, Index ID 255, index extents 6, pages 37, mixed extent
>pages 22.
> Object ID 3, Index ID 0, data extents 1, pages 14, mixed extent pages 9.
> Object ID 3, Index ID 2, index extents 0, pages 7, mixed extent pages 7.
> Object ID 4, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
> Object ID 4, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
> Object ID 6, Index ID 0, data extents 3, pages 27, mixed extent pages
>10.
> Object ID 8, Index ID 0, data extents 0, pages 2, mixed extent pages 2.
> Object ID 9, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
> Object ID 10, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
> Object ID 10, Index ID 2, index extents 0, pages 2, mixed extent pages
>2.
> Object ID 10, Index ID 3, index extents 0, pages 2, mixed extent pages
>2.
> Object ID 12, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
> Object ID 12, Index ID 2, index extents 0, pages 4, mixed extent pages
>4.
> Object ID 96, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
> Object ID 96, Index ID 2, index extents 0, pages 2, mixed extent pages
>2.
> Object ID 99, Index ID 0, data extents 22, pages 91, mixed extent pages
>1.
> Object ID 1061578820, Index ID 0, data extents 2261, pages 18102, mixed
>extent pages 26.
> Object ID 1061578820, Index ID 4, index extents 243, pages 1957, mixed
>extent pages 24.
> Object ID 1061578820, Index ID 5, index extents 283, pages 2274, mixed
>extent pages 26.
> Object ID 1061578820, Index ID 255, index extents 11036, pages 88231,
>mixed extent pages 28.
> Object ID 1333579789, Index ID 0, data extents 0, pages 3, mixed extent
>pages 3.
> Object ID 1333579789, Index ID 2, index extents 0, pages 2, mixed
>extent pages 2.
> Object ID 1333579789, Index ID 5, index extents 0, pages 2, mixed
>extent pages 2.
> Object ID 1509580416, Index ID 0, data extents 0, pages 7, mixed extent
>pages 7.
> Object ID 1509580416, Index ID 3, index extents 0, pages 2, mixed
>extent pages 2.
> Object ID 1669580986, Index ID 0, data extents 0, pages 4, mixed extent
>pages 4.
> Object ID 1669580986, Index ID 2, index extents 0, pages 2, mixed
>extent pages 2.
> Object ID 1669580986, Index ID 3, index extents 0, pages 2, mixed
>extent pages 2.
> Object ID 1993058136, Index ID 0, data extents 0, pages 3, mixed extent
>pages 3.
> Object ID 2009058193, Index ID 0, data extents 5597, pages 44780, mixed
>extent pages 10.
> Object ID 2009058193, Index ID 6, index extents 1266, pages 10119,
>mixed extent pages 10.
> Object ID 2009058193, Index ID 255, index extents 1133279, pages
>9062113, mixed extent pages 28.
> Object ID 2025058250, Index ID 0, data extents 102, pages 821, mixed
>extent pages 26.
> Object ID 2025058250, Index ID 255, index extents 93, pages 487, mixed
>extent pages 20.
>Total number of extents = 1154232, used pages = 9229133, reserved pages =>9233856 in this database.
> (number of mixed extents = 40, mixed pages = 320) in this database.
>CHECKALLOC found 0 allocation errors and 0 consistency errors in database
>[Database Name].
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>
>
--020103030708070402090004
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The DBCC SHOWCONTIG you did is only on the clustered index of the
table (index ID 1). 
 
It will most likely take a long time to run (because it'll have to scan
about 70GB worth of pages/extents) but is it possible to get a DBCC
SHOWCONTIG on index 255 and post it in this thread to see the
fragmentation of the text data in that table? WARNING: It'd be best to
do this out of hours because it will probably take several hours to run
and will hold shared locks on the entire table while it's running,
which will cause blocking issues with data modification statements to
that table.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Robert Richards via SQLMonster.com wrote:
<blockquote cite="mid4a0b6d430d914267a3283d041ca13783@.SQLMonster.com"
type="cite">
<pre wrap="">I am not sure if this is helpful but I have included the results of running
"dbcc showcontig" on the table:
DBCC SHOWCONTIG scanning [Table Name] table...
Table: [Table Name] (2009058193); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 42259
- Extents Scanned.......................: 5292
- Extent Switches.......................: 5291
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.83% [5283:5292]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.13%
- Avg. Bytes Free per Page................: 781.0
- Avg. Page Density (full)................: 90.35%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The following is from running dbcc checktable (run two days ago):
DBCC results for [Table Name].
There are 3880797 rows in 44707 pages for object [Table Name].
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Lastly the following is from running dbcc checkalloc against the database:
DBCC results for [Database Name].
***************************************************************
Table sysobjects Object ID 1.
Index ID 1. FirstIAM (1:10). Root (1:11). Dpages 3.
Index ID 1. 5 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:25). Root (1:15). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:33). Root (1:31). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysindexes Object ID 2.
Index ID 1. FirstIAM (1:13). Root (1:14). Dpages 4.
Index ID 1. 6 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (1:27). Root (1:64). Dpages 0.
Index ID 255. 37 pages used in 6 dedicated extents.
Total number of extents is 6.
***************************************************************
Table syscolumns Object ID 3.
Index ID 1. FirstIAM (1:26). Root (1:17). Dpages 12.
Index ID 1. 14 pages used in 1 dedicated extents.
Index ID 2. FirstIAM (1:35). Root (1:34). Dpages 5.
Index ID 2. 7 pages used in 0 dedicated extents.
Total number of extents is 1.
***************************************************************
Table systypes Object ID 4.
Index ID 1. FirstIAM (1:29). Root (1:30). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:37). Root (1:36). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syscomments Object ID 6.
Index ID 1. FirstIAM (1:52). Root (1:51). Dpages 24.
Index ID 1. 27 pages used in 3 dedicated extents.
Total number of extents is 3.
***************************************************************
Table sysfiles1 Object ID 8.
Index ID 0. FirstIAM (1:12). Root (1:32). Dpages 1.
Index ID 0. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syspermissions Object ID 9.
Index ID 1. FirstIAM (1:72). Root (1:63). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysusers Object ID 10.
Index ID 1. FirstIAM (1:39). Root (1:38). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:42). Root (1:41). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:44). Root (1:43). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysproperties Object ID 11.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysdepends Object ID 12.
Index ID 1. FirstIAM (1:55). Root (1:54). Dpages 3.
Index ID 1. 5 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:58). Root (1:57). Dpages 2.
Index ID 2. 4 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysreferences Object ID 14.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 2. 0 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 3. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextcatalogs Object ID 19.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 2. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextnotify Object ID 24.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfilegroups Object ID 96.
Index ID 1. FirstIAM (1:47). Root (1:46). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:50). Root (1:49). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table dtproperties Object ID 517576882.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 255. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table BTSError Object ID 1061578820.
Index ID 1. FirstIAM (1:1076526). Root (1:1145807). Dpages 18042.
Index ID 1. 18102 pages used in 2261 dedicated extents.
Index ID 4. FirstIAM (1:1076527). Root (1:1157874). Dpages 1933.
Index ID 4. 1957 pages used in 243 dedicated extents.
Index ID 5. FirstIAM (1:1159153). Root (1:1159579). Dpages 2242.
Index ID 5. 2274 pages used in 283 dedicated extents.
Index ID 255. FirstIAM (1:355450). Root (1:355449). Dpages 0.
Index ID 255. 88231 pages used in 11036 dedicated extents.
Total number of extents is 13823.
***************************************************************
Table DriveSpace Object ID 1333579789.
Index ID 1. FirstIAM (1:1159157). Root (1:1159158). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:273386). Root (1:1159159). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 5. FirstIAM (1:273388). Root (1:273387). Dpages 1.
Index ID 5. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table DBSize Object ID 1509580416.
Index ID 1. FirstIAM (1:1076523). Root (1:1076524). Dpages 5.
Index ID 1. 7 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:1159155). Root (1:1076525). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table MessageCount Object ID 1669580986.
Index ID 1. FirstIAM (1:355452). Root (1:355448). Dpages 2.
Index ID 1. 4 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:411009). Root (1:411008). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:411012). Root (1:411010). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table IFSMessageTypes Object ID 1993058136.
Index ID 1. FirstIAM (1:618). Root (1:619). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table IFSMessages Object ID 2009058193.
Index ID 1. FirstIAM (1:432792). Root (1:9362263). Dpages 44700.
Index ID 1. 44780 pages used in 5597 dedicated extents.
Index ID 6. FirstIAM (1:432793). Root (1:9404245). Dpages 10083.
Index ID 6. 10119 pages used in 1266 dedicated extents.
Index ID 255. FirstIAM (1:268). Root (1:267). Dpages 0.
Index ID 255. 9062113 pages used in 1133279 dedicated extents.
Total number of extents is 1140142.
***************************************************************
Table IFSUpdateError Object ID 2025058250.
Index ID 1. FirstIAM (1:116). Root (1:1168777). Dpages 800.
Index ID 1. 821 pages used in 102 dedicated extents.
Index ID 255. FirstIAM (1:113). Root (1:112). Dpages 0.
Index ID 255. 487 pages used in 93 dedicated extents.
Total number of extents is 195.
***************************************************************
Processed 46 entries in sysindexes for database ID 7.
File 1. Number of extents = 1154232, used pages = 9229133, reserved pages =9233856.
File 1 (number of mixed extents = 40, mixed pages = 320).
Object ID 1, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
Object ID 1, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 1, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
Object ID 2, Index ID 0, data extents 0, pages 6, mixed extent pages 6.
Object ID 2, Index ID 255, index extents 6, pages 37, mixed extent
pages 22.
Object ID 3, Index ID 0, data extents 1, pages 14, mixed extent pages 9.
Object ID 3, Index ID 2, index extents 0, pages 7, mixed extent pages 7.
Object ID 4, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 4, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 6, Index ID 0, data extents 3, pages 27, mixed extent pages
10.
Object ID 8, Index ID 0, data extents 0, pages 2, mixed extent pages 2.
Object ID 9, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 2, index extents 0, pages 2, mixed extent pages
2.
Object ID 10, Index ID 3, index extents 0, pages 2, mixed extent pages
2.
Object ID 12, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
Object ID 12, Index ID 2, index extents 0, pages 4, mixed extent pages
4.
Object ID 96, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 96, Index ID 2, index extents 0, pages 2, mixed extent pages
2.
Object ID 99, Index ID 0, data extents 22, pages 91, mixed extent pages
1.
Object ID 1061578820, Index ID 0, data extents 2261, pages 18102, mixed
extent pages 26.
Object ID 1061578820, Index ID 4, index extents 243, pages 1957, mixed
extent pages 24.
Object ID 1061578820, Index ID 5, index extents 283, pages 2274, mixed
extent pages 26.
Object ID 1061578820, Index ID 255, index extents 11036, pages 88231,
mixed extent pages 28.
Object ID 1333579789, Index ID 0, data extents 0, pages 3, mixed extent
pages 3.
Object ID 1333579789, Index ID 2, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1333579789, Index ID 5, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1509580416, Index ID 0, data extents 0, pages 7, mixed extent
pages 7.
Object ID 1509580416, Index ID 3, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1669580986, Index ID 0, data extents 0, pages 4, mixed extent
pages 4.
Object ID 1669580986, Index ID 2, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1669580986, Index ID 3, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1993058136, Index ID 0, data extents 0, pages 3, mixed extent
pages 3.
Object ID 2009058193, Index ID 0, data extents 5597, pages 44780, mixed
extent pages 10.
Object ID 2009058193, Index ID 6, index extents 1266, pages 10119,
mixed extent pages 10.
Object ID 2009058193, Index ID 255, index extents 1133279, pages
9062113, mixed extent pages 28.
Object ID 2025058250, Index ID 0, data extents 102, pages 821, mixed
extent pages 26.
Object ID 2025058250, Index ID 255, index extents 93, pages 487, mixed
extent pages 20.
Total number of extents = 1154232, used pages = 9229133, reserved pages =9233856 in this database.
(number of mixed extents = 40, mixed pages = 320) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database
[Database Name].
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
</pre>
</blockquote>
</body>
</html>
--020103030708070402090004--|||That rebuilds all indexes against that one specific table.
Sincerely,
Anthony Thomas
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:fdb75573b04e4e2fba5eb109094771e4@.SQLMonster.com...
I have a weekly job that executes the following:
dbcc dbreindex(N'[dbo].[Table Name]', N'', 0, sorted_data_reorg)
Does that rebuild the index?
--
Message posted via http://www.sqlmonster.com|||In Query Analyzer, switch to the database this refers to then execute the
following:
SELECT OBJECT_NAME(object_id)
This will return the name of the object this id belongs to. Or,
SELECT [name]
FROM sysobjects
WHERE id = <object_id>
Sincerely,
Anthony Thomas
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:3a0844e9a3c54080a0700d4b1565d3a7@.SQLMonster.com...
This table has 8 rows, with a primary key as a clustered index.
How can I know what objectid refers to?
--
Message posted via http://www.sqlmonster.com|||So if I have a clustered index on this table (a primary key on a identity
seed which is IndexId = 1), then how can I reduce extent usage on the LOB?
--
Message posted via http://www.sqlmonster.com|||This is a multi-part message in MIME format.
--020900020706080309080505
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
You can't. The clustered index has nothing to do with the text data
fragmentation (i.e. extent usage) in the table. The text data appears as
index 255. The clustered index displays as index 1.
Besides, you already have a clustered index in that table (index ID 1).
If it was a heap then there would be no index 1 and instead there would
be an index ID 0 (the unsorted table data).
Does anyone know if you can defrag the text data with "DBCC INDEXDEFRAG
(7, 2009058193, *255*)" in order to reclaim LOB space? I suspect you
can't but I've never tried before and I can't see any mention of it in
BOL (mentions clustered & nonclustered indexes but not index 255).
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Robert Richards via SQLMonster.com wrote:
>So if I have a clustered index on this table (a primary key on a identity
>seed which is IndexId = 1), then how can I reduce extent usage on the LOB?
>
>
--020900020706080309080505
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can't. The clustered index has nothing to do with the text
data fragmentation (i.e. extent usage) in the table. The text data
appears as index 255. The clustered index displays as index 1. 
 
Besides, you already have a clustered index in that table (index ID
1). If it was a heap then there would be no index 1 and instead there
would be an index ID 0 (the unsorted table data). 
 
Does anyone know if you can defrag the text data with "DBCC INDEXDEFRAG
(7, 2009058193, 255)" in order to reclaim LOB space? I suspect
you can't but I've never tried before and I can't see any mention of it
in BOL (mentions clustered & nonclustered indexes but not index
255). 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Robert Richards via SQLMonster.com wrote:
<blockquote cite="mid32cdee166af64d06b136fe16b1ba9630@.SQLMonster.com"
type="cite">
<pre wrap="">So if I have a clustered index on this table (a primary key on a identity
seed which is IndexId = 1), then how can I reduce extent usage on the LOB?
</pre>
</blockquote>
</body>
</html>
--020900020706080309080505--|||My apologies, but I commented on the lack of a clustered index on this table
but clearly the output shows one. I inadvertenly scanned through all the
output attempting to find the large set and misinterpreted the last part of
the CHECKALLOC output.
DBCC SHOWCONTIG scanning [Table Name] table...
Table: [Table Name] (2009058193); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 42259
- Extents Scanned.......................: 5292
- Extent Switches.......................: 5291
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.83% [5283:5292]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.13%
- Avg. Bytes Free per Page................: 781.0
- Avg. Page Density (full)................: 90.35%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
***************************************************************
Table IFSMessages Object ID 2009058193.
Index ID 1. FirstIAM (1:432792). Root (1:9362263). Dpages 44700.
Index ID 1. 44780 pages used in 5597 dedicated extents.
Index ID 6. FirstIAM (1:432793). Root (1:9404245). Dpages 10083.
Index ID 6. 10119 pages used in 1266 dedicated extents.
Index ID 255. FirstIAM (1:268). Root (1:267). Dpages 0.
Index ID 255. 9062113 pages used in 1133279 dedicated extents.
Total number of extents is 1140142.
***************************************************************
Object ID 2009058193, Index ID 0, data extents 5597, pages 44780, mixed
extent pages 10.
Object ID 2009058193, Index ID 6, index extents 1266, pages 10119,
mixed extent pages 10.
Object ID 2009058193, Index ID 255, index extents 1133279, pages
9062113, mixed extent pages 28.
What does bother me though is that not only does the LOB comprise the bulk
of the data, it is also sitting in the head of the file (FirstIAM(1:268)).
Somewhere in this thread I know I recommended seperating you LOBs out to a
seperate 1 to 1 related table, placed on a dedicated filegroup, with
multiple dedicated files. This would certainly clear this up, at least
once. But at this point I'm still not sure that we can even verify that the
LOB data is fragmented at all.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23UaN8CiaFHA.2900@.TK2MSFTNGP15.phx.gbl...
Well, certainly the bulk of your data resides in the LOB for the following
object:
Object ID 2009058193, Index ID 255, index extents 1133279, pages
9062113, mixed extent pages 28.
Another problem I see is the lack of usage for Clustered Indexes. Every
table should be defined with a Clustered Index. How to choose the
attributes for these indexes is a whole other discussion, and argument among
my peers here; however, the need for them can not be overstated, especially
when using LOBs.
I would certainly try to place one on Object ID 2009058193 to see if it
improves the storage.
Sincerely,
Anthony Thomas
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4a0b6d430d914267a3283d041ca13783@.SQLMonster.com...
I am not sure if this is helpful but I have included the results of running
"dbcc showcontig" on the table:
DBCC SHOWCONTIG scanning [Table Name] table...
Table: [Table Name] (2009058193); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 42259
- Extents Scanned.......................: 5292
- Extent Switches.......................: 5291
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.83% [5283:5292]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.13%
- Avg. Bytes Free per Page................: 781.0
- Avg. Page Density (full)................: 90.35%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The following is from running dbcc checktable (run two days ago):
DBCC results for [Table Name].
There are 3880797 rows in 44707 pages for object [Table Name].
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Lastly the following is from running dbcc checkalloc against the database:
DBCC results for [Database Name].
***************************************************************
Table sysobjects Object ID 1.
Index ID 1. FirstIAM (1:10). Root (1:11). Dpages 3.
Index ID 1. 5 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:25). Root (1:15). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:33). Root (1:31). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysindexes Object ID 2.
Index ID 1. FirstIAM (1:13). Root (1:14). Dpages 4.
Index ID 1. 6 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (1:27). Root (1:64). Dpages 0.
Index ID 255. 37 pages used in 6 dedicated extents.
Total number of extents is 6.
***************************************************************
Table syscolumns Object ID 3.
Index ID 1. FirstIAM (1:26). Root (1:17). Dpages 12.
Index ID 1. 14 pages used in 1 dedicated extents.
Index ID 2. FirstIAM (1:35). Root (1:34). Dpages 5.
Index ID 2. 7 pages used in 0 dedicated extents.
Total number of extents is 1.
***************************************************************
Table systypes Object ID 4.
Index ID 1. FirstIAM (1:29). Root (1:30). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:37). Root (1:36). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syscomments Object ID 6.
Index ID 1. FirstIAM (1:52). Root (1:51). Dpages 24.
Index ID 1. 27 pages used in 3 dedicated extents.
Total number of extents is 3.
***************************************************************
Table sysfiles1 Object ID 8.
Index ID 0. FirstIAM (1:12). Root (1:32). Dpages 1.
Index ID 0. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syspermissions Object ID 9.
Index ID 1. FirstIAM (1:72). Root (1:63). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysusers Object ID 10.
Index ID 1. FirstIAM (1:39). Root (1:38). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:42). Root (1:41). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:44). Root (1:43). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysproperties Object ID 11.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysdepends Object ID 12.
Index ID 1. FirstIAM (1:55). Root (1:54). Dpages 3.
Index ID 1. 5 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:58). Root (1:57). Dpages 2.
Index ID 2. 4 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysreferences Object ID 14.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 2. 0 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 3. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextcatalogs Object ID 19.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 2. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextnotify Object ID 24.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfilegroups Object ID 96.
Index ID 1. FirstIAM (1:47). Root (1:46). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:50). Root (1:49). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table dtproperties Object ID 517576882.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 255. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table BTSError Object ID 1061578820.
Index ID 1. FirstIAM (1:1076526). Root (1:1145807). Dpages 18042.
Index ID 1. 18102 pages used in 2261 dedicated extents.
Index ID 4. FirstIAM (1:1076527). Root (1:1157874). Dpages 1933.
Index ID 4. 1957 pages used in 243 dedicated extents.
Index ID 5. FirstIAM (1:1159153). Root (1:1159579). Dpages 2242.
Index ID 5. 2274 pages used in 283 dedicated extents.
Index ID 255. FirstIAM (1:355450). Root (1:355449). Dpages 0.
Index ID 255. 88231 pages used in 11036 dedicated extents.
Total number of extents is 13823.
***************************************************************
Table DriveSpace Object ID 1333579789.
Index ID 1. FirstIAM (1:1159157). Root (1:1159158). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:273386). Root (1:1159159). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 5. FirstIAM (1:273388). Root (1:273387). Dpages 1.
Index ID 5. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table DBSize Object ID 1509580416.
Index ID 1. FirstIAM (1:1076523). Root (1:1076524). Dpages 5.
Index ID 1. 7 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:1159155). Root (1:1076525). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table MessageCount Object ID 1669580986.
Index ID 1. FirstIAM (1:355452). Root (1:355448). Dpages 2.
Index ID 1. 4 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:411009). Root (1:411008). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:411012). Root (1:411010). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table IFSMessageTypes Object ID 1993058136.
Index ID 1. FirstIAM (1:618). Root (1:619). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table IFSMessages Object ID 2009058193.
Index ID 1. FirstIAM (1:432792). Root (1:9362263). Dpages 44700.
Index ID 1. 44780 pages used in 5597 dedicated extents.
Index ID 6. FirstIAM (1:432793). Root (1:9404245). Dpages 10083.
Index ID 6. 10119 pages used in 1266 dedicated extents.
Index ID 255. FirstIAM (1:268). Root (1:267). Dpages 0.
Index ID 255. 9062113 pages used in 1133279 dedicated extents.
Total number of extents is 1140142.
***************************************************************
Table IFSUpdateError Object ID 2025058250.
Index ID 1. FirstIAM (1:116). Root (1:1168777). Dpages 800.
Index ID 1. 821 pages used in 102 dedicated extents.
Index ID 255. FirstIAM (1:113). Root (1:112). Dpages 0.
Index ID 255. 487 pages used in 93 dedicated extents.
Total number of extents is 195.
***************************************************************
Processed 46 entries in sysindexes for database ID 7.
File 1. Number of extents = 1154232, used pages = 9229133, reserved pages =9233856.
File 1 (number of mixed extents = 40, mixed pages = 320).
Object ID 1, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
Object ID 1, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 1, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
Object ID 2, Index ID 0, data extents 0, pages 6, mixed extent pages 6.
Object ID 2, Index ID 255, index extents 6, pages 37, mixed extent
pages 22.
Object ID 3, Index ID 0, data extents 1, pages 14, mixed extent pages 9.
Object ID 3, Index ID 2, index extents 0, pages 7, mixed extent pages 7.
Object ID 4, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 4, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 6, Index ID 0, data extents 3, pages 27, mixed extent pages
10.
Object ID 8, Index ID 0, data extents 0, pages 2, mixed extent pages 2.
Object ID 9, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 2, index extents 0, pages 2, mixed extent pages
2.
Object ID 10, Index ID 3, index extents 0, pages 2, mixed extent pages
2.
Object ID 12, Index ID 0, data extents 0, pages 5, mixed extent pages 5.
Object ID 12, Index ID 2, index extents 0, pages 4, mixed extent pages
4.
Object ID 96, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 96, Index ID 2, index extents 0, pages 2, mixed extent pages
2.
Object ID 99, Index ID 0, data extents 22, pages 91, mixed extent pages
1.
Object ID 1061578820, Index ID 0, data extents 2261, pages 18102, mixed
extent pages 26.
Object ID 1061578820, Index ID 4, index extents 243, pages 1957, mixed
extent pages 24.
Object ID 1061578820, Index ID 5, index extents 283, pages 2274, mixed
extent pages 26.
Object ID 1061578820, Index ID 255, index extents 11036, pages 88231,
mixed extent pages 28.
Object ID 1333579789, Index ID 0, data extents 0, pages 3, mixed extent
pages 3.
Object ID 1333579789, Index ID 2, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1333579789, Index ID 5, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1509580416, Index ID 0, data extents 0, pages 7, mixed extent
pages 7.
Object ID 1509580416, Index ID 3, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1669580986, Index ID 0, data extents 0, pages 4, mixed extent
pages 4.
Object ID 1669580986, Index ID 2, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1669580986, Index ID 3, index extents 0, pages 2, mixed
extent pages 2.
Object ID 1993058136, Index ID 0, data extents 0, pages 3, mixed extent
pages 3.
Object ID 2009058193, Index ID 0, data extents 5597, pages 44780, mixed
extent pages 10.
Object ID 2009058193, Index ID 6, index extents 1266, pages 10119,
mixed extent pages 10.
Object ID 2009058193, Index ID 255, index extents 1133279, pages
9062113, mixed extent pages 28.
Object ID 2025058250, Index ID 0, data extents 102, pages 821, mixed
extent pages 26.
Object ID 2025058250, Index ID 255, index extents 93, pages 487, mixed
extent pages 20.
Total number of extents = 1154232, used pages = 9229133, reserved pages =9233856 in this database.
(number of mixed extents = 40, mixed pages = 320) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database
[Database Name].
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
Message posted via http://www.sqlmonster.com|||Hey, Robert? Didn't you just ask the same question back in February?
Paul Randal answered your question. You can't do much better than straight
from the horses mouth, as they say.
"The space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine"
Sincerely,
Anthony Thomas
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4ba2145437d645fb8d8d110f8c01ea71@.SQLMonster.com...
I do not believe I am being understood. When I delete the 500,000, it is
not to an archive table. These 500,000 records are gone.
The 80GB max is due to available disk space.
When I delete records, I need to reclaim the unused space. Are the two
steps below a way of doing that?
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)"
Is there a better way to reclaim unused space?
--
Message posted via http://www.sqlmonster.com|||I work with a much smaller subset in my TEST environment and space is
reclaimed after a delete. It doesn't make sense why it does not reclaim any
space on a larger scale.
--
Message posted via http://www.sqlmonster.com|||Just curious, how did the FirstIAM tell you that it was at the head of the
file? Sounds like a good thing to be able to understand. Thanks for all
your help.
--
Message posted via http://www.sqlmonster.com|||This is a multi-part message in MIME format.
--020600010101060506080807
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
BOL gives a good summary of how IAM (Index Allocation Map) pages work:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1bar.asp
This is also a fairly helpful page in understanding index structures:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp
Basically, extents used by indexes (and LOBs) are tracked by a linked
list of IAM pages. The head of that linked list is called the
FirstIAM. So, if you have a scan through the results of that DBCC
CHECKALLOC you did, you'll see the head of the linked list of IAM pages
that tracks the LOB data for the IFSMessages table is at 1:268 (this
represents the 268th page in file 1; if your filegroup has only a single
file in it then the file number is irrelevant). Even though this page is
not at the very beginning of the file (all the system tables in the
database, eg. sysobjects (1:10), start their IAM page chain before
1:268), it's far closer to the beginning than the end (eg. the clustered
index of BTSError starts at 1:1076526).
While this is a moderately interesting fact, I'm not sure how relevant
it really is because the IAM pages (and the extents that they map) are
not necessarily in consecutive order, depending on how fragmented they
are. Remember, it's a linked list and the links could jump all over the
place. It's probably a reasonable assumption, however, that the IAM
pages in the LOB chain for IFSMessages are all together because that
data accounts for the vast majority of the data in the database (as
we've already determined) and if the chain starts at 1:268 and others
start at 1:1076526 (and above) then the huge gap between those points is
most likely filled with the rest of the IAM chain for IFSMessage LOB data.
Does that make sense?
What that really means, to me at least, is that most of those IAM pages
(for the IFSMessage LOB data) were allocated before (chronologically)
the IAM pages for other objects in your DB (like BTSError for example,
and even the clustered index of IFSMessages itself). I'm not sure how
that's possible (that the LOB IAM chain is all before the clustered
index IAM chain for the same table). Perhaps the head of the IAM chain
for the clustered index got shuffled towards the end during an index
rebuild (I don't know if index rebuilds & defrags change the order of
the IAM pages or just the extents that they refer to). Do any SQL
Server architecture gurus out there want to confirm/deny that?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Robert Richards via SQLMonster.com wrote:
>Just curious, how did the FirstIAM tell you that it was at the head of the
>file? Sounds like a good thing to be able to understand. Thanks for all
>your help.
>
>
--020600010101060506080807
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>BOL gives a good summary of how IAM </tt><tt>(Index Allocation
Map) </tt><tt>pages work: 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1bar.asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1bar.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1bar.asp</a> 
 
This is also a fairly helpful page in understanding index structures: 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp</a> 
 
Basically, extents used by indexes (and LOBs) are tracked by a linked
list of IAM pages. The head of that linked list is called the
FirstIAM. So, if you have a scan through the results of that DBCC
CHECKALLOC you did, you'll see the head of the linked list of IAM pages
that tracks the LOB data for the IFSMessages table is at 1:268 (this
represents the 268th page in file 1; if your filegroup has only a
single file in it then the file number is irrelevant). Even though this
page is not at the very beginning of the file (all the system tables in
the database, eg. sysobjects (1:10), start their IAM page chain before
1:268), it's far closer to the beginning than the end (eg. the
clustered index of BTSError starts at 1:1076526). 
 
While this is a moderately interesting fact, I'm not sure how relevant
it really is because the IAM pages (and the extents that they map) are
not necessarily in consecutive order, depending on how fragmented they
are. Remember, it's a linked list and the links could jump all over
the place. It's probably a reasonable assumption, however, that the
IAM pages in the LOB chain for IFSMessages are all together because
that data accounts for the vast majority of the data in the database
(as we've already determined) and if the chain starts at 1:268 and
others start at 1:1076526 (and above) then the huge gap between those
points is most likely filled with the rest of the IAM chain for
IFSMessage LOB data. 
 
Does that make sense? 
 
What that really means, to me at least, is that most of those IAM pages
(for the IFSMessage LOB data) were allocated before (chronologically)
the IAM pages for other objects in your DB (like BTSError for example,
and even the clustered index of IFSMessages itself). I'm not sure how
that's possible (that the LOB IAM chain is all before the clustered
index IAM chain for the same table). Perhaps the head of the IAM chain
for the clustered index got shuffled towards the end during an index
rebuild (I don't know if index rebuilds & defrags change the order
of the IAM pages or just the extents that they refer to). Do any SQL
Server architecture gurus out there want to confirm/deny that? 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Robert Richards via SQLMonster.com wrote:
<blockquote cite="mide53454dc2ecf44559526bc0dd9b86411@.SQLMonster.com"
type="cite">
<pre wrap="">Just curious, how did the FirstIAM tell you that it was at the head of the
file? Sounds like a good thing to be able to understand. Thanks for all
your help.
</pre>
</blockquote>
</body>
</html>
--020600010101060506080807--|||It is not a matter of scale; it is just that the scale makes the matter more
visable.
Once the pages are marked for deletion, they are not reclaimed, but can be
reused. On a smaller scale, this happens a lot sooner.
I just hadn't realized that no Database Maintenance operation could clear up
old unused LOB pages. But, once deleted, new data could reuse it. In your
case, however, you are slowly reducing the need of this database and were
hoping to decrease its size over time. Apparently, your only option is to
Create a temp table, move the data, drop the old table, and rename the temp
in order to reclaim this space, accounting for RI restrictions, of course.
I would consider scheduling this activity after every large scale delete:
weekly, monthly, or quarterly, as the case may be.
Sincerely,
Anthony Thomas
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:60a2af8b2b12488b8acc76fa34b27bd0@.SQLMonster.com...
I work with a much smaller subset in my TEST environment and space is
reclaimed after a delete. It doesn't make sense why it does not reclaim any
space on a larger scale.
--
Message posted via http://www.sqlmonster.com

Thursday, March 29, 2012

Data File size

No comments:

Post a Comment

Data Flow Task Problem

Blog Archive

About Me