July 23, 2013 at 2:43 am
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).
July 23, 2013 at 3:40 am
Haven't tried it myself, but I think the -Verbose parameter is what you need.
July 23, 2013 at 4:11 am
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.
July 23, 2013 at 4:27 am
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.
July 23, 2013 at 5:18 am
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.
July 23, 2013 at 8:06 am
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
July 23, 2013 at 8:34 am
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).
July 23, 2013 at 10:03 am
Good link, thanks. I wasn't aware of that. It is always a good day when you learn something 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply