November 20, 2007 at 3:13 am
hi , wht r all dbcc cmds requeried in producation server for dba
any 1 pls help...........
November 20, 2007 at 7:04 am
Go to BOL (Books on Line) and type 'DBCC' and you'll get a nice list of all the documented DBCC commands.
/Kenneth
November 20, 2007 at 11:15 am
November 20, 2007 at 11:22 am
November 21, 2007 at 11:45 pm
NONE. DBCC Commands are resource hungry and can cause locking issues DBCC reindex for example. Recommend you have a full set of backups and your resume and job references in order before you attempt these on a production system.
That said DBCC commands are powerful and should be tested against a test/backup database before using.
CodeOn 😛
November 22, 2007 at 12:26 am
Malcolm Daughtree (11/21/2007)
NONE.
You never use CheckDB on your systems?
Index rebuilds don't need DBCC anymore (alter index ... rebuild) and most of the other DBCCs aren't needed except when there are problems. CheckDB's the only one I'd consider crucial to run on a prod server from time to time. During maintenance periods, of course.
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
November 22, 2007 at 4:34 am
Hi,
As GilaMonster said, there a lot processes you can only do with DBCC commands.
I know that Some actions that in earlier releases require DBCC options are performed by ALTER. but DBCCs still useful commands.
DBCC commands have powerful documented functions and many undocumented capabilities that you might not know about (You can learn more about undocumented DBCC commands by using DBCC HELP with the 2520 trace flag turned on).
Otherwhise check http://msdn2.microsoft.com/en-us/library/ms188796.aspx where DBCC are grouped by categorie.
Microsoft also introduces 7 news DBCCs, check the following link http://www.sqlservercentral.com/articles/Administration/sqlserver2005dbcccommandquickreference/1628/
Regards,
Ahmed
November 22, 2007 at 3:57 pm
I'm not saying don't use them but please read what they do and their recovery possibilities
DBCC Checkdb
Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.
DBCC CHECKFILEGROUP
If any errors are reported by DBCC CHECKFILEGROUP, we recommend restoring the database from the database backup. Note that repair options cannot be specified to DBCC CHECKFILEGROUP.
If no backup exists, running DBCC CHECKDB with a repair option specified corrects the errors reported. The repair option to use is specified at the end of the list if reported errors. Correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require that some pages, and therefore data, be deleted. http://technet.microsoft.com/en-us/library/ms190488.aspx
DBCC SHRINKFILE
In SQL Server 2005, when a DBCC SHRINKFILE operation fails an error is raised. In earlier versions of SQL Server, this operation failed without raising an error. http://technet.microsoft.com/en-us/library/ms189493.aspx
DBCC DBReindex
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.
DBCC DBREINDEX is an offline operation. If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation. This prevents modifications to the table. If the clustered index is being rebuilt, an exclusive table lock is held. This prevents any table access, therefore effectively making the table offline.
DBCC DBRepair
This feature is not available in SQL Server 2005. Use DROP DATABASE instead. -- You have to ask yourself why ?? -- Answer the DBCC commands are written at a very low level and can be very invasive.
DBCC FREESYSTEMCACHE
Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. - not good on a Production machine.
DBCC INDEXDEFRAG
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.
DBCC SHRINKDATABASE
Consider the following information when you plan to shrink a database:
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
All I'm trying to convey is that these commands exist to help correct something that the normal operation of SQL server hasn't done.;)
CodeOn 😛
November 23, 2007 at 2:17 am
Malcolm Daughtree (11/22/2007)
All I'm trying to convey is that these commands exist to help correct something that the normal operation of SQL server hasn't done.;)
I think you're missing the point.
What (hopefully) most people use DBCC for, and what Gila talks about, is
*detection* of problems.
The 'find errors' aspect of DBCC is the most important one of this tool, and you indeed
want to run that on your production systems.
If you don't and something has happened that you should have detected, then I guess
that 'prepare resumé' thing would become imminent.
How to eventually fix any problems discovered, is something else entirely.
DBCC commands aren't a 'fix-it-all' tool at all.
/Kenneth
November 23, 2007 at 2:43 am
A few comments on your comments.
Malcolm Daughtree (11/22/2007)
DBCC CheckdbUse the REPAIR options only as a last resort.
imho, the use of CheckDB isn't to fix corruption errors, it's to detect if you have any errors, before they cause problems with user queries, or the database becomes suspect. Once you know that there's corruption, a plan can be made to fix that, either by rebuilding an index, dropping a table, restoring a page, a filegroup or the entire DB.
If no backup exists, running DBCC CHECKDB with a repair option specified corrects the errors reported. The repair option to use is specified at the end of the list if reported errors. Correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require that some pages, and therefore data, be deleted.
Repair_allow_data_loss WILL cause data to be lost. That's why it's called as it is. There are some corruptions that CheckDB with any of the repair operations cannot repair. (corruptions in the system tables, the PFS pages or other critical database structures)
If there's corruption in the production databasse and there's no backup, then it's definitly time to fix that CV up, though not because of running DBCC commands.
DBCC FREESYSTEMCACHE
Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server.
DBCC FREEPROCCACHE clears the procedure cache. FREESYSTEMCACHE takes a cache name as a parameter and can clear any of the system caches. Not recommended for production servers unless you really know what you're doing.
DBCC SHRINKDATABASE
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree.
Shrink usually causes massive fragmentation in indexes because it operates by taking pages from the end of the file and moving them earlier in the file. In worst cases you can have an index that is in a completely reversed order.
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
November 23, 2007 at 3:13 am
Whilst I agree that Malcolm's original post might have been a tad sweeping for my taste, I agree in principle with his words of caution. The originator of this thread was effectively asking, "I know there are a load of really powerful tools; which ones should I be using?". Imagine an airline pilots' forum, and someone posting, "Hey, in my plane, there are loads of buttons; which ones should I be using?"
I'm not trying to belittle the original poster, since they're doing a good thing coming and asking. However, the question as it was worded implies putting the tools before defining the "what we're trying to achieve", and I think Malcolm's quite right in steering someone away from using those tools until they know why they're needed.
Of course, the original poster may know that already, and simply be looking for a complete reference, but we've only got the post to go on.
Semper in excretia, suus solum profundum variat
November 25, 2007 at 5:15 pm
Most advisedly, and assured these DBCC commands are useful and yes I indeed use them as a part of the overall maintenance of my SQL Servers (at last count 33), but not as a "Silver Bullet" to cure all ills. I have actually learnt a great deal from the discussion herein and thereby motivated me to further develop and refine my own use of DBCC commands. Thanks all to contributing to my ongoing education 😎
Whilst on this discovery I found this useful. Needs some tweeking for SQL2005
CREATE PROCEDURE [dbo].[sp_defragment_indexes] @maxfrag DECIMAL
AS
/*
This stored procedure checks index fragmentation in a database and defragments
indexes whose scan densities fall below a specified threshold, @magfrag, which
is passed to the SP. This SP was initially based on a code sample in SQL Server 2000
Books Online.
Must be run in the database to be defragmented.
*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)
--check this is being run in a user database
SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT 'This procedure should not be run in system databases.'
RETURN
END
--begin Stage 1: checking fragmentation
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0
-- Create the temporary table to hold fragmentation information
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database running dbcc showcontig on each one
FETCH NEXT
FROM tables
INTO @tableidchar
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Report the ouput of showcontig for results checking
SELECT * FROM #fraglist
-- Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Write to output start time for information purposes
SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
-- Open the cursor
OPEN indexes
-- Loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
SELECT @execstr = 'DBCC DBREINDEX (' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) +
', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT 'Now executing: '
SELECT(@execstr)
EXEC (@execstr)
SET QUOTED_IDENTIFIER OFF
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Report on finish time for information purposes
SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
-- Delete the temporary table
DROP TABLE #fraglist
CodeOn:P
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply