Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

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:

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

Gravatar

# re: Database maintenance routines

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

Why?
7/2/2004 12:43 PM | Justin Pitts
Gravatar

# 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
Gravatar

# 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
Gravatar

# re: Database maintenance routines

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

lol
7/2/2004 3:29 PM | Greg
Gravatar

# LinkDemand 2004-07-05

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

# Database Maintainance Routines

Database Maintainance Routines
7/6/2004 2:00 AM | Harshal Mistry
Gravatar

# 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
Gravatar

# SQL Server: Database Maintenance Routine

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

# Question for bloggers

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

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

# re: Database maintenance routines

I decided to just edit this one. Updates complete.
9/23/2004 3:38 PM | Tara
Gravatar

# 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
Gravatar

# re: Database maintenance routines

You'll need to use the GRANT function to provide access.
6/10/2005 9:40 AM | Tara
Gravatar

# 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
Gravatar

# 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
Gravatar

# re: Database maintenance routines

Use nvarchar(256) instead of sysname then.
7/18/2006 10:34 AM | Tara
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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.

Powered by:
Powered By Subtext Powered By ASP.NET