Maintenance - Defrag/Reindex/CleanTable

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

General SQL Server Forums

New to SQL Server Administration

Maintenance - Defrag/Reindex/CleanTable

Author

Topic

txgeekgirl
Starting Member

18 Posts

Posted - 2012-10-17 : 18:36:35

I am trying to set up a maintenance to defrag, reindex, and cleantable on the larger tables in a 96 GB database to be run in between regular maintenance as we have seen these tables get his pretty hard and have the most issues.

Below is a sample. I have noticed that actually cleaning the tables is "growing" the Data file. I have played a little with the variables in Reindex and CleanTable only to see it grow more. Any suggestions?

USE [master]
GO
ALTER DATABASE [TmsEPly] SET RECOVERY SIMPLE WITH NO_WAIT
GO


USE TmsEPly
GO
DBCC INDEXDEFRAG(TmsEPly,'APPLICATION_LOG_DETAIL',PK_APPLICATION_LOG_DETAIL)
GO

Use TmsEPly
GO
DBCC DBREINDEX('APPLICATION_LOG_DETAIL',PK_APPLICATION_LOG_DETAIL, 100);
GO

USE TmsEPly
GO
DBCC CLEANTABLE(TmsEPly,'APPLICATION_LOG_DETAIL',0)
GO
/*********/

USE [TmsEPly]
GO
DBCC SHRINKFILE (N'TmsEPly_Log' , 0, TRUNCATEONLY)
GO

USE [master]
GO
ALTER DATABASE [TmsEPly] SET RECOVERY FULL WITH NO_WAIT
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-17 : 18:46:27

DBCC CLEANTABLE only recovers space from tables that have dropped varchar columns, it's not a regular maintenance task.

DBCC REINDEX will use additional space, as it builds new index structures. DBCC INDEXDEFRAG will generate more transaction log as it moves pages and consolidates free space. REINDEX will also block while it runs, INDEXDEFRAG will not. If you're on SQL Server 2005 or higher, and have the Enterprise Edition, you can do online index rebuilds (reindex) but those will use even more space, although they won't block.

It's not recommended to regularly shrink log or data files if they constantly grow. Size the files properly to support the largest transactions on your database. Do not shrink them unless you absolutely have to, i.e. no disk space left and cannot add more files on another drive.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-10-18 : 14:24:22

In addition to what 'robvolk' has stated, you really do not want to be switching recovery models like this. Switching to SIMPLE breaks the log chain and will prevent you from being able to successfully restore your system using a previous backup and the transaction log backups past that point.

And, once you switch back to full recovery - you have to perform either a full or differential backup to restart the log chain.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-18 : 17:35:00

Ohhh, good call Jeff, I didn't even notice that.

What Jeff said!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources