Database maintenance

Last Post 02 Apr 2008 11:58 AM by sqlserverdeveloper. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqlserverdeveloper
New Member
New Member

--
28 Mar 2008 01:40 PM
The following code runs as a sql job every sunday @1PM, which is slowing our system down during that time, so what I am planning is disable this job compeltely, and just schedule using teh database maint plan wizard for the following:
1. Reorganize data and index pages
2. Update stats
3. Check database integrity
How often do we need to run dbcc UPDATEUSAGE?
Does Reorganize data and index pages does the same as what the defrag and stats update does, if so if we run Reorganize data and index pages then we do'nt need to update statistics right?? Thanks!!

SQL code which I want to disable:
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DECLARE @db sysname, @sql nvarchar(4000)
DECLARE CRDB CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model')
OPEN CRDB
FETCH CRDB INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '=================== '+QUOTENAME(@db)+' "'+CAST(GETDATE() as varchar(100))+'" ====================='
SET @sql=N'SET XACT_ABORT OFF'
SET @sql=@sql+N' DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
SET @sql=@sql+N' IF EXISTS (SELECT * FROM sysindexes WHERE indid=1 AND id=OBJECT_ID(''?''))'
SET @sql=@sql+N' DBCC INDEXDEFRAG (0, ''?'', 1) WITH NO_INFOMSGS'
SET @sql=@sql+N' UPDATE STATISTICS ? EXEC sp_recompile ''?'''
SET @sql=N'USE ['+@db+'] EXEC sp_msforeachtable N'''+REPLACE(@sql,'''','''''')+N''''
EXEC(@sql)
DBCC UPDATEUSAGE (@db) WITH NO_INFOMSGS
DBCC CHECKDB (@db) WITH NO_INFOMSGS
FETCH CRDB INTO @db
END
CLOSE CRDB
DEALLOCATE CRDB
sqlserverdeveloper
New Member
New Member

--
02 Apr 2008 11:58 AM
Thanks very much for the information.
I tried to find under BooksOnLine for the below, but no luck.
BooksOnLine under DBCC SHOWCONTIG that will allow you to only reindex or defrag if the fragmentation is above a certain level to begin with.
Could you please send me the link that will allow to only reindex or defrag if the fragmentation is above a certain level.
I have only the weekend window, so I have to do the maint only during the weekends.
I disaabled the above sql code job and the perf over sunday afternoon was really good. Do I need to run
Updateusage and DBCC checkDB on all the databases? Thanks!
You are not authorized to post a reply.

Acceptable Use Policy