SQL Server Performance Forum – Threads Archive
Is that a way to suppress this DBCC message?I wonder whether there is any setting to suppress the following message genarated in issuing DBCC commands. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Well, sort of. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />DBCC CHECKDB (‘master’) WITH NO_INFOMSGS<br /><br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
derrickleggett, Thanks. WITH NO_INFOMSGS can be used for dbcc sqlperf. Since this is not in BOL, will this be considered as undocumented feature which may not work later on?
I kind of doubt they are taking this one away. It is documented as part of the DBCC process syntax. It’s just not spelled out specifically which processes can use or not use. From Books Online: DBCC CHECKDB Recommendations
In MicrosoftÂ® SQL Serverâ„¢ 2000, you can run DBCC CHECKDB while users are using the database because of a change in the type of locks that DBCC CHECKDB holds on the database tables as it checks each one. In SQL Server 7.0 and earlier, DBCC CHECKDB (which in turn runs DBCC CHECKTABLE and CHECKALLOC on each table in the database) used to hold shared locks (S) on the tables, thus blocking all data modification language (DML) statements. In SQL Server 2000, DBCC CHECKDB holds a schema lock on the table to prevent meta data changes while the table is being checked, thus allowing DML statements but not any data definition language (DDL) statements on the tables being checked. This change provides greater flexibility as to when you can run DBCC CHECKDB because DBCC CHECKDB does not deny system usage completely to the users. DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that requires checking must first be read from disk into memory. In addition, DBCC CHECKDB uses tempdb to do sorting. If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log. It is recommended that DBCC CHECKDB be run during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, expect a performance hit on the transaction throughput as well as DBCC CHECKDB completion time. Recommendations for Good DBCC Performance
Run CHECKDB when the system usage is low.
Be sure that you are not performing other disk I/O operations, such as disk backups.
Place tempdb on a separate disk system or a fast disk subsystem.
Allow enough room for tempdb to expand on the drive. Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.
Avoid running CPU-intensive queries or batch jobs.
Reduce active transactions while a DBCC command is running.
Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.
Consider using DBCC CHECKDB with the PHYSICAL_ONLY option to check the physical structure of the page and record headers. This operation performs a quick check if hardware-induced errors are suspect. Â©1988-2000 Microsoft Corporation. All Rights Reserved. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
derrickleggett, I find this in BOL under title DBCC (Level 4): SQL Server 6.x
The DBCC PERFMON and DBCC SQLPERF statements documented SQL Server performance statistics used for studying SQL Server performance. SQL Server 2000
No longer documented. These statements may change in a future release of SQL Server.
Use the Windows 2000 System Monitor Windows NT 4.0 Performance Monitor to monitor the performance counters for SQL Server. For more information, see Monitoring with Windows Performance Monitor. I guess this is written before MS decides to put DBCC SQLPERF back to BOL. Have you ever seen any message or error other than "DBCC execution completed. If DBCC printed error messages, contact your system administrator" in running dbcc sqlperf?
I believe there is a difference between other DBCC commands and this SQLPERF, may be Kalen’s Inside SQL Server 2000 might refer this topic, check it out. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.