SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Integrity check output to file using Invoke-Sqlcmd failing


Integrity check output to file using Invoke-Sqlcmd failing

Author
Message
Drenlin
Drenlin
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 165
Hi all, I am having fun trying to get a PowerShell called database integrity check to output to a file. This is so I can then search the output for specific lines/words to reduce the ammount to be read.

This works:
& sqlcmd -S $servername -E -Q "dbcc checkdb(N'$($dbName)') with all_errormsgs" >"$($ResultFile)"


but it feels like cheating when there is Invoke-Sqlcmd but I cannot get Invoke-Sqlcmd to produce the output to file.

This produces an empty file:
Invoke-Sqlcmd -ServerInstance $servername -Query "dbcc checkdb(N'$($dbName)') with all_errormsgs" | out-file "$($ResultFile)"



The following produces an error about not recognising -ServerInstance (I guess it is linking the switch to Add-Content)
Add-Content $ResultFile Invoke-Sqlcmd -ServerInstance $servername -Query "dbcc checkdb(N'$($dbName)') with all_errormsgs"



So I put the Invoke-Sqlcmd into bracers but this only produces an empty result file.
Add-Content $ResultFile (Invoke-Sqlcmd -ServerInstance $servername -Query "dbcc checkdb(N'$($dbName)') with all_errormsgs")



For the times that do not produce an error, time does pass before the prompt comes back so I guess it is performing the check.

Does anyone know how to get Invoke-Sqlcmd to output to a file, or should I give up and use the command prompt code (which works).
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7646 Visits: 6045
Haven't tried it myself, but I think the -Verbose parameter is what you need.

http://technet.microsoft.com/en-us/library/cc281720.aspx
Drenlin
Drenlin
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 165
Thanks for the link Gazareth.

I have read the article and tried the -Verbose with both lines that did not produce the error.

In both instances of the output was into the shell window (was tested in the ISE), even when I used the pipelined out-file it failed to redirect the output to the file.

It seems from that article that there is not much else that can be tried.
I shall have to add a comment to the code to explain why I did not use Invoke-Sqlcmd as a reminder.

Still open to other suggestions though.
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7646 Visits: 6045
Tableresults seems to work:

Invoke-Sqlcmd -Query "dbcc checkdb(N'$($dbName)') WITH ALL_ERRORMSGS, TABLERESULTS" | ft -AutoSize | Out-File "$($ResultFile)"


Interestingly, the Database class includes methods for Check Alloc, Catalog, and Table, but not an overall CHECKDB. So I think Invoke-SqlCmd might be the only way to achieve this.
Drenlin
Drenlin
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 165
That is an interesting output.

Before (sample):
DBCC results for 'xxxx'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 7.
Service Broker Msg 9667, State 1: Services analyzed: 4.
DBCC results for 'sys.sysrscols'.
There are 20881 rows in 250 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 3435 rows in 44 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysclones'.
There are 0 rows in 0 pages for object "sys.sysclones".


Your suggestion:

Error Level State MessageText RepairLevel Status DbId DbFragId ObjectId IndexId
----- ----- ----- ----------- ----------- ------ ---- -------- -------- -------
8997 10 1 Service Broker Msg 9675, State 1: Message Types analyzed: 14. 0 7 1 0 -1
8997 10 1 Service Broker Msg 9676, State 1: Service Contracts analyzed: 7. 0 7 1 0 -1
8997 10 1 Service Broker Msg 9667, State 1: Services analyzed: 4. 0 7 1 0 -1
2593 10 1 There are 20881 rows in 250 pages for object "sys.sysrscols". 0 7 1 3 1
2593 10 1 There are 3435 rows in 44 pages for object "sys.sysrowsets". 0 7 1 5 1
2593 10 1 There are 0 rows in 0 pages for object "sys.sysclones". 0 7 1 6 1


Too much info but I looked up the ft -AutoSize (hadn't used it before) and changed it to 'ft MessageText -HideTableHeaders' and this reduced it back to the info I required.

Now
   
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 7.
Service Broker Msg 9667, State 1: Services analyzed: 4.
There are 20881 rows in 250 pages for object "sys.sysrscols".
There are 3435 rows in 44 pages for object "sys.sysrowsets".
There are 0 rows in 0 pages for object "sys.sysclones".


I can easily trim the lines I need when read it in again. Now to test it against the test corrupt databases to check i still get the error messages. I recall (can't link it) an article that recommended using the 'with all_errormsgs' because if the database is so corrupt, it wouldn't produce an output (which makes it look like all is well when used with 'with no_infomsgs'). Wonder how this will output.

Many thanks, especially as I have learnt about Format-Tables. It is always a good day when you learn something, especially for an accidental DBA like me.
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7646 Visits: 6045
Cool, glad it's working!

Not sure about the all_errormsgs option - as far as I know the output contains all errors regardless of whether you specify this option or not.
I know Management Studio will limit the number of messages shown, but you shouldn't encounter that issue with Invoke-SqlCmd.

Cheers
Gaz
Drenlin
Drenlin
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 165
Found the link regarding database corruption
http://www.sqlskills.com/blogs/paul/teched-demo-corruptions-fatal-to-dbcc-checkdb/
The second example produces no output if you run integrity 'with no_infomsgs'. Although in this circumstance the database is so corrupt it would be unusable, I am just playing safe. Not all of our databases that I have to monitor are used on a regular basis.

I have just over 200 databases to monitor. This is why the script I am making reduces the result files by just looking for certain entries on each line to just important messages. The actual output is kept for reference. The reduced result is emailed and the title is based on whether an error occured or not.

For info...
I look for lines starting: "checkdb found" and "dbcc execution completed" which are the good messages.
I also look for lines starting: "database '$($dbName)' cannot" which is bad (and changes the $ErrorOccured flag).
I also look for lines containing: "corrupt" and "prevent further" which are also bad (again, changes $ErrorOccured).
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7646 Visits: 6045
Good link, thanks. I wasn't aware of that. It is always a good day when you learn something :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search