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
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 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)
Anyone had an issue like this?
Regards
Rob
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.
Our backups by default go to a network location, but I'd like to modify our maintenance plans to backup to an alternative location if the primary location isn't available. I've setup two Backup Database Tasks where the second one runs only if the first one Fails, and if the second one runs (on first one's failure) it then sends a notification to me so I know this occurred.
The Plan is running as expected, when I simulate a bad path in the first Backup Task the second one runs and the notification is sent, but the Job shows failure. I'd like to show the job as Successful when this occurs since I'm handling the issue and notification within the Plan, but I'm unable to find out how. I've set FailParentOnFailure to False on the Plan and I've changed the MaximumErrorCount to 2 with the assumption that this would work, but neither didn't.
Also I'm running into this in both SQL 2008 and SQL 2012.
Created 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?
Hi gurus:
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 created a maintenance plan to back up a database. It fails and I get the follow error in the log file.
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4064: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
What user is it referring to? What user does a Maintenance plan use to login as? SA on the server has full access to all databases. SA’s default database is master and I can read and write to it with the enterprise manager and query analyzer.
All replies appreciated
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
Hello,
On SQL 2005 with mixed authentication, I am simply trying to create a maintenance plan to backup one single database. When I get to the end of the wizard, the creation process fails with this message:
Create maintenance plan failed().
I installed SQL 2005 using my domain account and the services are running under my account credentials. I am a domain and local admin.
Any ideas?
Regards,
Chris
Hi,
I want to what check database integrity task does in Maintenance plan..
Anyone help.
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?
W2k3SP2 running SQL2k5SP2 in mixed mode. Trying to run a daily maintenance plan with a domain admin service account [domainsqlsvc]. This account is having login failure when I try to run the maintenance job. Would like to figure out why this account cannot authenticate with SQL. I believe I have local login denied, but login as a svc and login as a batch job are both enabled in Domain Policy.
What am I missing?
TIA for your help!
-Nick
EDIT: Here is the error data.
Date 11/14/2007 9:36:16 AM
Log SQL Server (Current - 11/14/2007 9:36:00 AM)
Source Logon
Message
Login failed for user 'domainsqlsvc'. [CLIENT: <local machine>]
Error:18456, Severity: 14, State: 16
Hi All,
I have built a package that firstly shrinks a database and then backs it up which is scheduled to run each day, but I would like to add a check (Check Database Integrity) in as the first task and if the Database checks out ok then continue on, if not send an email.
Now, I am unsure if the Check Database Integrity Task in SSIS actually returns the success or failure message back to the package that I am after. The SQL behind the task includes the NO_INFOMSGS option.
Can anyone advise if it is possible to have the status/integrity of the database returned to the SSIS task and to proceed based on the result?
Regards
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.
Any help would be appreciated!!
I have a scenario where i have to run update task on multiple servers in parallel and once all of them are completed (success or failure) another task is to be run on another server
1. in maintenance plan, if we add tasks which are not joined, will they run in paralled at the same time
2. if we link the last task to all the tasks with link type 'completed' will the last task complete after all tasks are completed or when any one of them is completed (i have big doubt here)
the business requirement behind this is to bring data from multiple servers into shadow copies locally and then process them together. its ok if some server data transfer fails, but its not ok to start processing centrally while data transfer is going on. further, we want to run data transfer from multiple servers in paralleled to save time.
Has anyone been able to successfully delete old backup files(*.bak) and tran logs (*.trn) TOGETHER using maintenance plan cleanup task in SQL 2005 SP2.
this is transact sql running in the back ground.
EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'"bak" & "trn"',N'2007-03-26T22:21:14',1
This DOESNT WORK.
It works if I just try to delete only one of the things ie trn or bak files.
EXECUTE master.dbo.xp_delete_file 0,N'F:MSSQL.2MSSQLBackupibmdir',N'bak',N'2007-03-26T22:21:14',1
But is there a way to delete both bak and trn files using one task ?
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.
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.
Hi,What methods can we use to check the integrity problems? Some records in mydatabase are having Foreign Keys but the database doesn't have any relatedrecords with required Primary Keys. I need to scan the whole database anddelete (maybe with backup, maybe not) all wrong records.Who can I do that? Is it need to write my own application to do that or wehave some standard way to fix these problems? I'm not a databaseadministrator and don't know these ways (yet). Can somebody help me withadvice?Thanks.Dmitri Shvetsov
View 4 Replies View RelatedI have a database in the 3rd normalized form. There is a need to load rows into a child table. To avoid having to drop RI, an ALTER TABLE WITH NOCHECK is being used. I am looking for a way to verify the integrity after the load is complete. After the load an ALTER TABLE WITH CHECK will be executed. Can I used DBCC to verify, that no orphaned child rows were loaded? If so, which parms would I have to use with DBCC?
View 0 Replies View Relatedim 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.
on sql server 2000, try to do integrity check on several database once a week but failed, what would be main area to look up? thank you
View 3 Replies View RelatedHello everyone,
Currently having some issues with my laptop. I'm no computer expert / programmer so please bear with me.
It is a Vista Home Premium based machine that was purchased about 6 months ago. I'm having issues with an application that relies on WDAC to work. The application worked perfectly in the past and I just recently tried to use it again and it doesn't get past the "Loading Application" screen.
From the company website, they make it clear that anyone using the software has to make sure to run the Windows update to bring the DOT NET framework and MDAC to their latest revisions.
Now, I know that MDAC has been replaced by WDAC to run under Vista. However, I learned that only after trying, for a completely different reason, to install MDAC 2.8 SP1 (I know, dumb idea).
All this to end up saying I'm pretty sure something was screwed up in my WDAC when I tried installing MDAC. I would like to know if there is any way to verify the WDAC installation integrity, basically a Microsoft Component Checker equivalent under Vista.
Thanks a lot for helping a newbie !!
I set up a test database to try and understand table partitioning. Since deleting the partition schemas, functions and all relevant info on the database properties page, I am getting the following errors during the weekly database integrity check:
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:223) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Can anyone suggest why this might be happening?
Thanks
Hi,
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?
cherriesh
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.
Thanks,
Rubal Jain
I found the following error in my maintenance plan please help me on this
Under maintenace plan history i found this error:-
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
and jobs failed with following error
i found this error in job history of optimization & integrity check jobs:-
Executed as user: XXXXadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Please help me find out the solution
Thanks in advance
Regards,
Sumit
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
Our system is 24/7, ldf file disk space is 30 gb.
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.
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.
thanks in advance
Ravi K
I have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan.
Thanks!