Automated SQL Servers Error Logs to single HTML file

Normally, we check SQL Server error log through SQL Server Management Studio or any text editor. By default, error log is located at ~\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ ERRORLOG.n1 (Here, n is instance name and n1 is nth number - dynamically appended). But, if you’re managing thousands or even just hundreds of SQL Server systems, you might spend most of the day manually checking each server.

So, what is better way to automate error log process and get error log results for each instance as an output to a single HTML file?

Solution:
One possible solution to achieve this is by using PowerShell script. Below is a PowerShell script to automate error log and generating a report for daily monitoring of SQL Server error logs.  We need to add all SQL servers' instance name in one text file. This script will read all instances mentioned in text file one by one, and generate a error log in HTML file.

Prerequisites:
Module invokesqlquery needs to be installed. Invokesqlquery can be downloaded from http://powershell4sql.codeplex.com

Powershell script:

<#

This script displays the error log entries for all listed servers for the last one day.

The script can take a few minutes to run if the error logs are large and you are looking back over several days.

#>

cls # This command will clear the screen of PowerShell window.

import-module invokesqlquery #This will import the module named "invokesqlquery" which we have downloaded and placed in module folder

$today    = (get-date).toString() #This will store today's date in $today variable

$all      = @() #This is a declaration of an array type variable to store the output in $all variable

$lookback = ((get-date).adddays(-1)).ToString()  # This will store previous date in $lookback variable

$serversList = "{your serverlist's text file path}\serverlist.txt" #This will store a full path of text file in which you have listed down the sql server's name

foreach ($server in get-content $serversList | select-object @{Name = 'serverName'; Expression = {$_}})  # This foreach loop will read the text file from given path and execute the code for each server name in that text file

{

"$((get-date).toString()) - Checking SQL Error Logs on $($server.servername)..."   #This line is for just displaying the name of server on the screen for which the process of checking SQL Error Logs is running

try  {

<# Explanation of the below code :

invoke-sqlquery -query : This is a command which executes the sql query

master..xp_readerrorlog : This is a stored procedure of in sql server to get the logs. We have passed some parameters to filter the logs.

select-object : This command is for specifying the columns which should be shown in the output.    

where-object : This command will filter the output and display only those data which contains the specific word like "error" in the Text. Other records will be discarded and will not be there in the output.

` : This symbol is to concat the line

 #>

#invoke-sqlquery -query :

$all += invoke-sqlquery -query "EXEC master..xp_readerrorlog 0, 1, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `

where-object {$_.Text -match 'error'}| `

where-object {$_.Text -notmatch 'setting database option recovery to'}

}

catch {"Unable to read SQL error log from server $server"}}

$all | ConvertTo-Html | Out-File "{Full Path of the output}\HTMLReport.html" #This line will export the output in the html file at given path.

 

This PowerShell script can be added as a Task Scheduler to run daily basis. Hope this is useful !

If you have any questions you can reach out our SharePoint Consulting team here.

Error occurred while restoring site to new tenant - 0x80070003

While restoring site collection from one farm to another using power shell command: Restore-SPSite, I am getting below error.

Error: :-  <nativehr>0x80070003</nativehr><nativestack></nativestack>.Exception.GetType().FullName, <nativehr>0x80070003</nativehr><nativestack></nativestack>.Exception.Message

Below are few possible reasons you might get this error:

1) SharePoint Version differs: Version may be lower in destination farm than source farm environment. It can be verified by Go to Central Administration --> System Settings --> Manage Servers in the farm and check version.

Resolution: Version in destination farm i.e. where we restore site collection must be same as source. Upgrade the version by installing necessary Service packs or Cumulative Updates.

2) SQL Server Version differs: SQL Instance version may differ in destination farm where we are restoring site collection, from the source farm where we have taken site collection backup.

Resolution: Install correct version of SQL Server with service pack in destination farm.

3) Memory Insufficient: In my case, both SharePoint farm and SQL Server versions were same. So, I checked SQL Server Logs through ULS log viewer and came to know that there was insufficient memory to execute queries in content database.

Resolution: Connect SQL Server by SQL Server Management Studio --> Right click and go to properties and increase Maximum Server Memory according to need.

\






 If you have any questions you can reach out our SharePoint Consulting team here.