March 12, 2012 at 1:33 pm
I am running DBCC INDEXDEFRAG on all of the table in my database and would like the output to go directly to a table. I have tried redirecting the output to a text file and importing that, but it was too messy. Is there any possible way to do this?
Thanks.
Steve
March 12, 2012 at 3:40 pm
This should help you (script at bottom of page)
http://msdn.microsoft.com/en-us/library/ms177571.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 14, 2012 at 3:57 am
sdownen05 (3/12/2012)
I am running DBCC INDEXDEFRAG on all of the table in my database and would like the output to go directly to a table. I have tried redirecting the output to a text file and importing that, but it was too messy. Is there any possible way to do this?Thanks.
Steve
Try WITH TABLERESULTS
March 22, 2012 at 1:46 pm
Sorry, I didn't get back to you sooner.
Thanks for the help. I have figured out to use this code to insert the data into a table:
INSERT INTO FragList EXEC ('DBCC SHOWCONTIG (customer, IX_customer_bank_code) WITH TABLERESULTS, ALL_LEVELS')
My problem is, I would like to add a time column to this, but I can't figure out a way to do a SELECT statement to handle it. Something like this:
INSERT INTO FragList SELECT GETDATE(), EXEC ('DBCC SHOWCONTIG (customer, IX_customer_bank_code) WITH TABLERESULTS, ALL_LEVELS')
Does anyone have any suggestions?
March 22, 2012 at 1:52 pm
Create the time column with a default constraint for your timestamp.
You can't mix the select and Exec in the insert statement like that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2012 at 2:07 pm
Sorry, I don't do much database design. How would I add a constraint such as the one you are talking about?
March 22, 2012 at 2:09 pm
Will you provide the code you are using to create fraglist?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2012 at 2:11 pm
Sure. Sorry, I should have already posted this.
CREATE TABLE FragList
(
ObjectName NVARCHAR (255),
ObjectId INT,
IndexName NVARCHAR (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
)
March 22, 2012 at 2:20 pm
CREATE TABLE [dbo].[FragList](
[ObjectName] [nvarchar](255) NULL,
[ObjectId] [int] NULL,
[IndexName] [nvarchar](255) NULL,
[IndexId] [int] NULL,
[Lvl] [int] NULL,
[CountPages] [int] NULL,
[CountRows] [int] NULL,
[MinRecSize] [int] NULL,
[MaxRecSize] [int] NULL,
[AvgRecSize] [int] NULL,
[ForRecCount] [int] NULL,
[Extents] [int] NULL,
[ExtentSwitches] [int] NULL,
[AvgFreeBytes] [int] NULL,
[AvgPageDensity] [int] NULL,
[ScanDensity] [decimal](18, 0) NULL,
[BestCount] [int] NULL,
[ActualCount] [int] NULL,
[LogicalFrag] [decimal](18, 0) NULL,
[ExtentFrag] [decimal](18, 0) NULL,
[InsertDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FragList] ADD CONSTRAINT [DF_FragList_ObjectName] DEFAULT (getdate()) FOR [InsertDate]
GO
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2012 at 2:31 pm
Jason,
Thank you very much! It worked perfectly! I will keep this handy for future reference.
Steve
March 22, 2012 at 4:20 pm
You are welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply