Re: DBCC DBREINDEX
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/21/04
- Next message: J McDermond: "Re: can't use ? after FROM"
- Previous message: Joe Horton: "Re: DBCC DBREINDEX"
- In reply to: Dave Mortenson: "Re: DBCC DBREINDEX"
- Next in thread: DHatheway: "Re: DBCC DBREINDEX - How Far Along Has the Reindex Process Gotten?"
- Reply: DHatheway: "Re: DBCC DBREINDEX - How Far Along Has the Reindex Process Gotten?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 21 Jul 2004 10:53:22 -0400
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 > > > "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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
- Next message: J McDermond: "Re: can't use ? after FROM"
- Previous message: Joe Horton: "Re: DBCC DBREINDEX"
- In reply to: Dave Mortenson: "Re: DBCC DBREINDEX"
- Next in thread: DHatheway: "Re: DBCC DBREINDEX - How Far Along Has the Reindex Process Gotten?"
- Reply: DHatheway: "Re: DBCC DBREINDEX - How Far Along Has the Reindex Process Gotten?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
