April 7, 2004 at 12:46 pm
I am using the example in the BOL on using DBCC Showcontig on all tables and storing data in a table.
I converted the code into a procedure. The key part I need help in modifying is here:
INSERT INTO SQLCentral..FragList
(ObjectName, ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFrag, ExtentFrag)
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
This code snippest performs a DBCC Showcontig on the table name, @tablename, passed as a variable and directs the output into a table in my SQLCentral database. I have the procedure in the master data (sp_FragStats), so I can run it for any database. The extensions I am looking for are:
Add a DBname column to the FragList table and insert database name into that field. Since I may have same table name in multiple databases, I must qualify with a DB name. I added the DBName field to the SQLCentral..FragList table with default db_name(). However when the procedure runs, it inserts "SQLCentral" for all entries as opposed to the actual DB name of the tables. I could create a temp table to load the results for each database and then insert into my permanent table SQLCentral..FragList along with explicitly specifying the DB Name. Are there any other means to achieve this?
Thanks.
April 12, 2004 at 8:00 am
This was removed by the editor as SPAM
June 17, 2004 at 7:09 am
Did you solve the DBCC SHOWCONTIG problem?
I've been working on getting DBCC SHOWCONTIG for every database in the server for the last day.
Problem #1: Getting DBCC SHOWCONTIG to execute in the context of each database. I'm having trouble getting a dynamic use statement merged into the DBCC SHOWCONTIG command.
Have you solve that or other problems?
June 17, 2004 at 9:46 am
this should get you going
exec dbo.sp_MSforeachdb @command1="use ?;DBCC SHOWCONTIG"
cheers
dbgeezer
June 17, 2004 at 12:44 pm
I have a stored procedure which does exactly what you are looking for, plus reindexing based on scandensity, and a "factor" based on number of pages.
I can post it if you like, or send me a private message with your e-mail, and I'll send it to you.
Steve
April 23, 2008 at 2:29 am
Just looking for a similar solution to this... basically i need to append a row to all the outputed lines from the DBCC command. Can this be done in the same insert into command.
For example i want to add the date, database, schema and tablename to the output.
May 21, 2008 at 9:25 am
hoo-t (6/17/2004)
I have a stored procedure which does exactly what you are looking for, plus reindexing based on scandensity, and a "factor" based on number of pages.
I can post it if you like, or send me a private message with your e-mail, and I'll send it to you.
Steve
Please post this, would be useful for me...TIA
May 21, 2008 at 9:30 am
The procedure can be found here...
http://www.sqlservercentral.com/scripts/Index+Management/31475/
By the way, this has not been tested by me on anything more recent than SQL 2000.
Steve
August 5, 2008 at 12:46 am
You can try on this command:
INSERT INTO ',1)')
August 5, 2008 at 12:48 am
You can try on this command again:
INSERT INTO ',1)')
August 5, 2008 at 12:53 am
You can try on this command again and again:
Maybe this is due to SQL injection that's why the command displayed as to my previous post.
Please see attachment. w00t:
October 4, 2008 at 12:02 am
Hi Steve,
Could you please post your SP here?
Thanks in advnace.
October 6, 2008 at 7:18 am
The script, as Steve stated, is on this site at:
http://www.sqlservercentral.com/scripts/Index+Management/31475/
-- You can't be late until you show up.
April 16, 2009 at 8:22 pm
I'm not sure if this question was ever resolved.
I came up with something you can use; at least as a start. It clobbers the server instance while it runs, but it should collect the data needed.
--Set environment characteristics
SET DEADLOCK_PRIORITY LOW;
SET NOCOUNT ON;
SET ANSI_DEFAULTS ON;
DECLARE @ExecutionDate smalldatetime;
DECLARE @CatalogName sysname;
DECLARE @SQLStatement VARCHAR(8000);
--Create temporary table to store Contig values for each database.
CREATE TABLE #ContigSpace (
[ObjectName] sysname NOT NULL,
[ObjectId] int NULL,
[IndexName] sysname NULL,
[IndexId] int NULL,
[Level] int NULL,
[Pages] int NULL,
[Rows] int NULL,
[MinimumRecordSize] int NULL,
[MaximumRecordSize] int NULL,
[AverageRecordSize] int NULL,
[ForwardedRecords] int NULL,
[Extents] int NULL,
[ExtentSwitches] int NULL,
[AverageFreeBytes] int NULL,
[AveragePageDensity] int NULL,
[ScanDensity] [decimal](9, 0) NULL,
[BestCount] int NULL,
[ActualCount] int NULL,
[LogicalFragmentation] [decimal](9, 0) NULL,
[ExtentFragmentation] [decimal](9, 0) NULL,
[CatalogName] sysname NULL,
[DateRecorded] smalldatetime NOT NULL DEFAULT(getdate())
);
SET @SQLStatement = 'USE [?];
DECLARE @CatalogName sysname;
SELECT @CatalogName = db_name();
IF (@CatalogName NOT IN (''master'', ''msdb'', ''tempdb'', ''model''))
BEGIN
INSERT INTO #ContigSpace ([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
EXEC (''DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'');
UPDATE #ContigSpace SET CatalogName = ''?'' WHERE CatalogName IS NULL;
END
'
EXEC sp_MSforeachdb @command1 = @SQLStatement;
SELECT * FROM #ContigSpace
DROP TABLE #ContigSpace;
Good luck!
Cheers,
Adam
June 15, 2009 at 8:44 pm
I am after somthing similar... I actually wanted to append the server name + Database Name to the output of dbcc showcontig. I was then going to insert this into a SQL Monitoring table and use it to get reports on index fragmentation over time.
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[contigHoldforEachDB]')) DROP TABLE [tempdb].[dbo].[contigHoldforEachDB];
CREATE TABLE [tempdb].[dbo].[contigHoldforEachDB](
[Server] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,[DatabaseName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,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
) ON
[PRIMARY]
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[contigHold]')) DROP TABLE [tempdb].[dbo].[contigHold];
CREATE TABLE [tempdb].[dbo].[contigHold](
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
) ON
[PRIMARY]
EXEC sp_MSforeachdb @command1 = 'insert into [tempdb].[dbo].[contigHold]
exec (''USE [?] DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES'')', @command2 = 'insert into [tempdb].[dbo].[contigHoldforEachDB] select CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,''?'' as DatabaseName, * from [tempdb].[dbo].[contigHold]', @Command3 = 'truncate table [tempdb].[dbo].[contigHold]'
Blog: http://crazyemu.wordpress.com/
Twit: @crazySQL
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply