Solved

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

Posted on 2007-11-20
23
473 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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:Scott Pletcher
Scott Pletcher 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:Scott Pletcher
Scott Pletcher 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
 

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:Scott Pletcher
Scott Pletcher 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:Scott Pletcher
Scott Pletcher 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:Scott Pletcher
Scott Pletcher 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:Scott Pletcher
Scott Pletcher 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:Scott Pletcher
Scott Pletcher earned 330 total points
ID: 20329916
PRINT @id
0
 
LVL 69

Expert Comment

by:Scott Pletcher
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

791 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