In earlier versions of SQL Server, it was recommended that DBCC statements be made a regular part of a database backup strategy.
It was recommended that databases be checked before being backed up. In SQL Server 7.0, this is no longer necessary.
Are integrity checks really no longer necessary in SQL Server 7? I have a third-party book in which the author states that he still does them
anyway. Should I continue to do DBCC CHECKDB?
Also, I am using database maintenance plans for several SQL Servers. When the integrity checks are performed will it log any errors
in the output log generated during the execution of the maintenance plan? The logs I receive look like the following:
[2] Database CPMCC_GL: Check Data and Index Linkage...
** Execution Time: 0 hrs, 39 mins, 54 secs **
If an error is detected would it be recorded here, or is this just a log to let me know that the checks were performed?
Hi, I'm new to SQL 7 (and fairly new to SQL Server), can anyone help with these basic questions on database maintenance plans generated by the wizard: (1) Scheduling - can SQLServer handle say a REORG running at the same time as a backup against the same database? This should never happen, but what will happen if say a backup is due to start before a REORG has finished? To try & simulate this 'problem' I've run REORGs and backups at the same time & have yet to encounter errors I presume SQL locking handles this OK. (2) Database integrity checks - (a) any comments on the wisdom of checking 'repair any minor problems'? Anyone had any problems with this? (b) While integrity checks are running do they take enforce a consistent view of the data? (I think this is probably the case as my reading of books online indicates that DBCC takes shared locks for the duration) (3) Backups - does the 'VERIFY INTEGRITY' option have any impact on the live database? (My reading of RESTORE VERIFYONLY indicates it doesn't) Thanks....
Hello everyone, I'm new to DB Maint Plans, so let me apologize upfront. I've taken over a system from a DBA who is no longer working here, and he set up Maint Plans for all of the existing DBs. The plans show up in the Enterprise Manager under "Management->Database Maintenance Plans" like they should, but there are also entries in the "Management->SQL Server Agent->Jobs" area. When I set up a new DB Maint Plan for a new DB, it seems to be working fine, but I don't have any corresponding entries in Jobs. Did the other DBA set these up manually? Does anyone know why he might have done this? Is it needed? The jobs and job steps look like the following:
We created maintenance plans for Backup, we configured as:
1. Backup set expires after 2 days. (but we still see backup files are at the location from day one) 2. There is Overwrite and Append in backup file settings. what eaxactly overwrites means, in case we set up expire the backup set after 2 days.
I have a database that is being restored to another instance of SQL server 2000 sp4 by attatching the mdf and ldf files. I then run EXEC sp_change_users_login to sync the users. When I try to run some delete commands on the new restored database I get 'Fatal error 8908'
I then run a DBCC CHECKDB on the database and told to run it again with REPAIR_ALLOW_DATA_LOSS
I have noticed that when I create a new databse and restore a .bak over this the delete commands work.
Am I correct in thinking that I can get rid of the corruption on the original database by creating a new database then restoring a valid .bak backup on this new database.
I've read the bad news in other post but hopefully I misunderstood them. We have a database on an old sql 2000 server which had a data drive failure. The last backup is a year old. They told me they stopped doing backups because nothing was going to change. Well, changes were made to structures and stored procedures on the production (only) server and they want to try to recover the database. Should I bother opening a support call with MS? I'm embarred to even post the question but you never know. The database is currently in emergency mode. I can't see any of the user tables or stored procedures in Mangement Studio.
Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:17913) with latch type SH. sysobjects failed.
I want to know if there is a "best-practice" for setting up DatabaseMaintenance Plans in SQL Server 7 or 2000. To be more specific, I wantto know the order in which I complete the tasks. Do I completeoptimization first, then integrity checks, then translog backup, thenfull backup??? OR is there a better order which should be used?Should I ALWAYS backup the transaction Log before I complete a fulldatabase backup, and if so, why??If someone can help, it would be great.....
Our supplyer of application we use say that dbcc checkdb or dbcc newalloc can give corrupt database (use version 6.5). I wonders about this statement. Are ther some how have explanation about this
Let me start off by saying that under normal circumstance I would just restore from the last good backup. However in this case it appears as though the last good backup was sometime last August ... arg! After much yelling at the person responsible I've been attempting to get my blood pressure below 200 and see what data is recoverable.
First off, this was a RAID5 system that failed 1 drive. Secondly, before we got someone in there to replace said drive it failed a second drive and the system went down. We managed to massage the system back online but it appears that there is some corruption as a result which is no surprise.
I've done DB repairs in the past and it hasn't been too bad, but this time it is looking a little gnarly.
I've kicked everyone off the server and tried starting SQLServer several different ways.
I tried starting the service normally and then flagging the bad DB into single user mode with "ALTER DATABASE foo SET SINGLE_USER". I then did a select * from sysdatabases to make sure it took, which it did. I also tried starting the whole SQLServer in single user mode from the command line, "SQLServr -m".
I can run "DBCC CHECKDB('foo')" and I get a long ugly list of allocation errors. I posted it to a link as the 1349 lines returned is a little long: http://chrisnet.net/sqlbad/dbcc_checkdb.txt
But when I attempt to bite the bullet and destroy data in an attempt to put things back together with: "DBCC CHECKDB('foo', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS" I get: Server: Msg 7919, Level 16, State 2, Line 1 Repair statement not processed. Database needs to be in single user mode. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
But yet the database is in single user mode, according to everything I check check on. Is this just SQL's way of telling me the corruption is too severe to be repaired? No output is displayed in the shell cmd window like it is for a successful DBCC either (when running sqlservr -m).
When I try to delete a job from Enterprise Manager Console I get the following error: Erro 644: Could not find the index entry for RID '163bd10000010000' in index page (1:553), index ID 0 database 'msdb'
Oh and this is on MSDE.
Here is my complete output of DBCC CHECKDB DBCC results for 'msdb'. DBCC results for 'sysobjects'. There are 280 rows in 6 pages for object 'sysobjects'. DBCC results for 'sysindexes'. There are 143 rows in 6 pages for object 'sysindexes'. DBCC results for 'syscolumns'. There are 1567 rows in 26 pages for object 'syscolumns'. DBCC results for 'systypes'. There are 26 rows in 1 pages for object 'systypes'. DBCC results for 'syscomments'. There are 357 rows in 108 pages for object 'syscomments'. DBCC results for 'sysfiles1'. There are 2 rows in 1 pages for object 'sysfiles1'. DBCC results for 'syspermissions'. There are 116 rows in 1 pages for object 'syspermissions'. DBCC results for 'sysusers'. There are 13 rows in 1 pages for object 'sysusers'. DBCC results for 'sysproperties'. There are 0 rows in 0 pages for object 'sysproperties'. DBCC results for 'sysdepends'. There are 1635 rows in 8 pages for object 'sysdepends'. DBCC results for 'sysreferences'. There are 12 rows in 1 pages for object 'sysreferences'. DBCC results for 'sysfulltextcatalogs'. There are 0 rows in 0 pages for object 'sysfulltextcatalogs'. DBCC results for 'sysfulltextnotify'. There are 0 rows in 0 pages for object 'sysfulltextnotify'. DBCC results for 'sysfilegroups'. There are 1 rows in 1 pages for object 'sysfilegroups'. DBCC results for 'backupset'. There are 1045 rows in 44 pages for object 'backupset'. DBCC results for 'sysjobschedules'. There are 7 rows in 1 pages for object 'sysjobschedules'. DBCC results for 'syscategories'. There are 19 rows in 1 pages for object 'syscategories'. DBCC results for 'systargetservers'. There are 0 rows in 0 pages for object 'systargetservers'. DBCC results for 'backupfile'. There are 1451 rows in 24 pages for object 'backupfile'. DBCC results for 'systargetservergroups'. There are 0 rows in 0 pages for object 'systargetservergroups'. DBCC results for 'systargetservergroupmembers'. There are 0 rows in 0 pages for object 'systargetservergroupmembers'. DBCC results for 'restorehistory'. There are 1 rows in 1 pages for object 'restorehistory'. DBCC results for 'sysalerts'. There are 9 rows in 1 pages for object 'sysalerts'. DBCC results for 'sysoperators'. There are 0 rows in 0 pages for object 'sysoperators'. DBCC results for 'sysnotifications'. There are 0 rows in 0 pages for object 'sysnotifications'. DBCC results for 'restorefile'. There are 2 rows in 1 pages for object 'restorefile'. DBCC results for 'systaskids'. There are 0 rows in 0 pages for object 'systaskids'. DBCC results for 'syscachedcredentials'. There are 0 rows in 0 pages for object 'syscachedcredentials'. DBCC results for 'restorefilegroup'. There are 1 rows in 1 pages for object 'restorefilegroup'. DBCC results for 'logmarkhistory'. There are 0 rows in 0 pages for object 'logmarkhistory'. DBCC results for 'sysdtscategories'. There are 3 rows in 1 pages for object 'sysdtscategories'. DBCC results for 'sysdtspackages'. There are 0 rows in 0 pages for object 'sysdtspackages'. DBCC results for 'sysdtspackagelog'. There are 0 rows in 0 pages for object 'sysdtspackagelog'. DBCC results for 'sysdtssteplog'. Server: Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. The previous link (1:343) on page (1:371) does not match the previous page (1:382) that the parent (1:300), slot 32 expects for this page. Server: Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. Page (1:371) is missing a reference from previous page (1:343). Possible chain linkage problem. There are 0 rows in 0 pages for object 'sysdtssteplog'. DBCC results for 'sysdtstasklog'. There are 0 rows in 0 pages for object 'sysdtstasklog'. DBCC results for 'sysdbmaintplans'. There are 4 rows in 1 pages for object 'sysdbmaintplans'. DBCC results for 'sysdbmaintplan_jobs'. There are 4 rows in 1 pages for object 'sysdbmaintplan_jobs'. DBCC results for 'sysdbmaintplan_databases'. There are 12 rows in 1 pages for object 'sysdbmaintplan_databases'. DBCC results for 'sysdbmaintplan_history'. There are 724 rows in 23 pages for object 'sysdbmaintplan_history'. DBCC results for 'log_shipping_primaries'. There are 0 rows in 0 pages for object 'log_shipping_primaries'. DBCC results for 'log_shipping_secondaries'. There are 0 rows in 0 pages for object 'log_shipping_secondaries'. DBCC results for 'mswebtasks'. There are 0 rows in 0 pages for object 'mswebtasks'. DBCC results for 'sqlagent_info'. There are 0 rows in 0 pages for object 'sqlagent_info'. DBCC results for 'sysdownloadlist'. There are 0 rows in 0 pages for object 'sysdownloadlist'. DBCC results for 'backupmediaset'. There are 1045 rows in 11 pages for object 'backupmediaset'. DBCC results for 'sysjobhistory'. Server: Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. The previous link (1:382) on page (1:564) does not match the previous page (1:371) that the parent (1:300), slot 33 expects for this page. There are 626 rows in 208 pages for object 'sysjobhistory'. CHECKDB found 0 allocation errors and 3 consistency errors in table 'sysjobhistory' (object ID 2073058421). DBCC results for 'sysjobs'. There are 7 rows in 1 pages for object 'sysjobs'. DBCC results for 'backupmediafamily'. There are 1045 rows in 20 pages for object 'backupmediafamily'. DBCC results for 'sysjobservers'. There are 7 rows in 1 pages for object 'sysjobservers'. DBCC results for 'sysjobsteps'. There are 9 rows in 1 pages for object 'sysjobsteps'. CHECKDB found 0 allocation errors and 3 consistency errors in database 'msdb'. repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (msdb ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have huge database on prod. One time I tried to run DBCC CHECKDB, it took more than a day. My question is can I created a snapshot of the prod database on the same server and run DBCC CHECKDB on the Snapshot DB? will doing this interfere production database? I don’t have option to make copy of the database on a test server and run it there.
we've been having this ancient database with old accounting data running in suspect mode since as long as I can remember (I started working here a year ago), and finally I had some time on my hands so I thought I'd try to get it online again. However I'm running in to problems:
DBCC CHECKDB (myDBName) gives this error: Msg 926, Level 14, State 1, Line 1 Database 'myDBName' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Running sp_helpdb only does not display the suspect database and sp_helpdb 'myDBName' gives this error even though I'm a system administrator: No permission to access database 'myDBName'.
It's possible that I might be able to dig up a backup but that would be quite tedious. Is it possible to bring the database to a state where I'm able to do a CHECKDB at least...?
-- Lumbago "Real programmers don't document, if it was hard to write it should be hard to understand"
We recently migrated our production server from SQL 2005 (Standard) on Win2003(32-bit) to SQL2012 (Standard; v11.0.3000) on Win2008-R2(64bit). Single-server Dell R510 with 1.2TB storage. Everything went smoothly; the only nagging issue remaining is failure of our maintenance jobs. I tracked the issue down to failure of DBCC CHECKDB. Specifically, the error is:Executed as user: NT SERVICESQLSERVERAGENT. The database could not be exclusively locked to perform the operation. [SQLSTATE 42000] (Error 5030) Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be lockedI have Googled this issue and read extensively. For instance, informative blogs (albeit dated) such as these by Paul Randal (Managing
CheckDB by default takes an internal DB snapshot to get the consistent, point-in-time view of the DB that it needs. If that snapshot creation fails, then it will try to get an exclusive database lock before proceeding (same as if you had executed DBCC CHECKDB WITH TABLOCK). The root problem is not that the lock could not be obtained, it's that the internal database snapshot could not be created. msdn.microsoft.com/en-us/library/ms188796.aspx details the specific situations when an internal database snapshot is not created and table locking is attempted.
I have verified the SQLSERVERAGENT service account has full permissions on the SQLDATA directory where the databases reside and has full permissions on each database within the directory. Just for giggles, I created a job (run as SQLSERVERAGENT) that creates and then deletes a text file in the SQLDATA directory. It runs fine.
Also testedI get the snapshot creation error when manually running DBCC CHECKDB against any of our databases and when executing under a variety of administrator accounts that are members of the SQL sysadmin role and the Domain Admins security group (the Domain Admins is a member of the local Administrators group that has full permissions on all SQL directories/folders).
Additionally, the databases in question are small (200MB to 6GB) and the disk has plenty of elbow room (978GB free on 1.22TB RAID5 array) to create the internal database snapshots. CHECKDB doesn't surface an error message that is detailed enough to determine the precise cause of the error. Any example successfully running DBCC CHECKDB on the SQL2012 (Standard) on a Win2008 R2 (64-bit) server.
I followed the advice of Paul Randal, but Im stumped as I am not able to determin what the corruption issues are. This is SQL 2000 and the database is a Solomon database that was recently upgraded to 6.5. the error I get when running the DBCC checkdb is as follows:
Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:18645) with latch type SH. sysindexes failed. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now, the bad news. I am a bit of a novice and have picked this up from someone who left my company. It appears the latch error has been around for some time and only reared up when I instituted a new back up system that runs a dbcc check befor backing up. I don't think I have any clean backups.
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
SQL7: I have added a Maintenance Plan to backup to 4mm dat tape the master and msdb SQL databases as well as another database relative to our application called WISE. This works fine; however, it appears to always append to the media as opposed to overwriting (preferred). Any help would be appreciated.....
I am going to set up maintenance plans on all our SQL servers (7.0 and 2000). I have found several 'tutorials' on how to do this, but no one is describing the options in detail. Can you guys/gals please help me out? We have alot of small databases and some medium (1-2GB).
We have Veritas' Backupexec running in our Enterprise and the Veritas Install actually installs MS SQL Server MSDN on each Server in the Enterprise.
It looks like it also sets up a default Maintenance plan within each of the MSDN Instances.
I guess my question is.. Can I manage the Maintenance Plans on these MSDN Instances via the SQL Server EM GUI from my desktop?? Seems like when I look at the Maintenance plans alot of the options are greyed out or not available. What I am trying to do is modify one of the maintenance plans to have the backups deleted after one week (One of the Instances has been running a complete backup on the Backupexec Databases for a year and there are a years worth of backups on the Server) but the option to "remove files older than" is 'greyed out' ??????
Does anyone get any issues creating "Backup" jobs as a Maintenance Plan when specifying the backup location as a UNC path (e.g. "\backup_bladeBACKUPS")?
For some reason, if i try using the UNC path for a 1-time backup, it works, but when I am trying to put it into a scheduled job, it does not 'seem' to perform the Backup step.
I created several Maint.Plans before installing SP2. Now I need to modify them and I get the following error. I cannot even Create new ones, because of the Enumerate error. Please advice if this error is due to the same issues mentioned on this blog.
When replying please cc me at Camilo.Torres@bellsouth.com
Thanks
TITLE: Microsoft SQL Server Management Studio
------------------------------
Enumerate target servers failed for Job 'Daily Maintenance Plan 1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+target+servers+Job&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476
I have a few extremely large databases in SQL Server 6.5 sp3 (soon to be 5a - but we won't talk about that!!) NT 4.0 sp4 (about 10 GIG each). I don't have a big window of down-time in order to do any maintenance. Does anyone know of a way to be able to run dbcc checkdb or other dbcc's that I can run to verify the database yet complete within a reasonable amount of time? The last time dbcc checkdb was run, it was started Friday night and still not complete Sunday night. Over a weekend, I may have up to a 24 hour maintenance window.
Any suggestions would be appreciated. Thanks! Toni
Hai , When I ran DBCC CHECK DB of userdatabase, its reporting along with usual messages as Descriptor for system table '8' in database '8' not found in the descriptor hash table. I could'nt understand being familiar error encounterd . Any one will appreciate for the help
I recently took over a SQL server with 300 MB of data. I am relatively new to SQL 6.5 and have been reading that DBCC checkdb and checkalloc should be run at least once per week. Apparently the person before me never ran any of those checks. Is not running the database consistency checks for so long going to present a problem? has anyone run into problems when running those checks? Any advise is greatly appreciated.
I ran "dbcc checkdb(MCMSdb) with no_infomsgs" and I get the following: Server: Msg 8946, Level 16, State 12, Line 2 Table error: Allocation page (1:274992) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
What cane be done to correct this problem? Can this error prevent a user from connecting to the database?
I ran checkdb and found 4 error message on the db.. it seen like same object.. can anyone tell me what it is.. and how can i fix it? thanks !!!!
1. Server: Msg 8976, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:809859) was not seen in the scan although its parent (1:77885) and previous (1:809767) refer to it. Check any previous errors. 2. Server: Msg 8978, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:809860) is missing a reference from previous page (1:809859). Possible chain linkage problem. 3. Server: Msg 8976, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:1453795) was not seen in the scan although its parent (1:1453347) and previous (1:1453796) refer to it. Check any previous errors. 4. Server: Msg 8978, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:1453801) is missing a reference from previous page (1:1453795). Possible chain linkage problem.
Does anybody know if the results of DBCC CHECKDB are stored anywhere? If yes, where? Also, if you don't select "attempt to repair minor problems" option when you set up the maintenance plan, will SQL Server let you know about any errors Integrity check encounters? If yes, where the erros can be found?
in the SQL 6.5 documentation it says when running the DBCC CHECKDB, you should make the database read Only or DBO use only. Do you guys know if SQL 6.5 locks rows while this runs? In SQL 7.0/2000 it only locks the schema.
I am using windows nt40 and sql server 6.5 on a DEC ALPHA and accidentlly started a dbcc checkdb. Is it possible to stop the process with out damaging the database?
When running dbcc checkdb from my workstation(nt) I recieve some of the output and then I get "Connection Broken" this is on a 6.5 machine with the service pack 5, what could be causing my ODBC connection to drop during the proccess of running checkdb?
