Rebuild Indexes SQL Server 2005

[inet]

Hi All,

I used the following code to re-build indexes in SQL Server 2000 and it worked fine:

EXECUTE master.dbo.xp_sqlmaint N'-D DBName -Rpt "T:\MaintPlanRpts\Optimization For DBName.txt" -DelTxtRpt 2DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

Backup Log DBName with truncate_only

dbcc shrinkfile (DBName_log, 150)


Now in SQL Server 2005 I use database mirroring for the disaster recovery so I can't use the above code. With new code I am looking for the same results.

How can I accomplish this?

Thanks.

[rmiao]

Sql2k5 still has maintenance plan. But why shrink log every time? It's double performance hit.

[inet]

I've tried using SQL Maintenance Plan for rebuild of the indexes. The transaction log grows to 9 GB after that. It is a little bit too big. I try to keep it under a certain size and I backup tl every 2 hours.

[rmiao]

It'll grow to that size again when you rebuild index. By the way, db file size doesn't affect backup speed,contents of db files do.

[inet]

That's why I need to know how to shrink tl with the database mirroring setup. This way I can run the scripts every time I rebuild indexes. I do that know once a week.

[rmiao]

The point is why shrink it even you know it'll grow again.

[inet]

I am trying to keep it under a certain size.

[rmiao]

Not good thing to do unless server doesn't have disk space.

[nosepicker]

Just to clarify, shrinking a transaction log file is generally not the best idea if it's going to constantly grow again. Every time the transaction log has to grow, all transactions have to wait for the file size to change. This is a major performance hit. So unless you have a serious disk space issue or this is a server that doesn't require optimal performance, it's better if you can leave the transaction log large enough to handle normal transactions without having to auto-grow.