Configure a Linux Service to Start Automatically After a Crash or Reboot

Configure a Linux Service to Start Automatically After a Crash or Reboot

Below articles defines how to start a service automatically after restart or server or when service is stopped by some other service or accidental user kill.

Note: this will work only with systemd

If service are enable under systemctl, service will start automatically after system reboot. However it does not restart when service was killed accidentally or by any other process.

To check if the service enable for auto restart run below command ( in this case I am running for mongodb)

systemctl status mongod.serivce

output should contain "mongod.service; enabled;" if yes you are good at server restart.
If not run below command to enable service:
systemctl enable mongod.serivce

Below are the steps to enable service to start automatically after unexpected service down:

1. open below file using text editor for mongodb for other serivces use different service name instead of mongod

/etc/systemd/system/multi-user.target.wants/mongod.service

2. Add "Restart=always" under [Service] section of the file, save and close the file.
3. Reload the systemctl demon

sudo systemctl daemon-reload

4. Restart the service in this case mongodb service

systemctl restart mongod.service

For testing we can kill the service, below are steps:
6. Get the pid with the help of below command for mongodb 

ps -ef | grep service

7. kill the process with the help process number which we have received from above command.

kill -9 process_number

8. wait for a second or two and run below command to check the status of serivce in this case mongodb.

systemctl status mongod.service

Note: All the information provided in this article is based on my experience usage or application  based on your own interest.

Thanks for the checking in yours Nag.

Start mongodb automatically after it gets crashed or down.

Start mongodb automatically after it gets crashed or down.

Recently we have started getting an issue where on one of our server we are running both Redis and Mongodb. When we have memory issue Redis is killing Mongod process on Linux box.

One of the best possible solutions until we separate mongodb and Redis server is we have to start Mongodb service automatically after it crash. Did lots of research on internet could not find direct and easy solution but most of them helped me to find right solution. Writing this article by hoping this might help someone.

Environment:

We are running on Mongodb on RHEL 7 which uses systemctl for handling all services and below details applies to same.

First we need to find the service file location which can be found as below:

Sudo systemctl status mongod.service

Check for “loaded” line to find the service location.

Example: Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)

Edit the file mongod.service using your favorite editor and add below two lines:

Restart=on-failure

RestartSec=5s

Restart on failure will help restart mognodb service automatically and restartsec parameter will allow services to wait for 5 seconds in this case. Completed reference for can be found on link:

https://www.freedesktop.org/software/systemd/man/systemd.service.html

Post this we need to reload units with the help of below command:

sudo systemctl daemon-reload

Once done restart your mongodb service with the help of below commands:

sudo systemctl stop mongod.service

sudo systemctl start mongod.service

For testing you can kill the mongodb service with the help of below commands:

ps -ef | grep mongod.service

Will get you PID number.

sudo kill -9 process_number

Will kill mongodb PID.

Wait for 5 seconds you will see mongodb back online again.

All the very best Thank you, Nag.

How to automate multiple script execution using SQLCMD and bath file.

How to automate multiple script execution using SQLCMD and bath file.

Problem

As a DBA’s many times we receive requests to execute multiple scripts, during some release times we end up having more than 100 scrips as well. Executing each script one at a time is always time consuming and chances of missing a script or two is high.

Solution

We can automate this by few simple steps:

1.      Create folder and place all scripts in the folder.

2.      Open notepad and add below script to and saves Execute_ALL_SQL.bat or whatever you feel comfortable.

@echo off

ECHO %USERNAME% started the batch process at %TIME%  >output.txt

for %%f in (*.sql) do (

echo %%f >>output.txt

sqlcmd.exe  -S -E -d yourDB -i "%%f" >>output.txt

    )

Pause

“ECHO %USERNAME% started the batch process at %TIME%  >output.txt” name will write name of the user who is executing script to output file.

Next line is For loop to loop through all .SQL files.

“echo %%f >>output.txt” while write SQL file name to output file.

Definition of “sqlcmd.exe  -S ServerName -E -d youDB -i "%%f" >>output.txt” SQLCMD.exe will help us run SQL commands. In this case

·        -S is SQL Server name on which you want to execute script.

·        -E is for windows or trusted authentication or we can use -U username and -P password for SQL Authentication.

·        -i input SQL files.

Added Pause at the last line just to let you know script execution completed and read out for any errors. Press space button it will close the window.

3.      Double click on the BAT file boom you are done with executing all scripts in one short.

4.      You can share the content of output file which will the script you have executed, how many rows effected if any.

Note: make sure both “BAT” file and “SQL” files are in same folder.

Capturing and decrypting Deadlock information from SQL Server Part 1

Capturing and decoding Deadlock information from SQL Server Part 1

We will have two articles in this series.

1. Different ways to capture deadlock

2. Decoding deadlock data from captured deadlock data

I know this is very common issue which DBA might not be able to completely resolve but DBA can provide detailed information regarding Deadlock.

Let us see what are the different ways we can capture deadlock data from SQL Server.

From SQL Server 2012 onwards we can capture deadlock data directly from System Health Extended event session, for this we don’t need enable any trace flags.

Earlier to SQL Server 2012, we need to enable flags 1204 and 1222 to get detailed deadlock information.

You can check my blog to find how to enable trace flags.

Below is the script to capture deadlock, This code is robust enough to check for SQL Server version, to get data from System health Event session or from SQL Server error log. If it does not find any information it will give suggestions as well.

SET NOCOUNT ON

DECLARE @stDate AS DATETIME = '2016-06-13 04:49:26'                                --Please key in the UTC time at which alert created

DECLARE @DecelockText AS VARCHAR (100) = 'Process ID 113'     --Replace 113 with the process ID part of deadlock

DECLARE @endDate AS DATETIME

SELECT @stDate = DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),@stDate)

SET @endDate = DATEADD(MINUTE, 1, @stDate)

SET @stDate  = DATEADD(MINUTE, -2, @stDate)

IF (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10)) ) >= 11

BEGIN

                DECLARE @PATH VARCHAR(500)

                SELECT  @PATH= [path] FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);

                SET @PATH = @PATH + 'system_health*.xel'

                CREATE TABLE #Deadloc_Trace (DLGraph XML, Execution_time datetime)

                INSERT INTO #Deadloc_Trace

                SELECT CONVERT(xml, event_data).query('/event/data/value/child::*'),

                                   CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time

                FROM sys.fn_xe_file_target_read_file(@PATH, null, null, null)

                WHERE object_name like 'xml_deadlock_report'

                SELECT * FROM  #Deadloc_Trace WHERE Execution_Time BETWEEN @stDate and @endDate

                DROP TABLE #Deadloc_Trace

END

ELSE

                BEGIN

                CREATE TABLE #TraceStatus(

                TraceFlag varchar(100)

                , [Status] int

                , [Global] int

                ,  [Session] int)

                insert #TraceStatus(TraceFlag, [Status], [Global], [Session])

                EXEC('DBCC TRACESTATUS(1204,1222) WITH NO_INFOMSGS;')

               

                IF EXISTS (select * from #TraceStatus where Global > 0)

                BEGIN

                                CREATE TABLE #ErrLog(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));

                                INSERT INTO #ErrLog

                                EXEC xp_readerrorlog 0, 1, @DecelockText;

                                SELECT TOP 1 @stDate = LogDate

                                FROM #ErrLog

                                ORDER BY LogDate DESC;

                                SET @endDate = DATEADD(minute,2,@stDate)

                                --select @stDate, @endDate

                                EXEC master.dbo.xp_readerrorlog 0, 1, null, null,@stDate, @endDate

                                DROP TABLE #ErrLog;

                END

                ELSE

                BEGIN

                                PRINT 'Currently deadlock traces are not enabled and hence we cannot gather more information.

                We need to enable Deadlock traces 1204,1222 to capture deadlock information in future.

                '

                END

                DROP TABLE #TraceStatus;

END

Hope this helps someone thanks for checking in.

Start SQL Server Management Studio (SSMS) Or connect to SQL Server using NT Authority\ System

Start SQL Server Management Studio (SSMS) Or connect to SQL Server using NT Authority\ System

Recently we have faced a problem where sysadmin access for all of our accounts were removed accidently and SA was in disabled state.

Later we have noticed that one of the possible solution to this case is to connect SQL Server using NT Authority\System as this is default user till SQL Server 2008 R2 who will have sysadmin access on DB instance. From SQL Server 2012 onwards NT Authority\System user will not have sysadmin access and this article might not help. Below are quick steps to connect SSMS using NT Authority\System:

1. Download PS tools from below link:

https://docs.microsoft.com/en-us/sysinternals/downloads/psexec

2. Extract them to a folder.

3. Right click on CMD and run as administrator.

4. Navigate to folder where you have extracted zip file downloaded part of above link.

5. Run below command to start SSMS using NT Authority\System user

psexec -i -s SSMS

Done you should be able to access DB instance and provide access to the users you need. Hope this helps someone as it did to me.

SQL Server patching and Fixing Missing MSI and MSP files issue

SQL Server patching and Fixing Missing MSI and MSP files issue

I know by looking at this article name many of you already might think that is well known issue and addressed on below MS Link.

https://support.microsoft.com/en-us/help/969052

However in this article I will describe an interesting new way I came across in recent past. Which I found be better and easy way  as this uses a GUI tool.

Recently one of my friend found this interesting tool and shared details with me. I thought why to stop with me and here is this article.

In this article we will see how easily FixSQLMSI.exe can fix Missing MSI and MSP files issues. This tool is engineer name “Simon Su” and available on below GitHub link.

https://github.com/suyouquan/SQLSetupTools/tree/8041ef5b47897cb7b576e5834f63c70a62d3fcb0

Please find how to use steps below:

1. Download the package from above link.

2. Execute the exe.

3. Select the SQL Server Install path and click scan.

4. It will check for both Missing MSI, MSP files and any miss matched files (missed matched files would be shown if you have cached any wrong files in installer folder)

5. Once scanning is done check for Status and Action columns to find and fix any issues. You will find many more details like Source path, command to fix and so on.

6. If you have any missing files, click on Fix It button, if source path is intact it will fix automatically if not it will ask for source path, please provide the same to fix automatically.

7. Once you are done you may select log button to generate log and share details.

8. More detailed document is published by author as part of attached link you may check the same.

It is better to use this exe all the times before you patch or if you find any issues while patching any SQL Server instance.

Note: this is just a guide that might help you. Please take your own decision to use it or not.

Being honest to yourself is one of the biggest success mantra.

Being honest to yourself is one of the biggest success mantra.

This is all about my personal experience and I found being honest to myself is one of the biggest success mantra. Many times we plan to change our habits or we plan to achieve something and every time we comprise on them and we don’t achieve them by consoling ourselves that it is ok. This where we are not honest with us.

If we have to achieve success just follow 3 simple steps, after sometime you will wonder how things can be achieved.

1. Plan your carrier (clarity).
If you already have a carrier plan that is really great. If not, This won’t come by so easily, keep searching start looking around and start asking yourself what you want, you will find a moment where you will know what you want to be. Once you are ready don’t change this and be honest to yourself.
2. Plan your next 3, 6 months or an year based on what you want to achieve. I would say start with 3 months or even 1 month is also fine.

3. Chunk your goals to small parts so that some progress can be found every day. 
This is where actual test begins if you want succeed, you have to write down what you want to achieve every day either same day morning or previous day evening. At the end of the day make sure to revisit and check what you have completed and what you have not. Make sure to tick them right or wrong. I suggest use Red pen for wrong and blue for right.
You might fail to meet all your small goals everyday but eventually you will see that you will be able to finish everything on your daily activity list. This is possible only if you are honest to yourself and tick your goals right or wrong every day and include them in your next day activities. Don’t move to another activity as long as you don’t finish the one which you have not completed previous day.

That's it you might feel this is very simple, but practice them to see how difficult they are at the beginning and how great things can be achieved once you have built this habbit or created a pattern in your brain.

Don’t give up be honest is the mantra. 

Hope this is useful to someone. All the very best to everyone.

How to enable Trace flag without restarting SQL Server immediately

How to enable Trace flag without restarting SQL Server immediately

Many times when DBA has to enable a Trace flag permanently we have to go to Customer and ask for a downtime or wait for monthly or quarterly agreed downtime. However we can avoid this wait time in two simple steps:

1. Enable Trace flag at global level with DBCC command as below.
DBCC TRACEON( 3226, -1) , this trace flag helps not to log successful backup completions.
2. Add trace flag as startup parameter with help of -T and dont restart SQL Services.

When we restart Windows box or SQL Services during OS or SQL patching, trace flag enabled using -1 will get disabled at shutdown of SQL Services and Trace flag added part of startup parameter will take effect from SQL Server startup.

This is one of the short article, hope this finds helpful to someone. Stay tunned for more knowledge sharing.

Effective management of SQL Server logs and different ways to read them

Effective management of SQL Server logs and different ways to read them

Why do we need to manage SQL Server error logs efficiently?
Most important reason is readability, I have faced this situation many times while working with many SQL Server Instances. When we are trouble shooting SQL Server it is very hard go through many lines of logs which are useless, sometimes as DBA we would be working under time pressure.
Few things in SQL Server error log are useless bye default like successful logins (if you have enabled), Successful backups and deadlocks from SQL Server 2008 R2 onwards. Based on my experience it is always good to keep SQL Server logs short and quickly readable from notepad when SQL is down or not accessible. Here are few of the changes we need to implement:

    1. Enable Trace flag 3226, this helps not to log all successful backup completions.
This can enabled by adding -T3226 as start up parameter permanently. This requires down time for  SQL Server however we can avoid this, I will explain the steps in my another article.

    2. Dont enable Deadlock trace flags from SQL Server 2008 R2 onwards, as complete deadlock details are available as part of default System health Extended event. I will explain on How get deadlock details from this extended event in a separate article, I will post the link once it is ready.
  
    3. Last but not least recycle your error logs to keep them small and number of logs or length of the logs can be decided based on Customer requirements.

So far we have reviewed few of the setting those can help keep our SQL Server error log short and sweet, now we will see how to read SQL Server Error log.

SQL Server error log can be read with the help of XP_READERRORLOG or SP_READERRORLOG. My favorite is XP_READERRORLOG as it has more parameters. Details are below:


EXEC master.dbo.xp_readerrorlog 0, 1, null, null, null, null, N'desc'
1st parameter is for error log file number you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2nd Parameter is for which log you want to read: 1 or NULL = error log, 2 = SQL Agent log
3rd Parameter is for 1st string you want to search on.
4th Parameter is for further fine tune your search, which means 4th parameter can be used to filter the output of 3rd or 1st string parameter.
5th Start date time in SQL Server error log.
6th End date time in SQL Server error log.
7th how do you want to sort your output based on date time of error log: N'asc' = ascending, N'desc' = descending.

SP_READERRORLOG error log internally uses XP_READERRORLOG however SP_READERRORLOG has limited number of parameters and they are:

1st parameter is for error log file number you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2nd Parameter is for which log you want to read: 1 or NULL = error log, 2 = SQL Agent log
3rd Parameter is for 1st string you want to search on.
4th Parameter is for further fine tune your search, which means 4th parameter can be used to filter the output of 3rd or 1st string parameter.

Hope this article finds useful. Thanks for reading.

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)

Recently while working on DR test, I have come across unknown issue. I could not find much help from google on this. This is regarding database mirroring and this should stand same for AlwaysON up to some extent as they both use Endpoints to communicate and service accounts for encrypting data over endpoints.

Problem description:

Part of the DR exercise we have disabled Mirroring on MSSQ 2014 databases and brought DR DB’s online. Post DR exercise when we try to re-establish mirroring we have started getting below error:

Primary logs:

2018-03-20 22:50:40.120 spid40s      Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://XX.XX.XX.XX:7077'.

2018-03-20 22:50:55.780 spid22s      Error: 1443, Severity: 16, State: 2.

As part of general trouble shooting I have stopped and started Endpoints on both servers no luck. Tried telnet from both servers everything was fine and no issues found.

Then tried researching error logs as they say for any issues a good troubleshooting will always start from SQL Server error logs. But in this case, we must check SQL Server error logs from both Primary and DR servers, as the issue is related to mirroring and we could not find any useful information from Primary.

When checked Mirror or DR server error logs surprisingly handshake errors were logged. This helped to find root cause the fix the issue.

Mirror logs:

2018-03-20 22:54:15.160 Logon        Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (80090311) 0x80090311(No authority could be contacted for authentication.). State 67.'.  [CLIENT: xx.xx.xx.x]

2018-03-20 22:54:20.200 Logon        Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (80090311) 0x80090311(No authority could be contacted for authentication.). State 67.'.  [CLIENT: xx.xx.xx.x]

2018-03-20 23:06:55.010 Logon        Error: 17806, Severity: 20, State: 14.

2018-03-20 23:06:55.010 Logon        SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon atte

2018-03-20 23:06:55.010 Logon        Error: 18452, Severity: 14, State: 1.

2018-03-20 23:06:55.010 Logon        Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xx.xx.xx.x]

Then logged to both servers using service accounts to make sure Service account is working fine. From Primary server both DB instances were accessible from DR server Primary instance was not accessible.

This gave a hint of confidence that I am on right path and helped to fix the issue.

Possible solutions are:

1.       If the service account password is changed make sure to update on both SQL Servers and restart the Services this should solve if issue is related to service account password.

2.       Sometimes this might not resolve the issue. If your hosted on complex domains like what happened in this case. Servers were hosted on different domain and service accounts used were from different domain. To fix this we have make sure proper trust is established between these two domains so that authenticity of the service account can be validated. This will be taken care by AD admins. This helped resolve the issue.

Hope this helps someone like me 😊.