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
-2 votes
1 answer
43 views

What is recommended approach for fixing corruption related errors?

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?
user avatar
  • 2,239
0 votes
1 answer
32 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 ...
user avatar
  • 2,239
1 vote
1 answer
63 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'...
user avatar
  • 11
1 vote
1 answer
64 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 ...
user avatar
  • 637
0 votes
0 answers
18 views

Create SQL Server foreign key link to multiple tables?

Can I have a constraint check if a column value is in either of two other columns? As an example, if I have a website and the users of the website should be staff or students, can I check the username ...
user avatar
5 votes
2 answers
173 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 ...
user avatar
  • 335
0 votes
2 answers
51 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 ...
user avatar
2 votes
1 answer
352 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' ...
user avatar
  • 21
1 vote
2 answers
51 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 ...
user avatar
0 votes
0 answers
17 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 ...
user avatar
  • 101
0 votes
1 answer
55 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 ...
user avatar
0 votes
0 answers
39 views

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

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 ...
user avatar
1 vote
1 answer
259 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 ...
user avatar
  • 13
1 vote
2 answers
652 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 ...
user avatar
0 votes
2 answers
82 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 (...
user avatar
  • 1,163
2 votes
1 answer
105 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 ...
user avatar
0 votes
2 answers
196 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 ...
user avatar
1 vote
1 answer
51 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 :...
user avatar
1 vote
1 answer
385 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 ...
user avatar
  • 764
5 votes
1 answer
630 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 ...
user avatar
  • 700
1 vote
2 answers
200 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 ...
user avatar
  • 24.2k
3 votes
0 answers
58 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 ...
user avatar
0 votes
0 answers
133 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' ...
user avatar
  • 11
4 votes
1 answer
373 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 ...
user avatar
0 votes
1 answer
1k 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 ...
user avatar
  • 1,285
6 votes
2 answers
1k 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 ...
user avatar
  • 184
2 votes
1 answer
397 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 ...
user avatar
1 vote
0 answers
3k 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 ...
user avatar
  • 41
10 votes
1 answer
472 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' , ...
user avatar
0 votes
1 answer
681 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/...
user avatar
7 votes
1 answer
555 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 ...
user avatar
  • 359
1 vote
1 answer
569 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 ...
user avatar
  • 3,488
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 ...
user avatar
0 votes
2 answers
124 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 ...
user avatar
  • 1
0 votes
1 answer
953 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 ...
user avatar
  • 24.2k
3 votes
3 answers
3k 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 ...
user avatar
  • 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 ...
user avatar
-1 votes
1 answer
368 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 |...
user avatar
  • 55.7k
1 vote
1 answer
3k 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 ...
user avatar
  • 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 ...
user avatar
9 votes
2 answers
13k 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 ...
user avatar
-1 votes
2 answers
622 views

How to schedule and perform shrinking of database-files

am using the below code to peform shrink the db to release unused space as my db's log files are growing exponentially. But i wanted to use this as a scheduled event,running every two days at night ...
user avatar
1 vote
0 answers
703 views

Database consistency errors [closed]

I am using SQL Server 2008 R2 and one of my databases has loads of consistency errors. When I try to query a particular record from a table it displays an error message similar to this Msg 824, Level ...
user avatar
3 votes
1 answer
3k views

Shrinking during backup

All, I'm familiar with the good reasons not to shrink a database and I am in the process of shrinking a 2TB one. We have a large database that gets backed up each day (by policy) and because of the ...
user avatar
0 votes
2 answers
146 views

How does a integer data record store in SQL Server page

I have seen the 8KB (8192 = 96 Header + 36 Row Offset + 8060 Free space) page architecture in SQL Server. When it comes to storing a data record in Page i am confused. In Below table i have create ...
user avatar
2 votes
1 answer
326 views

Page Number in SQL Server database is not starting from zero

I have a question on SQL Server page, I have created a new database with only one new table as below Here is the Code: CREATE TABLE PAGETEST ( ID int primary key ) When i see my staring page ...
user avatar
1 vote
0 answers
543 views

How can I fix SQL Server 2008 orphaned columns RE: msg 3853

I administer a SQL Server, but do not administer the actual application that runs on it. It is SQL 2008 r2 (10.50.1600.1) Recently when the company that develops and manages the application was ...
user avatar
  • 11
4 votes
1 answer
462 views

Is there a way to catch a dbcc checkident command

A colleague of mine noticed incorrect ids generated for inserted items. Most probable cause: some script issues a command like the following: dbcc checkident('dbo.table', reseed, 1) I have thought ...
user avatar
  • 1,199
0 votes
1 answer
1k views

Shrinking TEMPDB

Is it necessary to restart SQL Server after DBCC SHRINKFILE on tempdb.mdf and tempdb.ldf or is there any other method to reduce the size of tempdb.mdf and tempdb.ldf without restarting SQL Server? Is ...
user avatar
  • 191
5 votes
2 answers
954 views

How can I save the results of DBCC SHRINKFILE into a table?

I have the following script: -- available space in each of the database files PRINT @@SERVERNAME --SQLDWDEV01 USE [BodenStage] GO SELECT name ,(CAST(ROUND((size/128.0 - CAST(FILEPROPERTY(name, '...
user avatar