Solved

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

Posted on 2007-11-20
23
466 Views
Last Modified: 2010-04-21
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
Comment
Question by:richtree
  • 11
  • 8
  • 3
  • +1
23 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 120 total points
ID: 20319604
dbcc showconfig(tableName)
DBCC DBREINDEX()

check books online for more
0
 

Author Comment

by:richtree
ID: 20319668
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 120 total points
ID: 20319702
its tablename you need to pass
0
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 50 total points
ID: 20319713
0
 

Author Comment

by:richtree
ID: 20320319
When I use the tableName in bracket, I still receive syntax error. Can you post a detailed example? Please.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 330 total points
ID: 20322650
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
 

Author Comment

by:richtree
ID: 20328941
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 330 total points
ID: 20328992
dbcc showconTig
0
 

Author Comment

by:richtree
ID: 20329013
Can you check what I typed in and see what is wrong? I typed in: dbcc showconfig (my_tbl)
0
 

Author Comment

by:richtree
ID: 20329053
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20329328
can u check books online for the exact syntax
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:richtree
ID: 20329400
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 330 total points
ID: 20329441
Please try:

DBCC SHOWCONTIG ( 'my_tbl' )
0
 

Author Comment

by:richtree
ID: 20329480
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 330 total points
ID: 20329578
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
 

Author Comment

by:richtree
ID: 20329667
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 330 total points
ID: 20329697
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 330 total points
ID: 20329710
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
 

Author Comment

by:richtree
ID: 20329797
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 330 total points
ID: 20329916
PRINT @id
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 20329931
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
 

Author Comment

by:richtree
ID: 20330720
'my_tbl' is NOT found in sysobjects table. Any other way to find out the table ID of 'my_tbl'?
0
 

Author Closing Comment

by:richtree
ID: 31410114
Thank you very much for your time and try. Though it is not solved, I  just close it for now.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now