Questions tagged [dbcc]
DBCC is a T-SQL statement provided by SQL Server. This tag is used for questions regarding DBCC and its use.
136
questions
1
vote
1
answer
128
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 ...
jambajuice
0
votes
0
answers
36
views
DBCC error with sys.syscolpars DB upgrade failing
Consistency error in DB.
1 page from sys.syscolpars table is missing.
There is no any impact but the thing is can't able to upgrade the DB from 2008 to higher version. Backup restore fails. Old good ...
- 51
5
votes
1
answer
228
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....
- 123
1
vote
1
answer
165
views
SQL Server: DBCC UPDATEUSAGE does not return
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 ...
- 111
0
votes
1
answer
49
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&...
- 193
1
vote
1
answer
68
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 ...
0
votes
1
answer
94
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 ...
- 11
3
votes
1
answer
91
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....
- 785
0
votes
1
answer
763
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 ...
- 24k
-3
votes
1
answer
247
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 ...
- 3,360
-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?
- 3,360
0
votes
1
answer
74
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 ...
- 3,360
1
vote
1
answer
132
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'...
- 11
1
vote
1
answer
372
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 ...
- 657
5
votes
2
answers
465
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 ...
- 345
0
votes
2
answers
101
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 ...
2
votes
1
answer
2k
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'
...
- 21
1
vote
2
answers
134
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 ...
- 99
0
votes
0
answers
32
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 ...
- 101
0
votes
1
answer
118
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 ...
- 99
0
votes
0
answers
63
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 ...
1
vote
1
answer
516
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 ...
- 13
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 ...
- 1,077
0
votes
2
answers
219
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 (...
- 1,243
2
votes
1
answer
243
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 ...
0
votes
2
answers
447
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 ...
1
vote
1
answer
54
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 :...
- 111
2
votes
1
answer
950
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 ...
- 794
5
votes
1
answer
998
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 ...
- 710
1
vote
2
answers
851
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 ...
- 34k
3
votes
0
answers
100
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 ...
- 9,282
0
votes
0
answers
357
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' ...
- 21
4
votes
1
answer
485
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 ...
- 41
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 ...
- 1,295
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 ...
- 186
2
votes
1
answer
804
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 ...
- 399
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 ...
- 61
10
votes
1
answer
943
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' , ...
- 10.9k
0
votes
1
answer
966
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/...
- 55
7
votes
1
answer
784
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 ...
- 359
1
vote
1
answer
780
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 ...
- 3,548
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 ...
- 255
0
votes
2
answers
188
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 ...
- 1
0
votes
1
answer
1k
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 ...
- 34k
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 ...
- 294
0
votes
1
answer
1k
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 ...
- 145
-1
votes
1
answer
420
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 |...
- 61.4k
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 ...
- 29
0
votes
1
answer
2k
views
What can cause DBCC CheckDB to take longer than usual? [closed]
I have a maintenance task, which do a DBCC CHECKDB WITH PHYSICAL_ONLY on the Databases on my server (SQL Server 2016 Standard Edition).
This usually takes about 7h30 for about 120 databases and it is ...
- 1,999
10
votes
2
answers
17k
views
How to free the unused space for a table
This question is asked like tens of times, and to my surprise such a simple requirement comes so difficult. Yet I can't solve this problem.
I use SQL Server 2014 Express edition with 10GB limit of ...
- 1,407