Hi Nicholas,

Nice article, I enjoyed reading it.

BTW, what is the benifit of using the dts vs scheduling these steps from a job? I am assuming both will be done by someone with dba rights.

Thanks again

This was a great article.  I tried implementing, but have been having problems running the stored procedure USP_DBshowcontig_single_db.  #table# name error.  I have been having a provlem with this part of the stored procedure.

set @inserttable = ('INSERT INTO ADMINDB.DBO.INDEX_INFO_.'+@NAME+ 'ObjectName,
   ObjectId,
   IndexName,
   IndexId,
   Lvl,
   CountPages,
   CountRows,
   MinRecSize,
   MaxRecSize,
   AvgRecSize,
   ForRecCount,
   Extents,
   ExtentSwitches,
   AvgFreeBytes,
   AvgPageDensity,
   ScanDensity,
   BestCount,
   ActualCount,
   LogicalFrag,
   ExtentFrag'

 select ObjectName,
   ObjectId,
   IndexName,
   IndexId,
   Lvl,
   CountPages,
   CountRows,
   MinRecSize,
   MaxRecSize,
   AvgRecSize,
   ForRecCount,
   Extents,
   ExtentSwitches,
   AvgFreeBytes,
   AvgPageDensity,
   ScanDensity,
   BestCount,
   ActualCount,
   LogicalFrag,
   ExtentFrag
  FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''

I got it working by adding a 5th single quote to the line:

FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''

Dave H

Hiyas.

Had the same problem with the quotes ... noticed in QA that everything was red so I added a fifth quote.

Anyway. Got a small addition here for the same sproc USP_DBshowcontig_single_db :

set @sql = 'SELECT TABLE_NAME = ' + @dbname + '..SYSUSERS.NAME' + ' + ''.'' + ' + '' + @dbname + '..SYSOBJECTS.NAME FROM ' + @dbname + '..SYSOBJECTS JOIN ' + @dbname + '..SYSUSERS ON ' + @dbname + '..SYSOBJECTS.uid = ' + @dbname + '..SYSUSERS.uid WHERE XTYPE =' + '''' + 'U' + ''''

It adds the owner name of the table to TABLE_NAME, which allows you to run the script on databases which have user tables not created by sa.

My piece replaces the line:

set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + ''''

Good luck.

hot2use

Hi,

I am very new to these things.. can any of you guys tell me how to identify ideal fragmentation level...and how do i identify it..

Thanks in advance..

Kumar

This is fantastic. I have a client who is having fragmentation problems in a heavily used medical records database and this could be just what I need to measure just how quickly the database is becoming fragmented so we can schedule the maintenance plans appropriately.

Thanks a lot!

I'm not a fully fledged DBA, but looking at the scripts I'd assume that a scan density beloew 75 percent is bad.

Ken England has a good book on "Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook" which I'd recommend (ISBN# 1-55558-241-9). And then searching for DBCC SHOWCONTIG in Google should give you some more information at what to look for.

Regards

hot2use