December 14, 2012 at 4:20 am
Hi All,
This is Rangaraja.im fresher.
Can anyone please explain mostly used in real time DBCC Commands?
Regards,
Rangaraja
December 14, 2012 at 4:53 am
hi ,
please check below link..
in this commands,most frequetly used command are
DBCC INPUTBUFFER(Spid) to get query
DBCC shrinkfile(file_id ,size) to shrink file
December 14, 2012 at 5:45 am
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
December 14, 2012 at 6:37 am
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" 😉
December 14, 2012 at 7:56 am
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
December 14, 2012 at 9:20 am
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
December 14, 2012 at 11:59 am
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
December 17, 2012 at 12:00 am
hi ,i m executing DBCC checkdb on weekends for large database , bcoz its taking more time to execute .
Need suggestion whether to execute daily..
December 17, 2012 at 1:07 am
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" 😉
December 17, 2012 at 1:25 am
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
December 28, 2012 at 3:20 am
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.
December 28, 2012 at 3:39 am
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
January 2, 2013 at 3:15 am
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)
January 2, 2013 at 7:46 pm
On the other side... I have never run REPAIR_ALLOW_DATA_LOSS.
-- Itzik Ben-Gan 2001
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply