Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [dbcc]

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

141 questions
42
votes
3 answers

What is the difference between Shrink Database and File?

DBCC ShrinkDatabase() DBCC ShrinkFile() Do I need to run both DBCC commands in order to shrink the database? What is the difference between these two above?
Jango
33
votes
6 answers

Shrinking the log file does not reduce size

I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL. 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…
Navaneet
  • 923
  • 5
  • 12
  • 23
16
votes
1 answer

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) in file ’BrokenDatabase.mdf'. Error: 3043,…
Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125
12
votes
4 answers

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); and then delete the file: ALTER DATABASE tempdb…
AdamL
  • 415
  • 1
  • 5
  • 12
12
votes
4 answers

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 tuning and no DBCC commands are available for…
user32207
  • 141
  • 1
  • 1
  • 3
11
votes
1 answer

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 follow, but I want to build a definitive list that is…
Eric Higgins
  • 2,689
  • 1
  • 19
  • 25
10
votes
1 answer

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' , EMPTYFILE)' File ID x of database ID x cannot be…
LowlyDBA - John M
  • 11,059
  • 11
  • 45
  • 63
10
votes
2 answers

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 database size (not a filegroup size, database size).…
Saeed Neamati
  • 1,515
  • 3
  • 21
  • 35
9
votes
2 answers

Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?

I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time. Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due to space constraints and uptime requirements. I…
RelentlessMike
  • 93
  • 4
9
votes
1 answer

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 shrink this down to 50GB. I know that shrinking…
ptreston
  • 91
  • 1
8
votes
1 answer

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 it states that it should only be used while…
Andreas Ågren
  • 644
  • 4
  • 11
8
votes
1 answer

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 of my database its showing following output. Why…
Aasim Abdullah
  • 2,975
  • 4
  • 25
  • 40
8
votes
1 answer

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 servers: TraceFlag Status Global Session 1204 …
RBarryYoung
  • 3,051
  • 4
  • 25
  • 42
7
votes
3 answers

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 requires the sysadmin role (DBCC TRACEON(1224)).…
usr
  • 7,390
  • 5
  • 33
  • 58
7
votes
2 answers

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 GROUP BY on the same data causes an "internal…
Petrus Theron
  • 303
  • 2
  • 3
  • 9
1
2 3
…
9 10