Run SQL Server BLOG – Just another Select of Ms SQL DBA site

29th September 2025

Ubuntu

some daily operational tasks:

sudo apt upgrade
 
if [ -f /var/run/reboot-required ]; then echo 'Reboot required'; fi

sudo tail -n 80 /var/log/apache2/futas.net_error.log

sudo apt upgrade

if [ -f /var/run/reboot-required ]; then echo 'Reboot required'; fi

sudo tail -n 80 /var/log/apache2/futas.net_error.log

29th September 202529th September 2025

MySQL database performance

Database sizes and number of tables:

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',
    COUNT(*) AS 'Number of Tables'
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql')
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

SELECT

table_schema AS 'Database',

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',

COUNT(*) AS 'Number of Tables'

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql')

GROUP BY table_schema

ORDER BY SUM(data_length + index_length) DESC;

Database total size:

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS 'Data_Size_MB',
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS 'Index_Size_MB',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Total_Size_MB',
    ROUND(SUM(data_length) / SUM(data_length + index_length) * 100, 2) AS 'Data_Percentage'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql')
GROUP BY table_schema
ORDER BY Total_Size_MB DESC;

SELECT

table_schema AS 'Database',

ROUND(SUM(data_length) / 1024 / 1024, 2) AS 'Data_Size_MB',

ROUND(SUM(index_length) / 1024 / 1024, 2) AS 'Index_Size_MB',

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Total_Size_MB',

ROUND(SUM(data_length) / SUM(data_length + index_length) * 100, 2) AS 'Data_Percentage'

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql')

GROUP BY table_schema

ORDER BY Total_Size_MB DESC;

Recent Database Activity

SELECT 
    OBJECT_SCHEMA AS 'Database',
    COUNT_READ AS 'Reads',
    COUNT_WRITE AS 'Writes',
    COUNT_FETCH AS 'Fetches',
    COUNT_STAR AS 'Total_Operations'
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA IS NOT NULL
ORDER BY COUNT_STAR DESC
LIMIT 10;

SELECT

OBJECT_SCHEMA AS 'Database',

COUNT_READ AS 'Reads',

COUNT_WRITE AS 'Writes',

COUNT_FETCH AS 'Fetches',

COUNT_STAR AS 'Total_Operations'

FROM performance_schema.table_io_waits_summary_by_table

WHERE OBJECT_SCHEMA IS NOT NULL

ORDER BY COUNT_STAR DESC

LIMIT 10;

24th July 202524th July 2025

Rename and archive databases to name _old and move the datafiles and tr log files to old subfolder

Sometime the application installation want to create the same databases with the same data files.
In this case we need to rename them and move another folder not to update.

This PowerShell script renames two local SQL Server databases (database1 and database2),
Closes active connections with SINGLE_USER, detaches, moves their files to an “old” subfolder in their current location,
Reattaches them. All PowerShell output and commands are logged to Documents\log.

# Get user's 'My Documents' path and prepare log directory
$myDocs = [Environment]::GetFolderPath('MyDocuments')
$logDir = Join-Path $myDocs 'log'
if (-not (Test-Path $logDir)) {
    New-Item -Path $logDir -ItemType Directory | Out-Null
}

# Log file with timestamp
$logFile = Join-Path $logDir "SQLTask_$(Get-Date -Format 'yyyyMMdd_HHmmss').txt"
Start-Transcript -Path $logFile

# Parameters
$serverInstance = 'localhost' # Change if needed
$dbs = @('OWOC', 'OWOC_workflow')
$suffix = '_old'

Import-Module SqlServer -ErrorAction SilentlyContinue

function Exec-SqlQuery($query) {
    Invoke-Sqlcmd -ServerInstance $serverInstance -Query $query
}

# Step 1: Rename databases with SINGLE_USER to close connections
foreach ($db in $dbs) {
    $dbOld = "${db}${suffix}"
    Write-Host "Setting $db to SINGLE_USER and renaming to $dbOld..."
    Exec-SqlQuery @"
ALTER DATABASE [$db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [$db] MODIFY NAME = [$dbOld];
"@
}

# Step 2: Get file paths for each database
$dbFiles = @{}
foreach ($db in $dbs) {
    $dbOld = "${db}${suffix}"
    $result = Exec-SqlQuery "
        SELECT 
            mf.name AS LogicalName, 
            mf.physical_name AS PhysicalName, 
            mf.type_desc AS Type
        FROM sys.master_files mf
        WHERE mf.database_id = DB_ID('$dbOld');
    "
    $dbFiles[$dbOld] = $result
}

# Step 3: Detach databases
foreach ($db in $dbs) {
    $dbOld = "${db}${suffix}"
    Write-Host "Setting $dbOld to SINGLE_USER before detach..."
    Exec-SqlQuery "ALTER DATABASE [$dbOld] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"
    Write-Host "Detaching $dbOld..."
    Exec-SqlQuery "EXEC sp_detach_db '$dbOld';"
}

# Step 4: Move files to "old" subfolder in their current location
foreach ($dbOld in $dbFiles.Keys) {
    foreach ($row in $dbFiles[$dbOld]) {
        $filePath = $row.PhysicalName
        $baseDir = Split-Path $filePath
        $oldDir = Join-Path $baseDir 'old'
        if (-not (Test-Path $oldDir)) {
            Write-Host "Creating folder $oldDir"
            New-Item -Path $oldDir -ItemType Directory | Out-Null
        }
        $destPath = Join-Path $oldDir (Split-Path $filePath -Leaf)
        Write-Host "Moving $filePath to $destPath"
        Move-Item -Path $filePath -Destination $destPath
        # Update path for attach step
        $row.PhysicalName = $destPath
    }
}

# Step 5: Attach databases back with correct syntax
foreach ($dbOld in $dbFiles.Keys) {
    $fileClauses = @()
    foreach ($row in $dbFiles[$dbOld]) {
        $fileClauses += "( FILENAME = N'$($row.PhysicalName)' )"
    }
    $fileClauseString = $fileClauses -join ",`n"
    
    $sql = "CREATE DATABASE [$dbOld] ON $fileClauseString FOR ATTACH;"
    
    Write-Host "Attaching $dbOld with files:"
    Write-Host $sql
    Exec-SqlQuery $sql

}

Write-Host "DONE. Databases renamed, files moved to 'old' subfolder at their original location, and reattached. See log at $logFile"

Stop-Transcript

# Get user's 'My Documents' path and prepare log directory

$myDocs = [Environment]::GetFolderPath('MyDocuments')

$logDir = Join-Path $myDocs 'log'

if (-not (Test-Path $logDir)) {

New-Item -Path $logDir -ItemType Directory | Out-Null

}

# Log file with timestamp

$logFile = Join-Path $logDir "SQLTask_$(Get-Date -Format 'yyyyMMdd_HHmmss').txt"

Start-Transcript -Path $logFile

# Parameters

$serverInstance = 'localhost' # Change if needed

$dbs = @('OWOC', 'OWOC_workflow')

$suffix = '_old'

Import-Module SqlServer -ErrorAction SilentlyContinue

function Exec-SqlQuery($query) {

Invoke-Sqlcmd -ServerInstance $serverInstance -Query $query

}

# Step 1: Rename databases with SINGLE_USER to close connections

foreach ($db in $dbs) {

$dbOld = "${db}${suffix}"

Write-Host "Setting $db to SINGLE_USER and renaming to $dbOld..."

Exec-SqlQuery @"

ALTER DATABASE [$db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [$db] MODIFY NAME = [$dbOld];

}

# Step 2: Get file paths for each database

$dbFiles = @{}

foreach ($db in $dbs) {

$dbOld = "${db}${suffix}"

$result = Exec-SqlQuery "

SELECT

mf.name AS LogicalName,

mf.physical_name AS PhysicalName,

mf.type_desc AS Type

FROM sys.master_files mf

WHERE mf.database_id = DB_ID('$dbOld');

$dbFiles[$dbOld] = $result

}

# Step 3: Detach databases

foreach ($db in $dbs) {

$dbOld = "${db}${suffix}"

Write-Host "Setting $dbOld to SINGLE_USER before detach..."

Exec-SqlQuery "ALTER DATABASE [$dbOld] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"

Write-Host "Detaching $dbOld..."

Exec-SqlQuery "EXEC sp_detach_db '$dbOld';"

}

# Step 4: Move files to "old" subfolder in their current location

foreach ($dbOld in $dbFiles.Keys) {

foreach ($row in $dbFiles[$dbOld]) {

$filePath = $row.PhysicalName

$baseDir = Split-Path $filePath

$oldDir = Join-Path $baseDir 'old'

if (-not (Test-Path $oldDir)) {

Write-Host "Creating folder $oldDir"

New-Item -Path $oldDir -ItemType Directory | Out-Null

}

$destPath = Join-Path $oldDir (Split-Path $filePath -Leaf)

Write-Host "Moving $filePath to $destPath"

Move-Item -Path $filePath -Destination $destPath

# Update path for attach step

$row.PhysicalName = $destPath

}

# Step 5: Attach databases back with correct syntax

foreach ($dbOld in $dbFiles.Keys) {

$fileClauses = @()

foreach ($row in $dbFiles[$dbOld]) {

$fileClauses += "( FILENAME = N'$($row.PhysicalName)' )"

}

$fileClauseString = $fileClauses -join ",`n"

$sql = "CREATE DATABASE [$dbOld] ON $fileClauseString FOR ATTACH;"

Write-Host "Attaching $dbOld with files:"

Write-Host $sql

Exec-SqlQuery $sql

}

Write-Host "DONE. Databases renamed, files moved to 'old' subfolder at their original location, and reattached. See log at $logFile"

Stop-Transcript

26th April 202526th April 2025

The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Windows return code0x21c7, state: 15

Message
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/computername1.contoso.local:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

This message occurs when an existing spn (for computer) was registered.

Soludtion
Delete the existing computer account spn:

setpn -d MSSQLSvc/computername1.contoso.local:1433  computername1

1	setpn -d MSSQLSvc/computername1.contoso.local:1433 computername1

and register the service account if it is missing:

setspn -s setpn -d MSSQLSvc/computername1.contoso.local:1433 contoso\svcsqlacc1

1	setspn -s setpn -d MSSQLSvc/computername1.contoso.local:1433 contoso\svcsqlacc1

9th January 20259th January 2025

Missing index

New SQL DMV in SQL server 2019

SELECT TOP (25)
    improvement = CAST((misq.avg_total_user_cost * misq.avg_user_impact * (misq.user_seeks + misq.user_scans)) AS DECIMAL(20, 2)),
    (SUBSTRING(
                  sql_text.text,
                  misq.last_statement_start_offset / 2 + 1,
                  (CASE misq.last_statement_start_offset
                       WHEN-1 THEN
                           DATALENGTH(sql_text.text)
                       ELSE
                           misq.last_statement_end_offset
                   END - misq.last_statement_start_offset
                  ) / 2 + 1
              )
    ) AS sql_text,
    CAST(misq.avg_total_user_cost AS DECIMAL(20, 2)) AS avg_total_user_cost,
    CAST(misq.avg_user_impact AS DECIMAL(20, 2)) AS avg_user_impact,
    misq.user_scans,
    misq.user_seeks,
    misq.last_user_seek,
    misq.last_user_scan,
    DB_NAME(mid.database_id) AS database_name,
    mid.statement AS user_table,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats_query AS misq
    INNER JOIN sys.dm_db_missing_index_groups AS mig
        ON mig.index_group_handle = misq.group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid
        ON mig.index_handle = mid.index_handle
    CROSS APPLY sys.dm_exec_sql_text(misq.last_sql_handle) AS sql_text
WHERE mid.database_id = DB_ID()
ORDER BY improvement DESC;
GO

SELECT TOP (25)

improvement = CAST((misq.avg_total_user_cost * misq.avg_user_impact * (misq.user_seeks + misq.user_scans)) AS DECIMAL(20, 2)),

(SUBSTRING(

sql_text.text,

misq.last_statement_start_offset / 2 + 1,

(CASE misq.last_statement_start_offset

WHEN-1 THEN

DATALENGTH(sql_text.text)

ELSE

misq.last_statement_end_offset

END - misq.last_statement_start_offset

) / 2 + 1

)

) AS sql_text,

CAST(misq.avg_total_user_cost AS DECIMAL(20, 2)) AS avg_total_user_cost,

CAST(misq.avg_user_impact AS DECIMAL(20, 2)) AS avg_user_impact,

misq.user_scans,

misq.user_seeks,

misq.last_user_seek,

misq.last_user_scan,

DB_NAME(mid.database_id) AS database_name,

mid.statement AS user_table,

mid.equality_columns,

mid.inequality_columns,

mid.included_columns

FROM sys.dm_db_missing_index_group_stats_query AS misq

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON mig.index_group_handle = misq.group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid

ON mig.index_handle = mid.index_handle

CROSS APPLY sys.dm_exec_sql_text(misq.last_sql_handle) AS sql_text

WHERE mid.database_id = DB_ID()

ORDER BY improvement DESC;

— Selectivity from mssqltips.com

SELECT
    'MostUnique' AS [Column]
    ,COUNT(DISTINCT MostUnique) AS UniqueValues
    ,COUNT(*) AS TotalRows
    ,CAST(COUNT(DISTINCT MostUnique) AS FLOAT) / COUNT(*) AS Selectivity
FROM
    dbo.ColumnOrder
UNION ALL
SELECT
    'SecondMost' AS [Column]
    ,COUNT(DISTINCT SecondMost) AS UniqueValues
    ,COUNT(*) AS TotalRows
    ,CAST(COUNT(DISTINCT SecondMost) AS FLOAT) / COUNT(*) AS Selectivity
FROM
    dbo.ColumnOrder
UNION ALL
SELECT
    'ThirdMost' AS [Column]
    ,COUNT(DISTINCT ThirdMost) AS UniqueValues
    ,COUNT(*) AS TotalRows
    ,CAST(COUNT(DISTINCT ThirdMost) AS FLOAT) / COUNT(*) AS Selectivity
FROM
    dbo.ColumnOrder
UNION ALL
SELECT
    'FourthMost' AS [Column]
    ,COUNT(DISTINCT FourthMost) AS UniqueValues
    ,COUNT(*) AS TotalRows
    ,CAST(COUNT(DISTINCT FourthMost) AS FLOAT) / COUNT(*) AS Selectivity
FROM
    dbo.ColumnOrder;

SELECT

'MostUnique' AS [Column]

,COUNT(DISTINCT MostUnique) AS UniqueValues

,COUNT(*) AS TotalRows

,CAST(COUNT(DISTINCT MostUnique) AS FLOAT) / COUNT(*) AS Selectivity

FROM

dbo.ColumnOrder

UNION ALL

SELECT

'SecondMost' AS [Column]

,COUNT(DISTINCT SecondMost) AS UniqueValues

,COUNT(*) AS TotalRows

,CAST(COUNT(DISTINCT SecondMost) AS FLOAT) / COUNT(*) AS Selectivity

FROM

dbo.ColumnOrder

UNION ALL

SELECT

'ThirdMost' AS [Column]

,COUNT(DISTINCT ThirdMost) AS UniqueValues

,COUNT(*) AS TotalRows

,CAST(COUNT(DISTINCT ThirdMost) AS FLOAT) / COUNT(*) AS Selectivity

FROM

dbo.ColumnOrder

UNION ALL

SELECT

'FourthMost' AS [Column]

,COUNT(DISTINCT FourthMost) AS UniqueValues

,COUNT(*) AS TotalRows

,CAST(COUNT(DISTINCT FourthMost) AS FLOAT) / COUNT(*) AS Selectivity

FROM

dbo.ColumnOrder;

2nd September 202415th October 2024

Check the SQL Server version

There are always some new features in the database server, which has SQL version checking requirements. For example: GRANT VIEW DATABASE PERFORMANCE STATE TO [user] can run, if your server at least SQL Server 2022.

In this case if you don’t want to see error in an older version , you have to check it:

IF (CAST(LEFT( CAST(SERVERPROPERTY('ProductVersion') as varchar(50)), 2) AS INT) >= 16)

BEGIN
    -- Execute the command if the version is at least SQL Server 2022
    SELECT @@SERVERNAME AS ServeName,@@VERSION AS Version, 'GREAT! SQL Server version is at least 2022' as Message;
END
ELSE
BEGIN
    -- Optional: Handle the case where the version is less than SQL Server 2022
	SELECT @@SERVERNAME AS ServeName,@@VERSION AS Version, 'SQL Server version is less than 2022. There is no : VIEW DATABASE PERFORMANCE STATE.'  as Message;
END

IF (CAST(LEFT( CAST(SERVERPROPERTY('ProductVersion') as varchar(50)), 2) AS INT) >= 16)

BEGIN

-- Execute the command if the version is at least SQL Server 2022

SELECT @@SERVERNAME AS ServeName,@@VERSION AS Version, 'GREAT! SQL Server version is at least 2022' as Message;

END

ELSE

BEGIN

-- Optional: Handle the case where the version is less than SQL Server 2022

SELECT @@SERVERNAME AS ServeName,@@VERSION AS Version, 'SQL Server version is less than 2022. There is no : VIEW DATABASE PERFORMANCE STATE.' as Message;

END

7th July 202410th October 2025

DateTime calculator to UTC to Local Server time to a specific TimeZone

In SQL server you can get the local datetime UTC time . Convert it to different TimeZone:

DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentUTC DATETIME = GETUTCDATE();
DECLARE @TimeZoneLocal VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZoneLocal OUT

SELECT 
@TimeZoneLocal AS ServerTimeZone,
GETDATE() AS ServerCurrentDT,
GETUTCDATE()  AS 'GetUTCDATE',
GETDATE() AT TIME ZONE 'UTC' AS 'UTC',
GETUTCDATE() AT TIME ZONE 'UTC'  AT TIME ZONE 'Eastern Standard Time' AS 'to EST',
GETUTCDATE() AT TIME ZONE 'UTC'  AT TIME ZONE 'Pacific Standard Time' AS 'to PST',
GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS 'to CET',
GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE'India Standard Time' AS 'to IST';

DECLARE @CurrentDateTime DATETIME = GETDATE();

DECLARE @CurrentUTC DATETIME = GETUTCDATE();

DECLARE @TimeZoneLocal VARCHAR(50)

EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZoneLocal OUT

SELECT

@TimeZoneLocal AS ServerTimeZone,

GETDATE() AS ServerCurrentDT,

GETUTCDATE() AS 'GetUTCDATE',

GETDATE() AT TIME ZONE 'UTC' AS 'UTC',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS 'to EST',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS 'to PST',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS 'to CET',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE'India Standard Time' AS 'to IST';

Convert a local datetime or the server local time to different timezone:

DECLARE @OneDatetime DATETIME = '2024-07-05 16:21:00.000'; -- Replace with your datetime value in Local server datetime
Set @OneDatetime = GETDATE() -- you can comment out if you have spec datetime for local server  in previous line
DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT


SELECT 
@OneDatetime  AS OneDatetime ,
@OneDatetime AT TIME ZONE @TimeZone  AS 'Local_OneDatetime' ,
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Pacific Standard Time')AS 'to PST',
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Eastern Standard Time') AS 'to EST',
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'UTC' ) AS 'to UTC',
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Central European Standard Time') AS 'to CET',
Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'India Standard Time') AS 'to IST',
@TimeZone AS ServerTimeZone,
GETDATE()  AS Server_Currrent_GetDate,
@OneDatetime AT TIME ZONE @TimeZone AS  'Server_current_offset'

DECLARE @OneDatetime DATETIME = '2024-07-05 16:21:00.000'; -- Replace with your datetime value in Local server datetime

Set @OneDatetime = GETDATE() -- you can comment out if you have spec datetime for local server in previous line

DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT

SELECT

@OneDatetime AS OneDatetime ,

@OneDatetime AT TIME ZONE @TimeZone AS 'Local_OneDatetime' ,

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Pacific Standard Time')AS 'to PST',

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Eastern Standard Time') AS 'to EST',

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'UTC' ) AS 'to UTC',

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'Central European Standard Time') AS 'to CET',

Convert(DATETIME, @OneDatetime AT TIME ZONE @TimeZone AT TIME ZONE 'India Standard Time') AS 'to IST',

@TimeZone AS ServerTimeZone,

GETDATE() AS Server_Currrent_GetDate,

@OneDatetime AT TIME ZONE @TimeZone AS 'Server_current_offset'

Convert UTC datetime or the server UTC time to different timezone:

DECLARE @UTCOneDatetime DATETIME = '2024-07-05 20:21:00'; -- Replace with your datetime value in Local server datetime
Set @UTCOneDatetime = GETUTCDATE() -- yo can comment out if you have UTC datetime
DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT


SELECT 
@UTCOneDatetime  AS UTCOneDatetime ,
--@UTCOneDatetime AT TIME ZONE @TimeZone  AS 'UTC_OneDatetime' ,
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time')AS 'to PST',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') AS 'to EST',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'UTC' ) AS 'to UTC',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time') AS 'to CET',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time') AS 'to IST',
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'UTC') AS UTC,
Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone) AS '--> To_LocalServerTime <--',
@TimeZone AS ServerTimeZone,
GETDATE()  AS 'Server_Current_GetDate',
GETUTCDATE()  AS 'Server_Current_GetUTCDate',
GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS  'Server_Current_offset'

DECLARE @UTCOneDatetime DATETIME = '2024-07-05 20:21:00'; -- Replace with your datetime value in Local server datetime

Set @UTCOneDatetime = GETUTCDATE() -- yo can comment out if you have UTC datetime

DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT

SELECT

@UTCOneDatetime AS UTCOneDatetime ,

--@UTCOneDatetime AT TIME ZONE @TimeZone AS 'UTC_OneDatetime' ,

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time')AS 'to PST',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') AS 'to EST',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'UTC' ) AS 'to UTC',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time') AS 'to CET',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time') AS 'to IST',

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE 'UTC') AS UTC,

Convert(DATETIME, @UTCOneDatetime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone) AS '--> To_LocalServerTime <--',

@TimeZone AS ServerTimeZone,

GETDATE() AS 'Server_Current_GetDate',

GETUTCDATE() AS 'Server_Current_GetUTCDate',

GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS 'Server_Current_offset'

Continue reading “DateTime calculator to UTC to Local Server time to a specific TimeZone”

15th February 202416th February 2024

Install Oracle in docker

Install Oracle express

docker command in windows powershell (WSL installed):

docker login container-registry.oracle.com
docker container create -it   --name oracletest -p 1521:1521 -e ORACLE_PWD=alma123  container-registry.oracle.com/database/express:latest
docker start oracletest

# chek it later
docker exec oracletest env

docker login container-registry.oracle.com

docker container create -it --name oracletest -p 1521:1521 -e ORACLE_PWD=alma123 container-registry.oracle.com/database/express:latest

docker start oracletest

# chek it later

docker exec oracletest env

download oracle sqldeveloper Windows 64-bit with JDK 11 included:

https://www.oracle.com/database/sqldeveloper/technologies/download/

14th February 2024

Add read rights to all existing and new user databases

The following script add read user rights for every user databases and system model databases for the new databases to readeruser login.

USE [master]
GO
CREATE LOGIN [readeruser] WITH PASSWORD=N'supersecretpw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO


DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','distribution')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [readeruser] FOR LOGIN [readeruser]; EXEC sp_addrolemember N''db_datareader'', [readeruser];'


EXEC sp_executesql @statement


FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

USE [master]

CREATE LOGIN [readeruser] WITH PASSWORD=N'supersecretpw', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

DECLARE @dbname VARCHAR(50)

DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR

LOCAL FAST_FORWARD

FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('master','msdb','tempdb','distribution')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [readeruser] FOR LOGIN [readeruser]; EXEC sp_addrolemember N''db_datareader'', [readeruser];'

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname

END

CLOSE db_cursor

DEALLOCATE db_cursor

13th February 2024

Resolve SID to domain account

There are reasons you need to resolve SID. You can do it by powershell:

$SID = New-Object System.Security.Principal.SecurityIdentifier("S-1-5-21-9534051626-2041855937-2114229915-98301")
$objUser = $SID.Translate([System.Security.Principal.NTAccount])
$objUser.Value

$SID = New-Object System.Security.Principal.SecurityIdentifier("S-1-5-21-9534051626-2041855937-2114229915-98301")

$objUser = $SID.Translate([System.Security.Principal.NTAccount])

$objUser.Value