SQL Server Admin 2014 :: User Database Integrity Check Fail
Mar 8, 2015
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 have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W. My question is that after doing this whether my SQL server will start or not
we have our cubes in Server A and SQL DB resides on Server B (we are on SQL 2014), from last couple of days are cube started failing due to below error:
OLE DB or ODBC error: Protocol error in TDS stream; HY000; Protocol error in TDS stream; HY000; Protocol error in TDS stream; HY000; Communication link failure; 08S01; TCP Provider: An existing connection was forcibly closed by the remote host. ; 08S01
I have been going through some blogs to understand the error but don't find any specific yet.
DECLARE @File_Exists INT EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT print @File_Exists 1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INT EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupul', @File_Exists OUT print @File_Exists 0
If use xp_subdirs like:
EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists, Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
how to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
- An MSSQL 2014 Standard server that houses multiple small databases (in excess of a hundred). - These databases are frequently dropped and restored by an application that uses this SQL Server. - There is a business need for this setup at this time, so I can't get away from it. Therefore answers like "don't have so many small databases that are frequently dropped and restored" would be somewhat unuseful
This is the problem I have:
- When I connect SSMS 2014 to the server and expand the "Databases" node, it takes forever to display. In comparison, SSMS 2008 connected to SQL 2008R2 server with the same number of databases displays the Databases tree very quickly.
I ran a trace to see what exactly SSMS 2014 is doing. When the "Databases" node is expanded, it runs a query that checks each database for Memory-Optimized Tables (new and wonderful feature of SQL 2014 for sure, but I'm not using it, at least yet). Naturally, when you have to loop through over a hundred DBs, it takes time. Worse yet, if one of these DBs is in process of being restored, the query sits and waits to time out before proceeding to the next DB. Sometimes this causes outright timeouts. Here is the query:
use [MyDatabase] SELECT ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]
To be sure, this is NOT a SQL Server performance issue. This server processes a rather heavy workload and has been doing so for over a month, and the workload completes within expected time limits or better. Even so I've done some basic performance measuring, and the server itself is quite all right.
Moreover, if I connect SSMS 2008 to it, I get an error message (Index out of bounds or somesuch), but SSMS 2008 does connect, and displays the Databases tree much faster than SSMS 2014.
I'd like to turn off the option to check for Memory Optimized Objects altogether, as I'm not using the feature.
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 have a user, who is trying log into the server, but everytime he gets this error saying something about the Group policy denies him access.
This user needs access and i'm trying to understand how to grant it to him.
I have been looking into how i can access the group policy editor, but the farthest i can get is the Local group policy editor. How do i make sure this specific user has access?
I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....
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.
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error : Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
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 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 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?
And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql
I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
Query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
I have multiple SQL 2008 severs with databases. Also, 1 mirroring server in place.
Since my database count is increasing can i have only 1 mirroring server. Is there any limit of db at mirroring server. I would have approx. 150 databases.
I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master GO EXEC sp_dboption CurDataBase, 'Single User', True EXEC sp_renamedb 'CurDataBase', 'OldDataBase' GO 3- Rename Newdb to current DB. USE master GO EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)
My database went into suspected mode. and after we had run some script, it came out from the suspected mode. but we encountered this error while opening table in database.
2009-11-02 15:46:42.90 spid51 Error: 824, Severity: 24, State: 2. 2009-11-02 15:46:42.90 spid51 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file 'H:MSSQL.SQL2008MSSQLDATAmy_db.mdf'.
Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
