Think before you restart the SQL Server Service

When we face an issue with SQL Server or any other software, we simply think that either restart of the server or service would solve the issue, and we do it. The situation can be something like; No Response, Takes long time to produce the result, or does not allow to make new connections. Whatever the situation, you need to think twice before restarting the service.

Why we should not restart it immediately? One thing you need to remember is, SQL Server logs all possible information related its processes in the server logs. By default SQL Server maintains 6 archives along with the current file and files are getting replaced with every restart. For example, when you restart, it creates a new log file, makes the old-current-file as Archive #1, Old Archive #1 as Archive #2, and so on, deleting the last Archive file. If multiple restarts were done, useful information in log files would erased and will not be able to see what caused the issue.

Even though it maintains 6 files, I think it is better to maintain more than six files. A number like 10-15 would do better even though it consumes space unnecessary. Who knows, one day they will be very useful if something happens to the server and need to find reasons for it.

Considering the fact discussed, make sure that SQL Server is not getting restarted unnecessarily. If need, it is better to take a copy of all existing files and then do the restart.

What if that the required log has been over-written?

You may experience this when you troubleshoot an issue. There is another way to see what has happened with SQL Server though the container is not as rich as the log. It is the default trace (Thanks for the Data Platform MVP group who helped me to understand this). Generally, default trace records many events including errors. By default it is enabled but for some reasons if you need to enable or disable, it can be done with sp_configure.

By Querying sys.traces, the path for the trace files can be found and files can be opened either using TSQL or Profiler.

You can read more info on the Default Trace at: https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server-2005-and-sql-server-2008/

 

Reading SQL Server Error Log using XP_READERRORLOG

We use sp_readerrorlog for reading the content of the error log of SQL Server and it actually uses xp_readerrorlog extended stored procedure. Since I had to do some troubleshoot with one of servers, I use this for finding some info, and thought share it.

Here are some ways of reading the error log;

In case of you need the code;

USE master;
GO

-- without parameters, load the current log
EXEC xp_readerrorlog;

-- Load the specific error log 
-- 0 is current one and by default there are 6 more old logs
EXEC xp_readerrorlog 0;

-- Second parameter is for log type
-- 1 is for engine and 2 is agent
EXEC xp_readerrorlog 0, 1;

-- Two more additional parameters can be passed 
-- for searching, 1st one is for searching
-- and second one for filter the search - or further searchin within the result
EXEC xp_readerrorlog 0, 1, N'Server', N'Listening';

-- And this can take three more parameters
-- for setting the date range and sorting order
EXEC xp_readerrorlog 0, 1, N'Database', N'resource'
 , '2016-12-21 23:00:18.860', '2016-12-21 23:00:18.930', N'DESC';

Troubleshooting agent jobs

Administrators always try to automate routine tasks, making sure that all required operations are done on time without failures. The main component used for automation is SQL Server Agent, which is used for creating jobs. If you have noticed that a scheduled job has not run or all jobs are not running, how do you troubleshoot? How do you start troubleshooting?

Here are some guidelines for troubleshooting based on a discussion I had;

• First thing needs to be checked is whether SQL Server Agent service is running or not. You need to make sure that Startup type is set to automatic, this makes sure that service is started when the server is restarted. If, for some reasons, service is not running, and you cannot even manually start it, check following;
• Check and see whether account assigned for the Agent is valid and no issues with the password. The account assigned may have been expired, changed or disabled. Check the system log for more details.
• Check the msdb database. If it is corrupted or offline, Agent will not be started.
• Check the job history. Check whether last run was successful. There can be some issues with business logic implemented.
• Check whether the job is enabled. Someone might have disabled it.
• Check whether the schedule set is either expired or disabled.
• Check and see whether proxy accounts are working properly if you have used. Check credentials used for proxy accounts.
• Check dependencies. There can be steps in the job that run without any issues but some. Check whether all required items, such as files, folders and all required permissions for accessing are available.