Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [dbcc]

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

Filter by
Sorted by
Tagged with
0 votes
2 answers
28 views

Should I stop replication logreader and distribution jobs during database maintenance (reindexing, updating stats, and checkdb)?

I have transactional replication setup in SQL Server 2022. I have a distribution server running push subscriptions to subscribers. I'm trying to figure out if I need to stop replication jobs (log ...
dba_gal's user avatar
0 votes
0 answers
14 views

Running DBCC on NON-READABLE replica

Since the system databases are not duplicated in a AG environment, I want to be able to run DBCC on them on a weekly basis and I would also like to be able to run DBCC on any databases where the AG ...
Keith Clark's user avatar
5 votes
1 answer
1k views

how to kill a rogue spid that is already killed?

I have an obstinate spid that I cannot kill and it is preventing the transaction log of my tempdb to get truncated this is how I found this rogue spid: if object_id('tempdb..#OpenTranStatus','U') is ...
Marcello Miorelli's user avatar
1 vote
1 answer
586 views

How to fix table with corrupt rows

I was inserting records into a table, when I suddenly got disconnected from the DB server When I tried doing a row count on the table, I got a fatal error 824 I tried DBCC CHECKTABLE and it indicated ...
user avatar
5 votes
1 answer
360 views

DBCC CLONEDATABASE & User Defined Table Types conflict

I am using DBCC CLONEDATABASE to create database copies without data. There are a number of databases that will not clone. The error message is: Cannot insert duplicate key row in object 'sys....
beehive's user avatar
  • 137
0 votes
1 answer
284 views

SQL Server: DBCC UPDATEUSAGE does not return [closed]

I am an experienced programmer (not a DBA) and would appreciate troubleshooting guidance if no immediate answer is possible. The database itself is relatively small - a couple of million rows spread ...
AlanK's user avatar
  • 109
0 votes
1 answer
65 views

DBCC RESULTS - 1719 Errors - how best to proceed with repair

A production database which is continually updated with external data has reported following an integrity check 1719 errors. These can be grouped into: 116 x <Dbcc ID="1" Error="8909&...
Damo's user avatar
  • 193
1 vote
1 answer
81 views

Can't drop and recreate FK in SQL

I have a DBCC check that will always fail with the following error: Msg 8992, Level 16, State 1, Line 2 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=517752334,key_index_id=1) of ...
Dominic Legendre's user avatar
0 votes
1 answer
134 views

DBCC CLONEDATABASE called from stored procedure fails

SQL Server 2019 DBCC CLONEDATABASE runs successfully when issued in a query window ... but when called from a stored procedure, fails with "User 'dbo' does not have permission to run DBCC ...
Ollie2308's user avatar
3 votes
1 answer
115 views

Formula for memory grant for DBCC CHECKDB

How is the memory grant for a DBCC checkdb determined (other than the max size for memory grants which is from resource governor, possibly the default pool)? I'm not trying to solve a specific problem....
xhr489's user avatar
  • 807
1 vote
1 answer
1k views

DBCC CLONEDATABASE returns error: Cannot insert duplicate key row in object 'sys.syssingleobjrefs

I'm trying to run the following line of code: DBCC CLONEDATABASE ([DBName],[DBName_Staging]) WITH VERIFY_CLONEDB And I'm getting the following error: Msg 2601, Level 14, State 1, Line 15 Cannot insert ...
Kenneth Fisher's user avatar
-3 votes
1 answer
329 views

How to get `dbcc checkdb` report when run via agent job?

When I run dbcc checkdb via management studio, then I get the full report as output. However when I run dbcc checkdb via sql agent job, then I think that the job status will either show as success if ...
variable's user avatar
  • 3,634
-2 votes
1 answer
46 views

What is recommended approach for fixing corruption related errors? [closed]

When there are errors of code 823 in the log or dbcc has detected corruption, then should the 1st choice to solve this be: dbcc checkdb with no data loss or restore from full backup+tlog backups?
variable's user avatar
  • 3,634
0 votes
1 answer
84 views

What is the difference between dbcc checks and suspect pages table in terms of data page corruption?

Dbcc checkdb returns errors if it detects corruption on data pages. Similarly the suspect pages table hold corruption info of data pages. What is the difference between dbcc checks and suspect pages ...
variable's user avatar
  • 3,634
1 vote
1 answer
148 views

dbcc checkdb errors, or msg 601 when restoring SQL 2012 backup to SQL 2016

I need to move two databases, WES and WILL from a Win2012/SQL2012 instance to a Win2016/SQL2016 instance for a software upgrade. I'm using the full backup files from the SQL2012 maintenance plans. I'...
JeffH's user avatar
  • 11
1 vote
1 answer
456 views

Unable to shrink data file with LOB data sql server 2017

I have a huge table that stores pdf documents using datatype "image".  Recently we started to offload files to blob storage, that is why we want to shrink main data file. All my tries to ...
Serdia's user avatar
  • 687
5 votes
2 answers
633 views

SQL Server, consistency error that I can't fix

DBCC CHECKDB on a user database returns this error Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3851, State 1: An invalid row (class=128,depid=65536,depsubid=0) was found in the system table ...
Alsin's user avatar
  • 375
0 votes
2 answers
120 views

Can you embed DBCC Shrinkfile into a IF statement?

I have an issue where my maintenance job Transaction logs do not always shrink when DBCC SHRINKFILE is run. My thought was to place DBCC SHRINKFILE within an if statement that checks the size of the ...
AJPearson's user avatar
2 votes
1 answer
3k views

SQL Server log_reuse_wait_desc is ACTIVE_TRANSACTION but there are no active transactions

Just had an issue with a SQL Server 2016 database where this query returned ACTIVE_TRANSACTION for log_reuse_wait_desc: select name,log_reuse_wait_desc from sys.databases where name='database name' ...
NBender's user avatar
  • 21
1 vote
2 answers
195 views

DBCC shrink file history

I ran shrink file(.mdf) through GUI by selecting Task -> Shrink -> Files and selected second option i.e Reorganize pages before releasing unused space. Now, I would like to know the history of ...
amanullah's user avatar
0 votes
0 answers
34 views

Getting database in suspected mode while running DBCC checkdb command

On production we had setup maintenance job for every weekend. In this maintenance job we use dbcc checkdb command in one of step. sometime while dbcc checkdb command run it gives database consistency ...
NP007's user avatar
  • 101
0 votes
1 answer
135 views

BDBID in DBCC PAGE command output in SQL Server

There is a famous undocumented DBCC PAGE command in SQL Server (as well as in SAP ASE) which if successful sends output to errorlog. Noticed that it has 4 main sections: Buffer, Page Header, Data, and ...
Sonali Gupta's user avatar
0 votes
0 answers
67 views

DBCC SHOWCONTIG returns 2 row against certain tables in one environment and 1 row in another environment [closed]

I am seeing that in 2 different SQL 2016 envs one is returning 1 row against DBCC SHOWCONTIG and in other 2 rows against the same table. Why would this be happening? Both the servers are on SQL2016 ...
kanishka basak's user avatar
1 vote
1 answer
650 views

Log File Size and dbcc sqlperf (logspace)

Consider the following log file setting for a database named XYZ: Initial size = 250MB, Autogrowth by 500MB, Limited to 1GB I have created a SQL Job to monitor the size of XYZ database ldf using the ...
Khairul's user avatar
  • 113
1 vote
2 answers
2k views

DBCC CheckDB space requirements?

Ran into the following error... K:\Microsoft SQL Server\Data\MyAudit3_Dat.NDF_MSSQL_DBCC50: Operating system error 112(There is not enough space on the disk.) encountered. In this case the disk ...
user2368632's user avatar
  • 1,097
0 votes
2 answers
264 views

Shoudn't DBCC SHIRNKFILE(Data_File, 0) command shrink data file to the last allocated extent?

I have a datafile with the following parameters: When I execute DBCC SHRINKFILE (DBAtools_data,1), it shows the following resuls: I am OK with that. My complaint is about DBCC SHRINKFILE (...
Rauf Asadov's user avatar
  • 1,273
2 votes
1 answer
334 views

fulltext_stoplists to database_principals issue

When I run a DBCC check against the database, it informs me that there is an issue with the sys.fulltext_stoplists object. It appears that the principal_id in stoplists does not have a relationship ...
CalgaryCoolCat's user avatar
0 votes
2 answers
659 views

DBCC Shrinkfile not working on SQL 2016

Executed the DBCC Shrinkfile('file1',emptyfile) successfully on first file in the filegroup and removed the file that I shrank. Executed the DBCC ShrinkFile('file2',emptyfile) unsuccessfully on file ...
Paul McKibben's user avatar
1 vote
1 answer
55 views

Lock addresses and page addresses don't match

Today, I discovered the command DBCC PAGE(). I understand that it displays information about the data stored at the page level. (This question is about SQL Server 2017 - v14.0) So this is what I do :...
Vincent PHILIPPE's user avatar
2 votes
1 answer
1k views

DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1

In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate ...
NateJ's user avatar
  • 804
5 votes
1 answer
1k views

What are the downsides of running DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') hourly?

I have been seeing some concerning behavior in the SQL Server memory caches on one of our production servers. We are on SQL Server 2014 SP2 Enterprise Edition, 16 cores, 400GB RAM (300 GB dedicated to ...
Jacob H's user avatar
  • 710
1 vote
2 answers
1k views

What realistic issues can occur from shrinking the log files on my database (either through the SSMS UI or issuing a DBCC ShrinkFile command)?

I've heard talk of potential corruption or data loss occurring from shrinking the database data or log files. I'm curious in particular about only shrinking just the log files, what are some real ...
J.D.'s user avatar
  • 37.8k
3 votes
0 answers
115 views

A curious discrepancy between DBCC PAGE and %%physloc%% for System Base Tables

During an attempt to dig into some odd system behavior I've come up against a scenario where I'm using the undocumented routine DBCC PAGE to find some information within a page, but I'm not getting ...
John Eisbrener's user avatar
0 votes
0 answers
413 views

DBCC Command Not being run by current user

I have a stored Proc that runs a DBCC command CHECKIDENT The sproc is being run by a user with adequate permissions. however, it is failing on the DBCC command with the error. User 'DifferentUser' ...
Swoozie's user avatar
  • 21
4 votes
1 answer
532 views

Unexplainable DBCC CHECKDB Command Output

Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1 (X64) Mar 22 2019 22:33:11 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter ...
Lee Crain's user avatar
0 votes
1 answer
2k views

DBCC INPUTBUFFER shows Event Type as Language Event for an unused Database

In my production SQL Server 2016 server,i had few dummy databases created as part of migrating from SQL Server 2008. I was cleaning up all the unwanted databases and i dropped few databases.But when i ...
user9516827's user avatar
  • 1,315
6 votes
2 answers
2k views

dbcc cleantable batch size explanation

I have a very large table with 500 mil rows and a Text column that I will be dropping. In my Dev environment, I have dropped the column and began the reclaim process, but im not sure what the batch ...
Tomasz's user avatar
  • 186
2 votes
1 answer
913 views

Calculating total "on row" bytes for each row ... the easy way

We want to calculate the total "on row" storage bytes for each row in the table. As we understand it, we must add up the DATALENGTH() of each column while also accounting for NULLs and things like ...
UnLogicGuys's user avatar
3 votes
0 answers
4k views

MSSQL - Unable to find index entry in index ID 1

Currently keep getting this severity 21 error through for a database that is hosted on SQL 2014 Enterprise with SP3 CU1 applied: DESCRIPTION: Unable to find index entry in index ID 1, of table ...
HBollah's user avatar
  • 61
10 votes
1 answer
1k views

SHRINKFILE Failure - Why does increasing file size resolve it?

I am running some SHRINKFILE operations to clean up a bunch of tiny, unnecessary files in a filegroup. For one of the shrinks, the command below results in an error: DBCC SHRINKFILE (N'myfile' , ...
LowlyDBA - John M's user avatar
0 votes
1 answer
1k views

DBCC on SSAS Cube fails

DBCC on the Cube fails(SQL version 2014 SP2, 12.0.5000.0). My XMLA query: Error message: Executing the query ... The DBCC element at line 7, column 88 (namespace https://schemas.microsoft.com/...
VitalicJJ's user avatar
7 votes
1 answer
885 views

DBCC CHECKTABLE taking 15+ minutes to run on an empty table

I have a database where DBCC CHECKTABLE on a number of small or empty tables taking over 15 minutes to run. When it finishes there are no failures or errors. The performance on everything else on ...
Russ960's user avatar
  • 359
1 vote
1 answer
852 views

Why does DBCC CHECKTABLE WITH TABLERESULTS only report Error 8990 on corrupt tables?

I have a production VLDB that I'm using Paul Randal's guidance on to spread DBCC CHECKDB workload over a week. I'm logging the results of DBCC CHECKTABLE WITH TABLERESULTS and DBCC CHECKALLOC WITH ...
S3S's user avatar
  • 3,578
2 votes
0 answers
1k views

DBCC ShrinkFile not working [duplicate]

I have searched the site and found numerous posts on this issue, but none really answer what I am experiencing. Background ( I know shrinkfile is bad, but in this case, it makes sense) Our client ...
DBA Greg14's user avatar
0 votes
2 answers
210 views

DBCC Shrinkfile after full backup restoration but before differential restoration

My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to ...
kalpna's user avatar
  • 1
0 votes
1 answer
2k views

MS SQL Server: DBCC ShrinkDatabase - Does it really not shrink the database below the minimum size?

Per Microsoft's documentation on DBCC ShrinkDatabase command, it shouldn't shrink the database below it's "minimum size". But when I ran the command, my database data files' initial file sizes shrunk ...
J.D.'s user avatar
  • 37.8k
3 votes
3 answers
4k views

Shrink multiple database files using sp_MSForEachDB

We have recently cleared out old data from our production database. The database is 3 TB with 1.4 TB empty this however poses a problem on the development and QA instance as we are not utilizing space ...
Vorster's user avatar
  • 294
0 votes
1 answer
2k views

Is there a specific SQL Server 2008 R2 Permission to run DBCC CHECKIDENT?

MS Docs (Books online) states the caller must either own the containing schema or be a member of fixed DB roles. (sysadmin, db_owner, db_ddladmin). Is there a specific permission or set of ...
Simon Jones's user avatar
-1 votes
1 answer
434 views

sp_blitz shows "Last good DBCC CHECKDB over 2 weeks old"

When I run sp_blitz, I see -[ RECORD 5 ]------------------------- Priority | 1 FindingsGroup | Reliability Finding | Last good DBCC CHECKDB over 2 weeks old DatabaseName |...
Evan Carroll's user avatar
  • 63.5k
1 vote
1 answer
4k views

Killed DbccFilesCompact found running for days

There were 2 running, actually, the first one I killed went away quickly. The second is in suspended status, from sys.dm_exec_requests the percent_complete doesn't change (88.87) and the ...
MariusD's user avatar
  • 29