September 2021 – Run SQL Server BLOG

20th September 2021

Get the subfolder sizes in a folder with PowerShell

List the subfolder sizes in a folder

Get-ChildItem  -force '\\servername\sql_backup\BACKUP_OLA' -ErrorAction SilentlyContinue | Where-Object { $_ -is [io.directoryinfo] } | % {
    $len = 0
    Get-ChildItem -recurse -force $_.fullname -ErrorAction SilentlyContinue | % { $len += $_.length }
    $_.fullname, '{0:N2} GB' -f ($len / 1Gb)
    }

Get-ChildItem -force '\\servername\sql_backup\BACKUP_OLA' -ErrorAction SilentlyContinue | Where-Object { $_ -is [io.directoryinfo] } | % {

$len = 0

Get-ChildItem -recurse -force $_.fullname -ErrorAction SilentlyContinue | % { $len += $_.length }

$_.fullname, '{0:N2} GB' -f ($len / 1Gb)

}

Result sample:

    /#  result

    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AI 15.61 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e002b 718.37 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BI 22.81 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e001b 94.21 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102184_STSS50E 97.33 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AP 17.52 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BP 36.99 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e003c 355.39 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102318e004b 3312.04 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102249_STS05AP 1.85 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102250_STS05BP 2.39 GB
    \\servername\sql_backup\BACKUP_OLA\AGSQL102318E005 0.90 GB
    \\servername\sql_backup\BACKUP_OLA\SQL102185_STSS50P 0.07 GB


    #/

/# result

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AI 15.61 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e002b 718.37 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BI 22.81 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e001b 94.21 GB

\\servername\sql_backup\BACKUP_OLA\SQL102184_STSS50E 97.33 GB

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS01AP 17.52 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS01BP 36.99 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e003c 355.39 GB

\\servername\sql_backup\BACKUP_OLA\SQL102318e004b 3312.04 GB

\\servername\sql_backup\BACKUP_OLA\SQL102249_STS05AP 1.85 GB

\\servername\sql_backup\BACKUP_OLA\SQL102250_STS05BP 2.39 GB

\\servername\sql_backup\BACKUP_OLA\AGSQL102318E005 0.90 GB

\\servername\sql_backup\BACKUP_OLA\SQL102185_STSS50P 0.07 GB

20th September 2021

Find old files in subfoders – older than 30 days with PowerShell

Find old files recursively with powershell script:

Get-ChildItem -path \\servername\fileshare\folder -Recurse -Force -File | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)}

1	Get-ChildItem -path \\servername\fileshare\folder -Recurse -Force -File \| Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)}

Find the oldest file:

Get-ChildItem -path \\servername\share\folder -Recurse -Force -File  | Sort-Object CreationTime | Select-Object -First 1

1	Get-ChildItem -path \\servername\share\folder -Recurse -Force -File \| Sort-Object CreationTime \| Select-Object -First 1

7th September 20217th September 2021

DISTRIBUTED TRANSACTION: DTC in SQL server

There is two SQL servers and DTC is enabled both of them.

One SQL server is local and another one is a linked server named test.

Run a Distributed Transaction in SQL server for test purpose:

USE AdventureWorks2014;  

Select * from  [AdventureWorks2014].[Production].[WorkOrder] 
where WorkOrderID <100



USE AdventureWorks2014;  
GO  
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION;  
-- Delete candidate from local instance.  
DELETE [AdventureWorks2014].[Production].[WorkOrder]
     WHERE WorkOrderID = 2;  
-- Delete candidate from remote instance.  
DELETE test.[AdventureWorks2014].[Production].[WorkOrder]
      WHERE WorkOrderID = 2;   
COMMIT TRANSACTION;  
GO

USE AdventureWorks2014;

Select * from [AdventureWorks2014].[Production].[WorkOrder]

where WorkOrderID <100

USE AdventureWorks2014;

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION;

-- Delete candidate from local instance.

DELETE [AdventureWorks2014].[Production].[WorkOrder]

WHERE WorkOrderID = 2;

-- Delete candidate from remote instance.

DELETE test.[AdventureWorks2014].[Production].[WorkOrder]

WHERE WorkOrderID = 2;

COMMIT TRANSACTION;

after running the T-SQL transaction you can check in the DTC report.

you can check the DTC connection and firewall settings:

DTC ping tool

In powershell:

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0058" -ResourceManagerPort 17100 -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0059"  -ResourceManagerPort 17100 -Verbose
Test-Dtc -RemoteComputerName "SERVER0059"  -ResourceManagerPort 17100 -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "APPSERVER418"

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0058" -ResourceManagerPort 17100 -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0059" -ResourceManagerPort 17100 -Verbose

Test-Dtc -RemoteComputerName "SERVER0059" -ResourceManagerPort 17100 -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "APPSERVER418"

If the remote server is another domain, you will get an error:
New-CimSession : WinRM cannot process the request. The following error with errorcode 0x80090322 occurred while using Kerberos authentication: An unknown security error occurred.

In this case you can set: No Authentication Required.

7th September 20217th September 2021

DTC and Java

How to enable DTC in SQL server for Java:

– copy sqljdbc_xa.dll to sql server binn folder from the EXTRACTED (but not installed) jdbc driver
– enable xa transactions in component services > computerts > Distributed Transaction Coordinator > Local DTC (properties, security tab) Enable XA transactions
– restart sql server (all components to be sure)
– run script on master db. (xa_install.sql it is also in the jdbc driver, next to the .sql)
– the user that will perform distributed transactions must have the role “SqlJDBCXAUser” set on the MASTER db!