Does any body have any good recommendations for maintenace plans?
Here are few questions I have.
When should indexes be re-indexed?
What should be done first? Reorganize indexes or rebuild indexes?
How often backups should be done and what kind?
How often should database statistics be updated?
Do database statistics need need to be updated on system databases?
How often should a database integrity check be done?
How long should history be kept for?
What is a good order for tasks should be done?
Does any body have any good recommendations for maintenace plans?
Here are few questions I have.
When should indexes be re-indexed? What should be done first? Reorganize indexes or rebuild indexes? How often backups should be done and what kind? How often should database statistics be updated? Do database statistics need need to be updated on system databases? How often should a database integrity check be done? How long should history be kept for? What is a good order for tasks should be done?
I made a backup maintenace plan in sql7.0, but it doesn´t work. The message included on the event logger is:
SQLServerAgent SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Teste'' (0x219C5CB03C11D4119D8F0020188D7311) - Status: Failed - Invoked on: 4/17/00 3:25:00 PM - Message: The job failed. The Job was invoked by Schedule 12 (Schedule 1). The last step to run was step 1 (Step 1).
And before this message there is another one that can be making something goes wrong:
Someone can help me telling me the reason that is generating this kind of error and doesn´t work. I have do the same think in others two server and there it works ok. Thanks in regards
I have set SQL Database Maintenance plan in SQL Server 2000. it ws working fine but now it didnt work and fails everytime. can anyone advise me how to identify the reason why its not working.
I am trying to "Edit Recurring Job Schedule" in a new maintenance plane,however in start and end times there is no option or display for AM or PM toselect. I have seen on other systems an available AM/PM in the time window.I tries entering the time in 24 hour format, but that was not recognized.thank you
My question is recommendation for setting up maintenace plans that are safe and complete. What do you recommned I do for setting up a plan that backs up, and deletes old files older than 5 days. Should I do anything else and why?
Our Database Maintenance Plan has stopped backing up the database. I can manually backup the database but the plan does not work. We tried creating a new plan but the new plan is not backing up either. Any ideas would be appreciated. Thank you for your help!
Few days before, I had setup Maintanance plan for taking backup in 2005. It was running nice.But suddenly it stops automatically. It is not creating log file too.
Does anyone faced this type of problem ? Or does anybody knows the workaround for this. Thanks in advance
Hi again,I have setup a maintenance plan schedule, my question is; can I create anadditional plans located on another network location ?In my attempts the only locations displayed are those on the current server.thank you
I have two schematically identical databases on the same MS SQL 2000 server. The differences in the data are very slight. Here is my problem: the identical query has totally different execution plans on the different databases. One is (in my opinion) correct, the other causes the query to take 60 times as long. This is not an exaggeration, on the quick DB the query takes 3 seconds, on the other DB it takes 3 minutes. I have tried the following to help the optimizer pick a better execution plan on the slow db:
rebuild the indexes dbcc indexdefrag update statistics
I CAN put in a hint to cause the query to execute faster, but my employer now knows about the problem and he (and I) want to know WHY this is happening.
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).
I am new to SQL Server 2000 & need ya all's help!! I am trying to set up a database maintenance plan to back up databases & transactional logs. If I do a full backup once a week & a transactional backup every day...will I be safe enough to have enough backups to be able to restore to any point of time by restoring the full backup & the transactional logs upto that point? In other words, I am asking what are the points to consider & what should be a decent backup plan? Do transactional logs take stored procedureal changes also?
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.
HiCan you give me sone pointers to where I can get more information aboutthe various operations like index seeks,Bookmark Lookups,ClusteredIndex Scan in an execution plan.ThanksRagu
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
We have been asked by a customer who uses our software product whether they should upgrade from SQL 6.5 to SQL 7 or SQL 2000. They have one server with 250 clients accessing SQL Server.
I used to think that upgrading to SQL 7 would be cheaper in light of the complaints I've heard from administrators about the cost of going to SQL 2000's new pricing model, so I took a look at Microsoft's web site. Navigating around their web site looking for licensing info is always an adventure!
I saw only SQL 2000 (non-upgrade) pricing on their web site, so I'm assuming that buying SQL 2000 and then upgrading to SQL 7 would result in the same pricing structure whether a customer upgrades to SQL 7 or SQL 2000 (minus the discount for upgrading). I see that a processor based license ($2499 per processor standard edition) with its unlimited cals would be cheaper for a company with 250 clients, since the cost of cals for 250 clients with the standard edition server-based pricing would be prohibitive.
So if the pricing is the same, the decision to upgrade to SQL 7 or SQL 2000 would rest upon features available and when SQL 7 will be retired.
Does anyone know how long Microsoft will support SQL 7? (I'm defining support as until they stop providing the patches and hotfixes, since the product will no longer be actively supported. The information I saw on Microsoft's web site made it seem like SQL 7 will be supported for a long time yet.
Does anyone know how long Microsoft will support SQL 6.5? That will give me an idea how long our customers will have to upgrade?
What would you recommend to your customers so as to benefit them most?
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'm trying to get an application finished that works like Query Analizer in terms of returning query plans and statistics.
Problem the co-author is having:
>In using ADO to connect to SQL Server, I'm trying to retrieve multiple >datasets AND statistics that are usually returned via the OnInfoMessage >event. For those that are familiar with SQL Server, I need the results >returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options. >Anyone had any luck doing this before?
Can anyone shed any light on this please?
Thanks.
BTW if anyone wants to take a look at the tool so far - to see what I'm delving into: http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1
