SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables

SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.

When any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. DBCC DBREINDEX statement can be used to rebuild all the indexes on all the tables in database. DBCC DBREINDEX is efficient over dropping and recreating indexes.

Execution of Stored Procedure sp_updatestats at the end of the Indexes process ensures updating stats of the database.

Method 1: My Preference

USE MyDatabase
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO

Method 2:

USE MyDatabase
GO
CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
AS
DECLARE
@MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT
table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE
myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
GO

Reference: Pinal Dave (http://www.SQLAuthority.com)

About these ads

99 thoughts on “SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables

  1. just ran method 1 and it worked a treat, thanks a lot. I’ve relatively little experience in SQL and didnt fancy having to reindex hundreds of tables manually to free up space!

  2. This is good trick but Microsoft says that this command DBCC DBREINDEX will be removed in there future versions So for this its better to use ALTER INDEX like below.

    ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
    REBUILD;

    As Pinal sir used the DBCC DBREINDEX command in cusrsor same as we can use this commmand in the cursor.

  3. DBCC DBREINDEX is an off line process. Apart from suggesting DBCC INDEXDEFRAG as a method to deal with online indices, are there any techniques which allow a index to be reindex without going offline

  4. Does anybody know if reindexing a database can cause any performance impact?

    We are supporting an application with sql server 2005 as the database. We have a weekly job that reindexes the database. However after the reindexing the performance goes down significantly. We are trying to determine the rootcause of this issue ? Any pointers would be highly appreciated

  5. When you rebuild an index, SQL Server updates the index statistics as well. However, reorganize index does not do this. So you don’t have to update stats after a rebuild but you would want to after a reorg. I don’t think either touch column stats though.

  6. I ran the application and it seems to get stuck at some point, perhaps due to a lock. Is there any way to modify the script to:
    a. report in real time (no buffering) what table/index it is currently working on
    b. report the size in records for the table it is about to start work on
    c. avoid or at least indicate if there is a lock active that is blocking the reindex process?

  7. We’re using above mentioned functionality to re-index and update statistic on a weekly based. The database is roughly 30GB. While the re-indexing/update statistic took 3 hours 6 month ago, it just increased to 8.5 to 10 hours over the last 8 weeks without significant change in the amount of data. Any idea what might cause the dramatic increase in processing time? we’re using SQL Server 2005.

  8. Hello,

    I am using SQL Server 2000 and I have the following issue:

    After running the sp_updatestats the performance decreases too much.

    To solve that I ran a index rebuild and stopped running sp_updatestats

    I don’t why this is happening, and I could not reproduce this on a non production environment.

    Do you have any suggestion?

    Thanks in advance.

  9. This script of yours just solved a problem I have been working on at work for about 1 1/2 months. We don’t have a real DBA here, so no one could figure out what was going wrong. I was sometimes thinking about quitting my job over this problem. But, once I ran your script, our database ran as fast as ever. Thank you so much. Your website is the best!

  10. Ritesh,
    The fill factor could also be an issue for you. If your table has a lot of writes, you should use a lower fill factor. 80% might work for you. If you do not specify the fill factor, then it is 100% by default. This could could a lot of page splits if there is a lot of IO.
    Don’t set the fill factor too low though, because otherwise the query will have to read more pages. If your table is mostly reads, then 100% fill factor would be better. It all depends on how much IO your table has.

  11. Victor, in 2000, sp_updatestats re-calculates the index statistics with the sample rate. This overwrites the full sample that was done with the rebuild index. Don’t update statistics after a rebuild.
    In 2005 this is not a problem because sp_updatestats first checks if an update is necessary and skips the update if it’s not needed (this only true with the stored procedure and not the UPDATE STATISTICS command).

  12. hi.
    i would like to know if it is possible just to reindex 1 table from the entire database and is there a difference between the scripts for 2000 and 2005?

    thanking you in Advance
    Gavin

  13. @ Gavin,

    Question1 : I dont know if you mean Rebuild indexes ? what do you mean by reindex ?

    Well for Rebuild indexes on a single table ?

    Yes, you can Rebuild indexes on one table, and you have the option to rebuild one index among many index available on a table in a database.

    you would be shocked if I say, there is no way that you can rebuild indexes of all tables in a database, You have to write script if you want to rebuild indexes on database level ( all tables).

    script: SQL 2000

    use pubs
    DBCC DBreindex ( ‘dbo.authors’ ,UPKCL_auidind, 90)

    – The above script will only rebuild one index UPKCL_audind on the table authors in pub database.

    – If you want to rebuild all indexes on author table, then use below script

    use pubs
    DBCC DBreindex ( ‘dbo.authors’ ,’ ‘, 90)

    Question2 : What do you mean by script in 2000 and script in 2005.

    I could not understand the question.

    Hope this helps,
    Imran.

  14. I want to perform reindexing on my database, can I run the below script on my database…

    Will it help for performance improvement?

    USE MyDatabase
    GO
    CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
    AS
    DECLARE @MyTable VARCHAR(255)
    DECLARE myCursor
    CURSOR FOR
    SELECT table_name
    FROM information_schema.tables
    WHERE table_type = ‘base table’
    OPEN myCursor
    FETCH NEXT FROM myCursor INTO @MyTable
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT ‘Reindexing Table: ‘ + @MyTable
    DBCC DBREINDEX(@MyTable, ”, 80)
    FETCH NEXT
    FROM myCursor INTO @MyTable
    END
    CLOSE myCursor
    DEALLOCATE myCursor
    EXEC sp_updatestats
    GO

  15. Hi Pinal,

    I need your guidance. I have just started working as a Associate DBA.I want to know what all the basic things which is needed to do for DAY-To-DAY activities. Being an Associate DBA what all I have to do once I login to my system. I do have the theoretical knowledge but doesn’t have practical. Plzz help me out.

    I am responsible for BACK UP, Replication for 24/7 environment

    Q:1 What kind of backup strategy I should maintain if the environment is 24/7.

  16. Thanks a ton! I had been trying to figure out why my queries were so slow when I upgraded to SQL Server 2008 and this solved it. Now it’s running faster than ever.

  17. Why wouldn’t you just use the maintenance plan in SQL and selecting revbuild index? What’s the advantages of running this manually?

  18. Mike,

    We’ve got a DB in our software product that needs some maintenance on our cusotmers systems. A few of our customers have databases with tables so large that thier servers cannot reindex fast enough with no impact the normal processing of data.

    For us, we have to reindex the individual indexes on a schedule for the large tables. I’m not as familiar with SSIS and setting up maintenance plans, but based on what I know I don’t think you get that level of granularity by using the GUI creating a package, or/and especially by walking through the maintenance plan wizard. Hence, the need for some custom TSQL…

    –Robert

  19. Hi,

    Was wondering if anyone have had this problem – everytime the server is stop and restarted, this script (method 1) needs to be executed again to get performance back up. When I say performance back up, I mean, we have a stored procedure that returns summary data and after running the method 1 script, the duration it takes to execute drops by 50% and when restarted, duration jumps back up until we execute script. Please help, am stuck and have not had much luck researching on this issue.

    NS

  20. Hi
    I am using your solution for Reindexing but the problem is whenever I stop and start the SQLServer I need the Reindex to be run again, otherwise I don’t have a good performance on my SQL query.
    Does anybody have a solution for it

  21. hi there,

    i am running MS SQL 2005, the problem is i always encounter this error

    “The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.”

    do i need to defragment or reindex the database? how am i going to do that?

    is there a way i can reindex/defrag the whole database or do i need to reindex/defrag it per table?

    size of database is 120GB – Hard Disk Size is 410GB
    Memory used is 9+GB – Memory Size is 26GB
    HP Proliant DL380 G5 quad core

    we’re using SAP Business One application Patch Version PL45.

  22. Pinal –

    I am running SQL 2005 STANDARD – database about 40 GB. i need to have the database online 24/7. If i do RE_INDEX slowing down the user (grows 2 GB more). so i decided to DEFRAG index. now my database grown to 110 GB.

    1st quest: Trucation–>DEFRAG index–> Full backp (No update statistics yet) after that i am doing hourly Transaction log backup. please suggest me the best way to handle this.

    Also i am getting “[SQLSTATE 01000] (Message 4035) A nonrecoverable I/O error occurred on file” on Transaction Log backup. please help.thanks

  23. We are trying to search the 66 tables for a record
    the structure of the table like below

    scenario, seg1, seg2, seg3,seg4, period,qty,amt,price

    and all the tables have similar structure .

    Unique clustered index was created on each table.

    When we try to search a record from 66 tables it was taking approximatley 7 minutes. How to reduce the time to run the query faster.

    its a simple query like

  24. We are trying to search the 66 tables for a record
    the structure of the table ( sales_fact) like below

    scenario, seg1, seg2, seg3,seg4, period,qty,amt,price

    and all the tables have similar structure .

    Unique clustered index was created on each table.

    When we try to search a record from 66 tables it was taking approximatley 7 minutes. How to reduce the time to run the query faster.

    its a simple query like

    select top 1 from sales_Fact
    where seg1 = 111

    like above , 66 sql statements should run and each table has got around 10million records.

    I appreciate for quick reply

    Thanks in advance

    Murali

  25. I suppose you can re-index a copy of your online table and then rename the tables so that the new copy is now the online copy. Downtime, but 0 seconds, and renaming objects has drawbacks – in 2000 at least, SQL code attached with the renamed object (create statement, triggers) will contain its original name…

    Does Microsoft even make 24/7 products? I think if you need 24/7 availability, you should build it from components that are not required to be 24/7 individually. Switch to a backup server. Do something like RAID hard disks do.

    I’m also pursuing how to update statistics with fullscan. I’m not sure if I need it but I want to know how to get it.

  26. Hi,

    Using SQL Server Express 2005
    VB.NET 2005 app connecting to SQL database

    Option 1 works beautifully for me, but only when I have SQL Server open.

    As soon as I close the SQL session, my database performance falls to the floor again.

    Any assistance could be greatly appreciated

    Steven

  27. Hi,

    I have created the reindexing job from 1st option of reindexing.
    After executing the job I am getting the Job failure alert when I check with the steps Its completed successfully.
    We always get result like this
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    I believe jaob is failing due to above alert.
    When I go with second option of reindexing which is through procedure.
    Output is coming
    Command completed succesfully.
    So we can assume Reindexing have been completed on all the tables or Is there any way to know about reindexing status whether it is completed or not…

    Second question is above two method is also applicable for SQL 2000.

    Thanks in advance..

  28. sp_MSforeachtable is described in many places as an “undocumented stored procedure”, which to me means they don’t tell you if there are bugs in it, you’re not even supposed to know it’s there. A phantom error message could be that kind of a bug, I think, whether or not there’s a real error.

    If you’re anxious, this may be a prudent test for problems in your database: DBCC CHECKDB() WITH DATA_PURITY
    (The WITH part is only for SQL Server 2005 and for databases where it doesn’t run by default, including databases that were created on SQL Server 2000, and if it runs without errors it becomes the default for that database. It can also be reversed.)

    Incidentally, databases from 2000 also really need DBCC UPDATEUSAGE done. It may also ¤ix performance problems. I forget whether I read that in Microsoft documentation, but it’s genuine although apparently not written prominently enough in upgrade documentation for the colleague who upgraded some of our servers to be aware of it.

    Otherwise, both versions do basically the same thing: repeatedly construct and execute a string containing a SQL statement referring to each table (user table?) in a database in turn – table1, table2, table3.

    If you don’t frequently add or remove tables in your database, you can use a similar design to construct and print out a command for each table in a database, and make a stored procedure containing all the commands. That means you won’t be doing the fancy stuff during your maintenance process, only the actual maintenance commands.

  29. I wonder what actual error can occur in this process? Disk fills up, maybe.

    In method 2, instead of a PRINT statement you could do something to log the fact that the command is about to be run, a table somewhere is the obvious idea, and immediately after running it, capture and store the error state – to a variable is a good way. Something like this:

    DECLARE @returnerror int, @start datetime;
    SET @start = GETDATEUTC();
    INSERT LogOfReindexesByTable (tableName, startTime)
    VALUES (@MyTable, @start);
    DBCC DBREINDEX(@MyTable, ”, 80);
    SET @returnerror = @@ERROR;
    UPDATE LogOfReindexesByTable
    SET endTime = GETDATEUTC(), errorCode = @returnerror
    WHERE ( tableName = @MyTable AND startTime = @start );

    In SQL Server 2005 you can use an ALTER TABLE statement for this operation instead of DBCC.

  30. Hi, sorry for my english!

    do you know why the update statistic task has past from 10 min to more than 33 min from one day to another:
    monday: 10min
    Tuesday: 10min
    Wednesday: 33min
    Thursday: 33min
    Friday: 33 min

    Thanks
    Sylvain Dionne

  31. Hey Sylvain: If your database isn’t broken (test with DBCC CHECKDB), maybe it just got bigger and fell over a cliff of performance, for instance how tempdb database is used in this process. Or maybe tempdb is large and inefficient now and you need to fix that (be very careful, but restarting SQL Server may be all that you need). Or maybe someone added some statistics or indexes that you don’t know about. You could consider whether you need all your statistics, since they don’t come for nothing. I assume that we are supposed to choose one or more recommendations from the Tuning Advisor tool, not -all- of the recommendations.

    I also wonder why the time taken is – apart from the step up – the same every day. Perhaps you are repeating the same task every day on all of your data? You could change your database design to avoid that. For instance, use a partitioned view or partitioned table. Then you can have old data that you don’t UPDATE STATISTICS on so often, only the new data. That may be not a good solution for you, but I want to say that you have many different possible remedies.

  32. HI

    I am working on a database with more than 80 million of records. we are using sql server 2005 and have applied fulltext indexes to make search through the database.

    Problem we are facing is that if there are new records in the database then after every week we have to reindex the database and also recreate the fulltext indexes for the database. We have setup the Fulltext Index catalogu to update automatically when some new records comes but still reindexing the tables of the database is too much expensive for us. Is there a way to around so that we can avoid this reindexing the table.

    I do now know how to make a new post with question here on this website. I found this post related to my problem about reindex so i thought it would be good to post it here. Hope to get some solution.

    Regards,
    Qasim

  33. Fact is, the more often you reindex the less fragmented your indexes will be, and the less fragmented they are the faster performance you’ll see. I reindex nightly and can afford a little downtime for my bigger tables, in the middle of the night. This keeps my users happy in the daytime.

    If outage is an issue then seriously look into the SQL Enterprise license, it allows online reindexing. It’s pricey, though, at around $20k/proc.

  34. i just wondered if there is such more performance or an advantage in space while the indexes are creating from 0 or doing a re-index ..

    Because if im doing a database re-index logs can grow up fast and some filegroups too… so

    what is the advantage droping all the indexes and recreating all them again.. or doing a DBCC DBREINDEX???

    i have to do a db maintenance but all the databases are HUGE!.. they are between 600GB and 1.5 TB..

    your answers can be very helpfull

    Sending Regards…

  35. DBREINDEX is like running TSQL statements Dropping indexes and then Creating them … so I dont see any advantage

    question to Dave … why is there a need to run sp_updatestats when DBREINDEX updates the stats?

  36. Hi,

    Thanxs for above code, re-indexing works gr8.

    We are using .Net 1.1 and SQL SERVER 2005, and managing 40-50 different websites. But we are using only one database to facing records for all site.

    Issue is that after we shifted our query’s from code to SP we are getting Transaction deadlocked and when our websites hit huge traffic all sites went down. Is using one database for more than 50 site cause this issue.

    Also, when i checked SP_WHO on SQL server we are getting 750-800 processes concurrent. Do you have any suggestion? why we are getting Transaction deadlocked and lots of SP_WHO processes on sql server.

    appreciate for quick reply
    Thanks in advance

  37. Hi Pinal,

    I read your posts regularly.

    As it’s written at first line regarding (INSERT, UPDATE, or DELETE statements) . And it’s better over dropping and recreating index. Agreed.

    Now My question is,
    what I do usually, drop index, perform insert or update, create index again.

    As you described, insert or update, and run script. but with index on table, insert or update will be slow.

    Am I explaining you correct?

    Thanks in advance.

  38. Pinal,

    Not sure why you are updating stats after REINDEX as reindex does a update stats on the index with FULL TABLE SCAN details.

  39. I would be very-very weary of updating database statistics after rebuilding indexes. According to Paul Randal, “Index rebuilds automatically update statistics with a full scan.” [1] He adds, “If you manually update statistics after an index rebuild, it’s possible to end up with less accurate statistics! This can happen if a sampled scan from the manual update overwrites the full scan generated by the index rebuild.” In my experience, Paul is correct. Pursuant, I wouldn’t waiste cycles updating indexes after an index rebuild.

    [1] Top Tips for Effective Database Maintenance, http://technet.microsoft.com/en-gb/magazine/2008.08.database.aspx

  40. If you find that your transaction log grows to an unacceptable size when you run DBCC REINDEX, you can minimize this growth by switching from the Full Recovery mode to the Bulk-Logged mode before you reindex, and when done, switch back. This will significantly reduce the size of the transaction log growth.

  41. @Milthan

    I agree with you, Updating Statistics after rebuilding indexes is of no use (SQL Server 2005). But Rebuilding Indexes after Performing a Shrink File or Shrink DB Operation is a MUST.

    ~ IM.

  42. I had been trying to figure out why my queries were slow when I upgraded to SQL Server 2008. Thanks for helping me figure out the issue. It’s working perfectly fine now and faster than ever!

  43. Hi, I ran the method 1 query. where DB size is 100GB. it is taking huge time more than 1.5 hours.

    Normally how much time is required to ReIndex the 100GB of single database? on which basis time varies while running the ReIndexing query…

  44. I have to manage DBases of > 1TB too.
    unfortionatly the reindex process takes 24 hours, but we can be offline only during out of business hours.
    (some ppl work sat + sun too, and yes i know about online reindex possibilities)
    I wonder if it makes sense, just to rebuild the “bad ones”,
    while keeping the “good ones”.

    the query

    SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N’mydatabase’), NULL, NULL, NULL , ‘DETAILED’);

    should deliver the necessary information. (avg_fragmentation_in_percent)

    but i think i can’t detect “Out of Order”-Pages with that function ?

    the disadvante is that the function has to scan the whole DB once, wich is also very time consuming :(
    In my short test, rebuilding all indexes took less time then analysing all…
    i have to make more tests to come to a final result….

    what do you think of just rebuilding the index based on its fragmentation-value ?
    are there other indicators i should consider as well ?
    How to detect “out of order”-pages ?

  45. In VB 2005 the following command shows error, please help

    Dim cmd As SqlCommand = New SqlCommand(“Exec sp_msForEachTable @COMMAND1=DBCC DBREINDEX (‘?’)”, con)

  46. Here’s a sql statement that will reindex all tables in all databases. It skips standby databases and tempdb, you can also add additional dbs to skip if you wish – just mod the NOT IN clause.

    ——————————-
    DECLARE @dbname VARCHAR(50) — database name
    DECLARE @cmd VARCHAR(500)

    DECLARE db_cursor CURSOR FOR
    SELECT name as dbname
    FROM master.sys.databases
    WHERE name NOT IN (‘tempdb’) and is_in_standby = 0

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @dbname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @cmd = ‘use ‘ + @dbname + ‘; exec sp_MSForEachTable ”DBCC DBREINDEX(””?””,””””,90)”;’
    print @cmd
    exec (@cmd)

    FETCH NEXT FROM db_cursor INTO @dbname
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

    ——————————-

  47. Sorry should also mention.. the original blog executes sp_updatestats at the end. You dont need to do this.. a reindex actually recreates stats based on 100% of the data while a sp_updatestats will replace the stats based on a small percentage of data. So it is better to not update stats if you have just reindexed.

  48. Dear madhivanan,
    Following are my source code and the error i get is “Incorrect syntax near the keyword ‘DBCC’. Please help me.

    regards,

    Public Overrides Sub GetUIData()
    MyBase.GetUIData
    If Me.RE_INDEX.SelectedValue = “Yes” Then
    Dim con As SqlConnection = New SqlConnection(“Data Source=ACER\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=FAS”)
    Dim cmd As SqlCommand = New SqlCommand(“Exec sp_msForEachTable @COMMAND1= DBCC DBREINDEX (‘?’)”, con)
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
    Else
    Throw New Exception(“Indexing not done!”)
    End If
    End Sub

  49. I have a very basic question to ask you all, can any one explain the concept of re-indexing, why and how it is related with the performance of database or exactly what makes the db perform faster after re-indexing??

    Thanks Sagar

  50. HI,

    I am running SQL 2008 database about 40 GB. i need to have the database online 24/7. If i do RE_INDEX slowing down the user (grows 2 GB more). but the user asking no downtime requires. But we need to run the index rebuild in this situation wat will do? kindly advice any one about this.

  51. 1. Run the following command. Focus on the “avg_fragmentation_in_percen”, Higher the number on this column, these are table you need to consider rebuilding / reorganize index

    SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
    GO

    2. Once you identfied the index with a higer number on the “avg_fragmentation_in_percen” column

    3. Do some reading on Rebuild Index VS Reorganise

    http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12869/Default.aspx

    I don’t want to give you all the answers, the only way is you finding the best way…. hope the above hints are helpful

  52. Pinal Dave

    You are the man, you really helped. I spend more than two days trying to find out what the problem was with my queries, little did i know that I needed to reindex my database tables and then update the statistics.

    You are one great DBA, I must say

  53. Should this be copied and pasted to the query without editing? I got this message after execution, with no apparent changes made. Should ‘MyDatabase’ be edited to my database name?

    Msg 911, Level 16, State 1, Line 1
    Could not locate entry in sysdatabases for database ‘MyDatabase’. No entry found with that name. Make sure that the name is entered correctly.
    [dbo].[Event_Classes]

  54. I am planning to perform a rebuild of indexes every Sunday.
    However should I be running,
    1) sp_updatestats Mon – Sat
    OR
    2) reorg index job Mon – Sat
    Or
    something else you recommen ?

  55. Really good method for reducing dimension of a database which had a lot of insert/delete/update. It’s working in my SQL Server 2008 R2.

  56. One of the Stored Procedures was taking around 17 Minutes to run and now after following the Re-Indexing Method it takes 3 Secs only!!!

    Thanks for the article.

  57. hi all

    i have 25 Cr record in a table . At the time of select top 10000 record it will take 59 sec . so please tell how i can fast select statement.

  58. Hi Pinal,
    I am using SQL server 2000 database and size of the table is 128 GB. We ask our team to done DBCC reindex on one table size 128 GB with 30 Index. We started this process 20 hrs. before but it is still running also we are not getting any idea whether we need kill it or cancel it. We want to resume our operation before 24 hrs. and not sure how much indexing has been completed on this database table.

    Please respond ASAP.

  59. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

  60. Hi pinaldave,
    i’ve scheduled a sql2005job with only one step that is:

    USE eusebi
    GO
    EXEC sp_MSforeachtable@command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”
    GO
    EXEC sp_updatestats
    GO

    the story starts regularly but i obtain for the first line the error:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘=’.

    why???

    tnx
    d_borghi

  61. Hi Pinal,

    The above command will run one table competes the rebuild and then the other table or all the tables at a time or index by index of the with in the table.

    Thanks
    Bala…

  62. hi,
    I have a sql 2005 database about 280gb but only have 179gb free and I need to reindex. is this enough space for it or do it require additional space?

    • Depends on the size of the indices. But is sounds risky to me.
      You need to figure out the total disc space used by the index that uses the most disk space. If I did not have triple that total of free disk space I would not try re-indexing an index.

  63. Hey Hi Pinal After running the method 1 , it worked but i noticed that a similar file to mdf with size its equivalent has got created, what i see its like 40 GB and extension of that file is abc_1.L.. I am not sure what is this? Can someone advice please?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s