June 3, 2004 at 10:20 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column
June 6, 2005 at 2:59 pm
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
June 9, 2005 at 10:12 am
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))<> ''''
June 15, 2005 at 12:04 pm
I'm also have problems with this stored procedure. Have you had any luck with it?
June 20, 2005 at 9:17 am
Stll not having any luck.
August 28, 2005 at 11:06 pm
I got it working by adding a 5th single quote to the line:
FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''
Dave H
November 18, 2005 at 3:08 am
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
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
November 18, 2005 at 8:16 am
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
November 18, 2005 at 8:21 am
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!
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
November 18, 2005 at 8:27 am
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
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
November 18, 2005 at 1:04 pm
No luck getting this to output. Eventually got everything to run, but email output simply reads: THETEXT
November 21, 2005 at 7:22 am
Hi,
I am using a similar approach (running a SHOWCONTIG and use the output to decide which indexes need to be defragmented). However, i am always running into rapid growing log files once i start reindexing or DBCC INDEXDEFRAG.
Does anyone know a "safe" way on how to defragment those fragmentated ones without risking blowing up the logs?
Greets,
Stijn
November 21, 2005 at 1:58 pm
There are two schools in this matter. Pick what suit you best
1. Run parallel Tlog backups at the same time the reindexing is happening eg. Run a Tlog backup every 2 min while the reindexing is going on
2. Make a Tlog Backup, Switch the recovery model to Bulk_logged, perform the Reindexing, switch back to Full and when performing the next TLog backup do it on a drive that have plenty of space
Cheers,
* Noel
November 22, 2005 at 4:17 am
Thanks ... i'll see how it goes when i up the backup frequency when i'm reindexing.
Much appreciated,
Stijn
November 22, 2005 at 1:46 pm
Still can't get usp_dbshowcontig_single_db to work. I added the fifth quote but am still getting this error message: Invalid object name '#tablename'
Has anyone else had this error message and solved the problem?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy