Stack Overflow
Stack Overflow

Questions tagged [dbcc]

DBCC stands for Database Console Commands in Transact-SQL.

The Database Console Commands (DBCC) are a series of statements in Transact-SQL programming language to check the physical and logical consistency of a Microsoft SQL Server database. These commands are also used to fix existing issues. They are also used for administration and file management.

DBCC was previously expanded as Database Consistency Checker.

Source: Wikipedia (Database Console Commands)

106 questions
84
votes
10 answers

DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

I've got a database, [My DB], that has the following info: SQL Server 2008 MDF size: 30 GB LDF size: 67 GB I wanted to shrink the log file as much as possible and so I started my quest to figure out how to do this. Caveat: I am not a DBA (or even…
Ed Sinek
  • 4,829
  • 10
  • 53
  • 81
18
votes
1 answer

ODBC problems in SQL 2000 --> 2005 Upgrade

This wiki post outlines both a problem and a solution. I wanted to post this for others that may be having similar problems, as I couldn't find anything specifically to solve this problem elsewhere. We recently upgraded our SQL Server 2000 database…
Jayden
  • 2,656
  • 2
  • 26
  • 31
12
votes
3 answers

Currently running query inside a stored procedure

I have a stored procedure that is currently running, and seems to hang/lock on a specific query. How can i see which query? Preferably without modifying the proc. Using DBCC Inputbuffer (65) gives me Language Event 0 EXEC mySP;
cederlof
  • 7,206
  • 4
  • 45
  • 62
12
votes
5 answers

Reduce SQL Server table fragmentation without adding/dropping a clustered index?

I have a large database (90GB data, 70GB indexes) that's been slowly growing for the past year, and the growth/changes has caused a large amount of internal fragmentation not only of the indexes, but of the tables themselves. It's easy to resolve…
SqlRyan
  • 33,116
  • 33
  • 114
  • 199
9
votes
1 answer

SQL identity (1,1) starting at 0

I have a SQL table with an identity set: CREATE TABLE MyTable( MyTableID int IDENTITY(1,1) NOT NULL, RecordName nvarchar(100) NULL) Something has happened to this table, resulting in odd behaviour. I need to find out what. When an insert…
Keith
  • 150,284
  • 78
  • 298
  • 434
7
votes
2 answers

can i cleanup buffer for some specified database instead of the entire sql server

from what i know, if i execute 'DBCC FREEPROCCACHE' and 'DBCC DROPCLEANBUFFERS', the buffer of entire server will be cleanup. i wonder if there's anyway to cleanup buffer of specified database only. therefore, query for other databases will not be…
Narutokk
  • 964
  • 1
  • 8
  • 20
6
votes
2 answers

DBCC CLEANTABLE is not freeing used space

I have about 300 tables with 5.5kk rows each. One of the rows is using nvarchar(128) as a datatype (SQL Server 2012). We decided to change this to int and add FK to dictionary table with all nvarchars. After doing all of it I removed the nvarchar…
Dmytro Aleksin
  • 95
  • 1
  • 6
6
votes
1 answer

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 following statement when I was looking for how to…
agdiaz
  • 63
  • 3
6
votes
6 answers

tsql to know when dbcc checkdb was run on a database last time

Using Tsql, how can i know when DBCC checkdb was last run on SQL server (2000, 2005 or 2008)? Regards
Manjot
  • 11,166
  • 9
  • 38
  • 49
6
votes
2 answers

DBCC CHECKDB to check errors in SQL Server database

Is it possible to run: DBCC CHECKDB on a specific table in SQL Server 2005 database? I have the following syntax: DBCC CHECKDB [ [ ( database_name | database_id | 0 [ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST |…
PriceCheaperton
  • 5,071
  • 17
  • 52
  • 94
5
votes
4 answers

DBCC CHECKIDENT(myTable, RESEED,1) reseeding from 2

In SQL Server 2012, the following query is seeding the identity column myTable_id from 2 instead of 1. Why? myTable_id is also PK. DELETE FROM myTable; GO SELECT * FROM myTable --0 rows are returned as expected GO DBCC CHECKIDENT(myTable,…
nam
  • 21,967
  • 37
  • 158
  • 332
5
votes
1 answer

SQL Server: Hidden risks of using DBCC SHRINKFILE

We're getting a huge LDF file by using a full recovery mode in a SQL Server DB. so we're planning to shrink the log file. Is there any performance penalty for using DBCC SHRINKFILE to reduce the database log file size (LDF)? and what about applying…
SDReyes
  • 9,798
  • 16
  • 53
  • 92
5
votes
1 answer

DBCC SHOWCONTIG : Row Count size

Using the DBCC SHOWCONTIG command we get the size of a row in minimum, maximum and on average. Just to make sure, the unit of Measurement is Byte right?
Abdul K
  • 73
  • 1
  • 7
4
votes
1 answer

How to get table name from database id, file id, page id in MS SQL 2008?

I've deadlock graph in which the locked resource is mentioned by these three fields DB ID, File ID, Page ID. There is also some associated objectid. All I want to know is what table this page belongs. I tried DBCC PAGE(dbid, fileid, pageid) with…
Ankush
  • 2,454
  • 2
  • 21
  • 27
4
votes
1 answer

SQL Server logging failed queries

I'm trying to implement a system-wide logging, which would log all failed Stored Procedure executions in our dabatase and I'm looking at Extended Events. I've done some research and it seems quite easy to capture failed statements using following…
Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107
1
2 3 4 5 6 7 8