|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Rebuild indexeshttp://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt Here in procedure Kimberly created the following table: CREATE TABLE #ShowContigOutput ( ObjectName sysname, ObjectId int, IndexName sysname, IndexId tinyint, [Level] tinyint, Pages int, [Rows] bigint, MinimumRecordSize smallint, MaximumRecordSize smallint, AverageRecordSize smallint, ForwardedRecords bigint, Extents int, ExtentSwitches numeric(10,2), AverageFreeBytes numeric(10,2), AveragePageDensity numeric(10,2), ScanDensity numeric(10,2), BestCount int, ActualCount int, LogicalFragmentation numeric(10,2), ExtentFragmentation numeric(10,2) ) Then fill in the data: INSERT #ShowContigOutput EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS') I get the following error: Cannot insert the value NULL into column 'ExtentFragmentation', table 'tempdb.dbo.#ShowContigOutput___________________________________________________________________________________________________00000000056A'; column does not allow nulls. INSERT fails. DBCC execution completed. If DBCC printed error messages, contact your system administrator. The statement has been terminated. The problem is, that table #ShowContigOutput doesn't allow nulls on any column, and EXEC statement return a lot of nulls in almost all columns. How can I repair that? regards,Simon So, you can change it to accept NULLS , can't you?
Show quote "simonZ" <simon.zu***@studio-moderna.com> wrote in message news:eE1PL$FvGHA.4756@TK2MSFTNGP02.phx.gbl... > I'm using this procedure to update all indexes in my database: > > http://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt > > Here in procedure Kimberly created the following table: > > CREATE TABLE #ShowContigOutput > ( > ObjectName sysname, > ObjectId int, > IndexName sysname, > IndexId tinyint, > [Level] tinyint, > Pages int, > [Rows] bigint, > MinimumRecordSize smallint, > MaximumRecordSize smallint, > AverageRecordSize smallint, > ForwardedRecords bigint, > Extents int, > ExtentSwitches numeric(10,2), > AverageFreeBytes numeric(10,2), > AveragePageDensity numeric(10,2), > ScanDensity numeric(10,2), > BestCount int, > ActualCount int, > LogicalFragmentation numeric(10,2), > ExtentFragmentation numeric(10,2) > ) > > Then fill in the data: > INSERT #ShowContigOutput > EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS') > > I get the following error: > Cannot insert the value NULL into column 'ExtentFragmentation', table > 'tempdb.dbo.#ShowContigOutput___________________________________________________________________________________________________00000000056A'; > column does not allow nulls. INSERT fails. > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > The statement has been terminated. > > The problem is, that table #ShowContigOutput doesn't allow nulls on any > column, and EXEC statement return a lot of nulls in almost all columns. > > How can I repair that? > > regards,Simon > Hi,
yes, I can. But would then index rebuilding work? I don't know why Kimberly created table without NULLS. She is expert for indexes. If all the columns are usually not null, why than I get null values? Do you get any nulls in the resulting columns if you execute the following statement: EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS') Thank you, Simon Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:uEoazQGvGHA.416@TK2MSFTNGP04.phx.gbl... > So, you can change it to accept NULLS , can't you? > > "simonZ" <simon.zu***@studio-moderna.com> wrote in message > news:eE1PL$FvGHA.4756@TK2MSFTNGP02.phx.gbl... >> I'm using this procedure to update all indexes in my database: >> >> http://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt >> >> Here in procedure Kimberly created the following table: >> >> CREATE TABLE #ShowContigOutput >> ( >> ObjectName sysname, >> ObjectId int, >> IndexName sysname, >> IndexId tinyint, >> [Level] tinyint, >> Pages int, >> [Rows] bigint, >> MinimumRecordSize smallint, >> MaximumRecordSize smallint, >> AverageRecordSize smallint, >> ForwardedRecords bigint, >> Extents int, >> ExtentSwitches numeric(10,2), >> AverageFreeBytes numeric(10,2), >> AveragePageDensity numeric(10,2), >> ScanDensity numeric(10,2), >> BestCount int, >> ActualCount int, >> LogicalFragmentation numeric(10,2), >> ExtentFragmentation numeric(10,2) >> ) >> >> Then fill in the data: >> INSERT #ShowContigOutput >> EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS') >> >> I get the following error: >> Cannot insert the value NULL into column 'ExtentFragmentation', table >> 'tempdb.dbo.#ShowContigOutput___________________________________________________________________________________________________00000000056A'; >> column does not allow nulls. INSERT fails. >> DBCC execution completed. If DBCC printed error messages, contact your >> system administrator. >> The statement has been terminated. >> >> The problem is, that table #ShowContigOutput doesn't allow nulls on any >> column, and EXEC statement return a lot of nulls in almost all columns. >> >> How can I repair that? >> >> regards,Simon >> > > simon
DBCC SHOWCONTIG does not rebuild indexes. It shows how fragmented is your database Use DBCC DBREINDEX command Show quote "simonZ" <simon.zu***@studio-moderna.com> wrote in message news:eaCV%23pGvGHA.1888@TK2MSFTNGP03.phx.gbl... > Hi, > > yes, I can. But would then index rebuilding work? > > I don't know why Kimberly created table without NULLS. She is expert for > indexes. > > If all the columns are usually not null, why than I get null values? > > Do you get any nulls in the resulting columns if you execute the following > statement: > EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS') > > Thank you, > Simon > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:uEoazQGvGHA.416@TK2MSFTNGP04.phx.gbl... >> So, you can change it to accept NULLS , can't you? >> >> "simonZ" <simon.zu***@studio-moderna.com> wrote in message >> news:eE1PL$FvGHA.4756@TK2MSFTNGP02.phx.gbl... >>> I'm using this procedure to update all indexes in my database: >>> >>> http://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt >>> >>> Here in procedure Kimberly created the following table: >>> >>> CREATE TABLE #ShowContigOutput >>> ( >>> ObjectName sysname, >>> ObjectId int, >>> IndexName sysname, >>> IndexId tinyint, >>> [Level] tinyint, >>> Pages int, >>> [Rows] bigint, >>> MinimumRecordSize smallint, >>> MaximumRecordSize smallint, >>> AverageRecordSize smallint, >>> ForwardedRecords bigint, >>> Extents int, >>> ExtentSwitches numeric(10,2), >>> AverageFreeBytes numeric(10,2), >>> AveragePageDensity numeric(10,2), >>> ScanDensity numeric(10,2), >>> BestCount int, >>> ActualCount int, >>> LogicalFragmentation numeric(10,2), >>> ExtentFragmentation numeric(10,2) >>> ) >>> >>> Then fill in the data: >>> INSERT #ShowContigOutput >>> EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS') >>> >>> I get the following error: >>> Cannot insert the value NULL into column 'ExtentFragmentation', table >>> 'tempdb.dbo.#ShowContigOutput___________________________________________________________________________________________________00000000056A'; >>> column does not allow nulls. INSERT fails. >>> DBCC execution completed. If DBCC printed error messages, contact your >>> system administrator. >>> The statement has been terminated. >>> >>> The problem is, that table #ShowContigOutput doesn't allow nulls on any >>> column, and EXEC statement return a lot of nulls in almost all columns. >>> >>> How can I repair that? >>> >>> regards,Simon >>> >> >> > > simonZ wrote:
Show quote > I'm using this procedure to update all indexes in my database: If you're on SQL 2000, try this instead:> > http://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt > > Here in procedure Kimberly created the following table: > > CREATE TABLE #ShowContigOutput > ( > ObjectName sysname, > ObjectId int, > IndexName sysname, > IndexId tinyint, > [Level] tinyint, > Pages int, > [Rows] bigint, > MinimumRecordSize smallint, > MaximumRecordSize smallint, > AverageRecordSize smallint, > ForwardedRecords bigint, > Extents int, > ExtentSwitches numeric(10,2), > AverageFreeBytes numeric(10,2), > AveragePageDensity numeric(10,2), > ScanDensity numeric(10,2), > BestCount int, > ActualCount int, > LogicalFragmentation numeric(10,2), > ExtentFragmentation numeric(10,2) > ) > > Then fill in the data: > INSERT #ShowContigOutput > EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS') > > I get the following error: > Cannot insert the value NULL into column 'ExtentFragmentation', table > 'tempdb.dbo.#ShowContigOutput___________________________________________________________________________________________________00000000056A'; > column does not allow nulls. INSERT fails. > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > The statement has been terminated. > > The problem is, that table #ShowContigOutput doesn't allow nulls on any > column, and EXEC statement return a lot of nulls in almost all columns. > > How can I repair that? > > regards,Simon > > http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded I haven't yet updated it to use the new ALTER INDEX command in SQL 2005 Thank you Tracey.
If I execute your procedure I get the following errors: Server: Msg 515, Level 16, State 2, Procedure procAutoReindex, Line 87 Cannot insert the value NULL into column 'ExtentFrag', table 'tempdb.dbo.#fraglist___________________________________________________________________________________________________________00010000010C'; column does not allow nulls. INSERT fails. The statement has been terminated. Server: Msg 515, Level 16, State 2, Procedure procAutoReindex, Line 87 Cannot insert the value NULL into column 'ExtentFrag', table 'tempdb.dbo.#fraglist___________________________________________________________________________________________________________00010000010C'; column does not allow nulls. INSERT fails. The statement has been terminated. and so on... So, similar like Kimberly procedure. Will it still work okay if I change column ExtentFrag and other columns to allow nulls? Regards,Simon Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:%23RF9ptHvGHA.4512@TK2MSFTNGP05.phx.gbl... > simonZ wrote: >> I'm using this procedure to update all indexes in my database: >> >> http://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt >> >> Here in procedure Kimberly created the following table: >> >> CREATE TABLE #ShowContigOutput >> ( >> ObjectName sysname, >> ObjectId int, >> IndexName sysname, >> IndexId tinyint, >> [Level] tinyint, >> Pages int, >> [Rows] bigint, >> MinimumRecordSize smallint, >> MaximumRecordSize smallint, >> AverageRecordSize smallint, >> ForwardedRecords bigint, >> Extents int, >> ExtentSwitches numeric(10,2), >> AverageFreeBytes numeric(10,2), >> AveragePageDensity numeric(10,2), >> ScanDensity numeric(10,2), >> BestCount int, >> ActualCount int, >> LogicalFragmentation numeric(10,2), >> ExtentFragmentation numeric(10,2) >> ) >> >> Then fill in the data: >> INSERT #ShowContigOutput >> EXEC('DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS') >> >> I get the following error: >> Cannot insert the value NULL into column 'ExtentFragmentation', table >> 'tempdb.dbo.#ShowContigOutput___________________________________________________________________________________________________00000000056A'; >> column does not allow nulls. INSERT fails. >> DBCC execution completed. If DBCC printed error messages, contact your >> system administrator. >> The statement has been terminated. >> >> The problem is, that table #ShowContigOutput doesn't allow nulls on any >> column, and EXEC statement return a lot of nulls in almost all columns. >> >> How can I repair that? >> >> regards,Simon > > If you're on SQL 2000, try this instead: > http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded > > I haven't yet updated it to use the new ALTER INDEX command in SQL 2005 > > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com  |
|||||||||||||||||||||||
Other interesting topics