During my maintenance plans every night, my transaction log for a 17GB database grows to about 9.5GB. I am running transaction log backups every 2 hours, and my transaction log stays around 300MB during the day. However, every night when the optimization job runs, this explosive growth occurs. I have the optimizations set to Reorganize data and index pages, changing free space to 10%, and remove unused space from database files to 10%, shrinking when they grow beyond 50MB. This database handles cash register operations which are not active during the maintenance plan.
I'm the dba for a large company and have setup many maintenance plans for our sql servers. One of the options I have set is to delete old backups of databases and transaction logs after 2 days. For some reason, the old database backups are being deleted, but the old transaction logs backups are not. I have them being put in the same directory. Anybody have any ideas why this is happening and what if any fix is available?
SQL7: I have added a Maintenance Plan to backup to 4mm dat tape the master and msdb SQL databases as well as another database relative to our application called WISE. This works fine; however, it appears to always append to the media as opposed to overwriting (preferred). Any help would be appreciated.....
I am going to set up maintenance plans on all our SQL servers (7.0 and 2000). I have found several 'tutorials' on how to do this, but no one is describing the options in detail. Can you guys/gals please help me out? We have alot of small databases and some medium (1-2GB).
We have Veritas' Backupexec running in our Enterprise and the Veritas Install actually installs MS SQL Server MSDN on each Server in the Enterprise.
It looks like it also sets up a default Maintenance plan within each of the MSDN Instances.
I guess my question is.. Can I manage the Maintenance Plans on these MSDN Instances via the SQL Server EM GUI from my desktop?? Seems like when I look at the Maintenance plans alot of the options are greyed out or not available. What I am trying to do is modify one of the maintenance plans to have the backups deleted after one week (One of the Instances has been running a complete backup on the Backupexec Databases for a year and there are a years worth of backups on the Server) but the option to "remove files older than" is 'greyed out' ??????
Does anyone get any issues creating "Backup" jobs as a Maintenance Plan when specifying the backup location as a UNC path (e.g. "\backup_bladeBACKUPS")?
For some reason, if i try using the UNC path for a 1-time backup, it works, but when I am trying to put it into a scheduled job, it does not 'seem' to perform the Backup step.
I created several Maint.Plans before installing SP2. Now I need to modify them and I get the following error. I cannot even Create new ones, because of the Enumerate error. Please advice if this error is due to the same issues mentioned on this blog.
When replying please cc me at Camilo.Torres@bellsouth.com
Thanks
TITLE: Microsoft SQL Server Management Studio
------------------------------
Enumerate target servers failed for Job 'Daily Maintenance Plan 1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+target+servers+Job&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476
Is there a way to find out what Trans-SQL is being executed by a Maintenance Plan. More specifically, if I select Attempt to Repair on the Check Database Integrity screen, I know it is executing DBCC CheckDB, but which repair option is it using (repair_fast, repair_allow_data_loss, repair_rebuild)?
A year ago one of our SQL Server 6.5 servers was upgraded to SQL Server 7.0 sp1. My compatibility level still shows 6.5, however. The SQLAgent has been using the 'localsystem' account up until earlier this week. I changed the login to be a domain account with System Administrator permissions and removed the SA permissions from the BuiltinAdministrator group. (My ultimate goal is to limit the access NT Administrators have within my SQL databases.) All of my scheduled jobs run without error except the maintenance plans. (All jobs have an owner of sa.)
The errors that I receive are permission errors-not being able to get into tables on the MSDB database. However, if I open Query Analyzer with the SQLAgent domain account and perform a select on one of the tables in MSDB, it is successful. If I give the BuiltinAdministrator account the SA permissions again while still keeping the SQLAgent using the new domain account, the maintenance jobs succeed.
Is this an upgrade problem since I do have other SQL 7.0 servers that don't have this problem? How can I correct this?
Hi, I'm new to SQL 7 (and fairly new to SQL Server), can anyone help with these basic questions on database maintenance plans generated by the wizard: (1) Scheduling - can SQLServer handle say a REORG running at the same time as a backup against the same database? This should never happen, but what will happen if say a backup is due to start before a REORG has finished? To try & simulate this 'problem' I've run REORGs and backups at the same time & have yet to encounter errors I presume SQL locking handles this OK. (2) Database integrity checks - (a) any comments on the wisdom of checking 'repair any minor problems'? Anyone had any problems with this? (b) While integrity checks are running do they take enforce a consistent view of the data? (I think this is probably the case as my reading of books online indicates that DBCC takes shared locks for the duration) (3) Backups - does the 'VERIFY INTEGRITY' option have any impact on the live database? (My reading of RESTORE VERIFYONLY indicates it doesn't) Thanks....
I used the wizard to create maintenance plans on my SQL 2000 servers. Part of the plan fails (checking data and index linkeage) when the job runs in off hours. It fails because it says the DB is not in single user mode. Shouldn't something in the plan take care of this. The wizard gives you no options. Or is this a bug. These jobs ran fine in SQL 7
Hello everyone, I'm new to DB Maint Plans, so let me apologize upfront. I've taken over a system from a DBA who is no longer working here, and he set up Maint Plans for all of the existing DBs. The plans show up in the Enterprise Manager under "Management->Database Maintenance Plans" like they should, but there are also entries in the "Management->SQL Server Agent->Jobs" area. When I set up a new DB Maint Plan for a new DB, it seems to be working fine, but I don't have any corresponding entries in Jobs. Did the other DBA set these up manually? Does anyone know why he might have done this? Is it needed? The jobs and job steps look like the following:
I am currently using sql server 2005 standard edition. I just completed a maintenance plan using the wizard to perform a full backup nightly of a user database. I noticed there was no option to delete backups greater then a number of days old like we have with plans in sql server 2000. Is there a way of doing this through the plan in sql server 2005?
Ok, I must be blowing my mind here. I created a maintenance plan to run on Sundays at 10PM. This plan failed due to Tempdb running out of space. I rectified this and scheduled the maintenance plan to run the next day. This did not work either. The underlying jobs just did not kick off. Nothing in the event viewer or SQL Logs.
The reason, the jobs that this plan created also need to be changed. Has anybody encountered this?
Further, I have a SQL Express on my laptop, which I use for maintaining our db servers(SQL 2005 Standard Version). When I alter the portions of the Maintenance plans(e.g. I changed the selection of the databases for "Index rebuilding" part) using my work station connected to a Production/QA server,when I try to save the changes it gives me an error: "Failed GUID........and some 16 digit number most of which are all XXXXX". But if I change the schedule it does not give an error but it does not change it either.....
I am really lost. I never had such problems on 7 or 2000.
I am trying to set up a maintenance plan is SQL Server. I set one up to query one of my very small tables for testing purposes. I made sure that I have the server entered in correctly and also verified that I needed to make sure that I have both TCP/IP and Named Pipes both enabled. However, everytime I set something up and try to execute it. The plans ends up failing. This isn't a hard process but something else isn't setup that needs to be. Does anyone have any ideas pertaining to this.
Is there a special credential that needs to be setup for this?
We have msde 8.00.760 - which is reportedly sp3, on a couple servers and all are having a problem. One is on 2000 and the other on 2003. Looking at the maintenance plans - the full backup directory and "remove files older than" box are blank. I've looked at quite a few threads - microsoft doesn't have anything specifically about this problem. Only that pre sp1 you can't edit a maintenance plan ON a server, have to remote in. Found one person who says to create a registry for the default backup path - but I just can't believe that this kind of behavior would go on for 3 service packs - and I don't see any mention of it in the readme for sp4.
I'm not even sure where to post this, but has anyone had a problem with maintenance plans disappearing?
I mean the SSIS package and the job just disappearing....?
I have four servers. Two dev and two prod. I created a pretty simple maintenance plan to backup databases. There are four tasks in the plan. One for each of four databases. Backing each up to a separate file, all in the same folder.
The plan gets associated with a job that has it run daily, every six hours, with no end date.
The plan runs just fine. Then this morning, on one of the servers, the plan and its job are just gone.
This is the second time this has happened. And it's not the same server that it happened on the first time.
I am creating a SSIS package on my workstation, and I then move the plan to the SQL server. I am able to run and see the package in Microsoft SQL Server Management Studio. However, I am looking for the location of the package on the server (its location in the files and folders on the server).
2 SQL2000 ent sp4 servers. Try to 'one step' to export all jobs and related maintenance plans from serverA to serverB, via backup msdb from A and restore to B.
After restore msdb to serverB, I saw all jobs and related maintenance plans, but the jobs are not runable.
Do I have to save jobs scripts one by one from A, then rerun on B one by one?
This error msgs persist after installing SP2 Hotfixes. Any ideas?? Please see below:
TLE: Microsoft SQL Server Management Studio ------------------------------ Exception has been thrown by the target of an invocation. (mscorlib) ------------------------------ ADDITIONAL INFORMATION: Creating an instance of the COM component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS) ------------------------------ Creating an instance of the COM component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)
I am running a SQL maintenance job on a 40 GB database which performsoptimizations by re-orginizing data and indexes pages. After the jobis finished, a separate job peforming a SQL transaction log backup isrun on the same database, which produces a 30 GB transaction log backupfile. Is there any way to turn off logging during the maintenanceplan, so that when the transaction log backup occurrs it will notproduce a large backup file?
We created maintenance plans for Backup, we configured as:
1. Backup set expires after 2 days. (but we still see backup files are at the location from day one) 2. There is Overwrite and Append in backup file settings. what eaxactly overwrites means, in case we set up expire the backup set after 2 days.
If your principal fails and your mirror assumes the main role and it is in Full recovery mode (which it has to be in Standard edition), then the log files will grow and grow until a transaction backup and shrink is executed.
I have maintenance plans running daily (and hourly for transaction log backup/shrink) on my principal, but nothing on my mirror.
Do I need to set up the same maintenance plans on my mirror server???
I've searched high and low for documentation on this to no avail.
Basically my goal is to create a maintenance plan in SQLSERVER2005 via the command line. I need to create this plan in a way that it can be seen in the list of Maintenance Plans in the Management Studio Interface. I went into the SSIS designer and created my plan. I now have a DTSX file. I tried the dtutil.exe utility, however i never saw my maintenance plan in the list of plans.
I ran dtutil.exe and did /FILE to /SQL but i don't see the plan listed or in a way a user could modify it, which is of the utmost importance to my clients.
How do i get my file to turn into a real running Maintenance Plan that is seen in the list of Maintenance Plans via the Management Studio Interface and is editable by clients?
Other things to keep in mind, i'm attempting to create these via Installshield MSI installer. So i need to do it via command line, or file system-wise. No interface or user interaction.
Hi, I am not able to delete the Legacy maintenance plans created: for example I created a maintenance plan: "MyPlan" with: master.dbo.xp_sqlmaint and the plan created at: ManagementLegacyDatabase Maintenance PlansMyPlan
After this I tryed to delete this using SQL Server Management Studio, it is deleted (right click on the plan and select delete menu item) But when I refresh the server, the "MyPlan" is back again.
I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:
Cannot show editor for this task
Additional information:
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
I cannot rename, edit nor delete this task, Do you know what might be the problem?
I've been looking for treads with a possible similar problem but thus far nothing. I recently upgraded SQL 2000 (STD ed.) instance to SQL 2005 (STD ed.). This includes the db's. All their compatibility levels were changed to level 90 (SQL 2005). Everything seems to be working fine but when trying to migrate a legacy maintenance plan I keep getting the message, "Cannot find folder "Maintenance Plans"." even though the folder is clearly listed. Not sure where to start. I have created two virtual images and both have the same problem. Any help would be greatly appreciated. RoviWil
