All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000
Kay
http://www.sql-server-performance.co...showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>
Showing posts with label executed. Show all posts
Showing posts with label executed. Show all posts
Sunday, March 25, 2012
DBCC SHOWCONTIG and Extent Switches
All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>
DBCC SHOWCONTIG and Extent Switches
All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.c..._showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your exten
t
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since th
e
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" an
d
> "LogicalFragmentation", I paid special attention to "Extent Swithces" whic
h
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSiz
e
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensit
y
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.32
2
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.41
7
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 16902
2
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 37
0
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 1
2
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 1
6
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>sql
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.c..._showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your exten
t
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since th
e
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" an
d
> "LogicalFragmentation", I paid special attention to "Extent Swithces" whic
h
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSiz
e
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensit
y
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.32
2
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.41
7
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 16902
2
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 37
0
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 1
2
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 1
6
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>sql
Wednesday, March 7, 2012
dbcc dbreindex question
Is this command, which I know can be executed on a per-table basis,
the same as the Rebuild Index Task in the SQL Server Management Studio
for maintenance tasks??
I would guess the maintenance task uses ALTER INDEX REBUILD which is a
more modern version of DBCC DBREBUILD, at least in terms of syntax.
the same as the Rebuild Index Task in the SQL Server Management Studio
for maintenance tasks??
The documentation I have mentions the reindex.sql script, is this the
script that is executed by the Rebuild Index Task??
Thank you, Tomtlyczko (tlyczko@.gmail.com) writes:
Quote:
Originally Posted by
Is this command, which I know can be executed on a per-table basis,
the same as the Rebuild Index Task in the SQL Server Management Studio
for maintenance tasks??
I would guess the maintenance task uses ALTER INDEX REBUILD which is a
more modern version of DBCC DBREBUILD, at least in terms of syntax.
I have not worked much with maintenance plans, but since they SSIS
packages, you should be able to look what's on the inside with help
of the Business Intelligence Development Studio.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
DBCC DBREINDEX Help
DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
when the above given command is executed i get the result as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
instead of
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
how can i get the individual index id once they are reindexed?
pls respond me as soon as possible
Regards
Sudarshan SelvarajaWhy do you need the individual index ID? You specified an index so you
should know what it's ID is. In your case only that one index will be
rebuilt.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:136BB536-3A7D-462E-9FC0-9A714A40FF2C@.microsoft.com...
> DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
> when the above given command is executed i get the result as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> instead of
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> how can i get the individual index id once they are reindexed?
> pls respond me as soon as possible
> Regards
> Sudarshan Selvaraja
>
>|||Andrew,
He has taken the example from BOL. Coming to think of it, though I never
realised, I have never got any message when index is being rebuilt.
I do it for defrag once in a while, so use showcontig after I run this.
And, to answer your question. The following doesn't return any messages
either.
use northwind
DBCC DBREINDEX (Employees, '', 80)
And its got two indexes.|||To be honest I can't remember if it shows these messages or not. But if you
reindex a clustered index that is not unique it will have to rebuild all the
non-clustered indexes as well. This is due to the way it enforces uniqueness
on the clustered index in 2000. So what you see may depend on if the
clustered index is unique or not.
Andrew J. Kelly SQL MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
> Andrew,
> He has taken the example from BOL. Coming to think of it, though I never
> realised, I have never got any message when index is being rebuilt.
> I do it for defrag once in a while, so use showcontig after I run this.
> And, to answer your question. The following doesn't return any messages
> either.
> use northwind
> DBCC DBREINDEX (Employees, '', 80)
> And its got two indexes.|||In BOL they have mentioned that when DBCC DBREINDEX is executed the output
will be
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator
Actually i need this because when i reindex all the tables in the db i need
to know what are the index reindexed.
Regards
Sudarshan Selvaraja
"Andrew J. Kelly" wrote:
> To be honest I can't remember if it shows these messages or not. But if y
ou
> reindex a clustered index that is not unique it will have to rebuild all t
he
> non-clustered indexes as well. This is due to the way it enforces uniquene
ss
> on the clustered index in 2000. So what you see may depend on if the
> clustered index is unique or not.
> --
> Andrew J. Kelly SQL MVP
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
>
>|||USE Northwind --Enter the name of the database you want to reindex
go
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
When the above given code is executed i get the output as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I dont know exactly what are the index reindexed ...any suggestion pls
"sudarshan selvaraja" wrote:
> In BOL they have mentioned that when DBCC DBREINDEX is executed the output
> will be
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator
> Actually i need this because when i reindex all the tables in the db i nee
d
> to know what are the index reindexed.
> Regards
> Sudarshan Selvaraja
>
> "Andrew J. Kelly" wrote:
>|||This will reindex all of them. Do you really need to know which ones when
it is all of them? You can find out what indexes are on each table by
looking in sysindexes or sp_helpindex. You can also build a cursor based on
either and rebuild the indexes one at a time but why bother if you are going
to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
that will only reindex or Defrag indexes above a certain fragmentation
level. Maybe this is more of what you want.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
> USE Northwind --Enter the name of the database you want to reindex
> go
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> When the above given code is executed i get the output as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I dont know exactly what are the index reindexed ...any suggestion pls
>
>
> "sudarshan selvaraja" wrote:
>|||Ok thx andrew i thought i could get the individual index id when it is
reindexed..any how it seems that i can use the code in BOL given with
showcontig..
"Andrew J. Kelly" wrote:
> This will reindex all of them. Do you really need to know which ones when
> it is all of them? You can find out what indexes are on each table by
> looking in sysindexes or sp_helpindex. You can also build a cursor based o
n
> either and rebuild the indexes one at a time but why bother if you are goi
ng
> to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
> that will only reindex or Defrag indexes above a certain fragmentation
> level. Maybe this is more of what you want.
> --
> Andrew J. Kelly SQL MVP
>
> "sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
> in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
>
>
when the above given command is executed i get the result as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
instead of
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
how can i get the individual index id once they are reindexed?
pls respond me as soon as possible
Regards
Sudarshan SelvarajaWhy do you need the individual index ID? You specified an index so you
should know what it's ID is. In your case only that one index will be
rebuilt.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:136BB536-3A7D-462E-9FC0-9A714A40FF2C@.microsoft.com...
> DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
> when the above given command is executed i get the result as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> instead of
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> how can i get the individual index id once they are reindexed?
> pls respond me as soon as possible
> Regards
> Sudarshan Selvaraja
>
>|||Andrew,
He has taken the example from BOL. Coming to think of it, though I never
realised, I have never got any message when index is being rebuilt.
I do it for defrag once in a while, so use showcontig after I run this.
And, to answer your question. The following doesn't return any messages
either.
use northwind
DBCC DBREINDEX (Employees, '', 80)
And its got two indexes.|||To be honest I can't remember if it shows these messages or not. But if you
reindex a clustered index that is not unique it will have to rebuild all the
non-clustered indexes as well. This is due to the way it enforces uniqueness
on the clustered index in 2000. So what you see may depend on if the
clustered index is unique or not.
Andrew J. Kelly SQL MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
> Andrew,
> He has taken the example from BOL. Coming to think of it, though I never
> realised, I have never got any message when index is being rebuilt.
> I do it for defrag once in a while, so use showcontig after I run this.
> And, to answer your question. The following doesn't return any messages
> either.
> use northwind
> DBCC DBREINDEX (Employees, '', 80)
> And its got two indexes.|||In BOL they have mentioned that when DBCC DBREINDEX is executed the output
will be
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator
Actually i need this because when i reindex all the tables in the db i need
to know what are the index reindexed.
Regards
Sudarshan Selvaraja
"Andrew J. Kelly" wrote:
> To be honest I can't remember if it shows these messages or not. But if y
ou
> reindex a clustered index that is not unique it will have to rebuild all t
he
> non-clustered indexes as well. This is due to the way it enforces uniquene
ss
> on the clustered index in 2000. So what you see may depend on if the
> clustered index is unique or not.
> --
> Andrew J. Kelly SQL MVP
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
>
>|||USE Northwind --Enter the name of the database you want to reindex
go
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
When the above given code is executed i get the output as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I dont know exactly what are the index reindexed ...any suggestion pls
"sudarshan selvaraja" wrote:
> In BOL they have mentioned that when DBCC DBREINDEX is executed the output
> will be
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator
> Actually i need this because when i reindex all the tables in the db i nee
d
> to know what are the index reindexed.
> Regards
> Sudarshan Selvaraja
>
> "Andrew J. Kelly" wrote:
>|||This will reindex all of them. Do you really need to know which ones when
it is all of them? You can find out what indexes are on each table by
looking in sysindexes or sp_helpindex. You can also build a cursor based on
either and rebuild the indexes one at a time but why bother if you are going
to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
that will only reindex or Defrag indexes above a certain fragmentation
level. Maybe this is more of what you want.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
> USE Northwind --Enter the name of the database you want to reindex
> go
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> When the above given code is executed i get the output as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I dont know exactly what are the index reindexed ...any suggestion pls
>
>
> "sudarshan selvaraja" wrote:
>|||Ok thx andrew i thought i could get the individual index id when it is
reindexed..any how it seems that i can use the code in BOL given with
showcontig..
"Andrew J. Kelly" wrote:
> This will reindex all of them. Do you really need to know which ones when
> it is all of them? You can find out what indexes are on each table by
> looking in sysindexes or sp_helpindex. You can also build a cursor based o
n
> either and rebuild the indexes one at a time but why bother if you are goi
ng
> to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
> that will only reindex or Defrag indexes above a certain fragmentation
> level. Maybe this is more of what you want.
> --
> Andrew J. Kelly SQL MVP
>
> "sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
> in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
>
>
Friday, February 17, 2012
dbcc checkdb in Yukon
Hi ,
When dbcc checkdb or checkcatalog executed on master db of Yukon got the
following error .
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be prepared for checking.
Re-run the command with the database in single-user or read-only mode. See
previous errors for more details.
Which build are you using? On the Dec CTP release (9.00.0981)
dbcc checkdb('master')
dbcc checkcatalog('master')
work just fine for me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi ,
> When dbcc checkdb or checkcatalog executed on master db of Yukon got the
> following error .
> Msg 5030, Level 16, State 12, Line 1
> The database could not be exclusively locked to perform the operation.
> Msg 7926, Level 16, State 1, Line 1
> Check statement aborted. The database could not be prepared for checking.
> Re-run the command with the database in single-user or read-only mode. See
> previous errors for more details.
>
>
>
|||Thanks
am using 9.00.852
so is it like it won't work only if I give Dbcc checkdb
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Which build are you using? On the Dec CTP release (9.00.0981)
> dbcc checkdb('master')
> dbcc checkcatalog('master')
> work just fine for me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
checking.[vbcol=seagreen]
See
>
|||No it does work if you are in master and do not specify the database name as
well, running:
dbcc checkdb
dbcc checkcatalog
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> rights.
> checking.
> See
>
|||Do you have these databases on a FAT file system? Checkdb in Yukon uses
database snapshots to allow online checks. If the database is on a
filesystem other than NTFS, or there is no space on the drive for a
snapshot, then online checks cannot be done and another mechanism for
achieving transactional consistency is required instead. You should have
seen some other errors which will have explained. This behavior is fully
documented in Beta 3 BOL.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> rights.
the
> checking.
> See
>
When dbcc checkdb or checkcatalog executed on master db of Yukon got the
following error .
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be prepared for checking.
Re-run the command with the database in single-user or read-only mode. See
previous errors for more details.
Which build are you using? On the Dec CTP release (9.00.0981)
dbcc checkdb('master')
dbcc checkcatalog('master')
work just fine for me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi ,
> When dbcc checkdb or checkcatalog executed on master db of Yukon got the
> following error .
> Msg 5030, Level 16, State 12, Line 1
> The database could not be exclusively locked to perform the operation.
> Msg 7926, Level 16, State 1, Line 1
> Check statement aborted. The database could not be prepared for checking.
> Re-run the command with the database in single-user or read-only mode. See
> previous errors for more details.
>
>
>
|||Thanks
am using 9.00.852
so is it like it won't work only if I give Dbcc checkdb
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Which build are you using? On the Dec CTP release (9.00.0981)
> dbcc checkdb('master')
> dbcc checkcatalog('master')
> work just fine for me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
checking.[vbcol=seagreen]
See
>
|||No it does work if you are in master and do not specify the database name as
well, running:
dbcc checkdb
dbcc checkcatalog
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> rights.
> checking.
> See
>
|||Do you have these databases on a FAT file system? Checkdb in Yukon uses
database snapshots to allow online checks. If the database is on a
filesystem other than NTFS, or there is no space on the drive for a
snapshot, then online checks cannot be done and another mechanism for
achieving transactional consistency is required instead. You should have
seen some other errors which will have explained. This behavior is fully
documented in Beta 3 BOL.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> rights.
the
> checking.
> See
>
dbcc checkdb in Yukon
Hi ,
When dbcc checkdb or checkcatalog executed on master db of Yukon got the
following error .
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be prepared for checking.
Re-run the command with the database in single-user or read-only mode. See
previous errors for more details.Which build are you using? On the Dec CTP release (9.00.0981)
dbcc checkdb('master')
dbcc checkcatalog('master')
work just fine for me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi ,
> When dbcc checkdb or checkcatalog executed on master db of Yukon got the
> following error .
> Msg 5030, Level 16, State 12, Line 1
> The database could not be exclusively locked to perform the operation.
> Msg 7926, Level 16, State 1, Line 1
> Check statement aborted. The database could not be prepared for checking.
> Re-run the command with the database in single-user or read-only mode. See
> previous errors for more details.
>
>
>|||Thanks
am using 9.00.852
so is it like it won't work only if I give Dbcc checkdb
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Which build are you using? On the Dec CTP release (9.00.0981)
> dbcc checkdb('master')
> dbcc checkcatalog('master')
> work just fine for me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
checking.[vbcol=seagreen]
See[vbcol=seagreen]
>|||No it does work if you are in master and do not specify the database name as
well, running:
dbcc checkdb
dbcc checkcatalog
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> rights.
> checking.
> See
>|||Do you have these databases on a FAT file system? Checkdb in Yukon uses
database snapshots to allow online checks. If the database is on a
filesystem other than NTFS, or there is no space on the drive for a
snapshot, then online checks cannot be done and another mechanism for
achieving transactional consistency is required instead. You should have
seen some other errors which will have explained. This behavior is fully
documented in Beta 3 BOL.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> rights.
the[vbcol=seagreen]
> checking.
> See
>
When dbcc checkdb or checkcatalog executed on master db of Yukon got the
following error .
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be prepared for checking.
Re-run the command with the database in single-user or read-only mode. See
previous errors for more details.Which build are you using? On the Dec CTP release (9.00.0981)
dbcc checkdb('master')
dbcc checkcatalog('master')
work just fine for me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi ,
> When dbcc checkdb or checkcatalog executed on master db of Yukon got the
> following error .
> Msg 5030, Level 16, State 12, Line 1
> The database could not be exclusively locked to perform the operation.
> Msg 7926, Level 16, State 1, Line 1
> Check statement aborted. The database could not be prepared for checking.
> Re-run the command with the database in single-user or read-only mode. See
> previous errors for more details.
>
>
>|||Thanks
am using 9.00.852
so is it like it won't work only if I give Dbcc checkdb
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Which build are you using? On the Dec CTP release (9.00.0981)
> dbcc checkdb('master')
> dbcc checkcatalog('master')
> work just fine for me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
checking.[vbcol=seagreen]
See[vbcol=seagreen]
>|||No it does work if you are in master and do not specify the database name as
well, running:
dbcc checkdb
dbcc checkcatalog
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> rights.
> checking.
> See
>|||Do you have these databases on a FAT file system? Checkdb in Yukon uses
database snapshots to allow online checks. If the database is on a
filesystem other than NTFS, or there is no space on the drive for a
snapshot, then online checks cannot be done and another mechanism for
achieving transactional consistency is required instead. You should have
seen some other errors which will have explained. This behavior is fully
documented in Beta 3 BOL.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> rights.
the[vbcol=seagreen]
> checking.
> See
>
dbcc checkdb in Yukon
Hi ,
When dbcc checkdb or checkcatalog executed on master db of Yukon got the
following error .
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be prepared for checking.
Re-run the command with the database in single-user or read-only mode. See
previous errors for more details.Which build are you using? On the Dec CTP release (9.00.0981)
dbcc checkdb('master')
dbcc checkcatalog('master')
work just fine for me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi ,
> When dbcc checkdb or checkcatalog executed on master db of Yukon got the
> following error .
> Msg 5030, Level 16, State 12, Line 1
> The database could not be exclusively locked to perform the operation.
> Msg 7926, Level 16, State 1, Line 1
> Check statement aborted. The database could not be prepared for checking.
> Re-run the command with the database in single-user or read-only mode. See
> previous errors for more details.
>
>
>|||Thanks
am using 9.00.852
so is it like it won't work only if I give Dbcc checkdb
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Which build are you using? On the Dec CTP release (9.00.0981)
> dbcc checkdb('master')
> dbcc checkcatalog('master')
> work just fine for me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> > Hi ,
> > When dbcc checkdb or checkcatalog executed on master db of Yukon got the
> > following error .
> >
> > Msg 5030, Level 16, State 12, Line 1
> >
> > The database could not be exclusively locked to perform the operation.
> >
> > Msg 7926, Level 16, State 1, Line 1
> >
> > Check statement aborted. The database could not be prepared for
checking.
> > Re-run the command with the database in single-user or read-only mode.
See
> > previous errors for more details.
> >
> >
> >
> >
> >
> >
>|||No it does work if you are in master and do not specify the database name as
well, running:
dbcc checkdb
dbcc checkcatalog
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
>> Which build are you using? On the Dec CTP release (9.00.0981)
>> dbcc checkdb('master')
>> dbcc checkcatalog('master')
>> work just fine for me.
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
>> > Hi ,
>> > When dbcc checkdb or checkcatalog executed on master db of Yukon got
>> > the
>> > following error .
>> >
>> > Msg 5030, Level 16, State 12, Line 1
>> >
>> > The database could not be exclusively locked to perform the operation.
>> >
>> > Msg 7926, Level 16, State 1, Line 1
>> >
>> > Check statement aborted. The database could not be prepared for
> checking.
>> > Re-run the command with the database in single-user or read-only mode.
> See
>> > previous errors for more details.
>> >
>> >
>> >
>> >
>> >
>> >
>>
>|||Do you have these databases on a FAT file system? Checkdb in Yukon uses
database snapshots to allow online checks. If the database is on a
filesystem other than NTFS, or there is no space on the drive for a
snapshot, then online checks cannot be done and another mechanism for
achieving transactional consistency is required instead. You should have
seen some other errors which will have explained. This behavior is fully
documented in Beta 3 BOL.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> > Which build are you using? On the Dec CTP release (9.00.0981)
> >
> > dbcc checkdb('master')
> > dbcc checkcatalog('master')
> >
> > work just fine for me.
> >
> > GertD@.SQLDev.Net
> >
> > Please reply only to the newsgroups.
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > You assume all risk for your use.
> > Copyright © SQLDev.Net 1991-2005 All rights reserved.
> >
> > "Aju" <ajuonline@.yahoo.com> wrote in message
> > news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> > > Hi ,
> > > When dbcc checkdb or checkcatalog executed on master db of Yukon got
the
> > > following error .
> > >
> > > Msg 5030, Level 16, State 12, Line 1
> > >
> > > The database could not be exclusively locked to perform the operation.
> > >
> > > Msg 7926, Level 16, State 1, Line 1
> > >
> > > Check statement aborted. The database could not be prepared for
> checking.
> > > Re-run the command with the database in single-user or read-only mode.
> See
> > > previous errors for more details.
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
When dbcc checkdb or checkcatalog executed on master db of Yukon got the
following error .
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be prepared for checking.
Re-run the command with the database in single-user or read-only mode. See
previous errors for more details.Which build are you using? On the Dec CTP release (9.00.0981)
dbcc checkdb('master')
dbcc checkcatalog('master')
work just fine for me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi ,
> When dbcc checkdb or checkcatalog executed on master db of Yukon got the
> following error .
> Msg 5030, Level 16, State 12, Line 1
> The database could not be exclusively locked to perform the operation.
> Msg 7926, Level 16, State 1, Line 1
> Check statement aborted. The database could not be prepared for checking.
> Re-run the command with the database in single-user or read-only mode. See
> previous errors for more details.
>
>
>|||Thanks
am using 9.00.852
so is it like it won't work only if I give Dbcc checkdb
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Which build are you using? On the Dec CTP release (9.00.0981)
> dbcc checkdb('master')
> dbcc checkcatalog('master')
> work just fine for me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> > Hi ,
> > When dbcc checkdb or checkcatalog executed on master db of Yukon got the
> > following error .
> >
> > Msg 5030, Level 16, State 12, Line 1
> >
> > The database could not be exclusively locked to perform the operation.
> >
> > Msg 7926, Level 16, State 1, Line 1
> >
> > Check statement aborted. The database could not be prepared for
checking.
> > Re-run the command with the database in single-user or read-only mode.
See
> > previous errors for more details.
> >
> >
> >
> >
> >
> >
>|||No it does work if you are in master and do not specify the database name as
well, running:
dbcc checkdb
dbcc checkcatalog
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
>> Which build are you using? On the Dec CTP release (9.00.0981)
>> dbcc checkdb('master')
>> dbcc checkcatalog('master')
>> work just fine for me.
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
>> > Hi ,
>> > When dbcc checkdb or checkcatalog executed on master db of Yukon got
>> > the
>> > following error .
>> >
>> > Msg 5030, Level 16, State 12, Line 1
>> >
>> > The database could not be exclusively locked to perform the operation.
>> >
>> > Msg 7926, Level 16, State 1, Line 1
>> >
>> > Check statement aborted. The database could not be prepared for
> checking.
>> > Re-run the command with the database in single-user or read-only mode.
> See
>> > previous errors for more details.
>> >
>> >
>> >
>> >
>> >
>> >
>>
>|||Do you have these databases on a FAT file system? Checkdb in Yukon uses
database snapshots to allow online checks. If the database is on a
filesystem other than NTFS, or there is no space on the drive for a
snapshot, then online checks cannot be done and another mechanism for
achieving transactional consistency is required instead. You should have
seen some other errors which will have explained. This behavior is fully
documented in Beta 3 BOL.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eBZ7WQtAFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Thanks
> am using 9.00.852
> so is it like it won't work only if I give Dbcc checkdb
>
> ARR
>
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:ucEiZ4sAFHA.1564@.TK2MSFTNGP09.phx.gbl...
> > Which build are you using? On the Dec CTP release (9.00.0981)
> >
> > dbcc checkdb('master')
> > dbcc checkcatalog('master')
> >
> > work just fine for me.
> >
> > GertD@.SQLDev.Net
> >
> > Please reply only to the newsgroups.
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > You assume all risk for your use.
> > Copyright © SQLDev.Net 1991-2005 All rights reserved.
> >
> > "Aju" <ajuonline@.yahoo.com> wrote in message
> > news:%23$IhL1sAFHA.1396@.tk2msftngp13.phx.gbl...
> > > Hi ,
> > > When dbcc checkdb or checkcatalog executed on master db of Yukon got
the
> > > following error .
> > >
> > > Msg 5030, Level 16, State 12, Line 1
> > >
> > > The database could not be exclusively locked to perform the operation.
> > >
> > > Msg 7926, Level 16, State 1, Line 1
> > >
> > > Check statement aborted. The database could not be prepared for
> checking.
> > > Re-run the command with the database in single-user or read-only mode.
> See
> > > previous errors for more details.
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)