richtree
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I use the tableName in bracket, I still receive syntax error. Can you post a detailed example? Please.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can you check what I typed in and see what is wrong? I typed in: dbcc showconfig (my_tbl)
ASKER
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.
Server: Msg 2560, Level 16, State 14, Line 1
Parameter 1 is incorrect for this DBCC statement.
can u check books online for the exact syntax
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried the following without luck:
dbcc showcontig (my_tbl)
dbcc showcontig (myDB.my_tbl)
dbcc showcontig ('my_tbl')
dbcc showcontig ('myDB.my_tbl')
dbcc showcontig (my_tbl)
dbcc showcontig (myDB.my_tbl)
dbcc showcontig ('my_tbl')
dbcc showcontig ('myDB.my_tbl')
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
I guess print out the id once you find it and enter it directly in the command.
ASKER
'my_tbl' is NOT found in sysobjects table. Any other way to find out the table ID of 'my_tbl'?
ASKER
Thank you very much for your time and try. Though it is not solved, I just close it for now.
ASKER
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.