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

3
votes
1answer
40 views

DBCC CHECKDB Consistency Error

I am having a problem with replicating a table, and I finally tracked it down to a consistency error on the source table, but I am stumped as to what is causing the consistency error. I ran DBCC ...
3
votes
0answers
35 views

Increasing the size of differential backups

I have a DB in which some transaction remains open that is increasing the size of differential backups, I had to kill those transaction frequently, I want to drill down to the transaction and find out ...
3
votes
1answer
57 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 ...
3
votes
1answer
66 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
103 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 ...
1
vote
0answers
40 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
324 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
61 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
75 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 ...
5
votes
1answer
294 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 ...
8
votes
0answers
618 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 ...
2
votes
2answers
752 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 ...
4
votes
1answer
398 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
218 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
3k 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
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 ...
1
vote
1answer
641 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 ...
1
vote
0answers
234 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
830 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 ...
2
votes
1answer
53 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 ...
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 ...
1
vote
0answers
626 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)] ...
0
votes
2answers
334 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 ...
1
vote
1answer
798 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
426 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
425 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 ...
1
vote
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
739 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
444 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 ...
7
votes
6answers
28k 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 ...
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 ...
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 ...
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 ...
2
votes
0answers
305 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 ...
2
votes
1answer
663 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
691 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
4k 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 ...
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
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: ...
10
votes
1answer
710 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 ...
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'. ...
6
votes
1answer
203 views

DBCC TRACEON with the global flag

In the question Need Help Troubleshooting Sql Server 2005 Deadlock Scenario there was a suggestion of using DBCC TRACEON (1204, -1) to trace deadlocks globally. When reading about this command in BOL ...
0
votes
1answer
285 views

DBCC CHECKDB Data Purity errors but no rows found

Any idea why the value -96.00 is out of range for the normalization_data column (defined as numeric(14,2) )? DBCC CHECKDB with DATA_PURITY flags this row: Msg 2570, Level 16, State 3, Line 1 Page ...
13
votes
1answer
1k views

What types of corruption can DBCC CheckDB miss?

This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following: BACKUP 'BrokenDatabase' detected an error on page (1:123456) ...
6
votes
2answers
223 views

SQL Server Internal Inconsistency on GROUP BY

I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS. Now I can select records from a table-valued function to retrieve sales records, but using a ...
2
votes
2answers
1k views

How does DBCC SHRINKFILE() affect backups?

I have a pile of SQL 2005 databases that have been around for a very long time and, for varying reasons, experienced transaction log autogrowth. Regular backups now keep the transaction log usage ...
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 ...