Reindexing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Reindexing

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2008-11-25 : 16:39:07

Hi Experts,

Can anyone help me in this problem.

I have the Reindexing Automatic done every weekday and I have shifted to otherday this week from wednesday to sunday to perform the reindex automatically.

The following day it was failed to do the reindex on the other hand it consumed almost 250 GB Free space from the disk.

This is the code which I run through the Maintenance plan.

The Fill Factor applied was 80 percent.

USE DB1
DECLARE @TableName1 varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName1
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@TableName1,' ',80)
FETCH NEXT FROM TableCursor INTO @TableName1
END
CLOSE TableCursor
DEALLOCATE TableCursor

The ABOVE DB1 is the database 1 and is now applicable by means of all the databases in the instance.

What needs to be done in order to come back to previous status like,

getting back the lost space;

Should I apply the above code in the T - SQL to get the reindexing done and also space being done?

Your help will be greatly appreciated.

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-25 : 17:18:44

Are you using SQL Server 2000 or 2005? You've posted in a 2005 forum, yet you are using a 2000 command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Carat
Yak Posting Veteran

92 Posts

Posted - 2008-11-25 : 17:20:31

I'm not familiar with maintenance plans (because its better to write your own code to do these maintenance tasks) but I think you should use "alter index on schema.table rebuild/reorganize" instead of "dbcc dbreindex". This last statement was used in SQL Server 2000.

Do you mean that your database has grown 250GB due to reindexing? When this is the case then you should first try to shrink your database.

If you use your code, every index in your database would be rebuild, which can take an enormous space. Its better to use the DMF "sys.dm_db_index_physical_stats" to see whether to rebuild (avg_fragmentation_in_percent > 30) or to reorganize an index (avg_fragmentation_in_percent > 5 and < 30).

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2008-11-25 : 17:33:40

Yes I am using the code in 2005.

I have shrunk the all Databases.

Do I need to use the statement "alter index on schema.table rebuild/reorganize" in the Maintenance plan OR shall I replace in the DBCC DBREINDEX in the code as:

USE DB1
DECLARE @TableName1 varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName1
WHILE @@FETCH_STATUS = 0
BEGIN
alter index on schema.table rebuild/reorganize
FETCH NEXT FROM TableCursor INTO @TableName1
END
CLOSE TableCursor
DEALLOCATE TableCursor

how to mention the fill factor.

Please let me know.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-25 : 17:37:55

You should not be using DBCC DBREINDEX in SQL Server 2005. And you should only reindex those indexes that need reindexing due to fragmentation. Do not just reindex them all as it is a waste of resources. You can use my script which intelligently decides which need to be reindexed: http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

Fill factors don't matter here.

Also do not shrink the database unless you are sure you no longer need the space.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2008-11-25 : 17:51:58

Hi Tara,

I know you gave me many times much needed timely help.

Thanks Carat for your comments.

Its wonderful, however, I do have a question how can I put it in the form of Maintenance plan. My Maintenance plan will work for those tables which require the reindexing, so I will be eliminating automatically.

Can you help me in this regard as to how I can depict the in the maintenance plan.

Thanks again for your time.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 20:03:33

Why maintenance plan? Did you see Tara's Post?

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2008-11-26 : 17:26:44

Tara Thanks. I have tested this environment on testing server and works fine, but no evidence of doing reinding with the script.
Can you help me how I can Steal your script to suit my needs.

Sodeep, I have gone through with Tara's post BLOG.

However, how about using the Script to fix it in maintenance plan so that it will be automated and will run on weekly basis.

Please guide me.

Thanks again

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 17:38:44

You can Put Tara's Script in SQL Server Agent Jobs and run on weekly basis.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2008-11-26 : 17:59:00

Thanks Tara and Sodeep

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-26 : 22:13:03

The whole purpose of a custom script like this is so that you don't need to use a maintenance plan.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog