Sunsick's Tech Blog

Friday, 29 May 2020

[SQL Server] SQL Server in Single-User Mode

[SQL Server] SQL Server in Single-User Mode

When to use?

SQL Server is damaged and can't start normally.
There's a SQL Server trigger and everybody is blocked to login.
You want to do some maintenance work, but you don't want to connect to the server without configuring firewalls or something.

Prerequisite

You are able to connect to the server via RDP.
You are an administrator of the server.
There's a SQL Server trigger and everybody is blocked to login.
You want to do some maintenance work, but you don't want to connect to the server without configuring the firewall or anything.

How to configure

with SQL Server Configuration Manager

Launch SQL Server Configuration Manager

Open SQL Server, Select Properties

Go to "Startup Parameters" and add "-m"

Check and "OK"

Restart SQL Server service

with Command Prompt

Check Service Name, (It's MSSQL$SQL2017 in this case)

use "Net stop" to stop service

net stop MSSQL$SQL2017

use "Net start" to start service with /m option

net start MSSQL$SQL2017 /m

Test

Now you will see the following error message when you're trying to connect to the server

Connect to SQL Server

SQL Server Management Studio

Now, you try to connect to the server as a local administrator

Somehow, you can't login to the server even if you're a local administrator

This is because "Object Explorer" takes a session already.

So, change the environment: Open new query window at Startup

"OK" and close all SSMS

Restart SQL Server

Launch SSMS manually with the options

"C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\ssms"

-S .\SQL2017 -d Master -E

Now it works and you're the only one.

Of course, you can't open another a new query window

SQLCMD

Connect to the server via sqlcmd

sqlcmd -S .\SQL2017 -d Master -E

References

Start SQL Server in Single-User Mode:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode?view=sql-server-ver15
SSMS - Options (Environment - Startup page):
https://docs.microsoft.com/en-us/sql/ssms/menu-help/options-environment-startup-page?view=sql-server-ver15

Thursday, 28 May 2020

[MySQL] Zabbix Error - Access denied : SHOW SLAVE STATUS

[MySQL] Zabbix Error - Access denied : SHOW SLAVE STATUS

How to grant a privilege to zabbix user for "SHOW SLAVE STATUS"

When you install zabbix on MySQL, you might see the following error message.

2017-01-01 01:01:01 zabbix[zabbix] @ localhost [] ERROR 1227: Access denied;

 you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation : SHOW SLAVE STATUS

Now, let's check the privileges.

SELECT USER, HOST, SUPER_PRIV, REPL_CLIENT_PRIV 
FROM mysql.user
WHERE USER = 'zabbix';

zabbix user does not have "REPL_CLIENT_"PRIV".

Let's grant the replication client permission to zabbix user.
Method #1. GRANT. (Recommendation)

GRANT REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'zabbix'@'127.0.0.1';

Method #2. UPDATE mysql.user Table

UPDATE mysql.user
SET
  REPL_CLIENT_PRIV = 'Y'
WHERE USER = 'zabbix';

Now, check the privileges again.

SELECT USER, HOST, SUPER_PRIV, REPL_CLIENT_PRIV 
FROM mysql.user
WHERE USER = 'zabbix';

That's it.

It will work.

Monday, 18 May 2020

[SQL Server] Implement Oracle "START WITH ~ CONNECT BY" clause

[SQL Server] Implement Oracle "START WITH ~ CONNECT BY" clause

# Oracle

SELECT   LEVEL, CATE_ID, LTRIM (SYS_CONNECT_BY_PATH (CATE_NAME, ' > '), ' > ') || ' > ' AS CATE_NAME
      FROM TBL_CATEGORY
START WITH CATE_ID = 1
CONNECT BY PRIOR CATE_ID = PARENT_CATE_ID

# SQL Server

WITH CTE_TABLE
AS
(
       SELECT 1 AS LEVEL, A.CATE_ID, PARENT_CATE_ID, CATE_NAME, CONVERT(VARCHAR(100), CAST(A.CATE_NAME as VARCHAR(100)) + ' > ') AS CATE_NAME
       FROM dbo.TBL_CATEGORY A
       WHERE A.CATE_ID = 1

       UNION ALL

       SELECT LEVEL + 1, M.CATE_ID, M.PARENT_CATE_ID, M.CATE_NAME, CONVERT(VARCHAR(100), N.CATE_NAME + ' > ' + CAST(M.CATE_NAME AS VARCHAR(100)) + ' > ' ) AS CATE_NAME
       FROM dbo.TBL_CATEGORY M JOIN CTE_TABLE N ON M.PARENT_CATE_ID = N.CATE_ID
)
SELECT * FROM CTE_TABLE
;

Sunday, 17 May 2020

[SQL Server] Display VLF(Virtual Log File) per database

[SQL Server] Display VLF(Virtual Log File) per database

Check Log Information for a single database

DBCC LOGINFO(AdventureWorks2016)

Check Log Information for all databases

CREATE TABLE #eachDB(
RecoveryUnitId INT
, FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] BIGINT
, Parity BIGINT
, CreateLSN NUMERIC(38)
);

CREATE TABLE #summary(
Database_Name sysname
, VLF_count INT
, Log_File_count INT
);

EXEC sp_MSforeachdb N'Use [?];
Insert Into #eachDB
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #summary
Select DB_Name(), Count(*), Count(Distinct FileID)
From #eachDB;
Truncate Table #eachDB;'

-- Check Resut
SELECT *
FROM #summary
ORDER BY VLF_count DESC;

-- Drop temp tables
DROP TABLE #eachDB;
DROP TABLE #summary;

[SQL Server] Email validation function (using regular expressions)

[SQL Server] Email validation function (using regular expressions)

Create a user-defined function

CREATE FUNCTION [dbo].UFN_ValidEmailCheck
(
       @EMail VARCHAR(255)
)
RETURNS BIT
AS
BEGIN
       DECLARE @IsValid BIT
       SET @IsValid = 0    
       IF @EMail IS NOT NULL
       BEGIN
             SET @EMail = LOWER(@EMail)

             IF @EMail like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%'
                    AND @EMail NOT like '%@%@%'
                    AND CHARINDEX('.@',@EMail) = 0
                    AND CHARINDEX('..',@EMail) = 0
                    AND CHARINDEX(',',@EMail) = 0
                    AND RIGHT(@EMail,1) between 'a' AND 'z'
             BEGIN
                    SET @IsValid = 1
             END
       END

       RETURN @IsValid
END

Test 01.

1	SELECT dbo.UFN_ValidEmailCheck('wwwwaaaa@aaabbcc.com') AS ValidEmail

ValidEmail
----------
1

(1 row affected)

Test 02.

1	SELECT dbo.UFN_ValidEmailCheck('^!afvb@bbbbb.com') AS ValidEmail

ValidEmail
----------
0

(1 row affected)

Test 03.

1	SELECT dbo.UFN_ValidEmailCheck('a.b.c@aaa.bbcc.com') AS ValidEmail

ValidEmail
----------
1

(1 row affected)

Test 04.

1	SELECT dbo.UFN_ValidEmailCheck('a.b.c@.aaa.bbcc.com') AS ValidEmail

ValidEmail
----------
0

(1 row affected)

Saturday, 16 May 2020

[MariaDB] How to change MariaDB error log from syslog to separated log file

[MariaDB] How to change MariaDB error log from syslog to separated log file

On my.cnf, you can see the message like this.

# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.

Therefore, if you want to see MariaDB Error Log, then you need to check syslog.

For e.g.

$ cat /var/log/syslog | grep "mysql"

$ cat /var/log/syslog | grep "mariadb"

But, you can store error log to a certain file instead of syslog by editing "/etc/mysql/conf.d/mysqld_safe_syslog.cnf".

$ sudo vi /etc/mysql/conf.d/mysqld_safe_syslog.cnf

[mysqld_safe]
skip_log_error
#syslog

Then, specify the error log file path on my.cnf.

$ vi /etc/mysql/my.cnf

log_error = /var/log/mysql/error.log

Restart MariaDB.
Then error log will be stored into /var/log/mysql/error.log.

Reference.
https://mariadb.com/kb/en/mariadb/error-log/

Thursday, 14 May 2020

[SQL Server] DB Size Check Script - Simple

Script

USE tempdb
GO

DECLARE @dbname SYSNAME;
SET @dbname = NULL;
IF @dbname IS NOT NULL
   AND @dbname NOT IN(SELECT name
                      FROM master.dbo.sysdatabases)
    BEGIN
        RAISERROR('You''re just one step away from the results. please verify the database name is correct and try again. www.sqlServer-Training.com', 16, 1);
END;
SET NOCOUNT ON;
IF EXISTS
(
    SELECT TOP 1 1  FROM tempdb..sysobjects
    WHERE name LIKE '#sizeinfo%' AND type = 'u'
)
DROP TABLE #sizeinfo;
;

CREATE TABLE #sizeinfo
(db_name         VARCHAR(100) NOT NULL PRIMARY KEY CLUSTERED , 
 total           DEC(7, 1), 
 data            DEC(7, 1), 
 data_used       DEC(7, 1), 
 [data (%)]      DEC(7, 1), 
 data_free       DEC(7, 1), 
 [data_free (%)] DEC(7, 1), 
 log             DEC(7, 1), 
 log_used        DEC(7, 1), 
 [log (%)]       DEC(7, 1), 
 log_free        DEC(7, 1), 
 [log_free (%)]  DEC(7, 1), 
 STATUS          DEC(7, 1)
);

SET NOCOUNT ON;
INSERT INTO #sizeinfo
(db_name, 
 log, 
 [log (%)], 
 STATUS
)
EXEC ('dbcc sqlperf(logspace)
with no_infomsgs');
PRINT '';
PRINT '';

IF @dbname IS NULL
    DECLARE dbname CURSOR
    FOR SELECT name
        FROM master.dbo.sysdatabases
        WHERE NOT STATUS = 32
              AND NOT STATUS = 512
     AND name <> 'AdventureWorks2014'
        ORDER BY name ASC;
    ELSE
    IF @dbname IS NOT NULL
        BEGIN
            DELETE FROM #sizeinfo
            WHERE db_name <> @dbname;
            DECLARE dbname CURSOR
            FOR SELECT name
                FROM master.dbo.sysdatabases
                WHERE NOT STATUS = 32
                      AND NOT STATUS = 512
                      AND name = @dbname
       AND name <> 'AdventureWorks2014'
    END;
OPEN dbname;
FETCH NEXT FROM dbname INTO @dbname;
WHILE @@fetch_status = 0
BEGIN
 ----- adding .0 at the end of interger to avoid divide by zero error
 EXEC (' use ['+@dbname+'] declare @total dec(7,1),
  @data dec (7, 1),
  @data_used dec (7, 1),
  @data_percent dec (7, 1),
  @data_free dec (7, 1),
  @data_free_percent dec (7, 1),
  @log dec (7, 1),
  @log_used dec (7, 1),
  @log_used_percent dec (7, 1),
  @log_free dec (7, 1),
  @log_free_percent dec (7, 1)
  set @total = (select sum(convert(dec(15),size)) from sysfiles) * 8192.0 /1048576.0
  set @data = (select sum(size) from sysfiles where (status & 64 = 0))* 8192.0 / 1048576.0
  set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes
  where indid in (0, 1, 255)) * 8192.0 / 1048576.0
  set
  @data_percent = (@data_used * 100.0 / @data)
  set @data_free = (@data - @data_used)
  set @data_free_percent = (@data_free * 100.0 / @data
  )
  set @log = (select log from #sizeinfo where db_name = '''+@dbname+''')
  set @log_used_percent = (select [log (%)] from #sizeinfo where db_name ='''+@dbname+''')
  set @log_used = @log * @log_used_percent / 100.0
  set @log_free = @log - @log_used
  set @log_free_percent =@log_free * 100.0 / @log
  update #sizeinfo set total = @total,
 
  data = @data ,
  data_used = @data_used,
  [data (%)] = @data_percent,
  data_free = @data_free,
  [data_free (%)] = @data_free_percent,
  log_used = @log_used,
  log_free = @log_free,
  [log_free (%)] = @log_free_percent
  where db_name = '''+@dbname+'''');
FETCH NEXT FROM dbname INTO @dbname;
    END;
CLOSE dbname;
DEALLOCATE dbname;

IF(
 (
  SELECT COUNT(*)
  FROM #sizeinfo
 ) <> 1)
    SELECT @@servername AS 'ServerName', 
           db_name, 
           total, 
           data, 
           data_used, 
           [data (%)], 
           data_free, 
           [data_free (%)], 
           log, 
           log_used, 
           [log (%)], 
           log_free, 
           [log_free (%)]
    FROM #sizeinfo
    ORDER BY db_name ASC;
    ELSE
    SELECT @@servername AS 'ServerName', 
           db_name, 
           total, 
           data, 
           data_used, 
           [data (%)], 
           data_free, 
           [data_free (%)], 
           log, 
           log_used, 
           [log (%)], 
           log_free, 
           [log_free (%)]
    FROM #sizeinfo;

DROP TABLE #sizeinfo;

Result example