Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

New Maintenance Plan needed 1

Status
Not open for further replies.

phil22046

Programmer
Sep 12, 2001
154
US
I stepped into this job in the second week of April. They had not had a backup since December. So I immediately did a full backup.

This is a 24 hour 7 day a week shop with no scheduled downtimes for maintenance.

The only thing I have succeeded in implementing so far is backups and I have been able to restore those backups to another SQL machine.

What I need to know is what things should be in a maintenance plan?

Database integrity checks?
Rebuilding indexes?

In the 5 years these databases have been up and running - nothing whatsoever has been done.

Issues recently have been identified that lead me to believe that this long overdue maintenance needs to be done.
People running reports after data entry sometimes don't see the new data and running the same report later, the new data shows up.

Also running a report after entering new data works fine, then running the same exact report later and the new data is missing.

So I need to implement maintenance immediately.

Please advise and give me some sample scripts to try.
 
hey phil,
Is this for a 2000 or 2005 SQL Server?

The first thing I would do is look at index fragmentation.

For 2000
This query here will give you a script to run that will show the index fragmentation for your 10 largest table.
Code:
SELECT TOP 10
'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')'
+ CHAR(10) +
'PRINT '' ''' + CHAR(10)
FROM
sysindexes
WHERE
indid = 1 or
indid = 0
ORDER BY rows DESC

You want the logic scan fragmentation to be as close to 0 as possible and the Scan Density to be close to 100% Any index that has a Fragmentation > 20% should be re-built.


This query will work on both 2000 and 2005. it will show you all your heap table. (Tables without a Clustered index)

Code:
SELECT sysobjects.name TableName,
   (SELECT rows FROM sysindexes
WHERE id = sysobjects.id
AND indid = 0) Rows
FROM sysobjects
WHERE type = 'U'
   AND OBJECTPROPERTY(sysobjects.id , 'TableHasIndex' ) = 0
ORDER BY TableName

For 2005
This is the same as show contig in 2000
Code:
SELECT avg_fragmentation_in_percent
      ,o.name as 'Table'
      ,Index_id
      ,index_type_desc
FROM sys.dm_db_index_physical_stats(db_id('1stComp'), null, null, null, null) i
     JOIN sys.objects o
     ON o.object_id = i.object_id 
ORDER BY o.name
        ,index_id

This script will show you all indexes that have never been used. You may want to drop them because they would just be added overhead on inserts/updates/deletes
Code:
SELECT d.name as db_name, s.name as table_name,si.name as index_name,
       i.index_id, si.rowcnt, i.system_scans
FROM sys.dm_db_index_usage_stats i
     JOIN sys.sysobjects s
       ON i.object_id = s.id
     JOIN sys.sysdatabases d
       ON i.database_id = d.dbid
     JOIN sys.sysindexes si
       ON i.object_id = si.id
WHERE user_seeks = 0
  AND user_scans = 0
  AND user_lookups = 0
  AND user_updates = 0
  AND i.index_id <> 1
  AND d.name not in ('tempdb')
ORDER BY rowcnt desc

After you find which tables need to be re-indexed you can run DBCC INDEXDEFRAG. This will defrag your indexes which will help perfomance. Defrag is an on-line fix. You will really want to run DBCC DBREINDEX but that will create a table lock that will block all users from the table till the index is re-built. I recommend you do that on off-hours.

You should also run DBCC CHECKDB weekly. Like maybe every Sunday morning.
I also run REINDEX (Alter index for 2005) weekly.

You should also do a security review of the database to make sure all logins/users are still good.



- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Oh,
Two other maintance tasks to consider. You should update your statistics nightly and check the overall size of your database files. You will want to manage your autogrow events so they don't happen at peak usage times. That will impact IO.

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
It is a SQL 2000 server. I have the development 2005 server on my desktop machine. Wow you sure have a lot of helpful scripts!
 
You find it is a good idea to build your own script library. Everytime I find something I like or write something usefull I store it. I've become a script pack rat! [smile]

Another maintance step you may want to include is to remove very old backup history to help keep the size of your msdb db in check. you can use this.

Code:
sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

I only keep a months of history. (Because we only keep one month on tape)

Post back if you need anything else.

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
I ran the first script and I got 10 lines like this:

DBCC SHOWCONTIG(12) PRINT ' '
 
this line seems to indicate a problem

DBCC SHOWCONTIG(85575343) PRINT ' '
 
That is correct, now copy those ten line into your query window and run it. you will get 10 sets of this.

DBCC SHOWCONTIG scanning 'AP_DESCRIPTION' table...
Table: 'AP_DESCRIPTION' (101575400); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 34609
- Extents Scanned..............................: 4342
- Extent Switches..............................: 4345
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.56% [4327:4346]
- Logical Scan Fragmentation ..................: 2.90%
- Extent Scan Fragmentation ...................: 0.97%
- Avg. Bytes Free per Page.....................: 781.5
- Avg. Page Density (full).....................: 90.35%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
I am getting results like this:

- Logical Scan Fragmentation ..................: 0.49%
- Extent Scan Fragmentation ...................: 31.85%
 
Trying to run the script for finding indexes that have never been run, I get these errors:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_db_index_usage_stats'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysobjects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysdatabases'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysindexes'.
 
That script is for 2005 only. In 2000 they didn't have those stats.


- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Here is my complete 'Library' of commands I know how to use other than the backup and restore commands.

I have 2 hours tonight from 12:01am - 2:00am to fix what I can.

1) My plan is to stop the sql server to quickly disconnect all users still connected and then do
2) a Full Backup.
3) Next I plan on doing a DBCC DBREINDEX
4) shrinking the Log File next:
BACKUP LOG S030 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(S030_log, 20)

Is there any other things that I should do? This system has not had any maintenance in 5 years. I have just the 2 hours to get as much done as I can.


 
I succeeded in rebuilding all the indexes. Users have informed me that they noticed better performance.

How can I update the statistics?
How can I check to see if tables are fragmented and defragment them?

What other maintenance items should be done?

I am still new at this and need some guidance.
 
That's good new about your performance.

1. How can I update the statistics?
This can be done through a maint plan. There is an update statistics object.

2.How can I check to see if tables are fragmented and defragment them?

If you are running DBCC DBREINDEX (ALTER INDEX in 2005) weekly you won't have to worry about fragmention. You don't need to have exclusive access to the database to run DBCC DBREINDEX. you should schedule it for off hours or off peak transaction time.

You can also break up your reindex job so it doesn't do all the tables at once. you could schedule re-indexing of the tables that are very transactional weekly and the other tables monthly.

You can also create a job that sends the results of DBCC SHOWCONTIG to a file that you could review weekly. Fragmentation isn't somthing that happens overnight. It's something that happenes over time that depends on the frequency of Insert, updates and deletes.

Because of auditing like SOX and SAS70
I consider monthly security audits to be part of my maintance. Are you SOX complient? If so you should have a review process of all your SQL Server accounts.


- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top