Monday, June 17, 2013

[SQL Server 2008 issues] CHECKDB issue

[SQL Server 2008 issues] CHECKDB issue

CHECKDB issue

Posted: 16 Jun 2013 06:57 PM PDT

Hi all,Someone restarted a server last week & SQL Server came back up as "in recovery" then completed after a while. Today I've just tried to run "DBCC CHECKDB WITH PHYSICAL_ONLY" on 2005 sp3. The output I get is as follows:[code]Msg 926, Level 21, State 6, Line 1Database 'mdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.[/code]My session is then disconnected from the database. It says suspect (although it later says to discard this) but when I reconnect and look at 'IsSuspect' it says it isn't (0). Any idea why this would be the case?Error log says:[code]Date,Source,Severity,Message06/17/2013 08:45:58,spid123,Unknown,Database 'mdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.06/17/2013 08:45:58,spid123,Unknown,Error: 926<c/> Severity: 21<c/> State: 6.06/17/2013 08:45:58,spid80,Unknown,E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6: Operating system error 1784(error not found) encountered.06/17/2013 08:45:58,spid80,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.06/17/2013 08:45:58,spid80,Unknown,The operating system returned error 1784(error not found) to SQL Server during a write at offset 0x0000006430e000 in file 'E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level 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<c/> see SQL Server Books Online.06/17/2013 08:45:58,spid123,Unknown,DBCC CHECKDB (mdb) WITH no_infomsgs<c/> physical_only executed by GBAHES777\GBAHES777 found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 3 seconds.06/17/2013 08:45:58,spid80,Unknown,E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6: Operating system error 1784(error not found) encountered.06/17/2013 08:45:58,spid80,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.06/17/2013 08:45:58,spid80,Unknown,The operating system returned error 1784(error not found) to SQL Server during a write at offset 0x0000034f840000 in file 'E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level 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<c/> see SQL Server Books Online.[/code]

merge query

Posted: 16 Jun 2013 06:46 PM PDT

i want to merge this query[size="1"]DECLARE @MinDate datetime,@MinDate datetimeSELECT @MinDate = MIN([Date]),@MaxDate = MAX([Date])FROM attend_logSELECT p.Date,q.TimeIn,q.TimeOut,q.shift,p.eidFROM(SELECT f.[Date],eidFROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) fCROSS JOIN (SELECT DISTINCT eid FROM attend_log) t)pLEFT JOIN attend_log qON q.eid = p.eidAND q.[Date] = p.[Date][/size]into this query[size="1"]--drop table #temp1select[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from Atendwhere eid = 26446group by [date]GOselectt.[date],t.eid,t.[Timein] as timein,t.[Timeout] as timeout,CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,case when (540 - Time_Minutes) > 0 then 'Short'when (540 - Time_Minutes) < 0 then 'Excess'else NULL end as ExcessShort,case when (540 - Time_Minutes) >= 120 then 'HalfDay' else '' end as RemarksFROM Atend tleft join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Recordwhere eid = 26446order by t.[date], t.[Timein][/size]and show result like this[size="1"]date-------------------------------eid------timein------------------------timeout-------------------spendtime--------excessshort2013-01-04 00:00:00.000---26446--2013-06-12 09:29:00.000---2013-06-12 18:47:00.000---09:18:00--------00:18:002013-01-05 00:00:00.000---26446--2013-06-12 09:08:00.000---2013-06-12 13:34:00.000---07:41:00-------01:19:002013-01-06 00:00:00.000---26446-------------null---------------------null--------------------------null-----------------null[/size]thanks for the help

Delete records without logging

Posted: 16 Jun 2013 06:15 PM PDT

Deal all,we have a database AA in SQL Server,while inserting the records to table DB log file size are growing same scenario happening while deleting the records.we dont want log growing for deleting the records without affecting DB Mirror,how to handle this ONE? Please help me on this?

Shrinking the log file does not reduce size

Posted: 29 Apr 2013 10:23 PM PDT

I have a database which had mdf size of 350 MB and ldf size 4.9 GBWhen i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file. The recovery model is set to FULL.Then i followed some steps:When i run dbcc SQLPerf(logspace) i found that logsize is 4932 MB and Log space used is 98.76%So large amount of (98%) of log is using now.Then i run this command use <databasename> dbcc loginfoNow almost all VLF has status 2 which means all are in use.then I tried to take log backup. After log backup also shrinking didnt reduce the size. Then i changed recovery model to simple and then tried shrinking.But this also didn't help. Also i ran dbcc opentran (database)and found that no transaction is open now. So what is making the database which does not allow me to shrink the log size.How can i solve this?

snapshot generation

Posted: 16 Jun 2013 04:38 PM PDT

what is the difference between snapshot generation process in snapshot replication and transactional replication

Maintenance plans

Posted: 16 Jun 2013 04:16 PM PDT

Hi all,Five days back i created a maintenance plan to delete backup files older than 2 days and the plan is running every day. Today i saw the disk filling up . So i went to maintenance plan history and saw the history. In history it is showing the task is executed successfully. I went to jobs and saw the history there . The jobs are running fine.I went to the location where where backups are taken. In this location all the backups are existing from the day i created the maintenance plan. I am unable figure it out..

which is better performance wise

Posted: 16 Jun 2013 02:48 PM PDT

Two Scenarios1) A table with 200 fields. OR2) 200 fields spanning across multiple tables? For e.g 10 tables with 20 fields each.Which one is better in terms of performance and space used and why?

How to avoid deadlocks

Posted: 16 Jun 2013 01:56 AM PDT

Hi,I am facing few problems with deadlocks. we have few tables and each table is having more than 6 million rows and we have done proper indexing on those tables. but when two different users are accessing the same table for different rows, ex: One user is retrieving the data from that table and another user is doing insert / update on one particular row in that time we are getting deadlocks.we are using SQL server 2008, but the compatibility level of database is 80. that we can't change right now because if we change that then our application will not work.so please help me on how to avoid the deadlocks.Regards,BSL

How can we find the Null values on indexed columns from all tables in SQL Server.

Posted: 06 Jun 2013 07:16 AM PDT

Hi,Please help me on finding the Null values on indexed columns from all tables in SQL Server.I need a SQL query to find all the indexed columns which is having null values.Please do me needful.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog