T-SQL: Find Out Who Has Locks In Your Databases

Here are a couple of ways to see who is locking up your SQL Server database.

First, the undocumented system stored procedure sp_who2 gives you a few more columns than the documented sp_who.

And second, this custom query gives a more tailored view of the user and the locks. It also gives you the option of tailoring the sort to your needs.

SELECT DISTINCT
  name AS database_name,
  session_id,
  host_name,
  login_time,
  login_name,
  reads,
  writes
FROM sys.dm_exec_sessions
LEFT OUTER JOIN sys.dm_tran_locks 
  ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
JOIN sys.databases 
  ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE 
  resource_type <> 'DATABASE'
  AND name ='MyDatabase'
ORDER BY 
  name

Hope this helps!

Posted in T-SQL | Leave a comment

Azure PowerShell: Copy All Files in Blob Storage to Local Directory

Here’s a Azure PowerShell script that downloads all blobs in a storage container to your local disk:

 
$storageAccountName = "mysuperstorage"
$containerName = 'my-blobs'
$destinationFolder = 'C:\temp\my-blobs'
 
New-Item -ItemType Directory -Force -Path $destinationFolder
 
Login-AzureRmAccount
 
$storageAccountContext = (Get-AzureRmStorageAccount | Where-Object{$_.StorageAccountName -eq $storageAccountName}).Context
 
$blobs = Get-AzureStorageBlob -Container $containerName -Context $storageAccountContext
foreach ($blob in $blobs)
{  
  Get-AzureStorageBlobContent -Container $containerName -Blob $blob.Name -Destination $destinationFolder -Context $storageAccountContext
}

Hope this helps!

Posted in Azure, PowerShell | Leave a comment

PowerShell: Read Secure String and Convert Back to Plain Text

Here’s a quick way to read in a secure string and then convert it back to plain text:

$SecureString = Read-Host -Prompt "What is the secret?" -AsSecureString
$ClearTextString = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString))

Also, take a look at the ConvertTo-SecureString and ConvertFrom-SecureString functions of PowerShell.

Hope this helps!

Posted in PowerShell | Leave a comment

PowerShell: Generate a Pseudo-Random String

Here’s a quick way to generate a pseudo-random string with PowerShell:

$Length = 20
$AllowedCharacters = "abcdefghijklmnopqrstuvwxyz0123456789".ToCharArray()
$MyRandomString = -join (Get-Random -Count $Length -InputObject $AllowedCharacters)

Hope this helps!

Posted in PowerShell | Leave a comment

PowerShell: Parse all Text Files in a Directory for a String Pattern

Here’s a snippet of PowerShell that goes through all files in a directory looking for a string. Those files that match the string pattern are then moved to another directory.

Set-Location C:\somewhere
Get-ChildItem -Recurse | Select-String -pattern "Delivery Status Notification" | Group Path | Select -ExpandProperty Name | Move-Item -Destination C:\temp\emails_moved\

Hope this helps!

Posted in PowerShell | Leave a comment

T-SQL: View and Set SQL Server Maximum Memory Usage

Here’s how you can use T-SQL to set the maximum memory to use. In this example, if the server has more than 4GB of RAM, it reduces SQL Server’s usage by 1GB; if it has less than 4GB, it reduces by 512MB.

DECLARE @ServerProperties TABLE (ID INT, Name sysname, Internal_Value INT, VALUE nvarchar(512))
 
DECLARE @PhsycialServerMemory INT
DECLARE @MaximumMemoryForSql INT
 
INSERT @ServerProperties EXEC xp_msver
 
SELECT @PhsycialServerMemory = Internal_Value FROM @ServerProperties WHERE Name = 'PhysicalMemory'
 
SELECT 'Current Memory Settings'
EXEC sp_configure 'max server memory'
 
IF @PhsycialServerMemory > 4096
  SET @MaximumMemoryForSql = @PhsycialServerMemory - 1024
ELSE 
  SET @MaximumMemoryForSql = @PhsycialServerMemory - 512 
 
EXEC sp_configure 'Show Advanced Options', 1
Reconfigure
EXEC sp_configure 'max server memory', @MaximumMemoryForSql
Reconfigure
 
SELECT 'New Memory Settings'
EXEC sp_configure 'max server memory'

Hope this helps!

Posted in T-SQL | Leave a comment