Database maintenance routines

<< Delete Backup History stored procedure | Home | Missing objects in MSDB database >>

Database maintenance routines

EDIT: Updated the link to the new version of isp_Backup.

Over the past couple of weeks, I have posted various database maintenance stored procedures. Here's a listing of them:

isp_Backup - full, differential, and transaction log backups
isp_DBCC_CHECKDB - perform integrity checks on all databases
isp_ALTER_INDEX - defragments and rebuilds indexes
isp_UPDATE_STATISTICS - performs update statistics on each table in the database
isp_Restore - restore newest BAK file in specified directory

Do not put these in the master database. An Admin database should be created where non-application specific objects are put. All of the stored procedures are designed so that they do not need to be in any particular database.

Use them at your own risk. If you don't use them, at the very least create your own. Don't use the maintenance plan wizards or the sqlmaint utility!

Print | posted on Friday, July 02, 2004 10:50 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

# re: Database maintenance routines

" Don't use the maintenance plan wizards or the sqlmaint utility!"

Why?

7/2/2004 12:43 PM | Justin Pitts

# re: Database maintenance routines

First, what's their to learn when you use wizards? You aren't very close to the process.

Second, have you ever tried to figure out why one was failing or not working as desired and it didn't give enough information in the SQL Server Error Log, in the job history, or in the maintnenace plan history? We often get questions on SQLTeam.com about a plan that isn't failing but also isn't working properly.

I'd say for developers that aren't DBAs that the wizards are a start in setting up the DBA routines. But there is more to DBA routines than what is available in the maintenance plans. And there's also stuff that shouldn't be turned on in there.

Besides, would you create a web site using a wizard? It's much more flexible when you create it on your own.

7/2/2004 12:51 PM | Tara

# re: Database maintenance routines

I think you can sum it up by saying this.

The Maintenance Plans SUCK!!! They were provided so you could have maintenance on your database if you didn't know anything about how SQL Server works. They have zero error reporting.

They SUCK!!!

Yep, that about sums it up. They SUCK!!!

7/2/2004 3:02 PM | Derrick Leggett

# re: Database maintenance routines

Derrick, don't hold back... how do you really feel?

lol

7/2/2004 3:29 PM | Greg

# LinkDemand 2004-07-05

7/5/2004 7:52 AM | William.Blog()

# Database Maintainance Routines

Database Maintainance Routines

7/6/2004 2:00 AM | Harshal Mistry

# re: Database maintenance routines

Derrick,

Really, you shouldn't be so blase...

You should really try to be a little bit more passionate about things....

7/6/2004 12:00 PM | Brett

# SQL Server: Database Maintenance Routine

7/29/2004 12:37 PM | Khurram Aziz

# Question for bloggers

7/29/2004 3:18 PM | Ramblings of a DBA

# re: Database maintenance routines

> The Maintenance Plans SUCK!!!

Sam Kinnison didn't die, he changed his name and became a DBA! :-)

7/29/2004 1:15 PM | Lee Dise

# re: Database maintenance routines

Tara, dunno if you just want to change the URLs on these to point to your newer versions? (Don't suppose it matters, you've edited the old ones to indicate there are new ones - but you see I was just doing a TEST!)

K

8/15/2004 7:17 AM | kristen

# re: Database maintenance routines

Once I get all the updated versions out there, I'm going to create a new list in a blog.

8/15/2004 8:18 PM | Tara

# re: Database maintenance routines

I decided to just edit this one. Updates complete.

9/23/2004 3:38 PM | Tara

# DBA routines

What kind of routines would you suggest to restrict access on specific tables to users as a DBA

6/9/2005 9:24 PM | Arundhathi

# re: Database maintenance routines

You'll need to use the GRANT function to provide access.

6/10/2005 9:40 AM | Tara

# re: Database maintenance routines

how do i edit a Database Maintenance Plan using the wizard
please reply to my id asap :
chintan_gandhi@infosys.com

10/25/2005 6:23 AM | Chintan

# re: Database maintenance routines

I'm currently training on my Windows XP Pro machine running SQL Server 200 PE SP4. When I try to execute your maintenance stored procedures, I'm getting error with reference to SYSNAME:

Server: Msg 2715, Level 16, State 3, Procedure isp_Backup_LS, Line 0
Column or parameter #-4: Cannot find data type SYSNAME.
Server: Msg 2715, Level 16, State 1, Procedure isp_Backup_LS, Line 0
Column or parameter #-5: Cannot find data type SYSNAME.
Parameter '@DBName' has an invalid data type.
Parameter '@cmd' has an invalid data type.

All other variables appear to be declared without error.

Newbie ... Ken

7/18/2006 10:32 AM | Ken

# re: Database maintenance routines

Use nvarchar(256) instead of sysname then.

7/18/2006 10:34 AM | Tara

# re: Database maintenance routines

Hi, I'm a web developer trying to be a DBA! If I shouldn't use a maintainence plan, what should I be doing?

Thanks in advance..

8/15/2006 2:57 AM | Manou

# re: Database maintenance routines

Manou, I provided my alternative to the maintenance plans in this blog post. Check out the stored procedures.

8/15/2006 9:25 AM | Tara

# place without abuse

I haven't gotten anything done , but whatever. Today was a loss. So it goes. I've just been sitting around not getting anything done. I can't be bothered with anything these days, but I don't care.

10/25/2006 10:55 AM | place without abuse

# re: Database maintenance routines

Hi tara,
I am thinking of using the isp_Backup_TLog Script to run on the weekend to backup the logs. Will this truncate all the logs. The recovery Model is set to simple.

Regards,

11/8/2006 4:23 AM | Anwar

# re: Database maintenance routines

Anwar,

You can't backup the transaction log of a database in SIMPLE recovery model.

11/8/2006 7:18 PM | Tara

Comments have been closed on this topic.

Ramblings of a DBA

My Links

Advertisement

News

Archives

Post Categories

Work