|
|
You're in the
sql server dts
group:DBCC DBREINDEX
sql server dts:
Can anyone confirm this for me. If I were to run this script below. Will it
reindex all tables indexes at one time as it goes through the cursor. Or will it reindex one table indexes at a time as it goes through the cursor. Basically will it pause each time the DBCC command is executed? If the first part is true where it executes all at once then what is a good way to only do one at a time? Can you pause between executions till each execution is done? THanks for the help. DECLARE @TableName varchar(255) DECLARE ReindexTableCursor CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR name = 'ID_Master') ORDER BY name OPEN ReindexTableCursor -- Perform the first fetch. FETCH NEXT FROM ReindexTableCursor INTO @TableName -- Check @@FETCH_STATUS to see if there are any rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. PRINT 'Reindexing' + ' ' + @TableName DBCC DBREINDEX (@TableName ) FETCH NEXT FROM ReindexTableCursor into @TableName END CLOSE ReindexTableCursor DEALLOCATE ReindexTableCursor
OK Thanks for the information. I will use INFORMATION_SCHEMA.TABLES. I do
understand if I only specify the table name it will reindex all indexes in table thius is what I am after. Although what I was wondering is if I use this script and my query returns 15 table names lets say will it execute the dbcc command on all 15 tables at the same time or will it execute the dbcc command one at a time as it sequencially goes through the cursor. I only want to reindex one table and all it's indexes at a time. Can you tell me if this is true? [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:eAa0NdpbEHA.1004@TK2MSFTNGP11.phx.gbl... > Dave, > > If you execute DBCC DBREINDEX with only the table specified it will rebuild > ALL indexes on that table. This happens all in one big transaction for each > table. You can specify a single index to be rebuilt but as you have it here > it will rebuild all of them on each table in the cursor. You can pause in > between each table by placing a WAITFOR DELAY after the DBREINDEX command. > You really should look at using one of the information schema views as the > source of the cursor instead of the system tables directly. > INFORMATION_SCHEMA.TABLES > > > > -- > Andrew J. Kelly SQL MVP > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > > Can anyone confirm this for me. If I were to run this script below. Will > it > > reindex all tables indexes at one time as it goes through the cursor. Or > > will it reindex one table indexes at a time as it goes through the cursor. > > Basically will it pause each time the DBCC command is executed? > > If the first part is true where it executes all at once then what is a > good > > way to only do one at a time? Can you pause between executions till each > > execution is done? > > > > THanks for the help. > > > > DECLARE @TableName varchar(255) > > DECLARE ReindexTableCursor CURSOR FOR > > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR > name > > = 'ID_Master') > > ORDER BY name > > > > OPEN ReindexTableCursor > > > > -- Perform the first fetch. > > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > > WHILE @@FETCH_STATUS = 0 > > BEGIN > > -- This is executed as long as the previous fetch succeeds. > > PRINT 'Reindexing' + ' ' + @TableName > > DBCC DBREINDEX (@TableName ) > > FETCH NEXT FROM ReindexTableCursor into @TableName > > END > > > > CLOSE ReindexTableCursor > > DEALLOCATE ReindexTableCursor > > > > > >
This is true. What I was trying to get at was in a query analyzer window it
takes only a few seconds to query and return all my tables. Will the cursor also loop through all the tables in a few seconds and issue multiple dbcc commands all at once. Or will it loop once issue the dbcc dbreindex command wait for it to finish then loop again to the next and so on this way it wont issue multiple commands all at once? This is what I was wondering if I needed to pause in between loops. THanks Andrew. [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:OD7KOCqbEHA.644@tk2msftngp13.phx.gbl... > Dave, > > The whole point of a Cursor is to loop thru the result set one row at a > time. The cursor will only return 1 table name with each loop and since > your specifying the table name via the variable that gets filled in thru the > cursor, DBCC DBREINDEX will only work on that individual table at that time. > As a matter of fact there is no way to reindex all tables or indexes all at > once with one command. Hope that helps. > > -- > Andrew J. Kelly SQL MVP > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > news:%23N3F7tpbEHA.3016@tk2msftngp13.phx.gbl... > > OK Thanks for the information. I will use INFORMATION_SCHEMA.TABLES. I do > > understand if I only specify the table name it will reindex all indexes in > > table thius is what I am after. Although what I was wondering is if I use > > this script and my query returns 15 table names lets say will it execute > the > > dbcc command on all 15 tables at the same time or will it execute the dbcc > > command one at a time as it sequencially goes through the cursor. I only > > want to reindex one table and all it's indexes at a time. Can you tell me > if > > this is true? > > > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > > news:eAa0NdpbEHA.1004@TK2MSFTNGP11.phx.gbl... > > > Dave, > > > > > > If you execute DBCC DBREINDEX with only the table specified it will > > rebuild > > > ALL indexes on that table. This happens all in one big transaction for > > each > > > table. You can specify a single index to be rebuilt but as you have it > > here > > > it will rebuild all of them on each table in the cursor. You can pause > in > > > between each table by placing a WAITFOR DELAY after the DBREINDEX > command. > > > You really should look at using one of the information schema views as > the > > > source of the cursor instead of the system tables directly. > > > INFORMATION_SCHEMA.TABLES > > > > > > > > > > > > -- > > > Andrew J. Kelly SQL MVP > > > > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > > news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > > > > Can anyone confirm this for me. If I were to run this script below. > Will > > > it > > > > reindex all tables indexes at one time as it goes through the cursor. > Or > > > > will it reindex one table indexes at a time as it goes through the > > cursor. > > > > Basically will it pause each time the DBCC command is executed? > > > > If the first part is true where it executes all at once then what is a > > > good > > > > way to only do one at a time? Can you pause between executions till > each > > > > execution is done? > > > > > > > > THanks for the help. > > > > > > > > DECLARE @TableName varchar(255) > > > > DECLARE ReindexTableCursor CURSOR FOR > > > > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' > OR > > > name > > > > = 'ID_Master') > > > > ORDER BY name > > > > > > > > OPEN ReindexTableCursor > > > > > > > > -- Perform the first fetch. > > > > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > > > > > > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > > > > WHILE @@FETCH_STATUS = 0 > > > > BEGIN > > > > -- This is executed as long as the previous fetch succeeds. > > > > PRINT 'Reindexing' + ' ' + @TableName > > > > DBCC DBREINDEX (@TableName ) > > > > FETCH NEXT FROM ReindexTableCursor into @TableName > > > > END > > > > > > > > CLOSE ReindexTableCursor > > > > DEALLOCATE ReindexTableCursor > > > > > > > > > > > > > > > > > > > >
Dave,
If you execute DBCC DBREINDEX with only the table specified it will rebuild ALL indexes on that table. This happens all in one big transaction for each table. You can specify a single index to be rebuilt but as you have it here it will rebuild all of them on each table in the cursor. You can pause in between each table by placing a WAITFOR DELAY after the DBREINDEX command. You really should look at using one of the information schema views as the source of the cursor instead of the system tables directly. INFORMATION_SCHEMA.TABLES -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Dave Mortenson" <dmortenson@dentrix.com> wrote in message news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > Can anyone confirm this for me. If I were to run this script below. Will it > reindex all tables indexes at one time as it goes through the cursor. Or > will it reindex one table indexes at a time as it goes through the cursor. > Basically will it pause each time the DBCC command is executed? > If the first part is true where it executes all at once then what is a good > way to only do one at a time? Can you pause between executions till each > execution is done? > > THanks for the help. > > DECLARE @TableName varchar(255) > DECLARE ReindexTableCursor CURSOR FOR > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR name > = 'ID_Master') > ORDER BY name > > OPEN ReindexTableCursor > > -- Perform the first fetch. > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > WHILE @@FETCH_STATUS = 0 > BEGIN > -- This is executed as long as the previous fetch succeeds. > PRINT 'Reindexing' + ' ' + @TableName > DBCC DBREINDEX (@TableName ) > FETCH NEXT FROM ReindexTableCursor into @TableName > END > > CLOSE ReindexTableCursor > DEALLOCATE ReindexTableCursor > >
Dave,
The whole point of a Cursor is to loop thru the result set one row at a time. The cursor will only return 1 table name with each loop and since your specifying the table name via the variable that gets filled in thru the cursor, DBCC DBREINDEX will only work on that individual table at that time. As a matter of fact there is no way to reindex all tables or indexes all at once with one command. Hope that helps. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Dave Mortenson" <dmortenson@dentrix.com> wrote in message news:%23N3F7tpbEHA.3016@tk2msftngp13.phx.gbl... > OK Thanks for the information. I will use INFORMATION_SCHEMA.TABLES. I do > understand if I only specify the table name it will reindex all indexes in > table thius is what I am after. Although what I was wondering is if I use > this script and my query returns 15 table names lets say will it execute the > dbcc command on all 15 tables at the same time or will it execute the dbcc > command one at a time as it sequencially goes through the cursor. I only > want to reindex one table and all it's indexes at a time. Can you tell me if > this is true? > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:eAa0NdpbEHA.1004@TK2MSFTNGP11.phx.gbl... > > Dave, > > > > If you execute DBCC DBREINDEX with only the table specified it will > rebuild > > ALL indexes on that table. This happens all in one big transaction for > each > > table. You can specify a single index to be rebuilt but as you have it > here > > it will rebuild all of them on each table in the cursor. You can pause in > > between each table by placing a WAITFOR DELAY after the DBREINDEX command. > > You really should look at using one of the information schema views as the > > source of the cursor instead of the system tables directly. > > INFORMATION_SCHEMA.TABLES > > > > > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > > > Can anyone confirm this for me. If I were to run this script below. Will > > it > > > reindex all tables indexes at one time as it goes through the cursor. Or > > > will it reindex one table indexes at a time as it goes through the > cursor. > > > Basically will it pause each time the DBCC command is executed? > > > If the first part is true where it executes all at once then what is a > > good > > > way to only do one at a time? Can you pause between executions till each > > > execution is done? > > > > > > THanks for the help. > > > > > > DECLARE @TableName varchar(255) > > > DECLARE ReindexTableCursor CURSOR FOR > > > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR > > name > > > = 'ID_Master') > > > ORDER BY name > > > > > > OPEN ReindexTableCursor > > > > > > -- Perform the first fetch. > > > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > > > > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > > > WHILE @@FETCH_STATUS = 0 > > > BEGIN > > > -- This is executed as long as the previous fetch succeeds. > > > PRINT 'Reindexing' + ' ' + @TableName > > > DBCC DBREINDEX (@TableName ) > > > FETCH NEXT FROM ReindexTableCursor into @TableName > > > END > > > > > > CLOSE ReindexTableCursor > > > DEALLOCATE ReindexTableCursor > > > > > > > > > > > >
OK, I see where you are going now. TSQL is a procedural language and can
not by itself run multiple paths or commands per say. Before the next command can be executed the one before it must finish completely. This includes commands inside a cursor. The next fetch operation will not occur before the previous DBCC command is completed. So in that regard you don't need a WAITFOR. Sometimes people want the system to take a breather in between tables since this can be a very intensive operation. Now this does not mean multiple users can not be doing stuff at the same time. Each user has it's own threads that run independently of each other. But within a single batch of TSQL it is essentially procedural. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Dave Mortenson" <dmortenson@dentrix.com> wrote in message news:%23Io2qeqbEHA.1408@TK2MSFTNGP12.phx.gbl... > This is true. What I was trying to get at was in a query analyzer window it > takes only a few seconds to query and return all my tables. Will the cursor > also loop through all the tables in a few seconds and issue multiple dbcc > commands all at once. Or will it loop once issue the dbcc dbreindex command > wait for it to finish then loop again to the next and so on this way it wont > issue multiple commands all at once? This is what I was wondering if I > needed to pause in between loops. > > THanks Andrew. > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:OD7KOCqbEHA.644@tk2msftngp13.phx.gbl... > > Dave, > > > > The whole point of a Cursor is to loop thru the result set one row at a > > time. The cursor will only return 1 table name with each loop and since > > your specifying the table name via the variable that gets filled in thru > the > > cursor, DBCC DBREINDEX will only work on that individual table at that > time. > > As a matter of fact there is no way to reindex all tables or indexes all > at > > once with one command. Hope that helps. > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > news:%23N3F7tpbEHA.3016@tk2msftngp13.phx.gbl... > > > OK Thanks for the information. I will use INFORMATION_SCHEMA.TABLES. I > do > > > understand if I only specify the table name it will reindex all indexes > in > > > table thius is what I am after. Although what I was wondering is if I > use > > > this script and my query returns 15 table names lets say will it execute > > the > > > dbcc command on all 15 tables at the same time or will it execute the > dbcc > > > command one at a time as it sequencially goes through the cursor. I only > > > want to reindex one table and all it's indexes at a time. Can you tell > me > > if > > > this is true? > > > > > > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > > > news:eAa0NdpbEHA.1004@TK2MSFTNGP11.phx.gbl... > > > > Dave, > > > > > > > > If you execute DBCC DBREINDEX with only the table specified it will > > > rebuild > > > > ALL indexes on that table. This happens all in one big transaction for > > > each > > > > table. You can specify a single index to be rebuilt but as you have > it > > > here > > > > it will rebuild all of them on each table in the cursor. You can > pause > > in > > > > between each table by placing a WAITFOR DELAY after the DBREINDEX > > command. > > > > You really should look at using one of the information schema views as > > the > > > > source of the cursor instead of the system tables directly. > > > > INFORMATION_SCHEMA.TABLES > > > > > > > > > > > > > > > > -- > > > > Andrew J. Kelly SQL MVP > > > > > > > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > > > news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > > > > > Can anyone confirm this for me. If I were to run this script below. > > Will > > > > it > > > > > reindex all tables indexes at one time as it goes through the > cursor. > > Or > > > > > will it reindex one table indexes at a time as it goes through the > > > cursor. > > > > > Basically will it pause each time the DBCC command is executed? > > > > > If the first part is true where it executes all at once then what is > a > > > > good > > > > > way to only do one at a time? Can you pause between executions till > > each > > > > > execution is done? > > > > > > > > > > THanks for the help. > > > > > > > > > > DECLARE @TableName varchar(255) > > > > > DECLARE ReindexTableCursor CURSOR FOR > > > > > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' > > OR > > > > name > > > > > = 'ID_Master') > > > > > ORDER BY name > > > > > > > > > > OPEN ReindexTableCursor > > > > > > > > > > -- Perform the first fetch. > > > > > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > > > > > > > > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > > > > > WHILE @@FETCH_STATUS = 0 > > > > > BEGIN > > > > > -- This is executed as long as the previous fetch succeeds. > > > > > PRINT 'Reindexing' + ' ' + @TableName > > > > > DBCC DBREINDEX (@TableName ) > > > > > FETCH NEXT FROM ReindexTableCursor into @TableName > > > > > END > > > > > > > > > > CLOSE ReindexTableCursor > > > > > DEALLOCATE ReindexTableCursor > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
You could get rid of your cursor by using sp_MSforeachtable
See notes below from web site re: undocumented SP's: http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm sp_MSforeachtable Sometimes, you need to perform the same actions for all tables in the database. You can create cursor for this purpose, or you can also use the sp_MSforeachtable stored procedure to accomplish the same goal with less work. For example, you can use the sp_MSforeachtable stored procedure to rebuild all the indexes in a database: EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')" [quoted text, click to view] "Dave Mortenson" <dmortenson@dentrix.com> wrote in message news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > Can anyone confirm this for me. If I were to run this script below. Will it > reindex all tables indexes at one time as it goes through the cursor. Or > will it reindex one table indexes at a time as it goes through the cursor. > Basically will it pause each time the DBCC command is executed? > If the first part is true where it executes all at once then what is a good > way to only do one at a time? Can you pause between executions till each > execution is done? > > THanks for the help. > > DECLARE @TableName varchar(255) > DECLARE ReindexTableCursor CURSOR FOR > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR name > = 'ID_Master') > ORDER BY name > > OPEN ReindexTableCursor > > -- Perform the first fetch. > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > WHILE @@FETCH_STATUS = 0 > BEGIN > -- This is executed as long as the previous fetch succeeds. > PRINT 'Reindexing' + ' ' + @TableName > DBCC DBREINDEX (@TableName ) > FETCH NEXT FROM ReindexTableCursor into @TableName > END > > CLOSE ReindexTableCursor > DEALLOCATE ReindexTableCursor > >
Andrew Excellent that is what I wanted to know. Thank you so much. Do you
know of a way to make it print as it completes each execution rather than all at the end? I do use the print command but like I said it will only print all at the end. THanks again I really appreciate the info. Dave [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:ORFRinsbEHA.636@TK2MSFTNGP12.phx.gbl... > OK, I see where you are going now. TSQL is a procedural language and can > not by itself run multiple paths or commands per say. Before the next > command can be executed the one before it must finish completely. This > includes commands inside a cursor. The next fetch operation will not occur > before the previous DBCC command is completed. So in that regard you don't > need a WAITFOR. Sometimes people want the system to take a breather in > between tables since this can be a very intensive operation. > Now this does not mean multiple users can not be doing stuff at the same > time. Each user has it's own threads that run independently of each other. > But within a single batch of TSQL it is essentially procedural. > > > -- > Andrew J. Kelly SQL MVP > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > news:%23Io2qeqbEHA.1408@TK2MSFTNGP12.phx.gbl... > > This is true. What I was trying to get at was in a query analyzer window > it > > takes only a few seconds to query and return all my tables. Will the > cursor > > also loop through all the tables in a few seconds and issue multiple dbcc > > commands all at once. Or will it loop once issue the dbcc dbreindex > command > > wait for it to finish then loop again to the next and so on this way it > wont > > issue multiple commands all at once? This is what I was wondering if I > > needed to pause in between loops. > > > > THanks Andrew. > > > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > > news:OD7KOCqbEHA.644@tk2msftngp13.phx.gbl... > > > Dave, > > > > > > The whole point of a Cursor is to loop thru the result set one row at a > > > time. The cursor will only return 1 table name with each loop and since > > > your specifying the table name via the variable that gets filled in thru > > the > > > cursor, DBCC DBREINDEX will only work on that individual table at that > > time. > > > As a matter of fact there is no way to reindex all tables or indexes all > > at > > > once with one command. Hope that helps. > > > > > > -- > > > Andrew J. Kelly SQL MVP > > > > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > > news:%23N3F7tpbEHA.3016@tk2msftngp13.phx.gbl... > > > > OK Thanks for the information. I will use INFORMATION_SCHEMA.TABLES. I > > do > > > > understand if I only specify the table name it will reindex all > indexes > > in > > > > table thius is what I am after. Although what I was wondering is if I > > use > > > > this script and my query returns 15 table names lets say will it > execute > > > the > > > > dbcc command on all 15 tables at the same time or will it execute the > > dbcc > > > > command one at a time as it sequencially goes through the cursor. I > only > > > > want to reindex one table and all it's indexes at a time. Can you tell > > me > > > if > > > > this is true? > > > > > > > > > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > > > > news:eAa0NdpbEHA.1004@TK2MSFTNGP11.phx.gbl... > > > > > Dave, > > > > > > > > > > If you execute DBCC DBREINDEX with only the table specified it will > > > > rebuild > > > > > ALL indexes on that table. This happens all in one big transaction > for > > > > each > > > > > table. You can specify a single index to be rebuilt but as you have > > it > > > > here > > > > > it will rebuild all of them on each table in the cursor. You can > > pause > > > in > > > > > between each table by placing a WAITFOR DELAY after the DBREINDEX > > > command. > > > > > You really should look at using one of the information schema views > as > > > the > > > > > source of the cursor instead of the system tables directly. > > > > > INFORMATION_SCHEMA.TABLES > > > > > > > > > > > > > > > > > > > > -- > > > > > Andrew J. Kelly SQL MVP > > > > > > > > > > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > > > > news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > > > > > > Can anyone confirm this for me. If I were to run this script > below. > > > Will > > > > > it > > > > > > reindex all tables indexes at one time as it goes through the > > cursor. > > > Or > > > > > > will it reindex one table indexes at a time as it goes through the > > > > cursor. > > > > > > Basically will it pause each time the DBCC command is executed? > > > > > > If the first part is true where it executes all at once then what > is > > a > > > > > good > > > > > > way to only do one at a time? Can you pause between executions > till > > > each > > > > > > execution is done? > > > > > > > > > > > > THanks for the help. > > > > > > > > > > > > DECLARE @TableName varchar(255) > > > > > > DECLARE ReindexTableCursor CURSOR FOR > > > > > > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = > 'Practice' > > > OR > > > > > name > > > > > > = 'ID_Master') > > > > > > ORDER BY name > > > > > > > > > > > > OPEN ReindexTableCursor > > > > > > > > > > > > -- Perform the first fetch. > > > > > > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > > > > > > > > > > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > > > > > > WHILE @@FETCH_STATUS = 0 > > > > > > BEGIN > > > > > > -- This is executed as long as the previous fetch succeeds. > > > > > > PRINT 'Reindexing' + ' ' + @TableName > > > > > > DBCC DBREINDEX (@TableName ) > > > > > > FETCH NEXT FROM ReindexTableCursor into @TableName > > > > > > END > > > > > > > > > > > > CLOSE ReindexTableCursor > > > > > > DEALLOCATE ReindexTableCursor > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
[quoted text, click to view]
"Dave Mortenson" <dmortenson@dentrix.com> wrote in message news:eSfnM#obEHA.1732@TK2MSFTNGP09.phx.gbl... > Can anyone confirm this for me. If I were to run this script below. Will it > reindex all tables indexes at one time as it goes through the cursor. Or > will it reindex one table indexes at a time as it goes through the cursor. > Basically will it pause each time the DBCC command is executed? > If the first part is true where it executes all at once then what is a good > way to only do one at a time? Can you pause between executions till each > execution is done? > > THanks for the help. > > DECLARE @TableName varchar(255) > DECLARE ReindexTableCursor CURSOR FOR > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR name > = 'ID_Master') > ORDER BY name > > OPEN ReindexTableCursor > > -- Perform the first fetch. > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > WHILE @@FETCH_STATUS = 0 > BEGIN > -- This is executed as long as the previous fetch succeeds. > PRINT 'Reindexing' + ' ' + @TableName > DBCC DBREINDEX (@TableName ) > FETCH NEXT FROM ReindexTableCursor into @TableName > END > > CLOSE ReindexTableCursor > DEALLOCATE ReindexTableCursor > > My $.02 on this is to suggest you use an INSENSITIVE cursor. I have a bias against taking out locks in the system tables and keeping them for a long time. In the INSENSITIVE case, you'll have to be prepared for the possibility that any of the tables could be missing by the time you cycle to it. This is unlikely, of course, and since this is simple preventative maintenance maybe it's OK if the proc aborts. Otherwise, you'll have to handle it. You might also order your cursor to start with the smallest table first. I think that would minimize expansion of the database during the procedure. I don't know if size information's in the INFORMATION_SCHEMA tables/views, though. Using them is a good thing, as another reply pointed out, but I don't. Too set in my ways, I guess, and portability is not a priority.
That is due to the way it buffers the output and there is no easy way to do
what your asking that I know of. It is something that a lot of people ask about but I haven't seen a good solution. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Dave Mortenson" <dmortenson@dentrix.com> wrote in message news:O0Bp86ybEHA.2944@TK2MSFTNGP11.phx.gbl... > Andrew Excellent that is what I wanted to know. Thank you so much. Do you > know of a way to make it print as it completes each execution rather than > all at the end? I do use the print command but like I said it will only > print all at the end. > > THanks again I really appreciate the info. > > Dave > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:ORFRinsbEHA.636@TK2MSFTNGP12.phx.gbl... > > OK, I see where you are going now. TSQL is a procedural language and can > > not by itself run multiple paths or commands per say. Before the next > > command can be executed the one before it must finish completely. This > > includes commands inside a cursor. The next fetch operation will not > occur > > before the previous DBCC command is completed. So in that regard you > don't > > need a WAITFOR. Sometimes people want the system to take a breather in > > between tables since this can be a very intensive operation. > > Now this does not mean multiple users can not be doing stuff at the same > > time. Each user has it's own threads that run independently of each > other. > > But within a single batch of TSQL it is essentially procedural. > > > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > news:%23Io2qeqbEHA.1408@TK2MSFTNGP12.phx.gbl... > > > This is true. What I was trying to get at was in a query analyzer window > > it > > > takes only a few seconds to query and return all my tables. Will the > > cursor > > > also loop through all the tables in a few seconds and issue multiple > dbcc > > > commands all at once. Or will it loop once issue the dbcc dbreindex > > command > > > wait for it to finish then loop again to the next and so on this way it > > wont > > > issue multiple commands all at once? This is what I was wondering if I > > > needed to pause in between loops. > > > > > > THanks Andrew. > > > > > > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > > > news:OD7KOCqbEHA.644@tk2msftngp13.phx.gbl... > > > > Dave, > > > > > > > > The whole point of a Cursor is to loop thru the result set one row at > a > > > > time. The cursor will only return 1 table name with each loop and > since > > > > your specifying the table name via the variable that gets filled in > thru > > > the > > > > cursor, DBCC DBREINDEX will only work on that individual table at that > > > time. > > > > As a matter of fact there is no way to reindex all tables or indexes > all > > > at > > > > once with one command. Hope that helps. > > > > > > > > -- > > > > Andrew J. Kelly SQL MVP > > > > > > > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > > > news:%23N3F7tpbEHA.3016@tk2msftngp13.phx.gbl... > > > > > OK Thanks for the information. I will use INFORMATION_SCHEMA.TABLES. > I > > > do > > > > > understand if I only specify the table name it will reindex all > > indexes > > > in > > > > > table thius is what I am after. Although what I was wondering is if > I > > > use > > > > > this script and my query returns 15 table names lets say will it > > execute > > > > the > > > > > dbcc command on all 15 tables at the same time or will it execute > the > > > dbcc > > > > > command one at a time as it sequencially goes through the cursor. I > > only > > > > > want to reindex one table and all it's indexes at a time. Can you > tell > > > me > > > > if > > > > > this is true? > > > > > > > > > > > > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > > > > > news:eAa0NdpbEHA.1004@TK2MSFTNGP11.phx.gbl... > > > > > > Dave, > > > > > > > > > > > > If you execute DBCC DBREINDEX with only the table specified it > will > > > > > rebuild > > > > > > ALL indexes on that table. This happens all in one big transaction > > for > > > > > each > > > > > > table. You can specify a single index to be rebuilt but as you > have > > > it > > > > > here > > > > > > it will rebuild all of them on each table in the cursor. You can > > > pause > > > > in > > > > > > between each table by placing a WAITFOR DELAY after the DBREINDEX > > > > command. > > > > > > You really should look at using one of the information schema > views > > as > > > > the > > > > > > source of the cursor instead of the system tables directly. > > > > > > INFORMATION_SCHEMA.TABLES > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > Andrew J. Kelly SQL MVP > > > > > > > > > > > > > > > > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > > > > > > news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > > > > > > > Can anyone confirm this for me. If I were to run this script > > below. > > > > Will > > > > > > it > > > > > > > reindex all tables indexes at one time as it goes through the > > > cursor. > > > > Or > > > > > > > will it reindex one table indexes at a time as it goes through > the > > > > > cursor. > > > > > > > Basically will it pause each time the DBCC command is executed? > > > > > > > If the first part is true where it executes all at once then > what > > is > > > a > > > > > > good > > > > > > > way to only do one at a time? Can you pause between executions > > till > > > > each > > > > > > > execution is done? > > > > > > > > > > > > > > THanks for the help. > > > > > > > > > > > > > > DECLARE @TableName varchar(255) > > > > > > > DECLARE ReindexTableCursor CURSOR FOR > > > > > > > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = > > 'Practice' > > > > OR > > > > > > name > > > > > > > = 'ID_Master') > > > > > > > ORDER BY name > > > > > > > > > > > > > > OPEN ReindexTableCursor > > > > > > > > > > > > > > -- Perform the first fetch. > > > > > > > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > > > > > > > > > > > > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > > > > > > > WHILE @@FETCH_STATUS = 0 > > > > > > > BEGIN > > > > > > > -- This is executed as long as the previous fetch succeeds. > > > > > > > PRINT 'Reindexing' + ' ' + @TableName > > > > > > > DBCC DBREINDEX (@TableName ) > > > > > > > FETCH NEXT FROM ReindexTableCursor into @TableName > > > > > > > END > > > > > > > > > > > > > > CLOSE ReindexTableCursor > > > > > > > DEALLOCATE ReindexTableCursor > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
[quoted text, click to view]
> You could get rid of your cursor by using sp_MSforeachtable sp_MSforeachtable uses a cursor to achieve this so your not really getting rid of the cursor. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message news:%230FQPGzbEHA.3636@TK2MSFTNGP10.phx.gbl... > You could get rid of your cursor by using sp_MSforeachtable > > See notes below from web site re: undocumented SP's: > http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm > sp_MSforeachtable > Sometimes, you need to perform the same actions for all tables in the > database. You can create cursor for this purpose, or you can also use the > sp_MSforeachtable stored procedure to accomplish the same goal with less > work. > > For example, you can use the sp_MSforeachtable stored procedure to rebuild > all the indexes in a database: > > EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')" > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > news:eSfnM%23obEHA.1732@TK2MSFTNGP09.phx.gbl... > > Can anyone confirm this for me. If I were to run this script below. Will > it > > reindex all tables indexes at one time as it goes through the cursor. Or > > will it reindex one table indexes at a time as it goes through the cursor. > > Basically will it pause each time the DBCC command is executed? > > If the first part is true where it executes all at once then what is a > good > > way to only do one at a time? Can you pause between executions till each > > execution is done? > > > > THanks for the help. > > > > DECLARE @TableName varchar(255) > > DECLARE ReindexTableCursor CURSOR FOR > > SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR > name > > = 'ID_Master') > > ORDER BY name > > > > OPEN ReindexTableCursor > > > > -- Perform the first fetch. > > FETCH NEXT FROM ReindexTableCursor INTO @TableName > > > > -- Check @@FETCH_STATUS to see if there are any rows to fetch. > > WHILE @@FETCH_STATUS = 0 > > BEGIN > > -- This is executed as long as the previous fetch succeeds. > > PRINT 'Reindexing' + ' ' + @TableName > > DBCC DBREINDEX (@TableName ) > > FETCH NEXT FROM ReindexTableCursor into @TableName > > END > > > > CLOSE ReindexTableCursor > > DEALLOCATE ReindexTableCursor > > > > > >
I don't know how to get the PRINT statements flushed to QA, either (although
you might try setting your network packet size very, very small but this is fraught with peril) but if one wants to know how far along the reindexing process is, I have two suggestions that may work: 1. Examine the syslockinfo tables to see what's got an exclusive lock for your connection. Mapping to table names is reasonably straightforward. 2. Create an in-progess table and update it as you go along. Since this is informational and not business critical, you wouldn't need any particular transaction management. You could even do things rather differently altogether. The following is kinda/sorta tested: Select so.name, 'N' as status into ReindexList from sysobjects so where so.type = 'U' and so.name <> 'ReindexList' /* maybe include a restriction to DBO-only tables, too, or include the owner here and as part of the PK */ go alter table ReindexList add constraint PK_ReindexList primary key clustered (name) go DECLARE @TableName varchar(255) DECLARE ReindexTableCursor CURSOR FOR SELECT name FROM ReindexList where status <> 'C' ORDER BY name for update of status OPEN ReindexTableCursor -- Perform the first fetch. FETCH NEXT FROM ReindexTableCursor INTO @TableName -- Check @@FETCH_STATUS to see if there are any rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. PRINT 'Reindexing' + ' ' + @TableName update ReindexList set status = 'P' where current of ReindexTableCursor /* P for processing */ DBCC DBREINDEX (@TableName ) update ReindexList set status = 'C' where current of ReindexTableCursor /* C for completed */ FETCH NEXT FROM ReindexTableCursor into @TableName END CLOSE ReindexTableCursor DEALLOCATE ReindexTableCursor go While the process is in progress, one should be able to query the ReindexList table and see the statuses. I say "should" because I commented out the actual reindex statement and this didn't take long enough for me to query the table. I suppose I could add a waitfor and do some poking around... Well, not today. Notice that using the in-progress table means that one could interrupt this and come back to it later without redoing work already done. I already included the <> 'C' restriction. [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message [snip]news:exIt6JzbEHA.2476@TK2MSFTNGP09.phx.gbl... > That is due to the way it buffers the output and there is no easy way to do > what your asking that I know of. It is something that a lot of people ask > about but I haven't seen a good solution. > > -- > Andrew J. Kelly SQL MVP > > > "Dave Mortenson" <dmortenson@dentrix.com> wrote in message > news:O0Bp86ybEHA.2944@TK2MSFTNGP11.phx.gbl... > > Andrew Excellent that is what I wanted to know. Thank you so much. Do you > > know of a way to make it print as it completes each execution rather than > > all at the end? I do use the print command but like I said it will only > > print all at the end. > > > > THanks again I really appreciate the info. > > > > Dave > > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |