Check Database Integrity In Maintenance Plan Not Working In SQL Server 2005 SP2
Apr 13, 2007
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.
Ever since upgrading SQL Server 2005 to SP2 build 3050 all my maintenance plans which begin with a Check Database Integrity Task are failing! If I remove the task from the plan they all execute properly. If I run the T-SQL equivalent manually it executes properly.
I upgraded the server to build 3054 and rebooted several times and Im still having no luck. What am I doing wrong? Thanks for your help with this. -Kenny
I met a very strange problem recently. I set up a database integrity check maintenance plan. But this job failed every time. I looked into the logs, the error message was that Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. I used the sp_helpdb to check the version of the databases included in my maintenance plan. The sp result shows that all the databases are above version 80....
Even more strange, i can successfully run the dbcc check query on each database.
Any comment and suggestion will be very appreciated.
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.
I created a user db integrity maintenance task. The created job runs once a week. It fails. How do you tell at what point is fails? The history log is not specific about where things go off the rails.
The job errors as follows - Execution of job "User Integrity check subplan_1 failed. See history log for details
If I run the sql in a query window it goes right through - error free.
USE [AcAspNetDB] GO DBCC CHECKDB(N'AcAspNetDB', NOINDEX) GO USE [LCCIntranet_Config] GO DBCC CHECKDB(N'LCCIntranet_Config', NOINDEX) GO USE [library_SSP_Search_DB] GO DBCC CHECKDB(N'library_SSP_Search_DB', NOINDEX) GO USE [Librarysspdb] GO DBCC CHECKDB(N'Librarysspdb', NOINDEX) GO USE [seeunity] GO DBCC CHECKDB(N'seeunity', NOINDEX) GO USE [SharedServicesv2_DB] GO DBCC CHECKDB(N'SharedServicesv2_DB', NOINDEX) GO USE [SharedServicesv2_Search_DB] GO DBCC CHECKDB(N'SharedServicesv2_Search_DB', NOINDEX) GO USE [SharePoint_AdminContent_bc87e79f-4873-4ec0-b2bb-734054a2564d] GO DBCC CHECKDB(N'SharePoint_AdminContent_bc87e79f-4873-4ec0-b2bb-734054a2564d', NOINDEX) GO USE [WSS_Content] GO DBCC CHECKDB(N'WSS_Content', NOINDEX) GO USE [WSS_Content_lccintranet80] GO DBCC CHECKDB(N'WSS_Content_lccintranet80', NOINDEX) GO USE [WSS_Content_LCCSSPv2] GO DBCC CHECKDB(N'WSS_Content_LCCSSPv2', NOINDEX) GO USE [WSS_Content_library] GO DBCC CHECKDB(N'WSS_Content_library', NOINDEX) GO USE [WSS_Content_librarymysite] GO DBCC CHECKDB(N'WSS_Content_librarymysite', NOINDEX) GO USE [WSS_Content_libraryssp] GO DBCC CHECKDB(N'WSS_Content_libraryssp', NOINDEX) GO USE [WSS_Content_mysitev2] GO DBCC CHECKDB(N'WSS_Content_mysitev2', NOINDEX) GO USE [WSS_Search_ISSMOSS] GO DBCC CHECKDB(N'WSS_Search_ISSMOSS', NOINDEX)
I am using SQL server 7.0. After restoring all the databses (Exceptdistribution) Everything seems to be working fine except the backupmaintenance plan. I put following detail in the maintenance plan"General Tab:Plan Name --- DB Maintenance Plan1() All DatabasesOptimizations tab:Reorganize Data and Index PageChange free space per page percentage to 10%Remove unused space from database filesShrink Database when it grows beyond 50 MBAmount of free space to remain after shrink 10% of the data spaceSchedule: Occurs every 1 week(s) on Thursday, at 1:30:00 PM.Integrity tab:Chack database integrityInclude indexes.Attempt to repair any minor problemSchedule : Occurs every 1 week(s) on Thursday, at 1:00:00 PM.Complete Backup tab:Backup the database as part of the maintenance planVerify the integrity of the backup upon completionDisk use this directory: d:SQL BackupsBackup file extension: BAKSchedule: Occurs every 1 day(s), at 2:10:00 PM.Transaction Log Backup tab:schedule: Occurs every 1 day(s), at 2:00:00 PM.Reporting tab:Write report a text file in Directory: D:SQL BackupsLOG "Problem:My databases(mroduction as well as systems) sre not at all gettingbacked up according to above plan.Error Message in the Log Directory:"Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO(ODBC SQLState: 42000)] Error 4062: [Microsoft][ODBC SQL ServerDriver][SQL Server]Cannot open user default database '<ID>'. Usingmaster database instead."In the SQL server registration properties the login through whichgetting logged into the EM for that, default database is "my productiondatabase".Is this problem because of the distribution db which I am not able torestore.Please helpDeepak Sinha
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 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
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 a weird one. I have 3 maintenance plans that were working for months and now suddenly are sort of not working. The jobs start and then take forever. I stop them from QA (EM doesn't appear to stop them). When I look at the Application log in the event viewer there are no errors because the jobs appeared to freeze. When I look at the maintenenace plan history it says the jobs completed in the usual short amount of time. When I look at the application log it says it did the backups of the data and transaction logs, etc. When I look on the server in the d:SQL ServerMSSQLLog directory for that maintenance plan, it says it completed the backups. So, essentially, EM is telling me the jobs haven't completed and everything else tells me the jobs did complete. But I won't feel secure until EM tells me the jobs have actually finished.
I did do a manual backup of one of the databases and then ran that maintenance plan for that database. I canceled the maintenance plan job after a few minutes (how long the job would usually take) and then looked at the size of the .BAK files. They were both exactly the same size.
Hi Friends, I am Ravi, I need a help. My case is like.. i need to upload data from csv file into sql server 2005 data table. but before that I need to check integrity of data for example: let us say csv data is like ISD CODE,STATE,NAME,QUALIFICATION 91,AP,KIRAN,MCA 01,MC,MIKE,MS
here i have to check that, wether there is an entry in ISD codes table for 91 and 01 (india and us) similarly AP , MC (Andhra Pradesh and MISSICippi) please suggest me a nice approach, no of records in ISD codes and States will 350-450 records.
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've been running the Ola Hallengren maintenance script for the last five months without missing a beat. Today I find an error stating the UserDatabase Integrity check job failed last night. This is running on SQL Server 2014 BI edition w/64 Gigs.
I ran a DBCC CHECKDB on each database manually and all worked until I tried it on the biggest one that is about 18 gbs. It just keeps running and I eventually stopped it so I'm guessing it is memory, but doesn't make sense considering it has 64 gbs. I have it set to 64/4 max / min. Again, this was never an issue until last night.I've been looking up all morning, but not seeing much on this error "The operating system returned error 1453"?
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
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 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 ?
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.
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.
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 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 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.
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.
im trying to set up maintenance plan for the check database integrity...
In sql 2000 you get a nice little log in SQL Logs DBCC CHECKDB (WSS_Search_db3) executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
But in SQL 2005 Im not getting a nice log of this but getting it against some system database and not the database i selected
Date1/22/2008 5:19:43 PM LogSQL Server (Current - 1/22/2008 5:19:00 PM) Sourcespid84 Message DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by XXX found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
Anyone got this to work? Trying to get the same message i got for SQL 2000 or at least so i can confirm it ran.
I have no way to test this. If let's say the database has logical integrity errors, will it throw back an error to the Check database integrity object such that, the error arrow (the red line which is the error handler stuff) will be triggered for notification purposes?
what does check database integrity in maintenance plan do.
i heard that the transactional log growth in sql server should be fixed to maximum available disk space and must be monitore the threshold through maintenance plan.how can it be done. please advice me through maintance plan instead of shrinking the log as it is unnecessary IO to the disk.
How often is the check database integrity scheduled. is it daily or weekly.
I have a database hosted that doesnt passes the integrity test. Here is the error it gives : Executing the query "USE [db_cs] failed with the following error: "Could not locate entry in sysdatabases for database 'db_cs'. No entry found with that name. Make sure that the name is entered correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any idea whats wrong and how I can let the integrity test pass for this db.
I have setup a full maintenance plan on SQL2005. When I run the job, I don't see any error but by loooking at the Log file viewer it appears that Check Database Integrity step failed because :
Alter failed for Server 'LUMONT001'.
Moreover, when I run DBCC CHECKDB from the console I have no error message. Any clue? Thanks, Paul
I searched the forum on this topic and saw the following explanation on Check Database Integrity Task, by DarrenSQLIS.
"The Check Database integrity task will fail if the DB has a problem. The task fails; the details are raised in the error event and dumped to the log etc. You can use on Failure precedence constraints or an OnError event handler to capture that failure and do something if you wish."
Here is the link to the quote above. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2290253&SiteID=1
I would like to continue this discussion on this new thread to help my understanding.
I understand that this command does dbcc checkdb( ) command on a specified server for the databases you want to check. And if a database has a problem the task will fail and raise an OnError event. DarrenSQLIS goes on to say that the error is dumped to a log. What log is this? Where do I specifiy the location of this log?
As per the advice given by somebody i set ldf initial log size as 29 gb and restricted the growth and set the autogrowth to 500 mb i did not run and shrink ldf command on daily basis. this is the advice given by another dba.
then he suggested to create a maintenance plan to check database integrity check. whick will check the disk space threshold.
i did not understand how will the maintenance plan check the disk space and give an alert. where should i check the alert for this maintenance plan. secondly how can i stop my database log file to deliver a message that my disk is full.
please suggest me a best method. where i am wrong , how should i handle the situation without the dba monitoring. and our system has lot of batches running for every minute.
