Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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
0answers
41 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' ...
3
votes
1answer
123 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 ...
0
votes
1answer
195 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 ...
5
votes
2answers
316 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 ...
2
votes
1answer
141 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 ...
1
vote
0answers
811 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 ...
10
votes
1answer
121 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' , ...
0
votes
1answer
100 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/...
7
votes
1answer
236 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 ...
1
vote
1answer
143 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 ...
2
votes
0answers
86 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 ...
0
votes
2answers
43 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 ...
0
votes
1answer
361 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 ...
3
votes
3answers
1k 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 ...
0
votes
1answer
567 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 ...
-1
votes
1answer
211 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 |...
2
votes
1answer
1k 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 ...
0
votes
1answer
957 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 ...
7
votes
2answers
5k 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 ...
0
votes
1answer
390 views

sp_cycle_errorlog issue

On two of my fifteen SQL Server 2008 R2 instances, my weekly sp_cycle_errorlog job fails very often. One instance has 28 failures out of the last 36 weekly runs, with error: Executed as user: [...
-1
votes
2answers
226 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 ...
2
votes
0answers
484 views

Database consistency errors

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 ...
3
votes
1answer
2k 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 ...
0
votes
2answers
91 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 ...
2
votes
1answer
186 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 ...
1
vote
0answers
339 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 ...
4
votes
1answer
310 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 ...
0
votes
1answer
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 ...
5
votes
2answers
603 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, '...
-1
votes
1answer
55 views

SHRINK LOG FILE

I have a general question, maybe too simple, but I really need to understand it. Is it correct to shrink a log file up to its initial size or it is better to give higher value ? Thanks in advance.
1
vote
1answer
1k views

Does 2 in the DBCC LOGINFO column “Status” mean it cannot be truncated?

When I run the command DBCC LOGINFO, a few of the Status rows contain 2. Now I want to check why my transaction log cannot be truncated. How do I see this? Does the 2 mean in the Status that they ...
2
votes
2answers
4k views

How can I check if my log is truncated?

I have a testdb. the recovery mode is on simple and I am back my system up with veeam. Now how can i check if my log from the testdb was being truncated after the backup or not? I just found some ...
1
vote
1answer
140 views

DBCC PAGE read page from buffer pool or directly from the file system?

We know that SQL Server always read pages from disk to memory for all its operations. So it's possible that a page in memory is different from it's counterpart in disk. I want to know the command DBCC ...
1
vote
1answer
3k views

Why does FREEPROCCACHE let you shrink tempdb?

Somebody did something stupid and tempdb exploded in size. Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked). ...
3
votes
2answers
386 views

Interesting DBCC CHECKDB scenario

I woke up this morning to corruption in one of my databases. I have a job that runs daily DBCC CHECKDB's for all databases on a server. The output indicated corruption in only ONE table (fortunately)...
1
vote
1answer
2k views

dbcc checkdb gives error “Incorrect PFS free space information for page”

dbcc checkdb query for database gives errors. What is it, is it dangerous and how to resolve? Full log file: download file or view on github (errors begin from string 364) Possible There was disk ...
3
votes
1answer
74 views

Why can't I shrink my logfile below to the requested value?

Experimenting VLFs behaviors with my test lab. Firsty, I have created two databases (BASE2 & BASE3) with different log files size (1024 & 2048 MO) : Secondly, I'am running the following ...
1
vote
1answer
2k views

How to enable multiple traceflags

I want to enable trace flags 4199, 8024, 9024, 1236 & 1117 on SQL SERVER (Startup Parameters). I went to SQL Server Configuration Manager -> SQL Server ("instance_name"), and then clicked ...
3
votes
1answer
622 views

DBCC IND not working

I'm trying to get the pages used by a clustered index on table1 in a SQL2008 R2 DEV box using the below query. dbcc traceon(3604, -1) go dbcc ind (0, 'dbo.table1', 1) go but it still displays the ...
3
votes
1answer
279 views

Difference size DBCC Page and DBCC Fileheader

I'm trying to read the current database size using dbcc command. In my test I execute following commands: DBCC Fileheader('tempdb',1) => size = 988152 DBCC Page('tempdb',1,0,3) => size = 5112 ...
1
vote
3answers
79 views

Is deleting secondary files a bad idea?

I have inherited a SQL DB that has 16 db files. All in the same filegroup of the 16 3 are set to grow the others are static consuming 1 GB each. Would there be a performance impact if I deleted the ...
1
vote
1answer
853 views

Real corruption?

We have a production database for Solarwinds Orion Network Performance Monitor in which we discovered corruption in a MapStudioFiles table following DBCC CHECKDB errors. CHECKDB Output: Msg 8961, ...
4
votes
2answers
1k views

DBCC Usage on production system

We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores). Another VM (the application server) within the system accesses the db server quite intensively for both ...
2
votes
1answer
535 views

Removing Tempdb .ndfs

Tempdb is configured with 8 files and we are reducing them to 4. I read many blogs where SQL Server will allow you to remove excess .ndf's if you run the 4 dbcc drop and free statements, then run the ...
0
votes
1answer
637 views

SQL Server 2012 - Deadlock happening daily

We have a performance issue with a 3rd-Party application using SQL Server 2012: deadlock happen at least 10 times an hour. The server is virtual server on VMWare 5.5 with PVSCSI adapter in persisted ...
5
votes
1answer
678 views

Which type of repair level is “DBCC CHECKDB (databasename, repair)”?

The MSDN's article about the command "DBCC CHECKDB" explains three ways to perform a database repair in the syntax section: - REPAIR_ALLOW_DATA_LOSS - REPAIR_FAST - REPAIR_REBUILD But I found the ...
2
votes
1answer
167 views

Questions about DBCC FREEPROCCACHE

I used spAskBrent for troubleshooting a SQL Server 2005 performance issue. I found the problem was someone had ran DBCC FREEPROCCACHE. It caused low performance (very low PLE) on my database and ...
1
vote
1answer
702 views

How to find percentage data change after last full backup?

I am trying to find the data change rate for all databases on a server. In the past, I used Paul Randal's script How much of the database has changed since the last full backup which worked great. ...
2
votes
1answer
138 views

Trace flag is running, but not message when deadlock happens

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior More information on the trace flags are here DBCC TRACESTATUS this above, is the ...
3
votes
2answers
198 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 ...