• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

How to find out how fragmented a SQL 7 database is?

I see it in books to use 'dbcc showconfig' to find out how fragmented a SQL 7 database is, but I could not figure out the exact syntax.
Q#1. What is the syntax to find out how fragmented a SQL 7 database (myDB) is?
Q#2. What is the syntax to rebuild the index of it?
Thanks a lot.
0
richtree
Asked:
richtree
  • 11
  • 8
  • 3
  • +1
10 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
dbcc showconfig(tableName)
DBCC DBREINDEX()

check books online for more
0
 
richtreeAuthor Commented:
I tried dbcc showconfig(myDB) and it did not run - syntax error.
Should we use tableName or database name in showconfig brackets?
If tableName is used, then how to specify the full path: server\database\table?
Thanks.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
its tablename you need to pass
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
James MurrellProduct SpecialistCommented:
0
 
richtreeAuthor Commented:
When I use the tableName in bracket, I still receive syntax error. Can you post a detailed example? Please.
0
 
Scott PletcherSenior DBACommented:
Run the code below in QA -- I think it will work on 7.0 as well.  Copy the result area back into the active/code area of QA.  The tables that have a DBREINDEX command after them are clustered; those that do not are not (there is no direct way to organize a non-clus table, you would have to force it).  So, if the DBREINDEX command shows, and the table is fragmented, you can run that command to rebuild the index(es).


USE yourDbNameGoesHere

EXEC sp_MSforeachtable '
PRINT ''DBCC SHOWCONTIG (''''?'''')''
IF EXISTS(SELECT 1 FROM dbo.sysindexes
WHERE OBJECT_NAME(id) = PARSENAME(''?'', 1) AND indid IN (1))
BEGIN
    DECLARE @outputLine VARCHAR(300)
    PRINT ''/*''
    SELECT @outputLine = ''    DBCC DBREINDEX (''''?'''''' +
        CASE WHEN indid = 0 THEN '''' ELSE
        '', '''''' + CAST(name AS VARCHAR(100)) + '''''''' END + '')''
    FROM dbo.sysindexes
    WHERE OBJECT_NAME(id) = PARSENAME(''?'', 1) AND indid IN (1)
    PRINT @outputLine
    PRINT ''/*''
END
PRINT ''''
'
0
 
richtreeAuthor Commented:
I run command: dbcc showconfig (my_tbl) and got the error message as follows.
Server: Msg 2526, Level 16, State 3, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
Can someone point me to the riggt syntax? Thanks.
0
 
Scott PletcherSenior DBACommented:
dbcc showconTig
0
 
richtreeAuthor Commented:
Can you check what I typed in and see what is wrong? I typed in: dbcc showconfig (my_tbl)
0
 
richtreeAuthor Commented:
OK, I typed in: dbcc showconTig (my_tbl) and got the following error message. What's wrong? Thanks.
Server: Msg 2560, Level 16, State 14, Line 1
Parameter 1 is incorrect for this DBCC statement.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
can u check books online for the exact syntax
0
 
richtreeAuthor Commented:
I did try here and there including the 'help' in SQL. I also searched the website. All I find is the concept not the syntax. Is it because the syntax is very complex? Or only very few people actually ever run it?
0
 
Scott PletcherSenior DBACommented:
Please try:

DBCC SHOWCONTIG ( 'my_tbl' )
0
 
richtreeAuthor Commented:
I tried the following without luck:
dbcc showcontig (my_tbl)
dbcc showcontig (myDB.my_tbl)
dbcc showcontig ('my_tbl')
dbcc showcontig ('myDB.my_tbl')
0
 
Scott PletcherSenior DBACommented:
Hmm, odd, research seems to show that SHOWCONTIG was available in 7.0.  I assume you have the latest service pack for 7.0.

*Specifically* what error(s) do you get for:

DBCC SHOWCONTIG ( 'my_tbl' )

when run stand-alone?
0
 
richtreeAuthor Commented:
I use Enterprise Manager (version 8.0) to connect to 'myDB' and I can open 'my_tbl'.
I open SQL Query Analyzer and run this command: dbcc showcontig ('my_tbl') with the following error message:
Server: Msg 2560, Level 16, State 14, Line 1
Parameter 1 is incorrect for this DBCC statement.
0
 
Scott PletcherSenior DBACommented:
Hmm, on 7.0, maybe you have to use the table id instead of the table name.

Please try this:

DECLARE @id INT

SELECT TOP 1 @id = id
FROM dbo.sysobjects
WHERE xtype = 'U' AND name = 'my_tbl'

DBCC SHOWCONTIG ( @id )
0
 
Scott PletcherSenior DBACommented:
Actually, OBJECT_ID() is a less system-dependent way to get the id, so you could do this instead:

DECLARE @id INT

SET @id = OBJECT_ID('my_tbl', 'U')

DBCC SHOWCONTIG ( @id )
0
 
richtreeAuthor Commented:
The following command executes successfully.
DECLARE @id INT

SELECT TOP 1 @id = id
FROM dbo.sysobjects
WHERE xtype = 'U' AND name = 'my_tbl'

But the following failed with error message at the bottom.
DECLARE @id INT

SELECT TOP 1 @id = id
FROM dbo.sysobjects
WHERE xtype = 'U' AND name = 'my_tbl'

DBCC SHOWCONTIG ( @id )

// error message from the above command:
Server: Msg 2501, Level 16, State 2, Line 7
Could not find table named '0'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What's wrong?
By the way, how to output the table ID?
0
 
Scott PletcherSenior DBACommented:
PRINT @id
0
 
Scott PletcherSenior DBACommented:
That's odd.  It works fine for me.

I guess print out the id once you find it and enter it directly in the command.
0
 
richtreeAuthor Commented:
'my_tbl' is NOT found in sysobjects table. Any other way to find out the table ID of 'my_tbl'?
0
 
richtreeAuthor Commented:
Thank you very much for your time and try. Though it is not solved, I  just close it for now.
0
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

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 11
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now