Monday, March 19, 2012
MS SQL Question regarding page/index defrag.
I know the shortcut to 'defragging' the database to eliminate andreclaim 'whitespace' in data and index pages is to setup a maintenanceplan. This doesn't work for me for two reasons:1. I like to know how things work.2. I can not create maintenance plans with my hosted provider.I use the following T-SQL to rebuild all indexes on all tables:-- exec sp_MSforeachtable "DBCC DBREINDEX ('?', ' ', 90)But, does that not only address indexes? What about getting whitepaceback from data in tables themselves?Am I over-thinking this?When you're paying for SQL by the MB and generate a lot of data (with afair amount of churn) you want to keep it optimized and efficient,right?You could run DBCC ShrinkDB, DBCC ShrinkFile and Truncat your transaction Log, I think all of the above will not return all of the space for you because there is some space SQL Server marks as Active. BTW Maintaince plans are Server based while Jobs can run your DBCCs for your database only. Hope this helps.
Labels:
andreclaim,
database,
defrag,
defragging,
eliminate,
index,
maintenanceplan,
microsoft,
mysql,
oracle,
page,
pages,
regarding,
server,
setup,
shortcut,
sql,
whitespace
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment