DBCC is a T-SQL statement provided by SQL Server. This tag is used for questions regarding DBCC and its use.

learn more… | top users | synonyms

6
votes
0answers
524 views

Trace Flag 1222 Not Working?

I have a customer site with two similarly configured 2008r2 SQL Servers "A" and "C". On both servers the trace flags 1204 and 1222 are enabled and DBCC tracestatus shows the following on both ...
3
votes
1answer
51 views

DBCC CHECKDB Estimation Alternative

I am in the process of rolling out DBCC CHECKDB across our instances. Each database will eventually either have a DBCC CHECKDB or DBCC CHECKDB PHYSICAL_ONLY. The way I have been gauging whether a ...
2
votes
0answers
283 views

Last Known Successful DBCC CHECKDB

I understand that looking at DBCC PAGE is supposed to show me the last known good DBCC CHECKDB but I am not seeing this work correctly. I have a database that I know is corrupted. If I run DBCC ...
3
votes
1answer
41 views

Determine which trace flags are set as a startup parameter vs. enabled globally?

Probably a quick question. We are looking to test out a couple trace flags as startup parameters in our environment, and while I know we can use DBCC TRACESTATUS to get global-scoped trace flags, it ...
2
votes
1answer
53 views

When using DBCC INPUTBUFFER to find query associated with spid, is there a way to show variable values of that query?

I have been troubleshooting an issue within a database that has some serious blocking issues. I found the command DBCC INPUTBUFFER a while back that allows me to see the query run for that spid. I ...
0
votes
2answers
10k views

How to print out the query SQL text of a transaction by querying the “fn_dblog” or DBCC LOG('DataBaseName')?

At some point in the last 24 hours some changes were made to a customer SQL server 2008 (v 9.0 SP4) by one of a couple if possible applications that use the DB. Its not particular critical, but it ...
1
vote
0answers
35 views

DBCC SHRINKFILE (N'XYZ' , EMPTYFILE) using single core

Is there any way to force DBCC SHRINKFILE to use more than one core? We're running SQL Server 2014 on a hex core machine - and SHRINKFILE is using one hyper threaded core. I/O is at a small fraction ...
2
votes
1answer
150 views

The operating system returned error 665(The requested operation could not be completed due to a file system limitation) on SQL Server 2014

Have a SQL Server 2014 instance running on Windows 2012 R2 that was running Ola Hallengren's DB integrity script. This is the error that resulted: The operating system returned error 665(The ...
-1
votes
1answer
60 views

shrink transaction log

Can I shrink transaction log when database in online using below command ? DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY) FYI my database is in production server & highly critical ...
0
votes
0answers
61 views

What is the appropriate value for DBCC CLEANTABLE batch size?

I have removed to NVARCHAR(MAX) columns from a table and what to calculated how exactly the table size was reduced. Usually, I am rebuilding the clustered indexes but the table has more then 1.6 ...
3
votes
3answers
2k views

Migrate SQL Server database to multiple files (shrinkfile emptyfile vs log shipping)

I have a SQL Server 2008 database in production that we are moving onto a new server. The current database has a single ~400GB .MDF file. The new server will be running SQL Server 2012, and we are ...
1
vote
1answer
493 views

DBCC Results: page ID contains an incorrect page ID in its page header

DBCC results for 'tablename' Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data), page ...
4
votes
1answer
302 views

Is there a way to suppress DBCC TRACEON/OFF messages in the Error Log?

We have quite a few dataloading processes that run throughout the day all day long. These processes are setting the 610 flag on for their connection then turning it off when they are done. Because ...
2
votes
1answer
169 views

TempDB not selectable for DBCC maintenance plan

I'm trying to clean up some of our maintenance plans and noticed that our TempDB is not getting it's weekly dose of DBCC CHECKDB goodness. I've tried to go into the maintenance plan and make sure ...
4
votes
2answers
2k views

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
5
votes
1answer
266 views

DBCC DBINFO showing wrong information

Though DBCC DBINFO() is undocumented, but we all know that dbi_crdate: Database creation datatime dbi_dbccLastKnownGood: the completion time of the last 'clean' run of DBCC CHECKDB For one ...
2
votes
2answers
574 views

SQL Server 2012 DBCC memorystatus has wrong information. How can I correct it?

I have a Windows 7 64-bit machine with 16GB ram, 24GB pagefile, 500GB HD and SQL Server 2012 Standard Edition 64-bit running on it. With 5 minutes of starting up, it routinely uses all my virtual ...
5
votes
5answers
23k views

Shrinking the log file does not reduce size

I have a database which had mdf size of 350 MB and ldf size 4.9 GB When 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 ...
5
votes
3answers
2k views

Using the sysadmin role with EXECUTE AS

It is my understanding that I can use the EXECUTE AS OWNER clause as part of a procedure that I create to make the body of that procedure run as a different user. My goal is to execute a command that ...
6
votes
2answers
743 views

Major Discrepancy Between SQLIO IOPS/Throughput Stats and DBCC CHECKDB Operations

I'm conducting time trials on a new SSD array running both SQLIO tests and a real-world workload of DB restores and DBCC CHECKDB calls. I'm seeing a major discrepancy between the IOPS and throughput ...
1
vote
0answers
199 views

DBCC TABLE CHECK seems to hang

Our nightly maintenance plan includes a database integrity check which normally takes 45 minutes to complete. Now it appears to hang. When looking at the Activity Monitor, a series of process items ...
6
votes
2answers
3k views

SQL Server 2005 database corruption. Advise

I have encounter some database corruptions in the past but I knew which object has hit the road. In this situation I run a DBCC CHECKDB against a problematic SQL Server 2005 database and I get the ...
1
vote
1answer
742 views

DBCC checkdb showing failure after a restore

We restored a backup into a new database. After running a few delete queries we received the following message: Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical ...
2
votes
1answer
52 views

Can we tell dbcc to ignore subnormal real values?

Currently dbcc DATA_PURITY complains about subnormal real values in some of my columns, such as 7.561407E-42. I would rather keep them in my tables, and ask dbcc to ignore them. I am OK with the ...
1
vote
0answers
541 views

Unable to insert into or update table

A have the following table in a SQL Server 2000 database (service pack 2), with about 600,000 rows: Id [int, primary key] Form [int] Element [int] Data [char(8000)] MoreData [varchar(8000)] ...
2
votes
2answers
1k views

SQL Server Replication breaks up after doing DBCC CheckDB with Allow data loss on Primary

We have one way transactional replication enabled on SQL Server 2008 databases. Due to some issues we had to run DBCC checkDB, bringing the database to single user mode. When we complete the DBCC ...
3
votes
2answers
2k views

Database consistency checker in postgresql

Are there any DBCC(Database consistency checker) commands in PostgreSQL? I could find SQL server DBCC commands but not for Postgres? I read that postgresql has inbuilt functionality of perfomance ...
0
votes
2answers
295 views

How Can I Determine Object Distribution in a Multiple-File Filegroup?

I am looking to eliminate an extraneous data file in a filegroup in my DB but don't want to use the dreaded DBCC SHRINKFILE method, I am preferring to rebuild indexes into an alternate FG, perform the ...
2
votes
1answer
386 views

DBCC CHECKDB ERROR Msg 8992

I have got the Following Error When i ran the checkDB over the database. What are the steps can be taken to resolve the issue. Without affecting the users. Msg 8992, Level 16, State 1, Line 2 Check ...
1
vote
0answers
388 views

DBCC Physical only giving Connection failure

I have a SQL server 2012 Ent DB. The hosting company is trying to setup new backup solution using Symantec Backup Exec. They have come back and said our database is corrupt because they get these ...
4
votes
2answers
1k views

What utilities can I use to simulate I/O load for a DB restore and DBCC check?

I'm trying to figure out the peak load I can sustain on new hardware I procured for validating backups and performing DBCC checks. I've been using Crystal Diskmark to get throughput stats which helped ...
5
votes
3answers
58k views

SQL Server 2008 R2 (Suspect) mode - how to repair?

I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this query: EXEC sp_resetstatus ‘yourDBname’; ALTER DATABASE yourDBname SET EMERGENCY DBCC checkdb(’yourDBname’) ALTER ...
0
votes
2answers
2k views

How do I interpret the log when I run DBCC TRACEON (3502, 3504, 3605, -1)

I have been using DBCC Traceon (3502, 3504, 3605, -1) because it was recommended in a blog for discovering performance issues related to I/O. I'm running MS SQL Server 2008 R2 SP1 Results in my SQL ...
2
votes
0answers
729 views

TempDB MDF file huge after update script ran

On the weekend we had a huge deployment run that affected a large chunk of our system. It was all run in one massive transaction (probably the wrong thing to do). As a result our TempDB DatabaseFile ...
7
votes
1answer
423 views

Strange behaviour DBCC Shrinkfile

I am attempting to run a dbcc shrinkfile in chunks of 1GB against a database where 95% of the data has been archived and deleted. I'm leaft with a 235GB file where 9GB is data/indexes. I want to ...
2
votes
3answers
1k views

Should I be regularly shrinking my DB or at least my log file?

My question is, should I be running one or both of the shrink command regularly, DBCC SHRINKDATABASE OR DBCC SHRINKFILE ============================= background Sql Server: Database is 200 ...
2
votes
1answer
631 views

emergency-mode repair failed, nothing to do?

A production server got I/O disk error's I tried to fix it with dbcc but it got unexpected errors and crashed after a few minutes. so I took the file down and copied it over to a new machine. I have ...
2
votes
2answers
666 views

Save Results of DBCC ShrinkFile

When we execute the following command: DBCC SHRINKFILE('MyDB_log', 1) We get the following results in SSMS: DBID | Field | CurrentSize | MinimumSize | UsedPages | Estimated Pages ...
3
votes
1answer
3k views

DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS

I had to run dbcc checkdb repair_allow_data_loss with all_errormsgs and it's been runing over 27 hours. DB size is over 15gb and the server (windows 2008) has 32gb memory. I did run this on SQL 2005 ...
1
vote
1answer
96 views

Where can I find guidelines for building a SQL Server machine specifically for DBCC CHECKDB work?

I'm working on defining specs for a new server to run DBCC CHECKDB scans on all databases in our portfolio (85 instances, 1300 DBs, 15 TB allocated). I have a custom application that copies dump files ...
2
votes
1answer
1k views

Mysterious disappearing consistency errors in SharePoint DBs

We have a pre-production SharePoint SQL server which threw an 824 error yesterday evening on the Content DB. As part of my investigation today, I ran DBCC CHECKDB on this DB and it came up clean. ...
2
votes
1answer
7k views

How do I fix this corrupted page?

My database is in simple recovery mode. One page got corrupted and I only have backups created using simple backup. When I run this: DBCC PAGE ('myDB', 1, 952663, 3) I get: DBCC PAGE error: ...
2
votes
1answer
2k views

Error attaching database after deleting NDF file

I have deleted .ndf file by mistake from a SQL Server 2008 database. While trying to attach a previous copy, SSMS raises this error: Attach database failed for Server 'MDSWIN34'. ...
10
votes
1answer
692 views

Definitive list of steps for SQL Server baseline testing?

Before running a performance test / baseline for an app that uses SQL Server, I want to be able to set the instance to a "clean" state, without restarting the instance. There are steps I tend to ...
3
votes
2answers
2k views

tsql: table data size won't shrink after large row deletion

i have a table with 12 months+ data in it. I backup the table then delete all rows keeping only rows inserted in the last month. But the table size won't shrink, nor the underlying physical file. ...
3
votes
1answer
3k views

To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question

We're freeing lots of space in a SQL Server 2008 R2 database -a Loooot! enough to care about- (we're dropping lots of unnecessary data). but the database file conserves its file size. we want to ...
2
votes
2answers
2k views

An inconsistency was detected

Am running asp.net application.Am searching records.if i click the view button i got this error, an inconsistency was detected during an internal operation in database(database name) on ...
1
vote
1answer
356 views

DBCC CHECKDB WITH DATA_PURITY gives out of range error

I have restore a SQL Server 2000 database onto SQL Server 2005 and then run DBCC CHECKDB WITH DATA_PURITY and I get this error: Msg 2570, Level 16, State 3, Line 2 Page (1:19558), slot 13 in object ...
2
votes
2answers
5k views

SQL Server 2008 DBCC Problems

We have a database Ms DBCC CHECKDB (MS) Or ALTER DATABASE MS SET SINGLE_USER DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE MS SET MULTI_USER Error message Msg 0, Level 11, State 0, ...
2
votes
1answer
3k views

Sql Server 2008 R2 DBCC SHRINKFILE Severe Error

I am trying to run the following command in Sql Server 2008 R2: DBCC SHRINKFILE('filename') When I try to run this command, I get the following error: Msg 0, Level 11, State 0, Line 0 A severe ...