DBCC SHOWCONFIG - save results anywhere 2

[Trevil]

Trying to capture output from DBCC SHOWCONFIG WITH ALL_INDEXES to a file.
I have seen examples using 'EXEC ...xp_cmdshell...' that allows output to a file, but access to the shell is blocked for security reasons -- and I don't want to enable it.

Is there some other way to get the statistics (file, table, etc.)

Thanks!

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[kss444]

If you are in the management studio why can't you just run the query, but save the results to a file rather than the "Reults to Grid"


USE AdventureWorks;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO

Ordinary Programmer

 

[Trevil]

Yes, I could do as you suggest, but being the lazy individual that I am, I want to automate the process as much as possible - plus I will not have 'hands-on' access to the database. We have servers running in 70 countries and I don't have access.
I already have a scheduled task that runs a VB program every day on those servers, so I was hoping to execute a SP to get the statistics, then do some analysis and send me an alert if things look bad.
If I could have the output directed to a table or a file, then I could have this VB program do that.
Thank you for your feedback!

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[gmmastros]

Well... if you're gonna use VB, then the world's your oyster.

Personally, I would be tempted to use SQLCMD to save the output to a file.

At a cmd prompt...

sqlcmd -S[!]ServerName\InstanceName[/!] -E -d[!]DatabaseName[/!] -Q"DBCC ShowContig With TableResults,ALL_Indexes" -o"[!]C:\Folder\OutputFile.txt[/!]"

Obviously, you will need to change the parts in RED. In VB, just shell out to a command prompt and run the command above.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Forgot to mention.... If you don't have SQLCMD on the computer you intend to run this on, then you will need to install it. You can download SQLCMD from Microsoft here.

http://www.microsoft.com/downloads/...60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Scroll down to:
Microsoft SQL Server 2005 Command Line Query Utility



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Trevil]

SWEEEEEET!!
I just ran this:
strSQL = "sqlcmd -S (local) -E -d MYDBNAME -Q""DBCC ShowContig With TableResults,ALL_Indexes"" -o""C:\TEMP\showcontig.txt""
MyAppId = Shell(strSQL, vbMinimizedFocus)

Must have done something wrong -- because IT WORKED - FIRST TRY!!
(And actually the output results are already formatted like I need!!)
Thank You Very MUCH!!!

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[gmmastros]

I'm glad I could help.... but really.... Don't thank me. Thank kss444. I didn't know about the "With TableResults" part. Without it, this probably wouldn't work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kss444]

Sure it would work, but not give you the results in a table format. it would just list them in text format.
For each table.

DBCC SHOWCONTIG scanning 'ProductProductPhoto' table...
Table: 'ProductProductPhoto' (18099105); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 1544.0
- Avg. Page Density (full).....................: 80.92%

Ordinary Programmer

 

[Trevil]

Thanks kss444. Yes, the sample you show is what you get in the window -- which is a nice report -- but it doesn't easily allow me to do analysis. The output file I get is actually a "fixed-width" text file with all of the columns for the data. I added code in my program to just import that text file into a table and can now better analyze hot spots. Thank you for your reply!
Below is a slightly-edited sample from the file:
ObjectName ObjectId IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
valBvDoc 45243216 BvDoc_PK 2 0 329 43316 23 125 53.579 0 42 41 778.491 90.381 100 42 42 0 4.761

Learn from the mistakes of others. You won't live long enough to make all of them yourself.