Maintenace Plans
Jan 25, 2008
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?
Any input would be great
Eytan
View 3 Replies
ADVERTISEMENT
Jan 25, 2008
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?
Any input would be great
Eytan
View 16 Replies
View Related
Apr 17, 2000
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:
SQLServerProfiler
Executable: C:WINNTsystem32mmc.exe
Description: Unable_To_Set_RegKey_Val
Source: ........srceventlog.c
Line: 315
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
Sandra Lino
View 1 Replies
View Related
Aug 23, 2004
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.
View 5 Replies
View Related
Dec 11, 2005
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
View 1 Replies
View Related
Aug 28, 2007
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?
thank you!
View 2 Replies
View Related
Jul 13, 2000
Help!!!
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!
View 3 Replies
View Related
May 1, 2008
Hello All,
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
--kneel
View 3 Replies
View Related
Feb 7, 2007
I am moving from one physical server to another. I have a full working maintenance plan backing up the databases on the production server.
Is there a way to "script" / move the maintenace plan from the productiuon server to the new one easily?
thanks for help!
www.mirrorgate.com
View 2 Replies
View Related
Dec 18, 2005
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
View 1 Replies
View Related
Nov 16, 2001
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.
Any ideas would be greatly appreciated.
Thanks.
-Scott
View 1 Replies
View Related
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
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
May 14, 2002
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?
Thanks for ur help,
Edward
View 3 Replies
View Related
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
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
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
Jul 23, 2005
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
View 2 Replies
View Related
Apr 13, 2006
I have 2 SQL databases which are the same and are giving me differentquery plans.select s.* from hlresults hinner join specimens s on s.specimen_tk = h.specimen_tkwhere s.site_tk = 9 and s.location in ('ABC','WIAD')and s.date_collected between '2/1/2003' and '2/3/2006'order by s.location, s.date_collectedBoth boxes have the same configuration, the only difference is that oneof them is a cluster.The Acluster box is taking twice as long to run the query.I have run statistics on both, and the cluster is still creating abitmap and running some parallelism which the other box is not.Also, the the first step, the A1 box estimates the rows returned to bearound 80K and the actual rows returned is about 40K - subtree cost =248. The Acluster box estimates 400K - subtree cost=533!After running statistics, how can it be so off?I've also reindexed to no avail . . .any insight would be very much appreciated. We just moved to this newsystem and I hate that the db is now slower -A1:affinity mask -2147483648 2147483647 0 0allow updates 0 1 0 0awe enabled 0 1 1 1c2 audit mode 0 1 0 0cost threshold for parallelism 0 32767 0 0Cross DB Ownership Chaining 0 1 0 0cursor threshold -1 2147483647 -1 -1default full-text language 0 2147483647 1033 1033default language 0 9999 0 0fill factor (%) 0 100 90 90index create memory (KB) 704 2147483647 0 0lightweight pooling 0 1 0 0locks 5000 2147483647 0 0max degree of parallelism 0 32 4 4max server memory (MB) 4 2147483647 14336 14336max text repl size (B) 0 2147483647 65536 65536max worker threads 32 32767 255 255media retention 0 365 0 0min memory per query (KB) 512 2147483647 1024 1024min server memory (MB) 0 2147483647 4096 4096nested triggers 0 1 0 0network packet size (B) 512 32767 4096 4096open objects 0 2147483647 0 0priority boost 0 1 0 0query governor cost limit 0 2147483647 0 0query wait (s) -1 2147483647 -1 -1recovery interval (min) 0 32767 0 0remote access 0 1 1 1remote login timeout (s) 0 2147483647 0 0remote proc trans 0 1 0 0remote query timeout (s) 0 2147483647 0 0scan for startup procs 0 1 1 1set working set size 0 1 0 0show advanced options 0 1 1 1two digit year cutoff 1753 9999 2049 2049user connections 0 32767 0 0user options 0 32767 0 0Acluster:affinity mask -2147483648 2147483647 0 0allow updates 0 1 0 0awe enabled 0 1 1 1c2 audit mode 0 1 0 0cost threshold for parallelism 0 32767 0 0Cross DB Ownership Chaining 0 1 0 0cursor threshold -1 2147483647 -1 -1default full-text language 0 2147483647 1033 1033default language 0 9999 0 0fill factor (%) 0 100 90 90index create memory (KB) 704 2147483647 0 0lightweight pooling 0 1 0 0locks 5000 2147483647 0 0max degree of parallelism 0 32 4 4max server memory (MB) 4 2147483647 14336 14336max text repl size (B) 0 2147483647 65536 65536max worker threads 32 32767 255 255media retention 0 365 0 0min memory per query (KB) 512 2147483647 1024 1024min server memory (MB) 0 2147483647 4095 4095nested triggers 0 1 0 0network packet size (B) 512 32767 4096 4096open objects 0 2147483647 0 0priority boost 0 1 0 0query governor cost limit 0 2147483647 0 0query wait (s) -1 2147483647 -1 -1recovery interval (min) 0 32767 0 0remote access 0 1 1 1remote login timeout (s) 0 2147483647 0 0remote proc trans 0 1 0 0remote query timeout (s) 0 2147483647 0 0scan for startup procs 0 1 1 1set working set size 0 1 0 0show advanced options 0 1 1 1two digit year cutoff 1753 9999 2049 2049user connections 0 32767 0 0user options 0 32767 0 0
View 1 Replies
View Related
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
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
Jul 24, 2001
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?
Thanks!
Barbara
View 1 Replies
View Related
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
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
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
View Related
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
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
Nov 6, 2003
Is there a way to script out maintenance plans (and their corresponding jobs) from a server ?
View 2 Replies
View Related
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
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
Jan 23, 2004
Gurus,
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
Much Appreciated!!
View 3 Replies
View Related
Apr 15, 2008
Hi Gurus,
What permissio0ns one should have to view execution plans on SQL SERVER 2005.
Thanks,
ServerTeam
View 1 Replies
View Related
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
