|
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 quoteHide 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 quoteHide 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 quoteHide 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 quoteHide 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 quoteHide 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
Optimize this query
How can I insert a new field between existing field? Fractional truncation of Input Paramater in a UDF Is explicit ordering from subquery propagated upwards? Deleting Different Records at different Parts of the day Stored Procedure IN statement Change rows into columns SQL 2000 ODBC Timeout Errors exec master..xp_cmdshell 'net use ' resultis NULL?? Select IIF Exists |
|||||||||||||||||||||||
