Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2013-05-28 : 22:08:30
|
| Patrons,The Output from CHECKdbDBCC results for 'SERVICEMIXS'.Service Broker Msg 9675, State 1: Message Types analyzed: 14.Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.Service Broker Msg 9667, State 1: Services analyzed: 3.Service Broker Msg 9668, State 1: Service Queues analyzed: 3.Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.DBCC results for 'sys.sysrowsetcolumns'.There are 8642 rows in 57 pages for object "sys.sysrowsetcolumns".CHECKDB found 0 allocation errors and 0 consistency errors in database 'SERVICEMIXS'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Though it seems no errors from the CHECKDB.....We dont have REINDEX process on any of the Database(s).We have Each Table is built with 1 Clustered Index and Several Non-Clustered Indexes, NowWill it be ok if non Clustered Index can be REBUILT without touching Clustered Index OR both to be rebuilt.By doing so, both Clustered and Non-Clustered Indexes were refresh altogether?Can you analyse this situation for me. Thanks All. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-29 : 00:37:12
|
| It's good that CHECKDB() returned no errors. This means there is no corruption in the database.You can rebuild non-clustered index without rebuilding the clustered index. Use the ALTER INDEX statement to do so.If you rebuild the clustered index, then all non-clustered indexes are automatically rebuilt. |
 |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2013-05-29 : 20:17:03
|
| Thank you for analysation.What I should learn here is, though there is no weekly REINDEX process going on and for the last couple of years, should still be REBUILD from Scratch as the existing index (non-Clustered) being DROP and RECREATE.What are the implications by drop and recreate on non-clustered.Please clarify. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-29 : 21:06:00
|
| Ok, first, checkdb and rebuilding indexes are 2 completely different topics.Please refer to This Page regarding rebuilding indexes.Typically, you will want to run a nightly job looking for fragmented indexes and rebuild them. There is a performance hit while the rebuild is taking place so you'll want a maintenance window to do it in. You can somewhat mitigate this by using the ONLINE = ON option.Some people will defragment indexes, but I never do. Either I rebuild 'em, or I leave them alone. A fragmentation % of 30 or so is a good candidate to rebuild. Ignore indexes with a small number of rows, pages or less than 3 levels deep.The performance impact of failing to rebuild indexes can be huge at times. Although it is often overstated.Tara (tkizer) on the forum has a catch-all script that you can use, or modify at will, to schedule your nightly index maintenance. OR we can help you by posting others.---------------DBCC CHECKDB() -- you want to run this as often as your backup cycle. This looks for and reports on, corruption in your databases. Just because a backup can be successfully restores does not mean that the restored database is not corrupt.You will want to create a comprehensive plan to restore your backups AND to periodically run CHECKDB(). You want to run it often enough to make sure that if your database does become corrupt that you have a recent backup that is NOT corrupt. Also, restoring backups (to a test environment) is the ONLY possible way to know that your backups are good. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2013-05-30 : 22:17:34
|
| Thanks for taking time to explain in detail.We have stopped doing REINDEX process, to be fortunate, there is no fragmentation reported when checked over the period of time.While doing the REINDEX it invokes fragmentation is the argument - and this is stopped.When checked with Restores of DB and found all are good.But going by your explanation - Restored database is not corruptWhat are "any other possible ways" need to check for the restored database may be corrupt.If I get a .BAK copy onto TEST environment doing CHECKDB, will it be still ok, not to do directly on Production ? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-31 : 00:29:20
|
| Yes, definitely a good idea to run checkdb on restored backup.This is my preferred method for a busy transactional database, when you don't have a long maintenance window. |
|
|
|
|
|
|
|
|
Indexes