Hi all-
I have a database maintenance plan set up to run for user databases. The integrity checks job for a user database failed today with the error 'error 7919 Repair cannot be processed, db must be in single user mode' . I have scheduled the job to run once every week. Is this a bug in sql server 2000? I thought that the db is placed into single user mode when the integrity job starts. Please advise.
Thanks,
ndba
I have inherited a server on which is a maintenance plan with two subplans on different schedules. In each subplan there are Execute T-SQL tasks with scripts for index rebuilds. Each task is set up with a Completion arrow to the next task and a Failure arrow to a Notify Operator Task. I was asked to add a task for index rebuilds to a specific subplan for a specific database, which is what the other tasks also do. I discovered that my task was failing but the others were fine. No notification was sent about my task failing even though the job is marked in MSDB as a failed job. I have sent a test email using the "Send Test Email..." option when right clicking Database Mail in SSMSand I receive an email so I know Database Mail works.
I set up a test job to model the index job that I can't get notifications from. I have two T-SQL tasks that just select the top row from a small table. The first task has a syntax error that I did so it would fail. I have a failure arrow to a Notify Operator Task and a Completion arrow to another T-SQL task with no syntax error which has a Success arrow to a Notify Operator task. As expected, when I execute this job I receive one failure email and one success email.
The only other troubleshooting step I know to try is to add a Notify Operator task before my failing task. That Notify Operator task will hopefully fire to tell me that the previous step was successful. I am not having problems with the other steps so I was just thinking I would try to get the subplan to send me a success email about one of the steps that has been working fine.
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
Any ideas? Thanks.
We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley
Maintenance plan for bakcup is failing with "Login failed for user 'sa'. [CLIENT: <local machine>]"
I went to the Maintenance Plan and opened the Subplan. I clicked the "Manage Connections"
It has three tabs:
Name: Local Server Connection
Server: prod
Authentication: SQL Server Authentication
I clicked the Edit and it shows the Connection Properties:
It says: Enter information to logon to the server. "Use a specific Username and Password" is checked. Username is set as "sa" while the password is empty. I typed in the correct password and pressed Ok. When I go back again, the password still shows empty. I tried to run the plan and it again fails. Do you know why it is not showing the password as blank even if I try to save the password.
Hi there,
We are using a database maintenance plan to backup and reindex our db's. Up until the end of last month this was working perfectly - however now it has stopped deleting the old backups (even though we have checked 'Delete files over 1 day old').
Does anyone have any ideas as to why they are now being deleted - and how we can remove them automatically - has something been corruped? Would it be a case of creating a new maintenance plan?
thanks in advance,
Matt
Hello, everyone:
I have a Database Maintenance Plan that back up some databases and delete old backup files that more than two day. Can any one have idea which action is first, backup or delete old files?
Thanks
ZYT
Overview of my database: size - 45 GB, Full Recovery Mode, Log shipping ever 15 minutes; Full Backup nightly; Optimization and integrity checks on Sunday night.
The Database takes a transactional log every 15 minutes and ships it to our failover database. Usually these are small logs and are executed in matter of seconds.
On sunday night there is a problem. The optimization job (part of DB maintenance plan) runs for 3 hours and during this time the transactional log backup continues to runs and takes close to 4 hours of complete. When the tran log completes its close to a 30 GB file and this is causing problems with space and shipping. This also causes the failover server to be out of sync and at times we have to re setup log shipping.
Does anyone know if there is a way to get around this problem. Appreciate all help and comments.
I need to create sql 2005 standard maintenance plan for system and user databases and database maintenance plan should include Reorganize index task, full backup task, maintenance cleanup task(for backup files), history cleanup task.
What should be the tasks order ? please let me know....I have to complete this on 8 servers by 11/16/2007 10 AM PST
In sql 2000 database maintenance plan, any rebuild index task is there ??
I have in production, the database maintenance plan that runs the nightly backups. I want to restore this to a completly different server I am setting up for testing. What is the best way to restore a backup on one server (that was done using the maintenance plan) to another server? I tried just mapping a drive and then forcing a restore, however, I got some error message about an ID or something. Maybe I need to do it through query analyzer and there is an easier way to force it? thanks!
View 5 Replies View RelatedI ran the Maintenance Plan Wizard and created 2 plans, one for the system databases and one for the User databases. While running the wizard I was asked if I wanted to configure an operator to e-mail reports to.
Once that operator is selected, how can you change it without recreating the maintenance plan?
This is separate from an operator that can be configured within each job.
Thanks,
C. Fischer
I am using Database Maintenance Plan to run backups. But I am quite short in disk space. So I declared to remove previous backups to release disk space. Still have problem when writing backup file onto the hard drive. I suspect that SQL first tries to complete backup and put in on the disk and only after that removes old file. Could not find any tips in BOL or elsewhere in what order this all goes - backup - then removal or removal - then backup. If anyone know I would be very happy for the rest of my life.
Thanks.
When i was going to run the Database Maintenance Plan Wizard on one of my databases, i received the following warning:
"Database 'HGB' is over 400MB in size. It is not recommended that you run the Database Maintenance Plan Wizard on databases exceeding this size. Do you still want to continue?"
My database has 1GB data space, with 757MB free and 341MB log, with 211MB free. What´s the matter on having too much free space? I thought that was good...
Hi there,
I have a Database maintenance plan (DMP) that always fails!
The plan reorganises data and index pages and checks database integrity.
The plan covers several databases, and it always fails on the same database.
The error message (in the DMP history) is the following: "[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation."
The odd thing is that the DMP is locally executed, so I don't see why the network could be an issue here.
Thank you for your time!
gilles25
Hello, everyone:
How to check the function of a Database Maintenance Plan? For example, I want to check how how many databases are backed up in this plan. Thanks
ZYT
Hi everybody,
I need a way to create a database maintenance plan in SQL Server 2000, without using the wizard. So, I am looking for a way to create this plan either using TSQL or (even better) using VB6 code (maybe by using SQLDMO library). I want the end-user of my vb6 application to decide for the options of the plan (like scheduling or backup directory). Does anybody have any sample vb6 code for doing this?
Thanks in advance.
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...
View 3 Replies View Relatedwhen i right click to create a new maintenance plan. (no maintenance plans exist yet). I get this error.
The action you attempted to perform on a remote instance of SQL Server has failed because the action requires a SQL Server Component that is not installed on the remote computer. To Proceed, install SQL Server 2005 Management Tools on the remote computer and try again.
What am i missing.
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
View 4 Replies View Related
hello,
we have changed the name of MS SQL server 2005 from XYZ to ABC using
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error
Date 03.05.2008 16:00:00
Log Job History (ADM_AdminDB_TP_Backup.Subplan_1)
Step ID 0
Server ABC
Job Name ADM_AdminDB_TP_Backup.Subplan_1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. Unable to determine if the owner (XYZSQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'XYZSQLServer', error code 0x534. [SQLSTATE 42000] (Error 15404)).
please help us in this issue
I learned how to make SQL database BACKUPs through Maintenance Plans in SQL server 2005 in my own computer.
So my question is: When I finally would be deploying my ( asp.net 2.0) web site in a distant real server, how could I set a Maintenance Plan there ?
Weird problem with Database Maintenance Plan....
I will mention this problem I am having with one our servers. This server has several databases and four maintenance plans that schedule the backup, integrity and optimizations.
Now for only one of these plans, the rest of the plans are identical, on this server when I select the option "Perform these tests before backing up the database or transaction log" in the "Integrity" tab and save it, it does not stay selected. By that I mean, if I go back to the maintenance plan properities and check if the option is selected, it shows that it is not. While I had just selected the option a moment ago and saved it.
I realized this when the integrity jobs did not run before the backups as they used to. So, somehow this option got unselected and cannot be selected anymore.
On another server, there are three maintenance plans and all of them have the same problem. No matter what I do, I cannot save the plan with that option.
To give you guys some background, I already SP3 applied to all these SQL2000 servers running on Windows 2000. Also, these plans used to work fine before. For sometime, I have had some problems like these with my local installation of SQL server but not on our production boxes. Now it seems like the problem has propogated to them too.
Has anyone come across anything like this. Any help or direction would be appreciated. Thanks in advance.
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
View 5 Replies View RelatedI recently migrated my database to SQL2005 from SQL7. It was simply a restore from a backup and was cake.
But now when I go to run a new maintenance plan I do not see my database.
I found that the database options are set to SQL7. If I change it to SQL 2005 I see the database.
What impact will this have on my data?
This is how I am arranged my database optimization maintenance plan package.
1. Backup database
2. Check database integrity
3. Reorganize Index
4. Clean up old files
5. Update statistics
6. Rebuild Index.
Are these steps in the right order. Are there steps that I should take out that are unecessary?
Hi,
I am administering several SQL Servers running SQL Server 2005 SP2 Build 3042.
I have a common maintenance plan that runs on each of the servers. The maintenance plan runs
fine on all the servers except for one. On the one server the Database Integrity check fails with the following error:
Check Database integrity on Local server connection
Databases: <list of databases>
Include indexes
Task start: 2008-02-21T00:05:42.
Task end: 2008-02-21T00:05:46.
Failed0) Alter failed for Server €˜XYZ€™
I created a test maintenance plan to just do the integrity check and selected one database only and this also failed with the same error message. I ran this test maintenance plan and configured it for each of the databases in question and it failed each time.
If I run the DBCC manually against the databases they all report fine.
I read some of the post that talked about the €œAllow Updates€? being set incorrectly but that does not apply to my problem since my configured and run values are set to 0.
Does anyone know what the problem could be?
I set up a Database Maintenance Plan for my SMS Databases. Through the wizard, I set up an Optimization Job which is causing tons of errors (5,400 since yesterday at 1:00 am) in my SMS logs.
The messages occur in a series of three kicking off approximately every minute (see below).
Thanks everyone for any help!
~~~~~~~~~~~~~
Error 602
Severity Level 21
Message Text:
Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes.
Explanation:
This error occurs when Microsoft® SQL Server™ cannot find a row in the sysindexes table for a needed table or index (for example, when executing a stored procedure that references a table that has been dropped).
Action:
If the error occurs because a stored procedure references a dropped table, drop and re-create the stored procedure. This error may also occur in conjunction with other error messages that better point to the root cause of the problem. Execute DBCC CHECKTABLE on sysindexes; also execute DBCC CHECKDB.
If the problem persists, contact your primary support provider. Have the output from the DBCC CHECKDB statement available for review.
~~~~~~~~~~~~~
Error 3903
The rollback transaction request has no corresponding begin transaction.
Microsoft article (Q198265):
SYMPTOMS:
Failure to prepare a statement, either by means of ODBC or OLE DB, incorrectly results in the failure of the entire transaction. Potential causes for such a failure include syntax errors or incorrect object names in the statement. Subsequent operations that depend on the transaction being active, including attempts to commit the transaction, will fail.
CAUSE:
SQL Server incorrectly closes the whole transaction after any error, rather than stopping execution of the statement only.
WORKAROUND:
To work around this problem, execute the statement without first preparing it, using SQLExecDirect or ICommandText::Execute. An error during either of these operations will cause only the statement, rather than the whole the transaction, not to be processed. The application can then determine the appropriate response, such as ignoring the error and continuing with other statements, resubmitting the query without the problem, or rolling back the transaction.
STATUS:
Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.
~~~~~~~~~~~~~
SMS Discovery Data Manager failed to process the discovery data record (DDR) "C:Program FilesSMSinboxesddm.box638JL00J.DDR", because it cannot update the data source.
Possible cause: On a Primary site, it is probably a SQL Server problem.
Solution:
1. Review the immediately preceding status messages from this component about SQL Server errors.
2. Verify that this computer can reach the SQL Server computer.
3. Verify that SQL Server services are running.
4. Verify that SMS can access the SMS site database.
5. Verify that the SMS site database, transaction log, and tempdb are not full.
6. Verify that there are at least 50 SQL Server user connections, plus 5 for each SMS Administrator console.
If the problem persists, check the SQL Server error logs.
Possible cause: On a secondary site, SMS Discovery Data Manager probably cannot write to a file on the site server, so check for low disk space on the site server.
Solution: Make more space available on the site server.
I have a query. Here are the steps:
1. I created a maintenance plan using Visual Studio 2013 (nothing fancy pretty basic)
2. Using ssms 2014 I imported it (the dtsx file) under the Integration Services and it appeared there successfully
3. I connected to the Database Engine again using ssms 2014 - my expectation was to see it under the Management > Maintenance Plans folder but it was not present.
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.
Thanks
E.G.
One of my programmers changed their database from full to Simple recovery. Saw that my job that backs up the Full Recovery mode databases failed, so I moved that database to my Simple database backup job plan and removed it from the Full Recovery job. I am unable to remove the db from the Transaction Log task on the Full Plan because when I try to edit that job "Databases with Simple Recovery will be excluded"
My transaction log backups are still failing with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Just want to remove that database so my Full Recovery backup job does not try to back it up.
I was trying to configure maintenance plan to take nightly full database backup and Log backup. I was trying to configure it like in attached file. Any links that i can follow and configure as in attached file.
View 7 Replies View RelatedCreated a maintenance plan to backup my sharepoint databases.
When I execute it the following error occurs:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details:
Additional Information:
Job 'SharePointBackUp.Backup_SharePoint faild. (SqlManagerUI)
- Execute maintenance plan. SharePointBackUP (Error)
Messages
* Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
------------------------------
ADDITIONAL INFORMATION:
Job 'SharePointBackUP.Backup_SharePoint' failed. (SqlManagerUI)
When checking the Maintenance PlansharePointBackUP log it is empty!
Under Job History I thinks this:
Date 4/18/2008 12:55:35 PM
Log Job History (SharePointBackUP.Backup_SharePoint)
Step ID 1
Server DESD7
Job Name SharePointBackUP.Backup_SharePoint
Step Name Backup_SharePoint
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
line 1? Is that line one of the T-SQL statement? Because if that's the case then it's not because I copied and pasted the line in to a query and it executed without error.
Under SQL Agent there are no entries!
So what bloody log am I suppose to be checking?! This is very frustrating.
I then copied and pasted every sql statement in to a query and THEY all ran just fine.
What's going wrong here, and how can I correct it?
Hello,
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Thanks.