DBCC Commands

Question

Post reply

DBCC Commands

brangaraja

SSC Journeyman

Points: 95
More actions
December 14, 2012 at 4:20 am

#286212

Hi All,
This is Rangaraja.im fresher.
Can anyone please explain mostly used in real time DBCC Commands?
Regards,
Rangaraja
rangarajasqldba@gmail.com

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Hemant.R Ten Centuries Points: 1286 More actions · Answer 1

hi ,

please check below link..

http://blog.sqlauthority.com/2007/10/19/sql-server-types-of-dbcc-commands-when-used-as-database-console-commands/

in this commands,most frequetly used command are

DBCC INPUTBUFFER(Spid) to get query

DBCC shrinkfile(file_id ,size) to shrink file

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Hemant.R (12/14/2012)
DBCC shrinkfile(file_id ,size) to shrink file

I would certainly hope shrink isn't a 'most frequently used command', as one should not shrink database files without good reason.

DBCC Inputbuffer can be mostly replaced with the sys.dm_exec_sql_text DMV.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

DBCC CHECKDB would be one of the most common if you're smart 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

I used to Use DBCC SHOWCONTIG quite a bit for looking at fragmentation but have replaced that with the SQL statements against DMV tables and functions, especially for partitioned tables.

DBCC REINDEX or INDEXDEFRAG, but again i've switched to using ALTER INDEX WITH REBUILD/REORGANISE.

DBCC ShrinkDB normally only after restoring to a db to dev environment with limited space or as an emergancy measure on a log file once its backed up if its very low on disk.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

DBCC Checkdb would be my vote for most frequently used - or at least should be very high on the list.

DBCC updateusage

DBCC CheckConstraints would be another useful command.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

SQLRNNR (12/14/2012)
DBCC CheckConstraints would be another useful command.

I hope people aren't having to use DBCC CHECKCONSTRAINTS all that often 😉

DBCC CHECKIDENT is one that comes to mind that I have seen become part of standardized processes although it probably should not have made it that far. It has many valid ad hoc uses, especially in production support and unit testing scenarios.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Hemant.R Ten Centuries Points: 1286 More actions · Answer 7

hi ,i m executing DBCC checkdb on weekends for large database , bcoz its taking more time to execute .

Need suggestion whether to execute daily..

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Hemant.R (12/17/2012)
hi ,i m executing DBCC checkdb on weekends for large database , bcoz its taking more time to execute .
Need suggestion whether to execute daily..

Try using the PHYSICAL_ONLY clause during the week and full checkdb at weekend

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

If you retain your backups for a week or more, running CheckDB just on weekends is fine. Just be sure that you retain backups (full and log) long enough that restoring from a backup is always an option if you do find corruption.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 10

I definitely use DBCC INPUTBUFFER(spid) and DBCC OPENTRAN the most and at times DBCC SQLPERF. These are the most basic, effective and commonly used DBCC commands.

Apart from that I've used DBCC SHOWCONTIG and DBCC INDEXDEFRAG as and when required.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

sqlnaive (12/28/2012)
Apart from that I've used DBCC SHOWCONTIG and DBCC INDEXDEFRAG as and when required.

Both of those were deprecated 7 years ago, are included only for backward compatibility with SQL 2000 and should not be used any longer.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 12

Correctly said Gail, these two were the DBCC commands which I had used a lot during those days and were really a blessing while looking for optimization. Though now there are new and efficient ways like using sys.dm_db_index_physical_stats (for DBCC SHOWCONTIG) and ALTER INDEX...REORG (OR REBUILD) (for DBCC INDEXDEFRAG)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 13

On the other side... I have never run REPAIR_ALLOW_DATA_LOSS.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001