SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Maintenance - Defrag/Reindex/CleanTable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

txgeekgirl
Starting Member

18 Posts

Posted - 10/17/2012 :  18:36:35  Show Profile  Reply with Quote
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

USA
15659 Posts

Posted - 10/17/2012 :  18:46:27  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
793 Posts

Posted - 10/18/2012 :  14:24:22  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 10/18/2012 :  17:35:00  Show Profile  Visit robvolk's Homepage  Reply with Quote
Ohhh, good call Jeff, I didn't even notice that.

What Jeff said!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000