I'm working with SQL 2000 and am just learning about Maintenance Plans (MP). They seem convenient, but after some time, I'm wondering if they're the best approach long-term. Here are my experiences.
Using the MP Wizard, I created a plan with tasks from all the dialogs:
I was puzzled to find 4 jobs were created, each with just 1 step, and staggered starting times. I expected to find 1 job with 4 steps. So, brimming with confidence, I did just that. I combined all 4 into 1 job, deleted the 3 other MP created jobs, and checked for any job-specific details in the code. However now when I open the MP, I get this pop-up:
"One or more of the jobs created for this plan has had additional steps added to it. It is not recommended that jobs created by the maintenance plan be modified in any way."
Okay, fair warning. Yet it appears the job and all steps run successfully, both on demand, and on a schedule. So now I'm wondering if jobs always need a MP. If I don't mind working with xp_sqlmaint syntax, it appears the only thing I'm giving up is the MP history. But I expect job history and '-WriteHistory' will minimize that loss.
I searched BOL, this Forum, and Google, and found a couple articles. One author preferred the ease of the Wizard, another preferred the control and added features of T-SQL, but both created a MP in their examples. So I'm hoping some experienced DBAs can advise.
If I create a job with multiple steps, and no MP, are there important things I give up or problems I create?
Is this approach a bad idea in SQL 2005?
At this stage, I don't need replication or other advanced features. Just simple database maintenance.
I am migrating the SQL server 7 to SQl server 2005 (side by side upgrade).... I copied all the jobs including the maintenance jobs .is that the right way ? do we need to copy the maintenance jobs as well ??? or do we have to create the maintenance plan which will create the new jobs itself ????? please Guide ....... its urgent ....
I was once told that I was not to use the GUI to setup a maintenance plan. What the person said was that I needed to setup a different plan to do each tab of the GUI instead of going through the tabs and making sure the times do not overlap. Has anyone ever heard of this or is this an old wise tale told by only one person? I am using version 8 of SQL on XP machines with 8 connections to that database and need to back up the database, clean it up and everything the GUI has, so teach me the correct ways and tell me why, thanks!
We've upgraded our SQL7.0 user db to a SQL2000 system using sp_detach_db and sp_attach_db stored procedures. And we'd like to transfer our sched jobs from a SQL 7.0 to SQL2000 systems, if the jobs are created outside a maintenance plan, we can script them out and run the script to recreate them on the 2000 system. But if the jobs are within a maintenance plan, we would have to recreate these plans manually on the 2000 system, is this the only option we have ? Any one has a better solution ? I think "restore msdb from abackup" should work better in this case?
In SQL2000, there is a new DTS task for transferring jobs between 2000 systems (same version), but I don't think it work to transfer them from 7.0 to 2000 ?? Anthony
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Hi guys. I set up a maintenance job on my SQL 2000 server to back up a database. Before the job kicks in to back up the database, I would like to set up a step to check for a file first on another box. If the file exists, continues; otherwise, terminate the job. When I create a step, how can I check whether a file from \testserver empflag.log is there? What kind of method I can use?
I tried to paste some vbscript codes in the step; however, I don't know how to use vbscript to execute the next step. Below is the vbscript codes - please see bolds.
Option Explicit On Error Resume Next Dim objFso, LogFile, dteStart, sFolder, WshShell sFolder = "\botest3ackupflag.log" Set objFSO = CreateObject("Scripting.FileSystemObject") Set WshShell = WScript.CreateObject("WScript.Shell") set LogFile = objfso.getfile(sFolder)
dteStart = Now()
Do Until DateDiff("n", dteStart, Now()) > 5 OR Not objFSO.FileExists(sFolder) Wscript.Sleep 10000 Loop
If objFSO.FileExists(sFolder) Then <don't know what to put here to execute the next step> Else WshShell.logevent 1, "Time Expired - Cannot Find Flag.log" <also don't know what to put here to stop the job> End if Wscript.Quit Set objFso = Nothing Set sFolder = Nothing Set dteStart = Nothing Set dteStart = Nothing Set WshShell = Nothing
hi.. i have one job has 50 steps. every step check one database. so is not relate it. if the step one success go to next one. if fail still go to next one.. so if one of them fail. you cant see it in enterprise manager.. my question is... is there a way that i can get a warning if one of them are fail? anything beside modify the sp i am running..... basically all the step are same.. (exec sp_xxxx 'database name') just alot of them. thanks
1. There are 2 steps in one job. Even if one is succeed then EXECUTE @IRetval = msdb.dbo.sp_get_composite_job_info @schedule_id = 8 IF (@IRetval <> 0) BEGIN SET @BResult = 0 --Failure END ELSE BEGIN SET @BResult = 1 --Success END gives success.
Do I need to create two jobs if I need to address whether all the steps are successful?
2. If the job fails then to which table does error code and error description is populated?
I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before).
This field in SQL2005 has allow nulls.
I've tried to add an empty string " " to the variable before being saved, but this still doesn't work.
hii got a query that takes about 14 minshere it isselect BDProduct.ProductCode,BDProduct.ProductName,SALTer ritory.TerritoryID,SALTerritory.TerritoryName,SALAccount.AccountID,S ALAccount.AccountName,sum(SalesNetFact.Qty2) as Quantity,sum(SalesNetFact.bonus) as Bonusfrom SalesNetFactinner join BDProducton BDProduct.ProductID=SalesNetFact.ProductIDinner join SALAccounton SALAccount.AccountID=SalesNetFact.AccountIDand SALAccount.BranchID=SalesNetFact.branchidinner join SALTerritoryon dbo.SALAccount.TerritoryID = dbo.SALTerritory.TerritoryIDand dbo.SALAccount.BranchID = dbo.SALTerritory.BranchIDgroup by BDProduct.ProductCode,BDProduct.ProductName,SALTerritory.TerritoryID,SALTerritory.TerritoryNa me,SALAccount.AccountID,SALAccount.AccountNamethe SalesNetFact table has BranchID,TransactionLineID as primary keythe BDProduct table has ProductID as primary keythe SALAccount table has AccountID,BranchID as primary keythe SALTerritory table has TerritoryID,BranchID as primary keyi have no other indices in any of these tablesthe execution plan shows that the sort step takes 96% cost,that is themost expensive step,it is done after all the joining steps and beforethe group by stepfor the sort step:the estimated row count is 1552242,the argumentsare:ORDER BY [BDProduct].[ProductCode]asc,[SALTerritory].[TerritoryID] asc,[SALTerritory].[TerritoryName]asc,[SalesNetFact].[AccountID] asc,[SALAccount].[AccountID] asc)any ideas about how to improve this sort step
I have two SSIS packages that I want to run in one SQL Agent job as two individual steps. The two packages run fine when they are in separate jobs. However, when I run the job conaining both SSIS packages (under the same proxy), the first SSIS package starts, but hangs in the middle.
I then tried setting the DelayValidation flag to True as suggested for a similar issue in another thread from this forum. After changing the DelayValidation flag to True for all containers and tasks on the second SSIS package, the first SSIS package ran completely through sucessfully, but the job continued executing for hours and the second SSIS package never started. I finally killed the job.
Any ideas as to what is the problem here? I have logged to the event viewer and see that the first package completes sucessfully. They run successfully in separate jobs, but I can not get them to run together within the same job without hanging.
I have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan. Thanks!
I have been given a SQL Server 2000 database to look after which has been set up with a Database maintenance plan. The plan is set to backup the complete database and the transaction log. The backups are written to the local disk correctly but the plan is also set to remove any backup files (both database .BAK and transaction log .TRN) that are over one week old. Complete database .BAK files are written daily and the .TRN are written every hour daily. The .BAK files are removed ok automatically but the .TRN files are not - they are just slowly filling the disk. There does not seen to be anything different between the way the main database and the transaction log is set up in the maintenance plan.
I have a strange thing in one of our Maintenance plans.
On the first tab where you check which databases you're including in the plan I have (say my database name is CAT) a 'CAT' and 'cat' database listed and the one chosen is 'cat'. However my database in all other views shows up in all caps. (even when I do an sp_helpdb)
The backups look like they're working, etc. but it just seems weird. If I go to create a new plan it only gives me the one option 'CAT' which is really what's there. I'm new and I'm thinking the database at one time was 'cat' and this is when the maintenance plan was created. Then it was renamed to 'CAT' and there's the two db's showing in the old mainenance plan.
What would you do? Create a new plan with "CAT" and just get rid of the old one with the weird 'cat' and 'CAT'?
I've created a database maintenance plan to backup a database, but it just isn't happening, am i missing something. The maintenance plan appears to be created successfully.
hi everyone.. this is a little bit weird .. i am trying to make a backup strategy. i am using sql2005. when i go to maintenance plan. right click >> new maintenance plan... nothing happens.. if i go with the maintenance plan wizard everything goes normally. after doing the backup, if i right click on it and press modify , nothing happens too. what i mean by nothing happens is that it doesn't open the "design view". the back up is doing normally.. but i need to set a range of 5 days before overwriting the oldest backup. any idea what is going on or what am i missing?! thank you
Created weekly (full backup) and a daily (differential backup) Maintenance Plans using the wizard. I formatted the server, installed the OS and SQL. Restored the full backup (No Recovery Mode), then restored the differential backup (Recovery Mode), tested and all worked well.
Then I noticed the original Maintenace Plans I created (Full and Differential) were gone; makes sense as I had formatted the server.
Is there a way to create a Maintenance Plan file or script that I can save and just add back to the server??
Hope that makes sense. Any help appreciated. Kerry
Could someone advise and/or correct me with my thoughts on how I would do my db maintenance plans?
(db's on SQL2000 as 'full' model)
Backups: 1) Daily Transaction log backups scheduled frequently enough. 2) Full Backup scheduled daily. Good way to start I presume ;)
Maintenance: Would be scheduled daily if possible, on non-production hours and if not colliding with daily full BU schedule.
3) Full DB reorg data&indexes. 4) Update Query Optimizer Stats (although 'Auto Update Stats' is on) 5) Shrink the logfile (ldf) as I presume this will have grown due to previous maintenance jobs. 6) If 5 ok, alter ldf filesize back to new allocated size.
Our products are VB6-based interacting with MSDE2000 i.e. none of our clients have EM. We have auto-backups performed twice a day by default and we encourage people to keep it to a least 2 per day.
Yesterday, one of our clients reported a problem. Upon investigation, I did a DBCC CheckDB WITH ALL_ERRORMSGS which returned the following:
Object ID 1461580245, forward row page (1:159), slot 50 points to page (1:234), slot 43. Did not encounter forward row. Possible allocation error.
So after reading a lot of posts and blogs from Paul Randal, I proceeded cautiously to copy the DB then perform a DBCC CheckDB REPAIR_REBUILD which had no effect, then a DBCC CheckDB REPAIR_ALLOW_DATA_LOSS which also had no effect. Then, I determined which table was at fault via DBCC CheckTable and I exported its data to a blank table where I discovered the missing data row and corrected for it manually. End of story.
Not a funny situation. The worst part is that this defect may have been there for a very long time, meaning that restoring the latest backup would not have helped the situation. I now realize that relying on backups alone is a huge no-no.
So, having been scarred into reality, I would like to install an automated maintenance plan. I'm used to doing it on my personal station using EM but, as stated above, I can't do that for the clientele. I was thinking about simply shrinking the databases and then doing a DBCC CheckDB WITH ALL_ERRORMSGS on all of them before performing a backup. Would this be a complete enough procedure or should I be doing something else? My understanding is that this will verify index structure and data integrity, and not attempt to repair anything which is uncovered.
Any thoughts of wisdom would be greatly appreciated.
I have created maintenance plan. It was working fine. When I tried to edit it gives me error. How to sollve this error? Please help me.
Cannot show the editor for this task.
ADDITIONAL INFORMATION:
Value of '11/8/2007 12:00:00 AM' is not valid for 'Value'. 'Value' should be between 'MinDate' and 'MaxDate'. Parameter name: Value (System.Windows.Forms)
Hi All, As a part of creating a maintenance plan i want to copy the backfiles from the server to another machine.How can i achieve this .Please help its very urgent.
I was wondering if there were any best practices for creating maintenance plans? Im just getting started into the DBA world and have been delegated the task of creating maintenance plans for our 8 SQL servers.
Right now, our backup policy is Fulls on Saturday, differentials Monday-Friday.
Also, since im new, if you defragment the database and rebuild the index, does that have the possibility of "breaking" anything?
Just looking for some good articles, or anything to get me started on best practices.
I am relatively new to SQL Server 2005. I have gone ahead and created a maintenance plan which backs up all our databases. This plan is scheduled to run every night. The problem is that each time the plan runs, new backup files are created which quickly uses up valuable disk space.
How do I set up SQL Server 2005 to only create one set of backup files and overwrite any existing files when the maintenance plan is run? I tried playing with the "backup set will expire" settings, this did not seem to do anything.....
I have a SQL 2000 server that has a small but very important database (about 5GB). The current maintenance plan does trans logs every hour and full every day. Currently they are to file on the same drive array. I would like to send them to a share on another server just to be really safe.
Would it be better to
1. Redirect the maintenance plan so that trans logs and backups go directly to the share
or
2. Keep the maintenance plan back ups to the current location and write a script that runs every hour and copies the .bak files to the share.
Also, since the database is so small should I just do full backups every hour instead of transaction?
Hi Recently I have moved sql 2005 from one box to another with bigger hdd space and more memory. Since then I am getting the attached error message when I Execute all the job.
In the Enterprise Manager of SQL Server 2000 I have set up a maintenance plan which rebuilds my indexes. I've stuided the documentation, and from what I've learned what happens behind the curtain is that several DBCC REINDEX commands are being issued. Question: If I have 20 tables and 40 indexes: Will SQL Server do the maintance plan in 1 single transaction, or will it divide the it up to eg. 20 or 40 transactions?
We are using a database maintenance plan to backup and reindex our db's. Up until the end of last month this was working perfectly - however now it has stopped deleting the old backups (even though we have checked 'Delete files over 1 day old').
Does anyone have any ideas as to why they are now being deleted - and how we can remove them automatically - has something been corruped? Would it be a case of creating a new maintenance plan?
I've set up a maintenance plan to back up the database at a certain time of day. But it never seems to work, which I don't understand. I'm the creator of the database as well as the DBA with all rights. I can create a manual backup, and I have full access to the default backup directory on SQL server. I've also tried scheduling automatic backups, but it won't work from there either. I've tried pretty much everything to get this to work. I've checked the Maintenance Plan History for that particular Maintanance Plan, and it shows no evidence of failure--or any history of activity at all--nothing. It just ignores the maintenance plan entirely. What could be the problem here?
I have created the backup plan for all the 40 user databases on one of the production servers.Scheduled to run the backup at Sunday 2.00AM every week on to a different drive on the same server.If all the databases are backed up at the same specified time,will it effect the performance or anything?Under the maintenance plan wizard,there is no option to select different times for different databases.Total size of all the databases will be 10GB.Does it take the backups one after another DB or all at the same time which is specified?Any idea on potential problems? Thanks!!
