SQL Server Maintenance Plans

Feb 18, 2000

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?

Thank you!
Toni

View 1 Replies


ADVERTISEMENT

SQL Server Maintenance Plans

Mar 7, 2008

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?

Any help on this would be great. Thanks

View 7 Replies View Related

What Should The Maintenance Plans Be On Your Mirror Server?

Apr 21, 2006

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???

Thanks

Ed

View 19 Replies View Related

SQL Server 2005 Maintenance Plans - Best Practises

Feb 21, 2008



Can anyone please provide me with some Best Practises for setting up Maintenance plans? IE what order should things be done in? What should be done daily, weekly, monthly, yearly... I am attempting to setup a new server and I am now 100% sure I have the maintenance plan setup correctly... expecially when I attempted to run it last night it and ran for over 8 hours and still didn't do the backup's yet. I had to reboot the server this morning to make it stop using 100% of the CPU. So please any help would be wonderful.

Regards,

Billy S.

View 1 Replies View Related

Sql Server 2005 Maintenance Plans Folder Missing

Aug 10, 2007

I've seen a few threads on this which seem to suppose the problem is on Express edition rather than Standard edition. Not so here.
This is definitely Standard edition, SP3 as seen by running
SELECT CONVERT(char(20), SERVERPROPERTY('Edition'))
SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
In SSMS
Under the Management folder there is no "Maintenance Plans" folder.
There IS a "Maintenance Plans" folder under the Legacy Folder.
I have another instance which is Standard and SP1 which looks just fine.

View 9 Replies View Related

Cannot Save Maintenance Plans (SQL Server 2005 X64, SP2, Patched To KB934458)

Nov 18, 2007

I have a SQL Server 2005 x64 bit with SP2 applied and patched to KB934458-x64-ENU (9.0.3054)
I am unable to create a maintenance plan via Management Studio or save a plan in Management Studio using the wizard.

If I just try and create a new maintenance plan, then I get the following problem:
"An OLE DB error 0x80004005 (Login timeout expired) occurred while enumerating packages. A SQL statement was issued and failed."

If I try and create any maintenance plan via the wizard, I get the following problem:
"The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed."

I've doen a bit of digging on this and had initially thought it was related to the fact that I have two instances installed on 1 server. I've since updated the MsDtsSrvr.ini.xml file to point at both instances, as I thought that might have been causing the problem. That hasn't helped at all. Edited example of xml file is below:

<?xml version="1.0" encoding="utf-8"?><DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown> <TopLevelFolders> <Folder xsi:type="SqlServerFolder"> <Name>MSDB SQL Server Instance 1</Name> <ServerName>ServerNameInstanceName1</ServerName> </Folder> <Name>MSDB Server Instance 2</Name> <ServerName>ServerNameInstanceName2</ServerName> </Folder> <Folder xsi:type="FileSystemFolder"> <Name>File System</Name> <StorePath>..Packages</StorePath> </Folder> </TopLevelFolders> </DtsServiceConfiguration>

Any suggestions would be most welcome!

View 4 Replies View Related

SQL Server Admin 2014 :: Correcting Server Connection In Maintenance Plans?

Aug 11, 2015

In SQL 2008 R2, if we clone an environment including SQL server, the maintenance plans retain a connection string to the source/original server they got cloned from and are not editable. But, I was able to use a work around by editing them in BIDS and saving them back on the server. But now with 2014, I am facing two issues:

1.I still can edit the package to correct the server connection, with SSDT; but the option to save back to the server is not available any longer!

2.I used to be able to see all my plans under SSIS in 2008 R2 but not in 2014 now. Although, they are listed in SSMS!

View 1 Replies View Related

SQL Maintenance Plans

Feb 20, 2003

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

View 1 Replies View Related

Maintenance Plans

Aug 22, 2005

Hi,

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

Thanks//Stefan

View 3 Replies View Related

Maintenance Plans

Feb 15, 2005

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' ??????

View 6 Replies View Related

SQL 2K5 Maintenance Plans

Feb 20, 2008

Eh uhhhh where did the old '-DelBkUps 7DAYS' (delete files older than 7 Days) option go?

Is this a future product enhancement?

I fee like such a dirty noob.

View 3 Replies View Related

Maintenance Plans Over UNC

Dec 1, 2007

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.

Help please...
seethem

View 4 Replies View Related

SP2 Maintenance Plans

Feb 28, 2007

Does anyone know if Sql Server 2005 Express SP2 support scheduled backups and maintenance plans?

Looking at http://msdn2.microsoft.com/en-us/library/bb283536.aspx , it appears to...

Thanks.

View 1 Replies View Related

SP2 And Maintenance Plans

Mar 5, 2007

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

View 2 Replies View Related

SQL7 Maintenance Plans

Sep 18, 2000

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)?

View 3 Replies View Related

Maintenance Plans / Backups

Jun 8, 2000

I defined a maintenance plan to do a backup, scheduled it for midnight, but no job ever ran. Is there some other step to do?



thanks!

View 2 Replies View Related

Database Maintenance Plans

Sep 15, 1999

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

View 1 Replies View Related

Database Maintenance Plans

Mar 18, 2003

What is the recommended schedule for SQL Server maintenance plans that perform database integrity checks and optimizations?

Also, more specifically, how often should we be re-building our indexes?

Thanks for your help.

View 4 Replies View Related

Scripting Maintenance Plans ?

Nov 6, 2003

Is there a way to script out maintenance plans (and their corresponding jobs) from a server ?

View 2 Replies View Related

Maintenance Plans Fail

Apr 25, 2001

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

View 1 Replies View Related

Database Maintenance Plans

Mar 17, 2006

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:

[DBName]Full:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "W:sqldataMSSQL$P001Backup" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'

[DBName]Maint Integrity Checks:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -CkDB '

[DBName]Maint Optimizations:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

Any help or insight would be greatly appreciated!
Thanks in advance,
Cat

View 12 Replies View Related

Can't Save Maintenance Plans

Jun 17, 2008

Hi,

Whenever I create a maintenance plan either manualy or via the wizard, I get the following error message when saving it.

Does anyone know what might be wrong?

Best regards

===================================

Fehler beim Speichern des Wartungsplanes.
(Error when saving the maintenance plan.)

===================================

Mindestens ein Argument ist ungültig.
(At least one argument is invalid.)


------------------------------
Speicherort des Programms:
(Save location of programme:)

bei Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
bei Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.Save()
bei Microsoft.SqlServer.Management.MaintenancePlanWizard.MaintenancePlanWizardForm.PerformActions()

View 7 Replies View Related

Maintenance Plans & Backups

Aug 16, 2006

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?

View 1 Replies View Related

SQL 2005 Maintenance Plans

Jan 24, 2007

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 not sure if these are co-related.

View 4 Replies View Related

Msde Maintenance Plans

Dec 7, 2007

Hi,

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.

View 1 Replies View Related

Maintenance Plans Are Disappearing...

May 25, 2007

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.



Why would a maintenance plan just disappear?



Anyone?



J

View 1 Replies View Related

Location Of Maintenance Plans

May 7, 2008

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

View 1 Replies View Related

How To Export Jobs And Maintenance Plans?

Mar 18, 2008

Hi:

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?

thanks
David

View 8 Replies View Related

Maintenance Plans Error Msgs

Jun 29, 2007

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)

View 2 Replies View Related

Turn Of Logging On SQL Maintenance Plans

Jul 10, 2006

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?

View 2 Replies View Related

Database Maintenance Plans For Backup

Aug 14, 2007



Hello

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.

please advice. Thanks, Jay

View 1 Replies View Related

Creating Maintenance Plans Via The CommandLine.

Feb 5, 2007

Hello All,

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.

Please Advise.

View 1 Replies View Related

Deleting Legacy Maintenance Plans

May 29, 2007

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.

Any idea how to delete Legacy Maintenance plans?

Thanks,
Venkat

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved