SQL Server Performance Forum – Threads Archive

job failed showing consistency error

One of my job failed .It was created by maint plan Integrity check up .Here is the error I got CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DB’.How to find what is wrong and how to trouble shoot it

I guess I have to run dbcc checkdb (db,REPAIR_REBUILD)I get error ,that db should be in single user mode.Now if I do sp_dboption ‘db’,’single user’,’true’ to bring the db into single user mode,I got the error other users are using this db
Is there is any way in which I can remove this consistency error without giving downtime to users so that my job should work fine

Firstly, Try to run: – DBCC Checkdb (Pubs)with PHYSICAL_ONLY Limits the checking to the integrity of the physical structure of the page and record headers, and to the consistency between the pages’ object ID and index ID and the allocation structures. Designed to provide a low overhead check of the physical consistency of the database, this check also detects torn pages and common hardware failures that can compromise a user’s data. PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any of the repair options. You are right database needs to be in single user mode, For that you need to take downtime: –
DBCC Checkdb (db_Name,REPAIR_REBUILD) REPAIR_REBUILD Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.
Deepak Kumar –An eye for an eye and everyone shall be blind

This db has only 2 users of which 1 user has 10 process .All these are in sleeping mode.But When I try to kill these processes I am unable to kill these.No error is comming while doing the killing process still none of the process get killed.Any ideas??

You need to have sysadmin permissions on server before killing any user process.. KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable
After killing any process from SQL Enterprises manager, Refresh it and also check SQL Server error log, it makes an entry after terminating user process. Alternative: – select * from master..sysprocesses where dbid= database_id
(you can check database id from sysdatabases table) Find ‘spid(s)’ from output related to your database.. and execute: – KILL {spid | UOW} [WITH STATUSONLY] — Search books online for ‘Kill’ Deepak Kumar –An eye for an eye and everyone shall be blind

Every time I kill a process another process gets created for the user.Should I have to stop the application which is using this db.
Is there is any alternative way by which I can kill all the process of this user,so taht new process not get created,without going to application server and stopping he application. or any alternative way by which I bring the db in single user mode

write a loop based script that can kill all users attached in database.. and just after that rename your database.. So User Application will not be able to connect to database.. Then execute checkdb Alternative <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> 1. Restart SQL Server. (But this will stop all other databases and their applicaiton’s.) 2. Findout user(s) [if less, and contactable] and ask them to stop processing. Deepak Kumar –An eye for an eye and everyone shall be blind

Restart SQL Server services and start the server in single-user mode in order to avoid any interaction with that database, refer to Books online for more information on keeping server or database in single-user mode. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>