We have a SQL Server 2005 instance that I am running a few databases on, nothing too big though.
CRM-3.0
GFI-Network Monitor Database
Future Use
Solomon, and Sharepoint
The goal is to have all of our databases running on one server for backup and maintenace. Currently we are using VMWare to hose all of our servers and we use nightly scripts to backup all of the data. So backups are good to go.
What I am most concerned about is preventative maintenance on the databases. What would people recommend that I set-up for nightly, weekly, or monthly cleaning, integrity checks, etc? Are there third party tools that would do a better job? I am fairly naive on SQL server so any help would be greatly appreciated.
I'm using Win SBS R2 2003 with SQL Server 2005 Workgroup Edition, and the database have been up and runnning now.
I was wondering whether I can perform a backup database from the workstation computers or not? That would mean I login as domain users and using the SQL Server Management Studio (located in the server), I will perform the particular database that I wish to backup... Can this be done? If so, perhaps someone could tell me how (step by step procedure) or is there a document about this?
I'm trying to create a maintenance plan to backup a database, and when I go to select the database in the dropdown, I don't have any of the user databases any more. They were there several weeks ago when I first created the 'plan'. Does anyone know how to get the user db's to show up in the dropdown?
I have a strange trouble on SQL Server 2005 on XP/local and Win 2003/server. When I connect local database and create a backup plan named by "backup plan", I can see the name under "maintenance plan" and "Jobs" under SQL Server Agent. But if I connect to server database and do the samething, I cannot see the name under "maintenance plan". I can see the name under "Jobs" under SQL Server Agent. But cannot delete this job, and message:
Drop failed for job "Backup Plan".(Microsoft.SqlServer.Smo).
I created a database maintenance plan on sql server 2005 (standard SP2, cluster environment). The plan created successfully on scheduled successfully. But when execute the plan, it fails. Here is the information in the log:
The last step to run was step 1 (TranLog backup).,00:00:01,0,0,,,,0 09/19/2007 12:06:38,Tranlog backups.TranLog backup,Error,1,LAIWWORKSITE1LA,Tranlog backups.TranLog backup,TranLog backup,,Executed as user: STROOCKSQLSRV. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:06:38 PM Warning: 2007-09-19 12:06:39.27 Code: 0x80012017 Source: Tranlog backups Description: The package path referenced an object that cannot be found: "PackageTranLog backup.Disable". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning DTExec: Could not set PackageTranLog backup.Disable value to false. Started: 12:06:38 PM Finished: 12:06:39 PM Elapsed: 0.688 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
We have lately experianced a strange problem with our SQL Server 2005 x64 (SP2) that is NOT consistent but when it happens it happens on the same time.
Almost every night at 03:30 one of our databases (not all) seems to be down or locked. When i have a look at the order table in this database I can see that we have stopped recieving orders after 03:30. Two hours later (05:30) I can see the following error each minute in the error log until we reboot the server:
All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS. Process Utilization 0%%.
As we have a maintenance job running at 03:30 it feels like this is the problem. The job performs the following tasks: "Check Database Integrity -> Rebuild Index -> Reorganize Index"
When i look at the history of the job it looks like it's not completed and only the "Check Database Integrity" task was runned. No error message here either.
Also when i look in the error log i can see that the Maintenance job is started but never ended. Worth to notice is that I get the follwoing info in the log after the start-message:
Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.
Also, when i run this job manually daytime it works great!
Anyone having any idees on this? Is it possible to track this even more? I'm tired of restarting the server 03:30 in the morning =)
Hello, I use SQL Server Express 2005 SP2.(Microsoft SQL Server 2005 - 9.00.3042.00) I want to make a maintenance plan, but I don't have a task called "maintenance plan" I am user sa with "sysadmin" How can I make a maintenance plan. Thanks for your help Thomas
I want to prepare for the TS: Microsoft SQL Server 2005 - Implementation and Maintenance exam. I want to know that how can i tackle it in the proper way. Can somebody please give me tips & tricks or study guide?
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.
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.
Hi all, We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night).
Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\FileServerTLDBLogs"
NB: if using the local drives, it work
Check List Security: - The account that I used to create the plan is an sa account - The location that I dump the TL files, I have full access to the folder
SQL Statement: exec xp_cmdshell 'dir FileServerTLDBLogs' (it list all files)
Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly
We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.
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:
I used the toolbox to select maintenance cleanup task to create the job to do this. In reading similar notes regarding this problem, some people mentioned that there was a choice to include subfolders. I do not have this choice. When I execute select @@version I get Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) . This is running on a cluster. Any idea what is going on here? Thanks.
I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:
Executed as user: DomainSqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance PlansTest Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.
Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.
I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09' " failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I need to find out on runtime, a list of Maintenance plans and Jobs. i dont know how to acquire the database name on which the plan/job are operating. i also dont know from which table can i get the tasks ( backup,integrity,rebuild index etc.) that belongs to the plan.
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.
The error happened on SQL 2005 sp2 32bit servers. Can someone please help. Thanks very much.
Brian
Server Name: CDNPCCVS3CDNPCCPRODDB3, Job Name: DatabaseMaintenance.Subplan_1, Step Name: Subplan_1, Message: Executed as user: CDNPCCPRODzpcc_sqlsvc. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:00:00 PM Error: 2007-12-22 00:56:32.60 Code: 0xC0024104 Source: Rebuild Index Description: The Execute method on the task returned error code 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: 2007-12-22 03:11:01.83 Code: 0xC002F210 Source: Update Statistics Execute SQL Task Description: Executin
I want to run a maintenance for a database in MS SQL Express 2005 running on Windows 2003 SP2 server. I know by default it is not there in Microsoft SQL Server Management Studio Express. Is there a way or any procidure to run maintenance for MS SQL Express 2005 ?
Hi, Does anyone here know of any on-line references on how to optimize index maintenance in sql 2005? Also do you know of any good DBA books that will explain database maintenance and or best practices?
I feel like a complete nitwit. I've been using SQL 2000 for about 5 years now and regularly set up automatic backups using the wizard for many people. I'm in no way an advanced user and have enjoyed some of the more user-friendly features of SQL 2000 such as the backup wizard.
Well now we have SQL 2005 and I can't find the "wizard". I did a lot of googling and found very little on this. The frustration is mounting - let alone the fact that I had to edit the registry just to be able to perform a manual backup in the first place... :-(
Apparently their is a "maintenance plans" folder but the only place I can find this is under "Legacy" and you can't create jobs there.
Has anybody else had this issue? Is there a bug in SQL 2005? I reinstalled the full version with all options selected and still can't find this folder. Am I just being completely "blonde"?
I need to create sql 2005 maintenance plan for system and user databases which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...
I successfully created a simple Backup Maintenance Plan under SSMS. But now that the plan is created I need to change the number of days the backups are kept. When I pull up the plan udner SSIS and lok at the tasks I can't locate that option. I couldn't find it under the sql agent either. Anyone know where it is? (Or do I need to recreate the plan from scratch?)
I want to create sql 2005 maintenance plan which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...ASAP
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?
There are some perculiar goings on happening on my servers. Plans across servers have been doing funny things like dumping back ups in different folders, some jobs have been hanging, so on and so forth.
And I think it's occuring after modifying SQL Server 2000 plans with SSMS. It's so intermitent, it's hard to put my finger on exactly when it's happening. It may have not even been reported yet. Fixing the anomolies are achieved by recreating the jobs (a matter of unticking and ticking the boxes in the Maintenance Plans).
In SQL 2000, if you create more than 1 schedule on a job that was itself created using a maintenance plan, SQL Server returns a message along the lines of "Dude, do this and weird things might happen". The message doesn't say what exactly, just that it can no longer guarentee the integrity of the plan. And indeed, weird things does happen, I tested it once.
I'm guessing this is a similar problem, but has not yet been pinned down. Has anyone come across this when using SSMS to maintain 2000 plans.
At this stage, I would like to point out that this is not a slagging off Maintenance Plans thread... I'm looking in the direction of anyone who's name starts with the letter Kristen or Tara
Drew
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."
Hi,We have a SQL-cluster with over 6300 databases, most of them prettysmall in size and usage.For this reason all these databases are on auto-close ( Otherwise SQLwould need many many gigabytes ).We've recently upgraded to SQL 2005 because of the Management Studio,because the EM is unusable on a SQL-Server with lots of databases.But whenever you choose 'all user databases' or 'all databases' in anyof the DB Maintenance task, MSSQL begins starting up every databasewhich takes about 30 minutes. Until then, you cannot use your Studioanymore.My question : is there a possibility to avoid this behavior, it shouldnot go and check all those db's, just add my task.PS : Detaching these databases is not an option ...Thanks in advance,Sven Peeters
I have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -
I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.
So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"
So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?
