mssql2005 database

Hello,I have a database that I wanted to see how fragmented the indexes are.
I right click on the database then click on reports then standard reports and then index usage statistics but I only got an error incorrect syntax near '(' if I try to use other command to look at the fragmentation of that table I receive the same error. How can I solve this problem and what can be the possible cause of this since the other databases works.
vera2006Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Execute the following statement in the database where it is failing:

DBCC CHECKDB WITH NO_INFOMSGS;
GO
And if you got error for any tables, then run this:

DBCC CHECKTABLE ('schema_name.ur_table_name')
GO
And if there are any errors, then do this

DBCC CHECKTABLE ('schema_name.ur_table_name',REPAIR_REBUILD)
GO

Once this is fixed, you would be able to create reports there..
Also apply SP3 for SQL Server 2005 to stay up to date( doesn't have anything to do over here)
0
 
Chris MConsulting - Technology ServicesCommented:
Here, I have written a script to use to analyse all your fragmented tables and display the extent of fragmentation.

God bless,
Chris Musasizi

-- ANALYSE FRAGMENTATION (NO DEFRAG PERFORMED).
BEGIN
	Declare @dbname sysname, @maxfrag DECIMAL(8,1)
	-- Check if database name has been parsed on calling procedure
	IF @dbname IS NULL SET @dbname = db_name();
	
	-- Declare variables
	SET NOCOUNT ON
	DECLARE @tablename VARCHAR(128), @execstr VARCHAR(255)
	DECLARE @objectid INT, @indexid INT, @frag DECIMAL(8,1)
	
	-- Decide on the maximum fragmentation to allow
	IF (@maxfrag IS NULL OR @maxfrag < 0.0 OR @maxfrag > 5.0) SET @maxfrag = 1.0
	
	-- Declare cursor
	DECLARE cur_tblz CURSOR FOR
	SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
	AND TABLE_NAME NOT IN (SELECT sysobjects.name TableName FROM sysobjects
	WHERE type = 'U' AND OBJECTPROPERTY(sysobjects.id, 'TableHasIndex') = 0)
	
	-- Create the table
	CREATE TABLE ##my_fragmented_table_list(
	   ObjectName CHAR (255),
	   ObjectId INT,
	   IndexName CHAR (255),
	   IndexId INT,
	   Lvl INT,
	   CountPages INT,
	   CountRows INT,
	   MinRecSize INT,
	   MaxRecSize INT,
	   AvgRecSize INT,
	   ForRecCount INT,
	   Extents INT,
	   ExtentSwitches INT,
	   AvgFreeBytes INT,
	   AvgPageDensity INT,
	   ScanDensity DECIMAL,
	   BestCount INT,
	   ActualCount INT,
	   LogicalFrag DECIMAL(9,2),
	   ExtentFrag DECIMAL
	)
	
	-- Open the cursor
	OPEN cur_tblz
	
	-- Loop through all the tables in the database
	FETCH NEXT FROM cur_tblz INTO @tablename
	WHILE @@FETCH_STATUS = 0
	BEGIN
	   -- Do the showcontig of all indexes of the table
	   INSERT INTO ##my_fragmented_table_list 
	   EXEC('DBCC SHOWCONTIG('''+@tablename+''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
	   FETCH NEXT FROM cur_tblz INTO @tablename
	END	
	-- Close and deallocate the cursor
	CLOSE cur_tblz
	DEALLOCATE cur_tblz
	
	IF EXISTS(SELECT * FROM ##my_fragmented_table_list WHERE LogicalFrag >= @maxfrag
		  AND INDEXPROPERTY(ObjectId,IndexName,'IndexDepth') > 0) 
	BEGIN
		SELECT rtrim(ObjectName)+' ' ObjectName, ObjectId, 
			Case when IndexId >= 1 then 'Y' else 'N' end Idx, IndexId, LogicalFrag,
			(select rows from sysindexes where id = (select id from sysobjects 
			 where name = rtrim(##my_fragmented_table_list.ObjectName)) and indid in(0,1))Rows
		FROM ##my_fragmented_table_list
		WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY(ObjectId,IndexName,'IndexDepth')>0
		ORDER BY LogicalFrag DESC, ObjectName ASC
	END
	ELSE PRINT 'No index is fragmented to ' + RTRIM(CONVERT(VARCHAR(11),@maxfrag))+'%'
END

Open in new window

0
 
Chris MConnect With a Mentor Consulting - Technology ServicesCommented:
To show the schema (on SQL 2005), use the function schema_name(object_id) in the select statement in the script above. :-)

Since the table created is a global temporary table, then after running the script in my previous post,  run the following script in a new query Window (see snippet attached).

The script in the previous post runs on SQL server 7, 2000 and 2005 whereas the one I have attached runs on SQL 2005 but no version below 2K5.

I am sure you will enjoy this.


SELECT rtrim(ObjectName)+' ' ObjectName, ObjectId, schema_name(ObjectId) As SCHEMA,
Case when IndexId >= 1 then 'Y' else 'N' end Idx, IndexId, LogicalFrag,
(select rows from sysindexes where id = (select id from sysobjects
where name = rtrim(##my_fragmented_table_list.ObjectName)) and indid in(0,1))Rows
FROM ##my_fragmented_table_list
WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY(ObjectId,IndexName,'IndexDepth')>0
ORDER BY LogicalFrag DESC, ObjectName ASC

Open in new window

0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
DDBConnect With a Mentor Commented:
1. create tem table
CREATE TABLE #SHOWCONTIG (
         ObjectName CHAR (255),
         ObjectId INT,
         IndexName CHAR (255),
         IndexId INT,
         Lvl INT,
         CountPages INT,
         CountRows INT,
         MinRecSize INT,
         MaxRecSize INT,
         AvgRecSize INT,
         ForRecCount INT,
         Extents INT,
         ExtentSwitches INT,
         AvgFreeBytes INT,
         AvgPageDensity INT,
         ScanDensity DECIMAL,
         BestCount INT,
         ActualCount INT,
         LogicalFrag DECIMAL,
         ExtentFrag DECIMAL)

2. populate the #SHOWCONTIG table with all heaps and clustered indexes within the database you wish to analyze.

INSERT #ShowContig
      EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS')

3. Now you can run different queries depends what you want:

Show the top tables with the LOWEST average page density
SELECT  TOP 20
            ObjectName,
            IndexName,
            AvgPageDensity
      FROM #ShowContig
      WHERE       ObjectName NOT LIKE 'dt%' AND
             ObjectName NOT LIKE 'sys%'
      ORDER BY  AvgPageDensity ASC

List the top tables with the highest extent fragmentation.

SELECT  TOP 10
            ObjectName,
            IndexName,
            ExtentFrag
      FROM #ShowContig
      WHERE       ObjectName NOT LIKE 'dt%' AND
             ObjectName NOT LIKE 'sys%'
      ORDER BY   ExtentFrag DESC

4. if necessary you can run DBCC INDEXDEFRAG
0
 
Chris MConsulting - Technology ServicesCommented:
DDB, I wonder how is your post any different from mine.
0
 
DDBCommented:
I don't use cursor. you can ignore my input.
0
All Courses

From novice to tech pro — start learning today.