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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 MConsulting - 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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DDBCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.