Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Integrity check output to file using Invoke-Sqlcmd failing Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 2:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:09 AM
Points: 174, Visits: 141
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).

Post #1476414
Posted Tuesday, July 23, 2013 3:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 1,862, Visits: 2,976
Haven't tried it myself, but I think the -Verbose parameter is what you need.

http://technet.microsoft.com/en-us/library/cc281720.aspx
Post #1476442
Posted Tuesday, July 23, 2013 4:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:09 AM
Points: 174, Visits: 141
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.
Post #1476466
Posted Tuesday, July 23, 2013 4:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 1,862, Visits: 2,976
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.
Post #1476472
Posted Tuesday, July 23, 2013 5:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:09 AM
Points: 174, Visits: 141
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.
Post #1476503
Posted Tuesday, July 23, 2013 8:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 1,862, Visits: 2,976
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
Post #1476596
Posted Tuesday, July 23, 2013 8:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:09 AM
Points: 174, Visits: 141
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).
Post #1476616
Posted Tuesday, July 23, 2013 10:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 1,862, Visits: 2,976
Good link, thanks. I wasn't aware of that. It is always a good day when you learn something
Post #1476695
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse