Helpful Linux Commands

December 20th, 2018 No comments
# Find all symbolic links in the directory 

# .(dot) - search starting from the current dir 
# maxdepth - adjust this to search recursively - this searches 5 levels deep 

find . -maxdepth 5 -type l -ls
Categories: Linux, Tips and Tricks Tags:

SQL Server shrinking log file size

November 13th, 2018 No comments

This is a quick post to show how to shrink the size of the log file for a database.
Be extremely careful when doing this and always backup the entire database. Try on a test database or server first.
Do this at your own risk.

-- database name = MyDatabaseName
-- the database log file name = MyDatabaseName_log
-- database recovery type = Full
USE MyDatabaseName;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
-- You need to do this if your database recovery mode is set to FULL
ALTER DATABASE MyDatabaseName SET RECOVERY SIMPLE;
GO

-- Shrink the log file to 100 MB.
DBCC SHRINKFILE (MyDatabaseName_log, 100);
GO

-- Reset the database recovery model.
ALTER DATABASE MyDatabaseName SET RECOVERY FULL;
GO
Categories: MSSQL, Tips and Tricks Tags:

Installing nodejs on CentOS 7 with NVM

November 8th, 2018 No comments

Using NVM(Node Version Manager) we can have multiple nodejs versions and switch between them quickly.
I am doing this on CentOS 7.

# install nvm
curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.33.11/install.sh | bash

# open a new terminal and check the version
nvm --version

# install latest node LTS and check version
nvm install --lts
node --version
npm --version

# install another node version
nvm install 8.12.0

# show all versions
nvm ls

# output
-> v8.12.0
v10.13.0
default -> lts/* (-> v10.13.0)
node -> stable (-> v10.13.0) (default)
stable -> 10.13 (-> v10.13.0) (default)
iojs -> N/A (default)
lts/* -> lts/dubnium (-> v10.13.0)
lts/argon -> v4.9.1 (-> N/A)
lts/boron -> v6.14.4 (-> N/A)
lts/carbon -> v8.12.0
lts/dubnium -> v10.13.0

# -> (arrow) means that is the active version for the current shell
# (default) means, for any new shell you open, that will be the version used
# always check the node version in your shells

# to set an active version
# then issue nvm ls to see the arrow(->) move to the required version
nvm use 10.13.0
nvm ls

# to change default node version
nvm alias default 10.13.0

# additional tools for building
sudo yum install gcc-c++ make

Categories: Javascript, Web Development Tags: ,

Virtualbox Set date and time manually

September 25th, 2018 No comments

This post is about setting time manually on a Linux VM independent of the host machine.
Virtualbox automatically syncs VM time to the host machine and sometimes the guest additions will do this as well.

I am doing this on the below
Host OS: Windows 10
Guest OS: CentOS 7
Guest VM Name: CentOS 7

Shutdown all VM’s and close the Virtualbox software.
Nothing related to Virtualbox should be running.

Important: Take a backup of these files as we will be modifying them.
Change the username accordingly

  1. The main Virtualbox xml file: C:\Users\{your-user-name}\.VirtualBox\VirtualBox.xml
  2. The xml file of the VM you need to change the time: C:\Users\{your-user-name}\VirtualBox VMs\CentOS 7\CentOS 7.vbox

Again take a backup before editing.
Edit both files and add the below lines to both files

<ExtraDataItem name="VBoxInternal/Devices/VMMDev/0/Config/GetHostTimeDisabled" value="1"/>

Save both files.
Start your Virtual machine
Once it boots up, you can set the date and time manually by using the below command

date -s "24 SEP 2018 22:58:00"

check the new date with the date command

You should disable things like ntp if they are set to update time automatically

Categories: Linux, Tips and Tricks Tags:

Kill all PHP, ngix, mysql processes

September 17th, 2018 No comments

Note: Please use with caution. I am not responsible if you mess up your production server. You have been warned !


# kill all PHP Processes
kill $(ps aux | grep '[p]hp' | awk '{print $2}')

# kill all Nginx Processes
kill $(ps aux | grep ‘[n]ginx’ | awk ‘{print $2}’)

# kill all MySQL Processes
kill $(ps aux | grep ‘[m]ysql’ | awk ‘{print $2}’)

Categories: Linux, Tips and Tricks Tags:

MSSQL: Session and Request info, Blocking sessions

September 17th, 2018 No comments

Here is a list of queries that can be helpful if you are trying to troubleshoot locks and blocking sessions on SQL server.


-- to get session and connections on the server
SELECT *
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_exec_connections AS conn ON ( sess.session_id = conn.session_id );


-- another command to see details
EXEC sp_who2
EXEC sp_who2 'active'


-- to find all requests and who is blokcing whom
-- see the column = blocking_session_id
SELECT * FROM sys.dm_exec_requests


-- show the longest running SPIDs
-- Till spid 50,it's all are sql server internal process sessions
select
P.spid
, right(convert(varchar, dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 121), 12) as 'batch_duration'
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in ('background', 'sleeping')
and P.cmd not in (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)
order by batch_duration desc


-- to see the SQL running for a given spid
declare
@spid int
, @stmt_start int
, @stmt_end int
, @sql_handle binary(20)

set @spid = XXX — Fill this in

select top 1
@sql_handle = sql_handle
, @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
, @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from master.dbo.sysprocesses
where spid = @spid
order by ecid

SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end – @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)

Categories: MSSQL, Tips and Tricks Tags:

SQL Server: Refresh View Metadata

September 10th, 2018 No comments

After making changes to tables, you may need to refresh the metedata of the views pointing to those tables.
To refresh the metadata of all views in the database, use this

USE {database name here}
GO

DECLARE @sqlcmd NVARCHAR(MAX) = ''
SELECT @sqlcmd = @sqlcmd + 'EXEC sp_refreshview ''' + name + ''';'
FROM sys.objects AS so
WHERE so.type = 'V'

SELECT @sqlcmd

-- uncomment to execute
--EXEC(@sqlcmd)

Categories: MSSQL, Tips and Tricks Tags:

Windows 10 multiple monitor backgrounds

June 13th, 2018 No comments

The Appearance and Customization panel is hidden in Win 10 and cannot be accessed from the Control Panel ( why Microsoft ? )
You can use a handy command to gain access and then set different desktop backgrounds for each of your monitors.

Open a command prompt and type and hit enter – a new window should open

control /name Microsoft.Personalization /page pageWallpaper

You can right click on the pictures and choose the monitor you want.

Categories: Tips and Tricks Tags:

SQL substring replacement

May 14th, 2018 No comments

There are times when you need to replace only a part of the string in the database column.
You can use the stuff() function in MSSQL or insert() function in MySQL

MSSQL

 Definition: STUFF ( string , start , length , replaceWith )
 Example: select stuff('My Original String', 4, 8, 'Replaced'); 
 Output: My Replaced String

MySQL

 Definition: INSERT( string, start, length, replaceWith )
 Example: select insert('My Original String', 4, 8, 'Replaced');
 Output: My Replaced String
Categories: MySQL, Tips and Tricks Tags:

Centos7 cleaning boot partition

March 20th, 2018 No comments

Boot partition might be full as it stores old kernels etc.

To clean it up and store only the latest 2 kernels do the below

Take a backup of your /boot partition somewhere first

edit /etc/yum.conf and set installonly_limit=2 

# install yum utilities 
yum install yum-utils

# cleanup old kernels 
package-cleanup --oldkernels --count=2
Categories: Linux Tags: