Improve company productivity with a Business Account.Sign Up

x
?
Solved

Would runnng dbcc showcontig lockup table?

Posted on 2012-03-20
4
Medium Priority
?
287 Views
Last Modified: 2012-03-31
I have a huge table, maybe 200 million rows, no clustered index.  I want to see the fragmentation on it by running dbcc showcontig. Should I be doing this off hours? would it lock up that table? This is SQL Server 2000
0
Comment
Question by:Camillia
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37742465
try to use the option "FAST". Or, better, use sys.dm_db_index_physical_stats (but don't know if it works in Sql 2000), with the "LIMITED" option.
0
 
LVL 7

Author Comment

by:Camillia
ID: 37742481
so it would lock up the table? this is sql 2000, what is "FAST" and i know it doesnt have that sys.dm_db_index_physical
0
 
LVL 8

Accepted Solution

by:
gpizzuto earned 2000 total points
ID: 37742566
This is the sintax of dbcc showcontig:

DBCC SHOWCONTIG
[ (
    { table_name | table_id | view_name | view_id }
    [ , index_name | index_id ]
) ]
    [ WITH
        {
         [ , [ ALL_INDEXES ] ]
         [ , [ TABLERESULTS ] ]
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ]
         [ NO_INFOMSGS ]
         }
    ]

as stated in MS documentation:

[...] The operation only requires an intent-shared (IS) table lock. This way all updates and inserts can be performed, except those that require an exclusive (X) table lock. This allows for a tradeoff between speed of execution and no reduction in concurrency against the number of statistics returned. However, if the command is being used only to gauge fragmentation, we recommend using the WITH FAST option for optimal performance. [...]
0
 
LVL 7

Author Comment

by:Camillia
ID: 37742640
so it locks the table.
0

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question