Even the faintest ink beats the strongest memory.

  • Two Examples of Recursion with T-SQL

    T-SQL is very good for dealing with set-based data, but it doesn’t shy away from recursion either. You can use either user defined functions (UDF) or common table expression (CTE) to achieve this and I put up hereunder two examples to solve the claissic problem of finding the organization levels of employees and their managers.…

  • T-SQL Stored Procedure to Get DB’s Recovery Model

    As a lazy DBA, I am not a fan of writing ad-hoc queries, therefore I keep wrapping up codes which I think that will come in handy some time later. Here is the piece of T-SQL that checks the recovery model of all or a specific DB on an instance. [sourcecode language=”SQL”] /* .SYNOPSIS Stored…

  • T-SQL Stored Procedure to Kill all Connections to a DB

    I’ve encountered the below error message many times when I was trying to do an adhoc restore operation on a DB server: Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.…

  • Powershell Function to Get SQL Server’s Network Protocols

    [sourcecode language=”powershell”] #Function to get SQL Server network protocols Function Get-SQLProtocols { Param([String]$ComputerName = $env:COMPUTERNAME) [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null $Server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ComputerName $VersionMajor = $Server.VersionMajor Get-WmiObject -ComputerName $ComputerName -NameSpace root\Microsoft\SqlServer\ComputerManagement$VersionMajor -Class ClientNetworkProtocol | Select-Object ProtocolName, ProtocolDisplayName, ProtocolOrder }#End of function [/sourcecode] The output of the above function usually looks like this: ProtocolName ProtocolDisplayName ProtocolOrder sm Shared Memory 1 tcp TCP/IP…

  • Powershell Function to Get Largest Files on a Drive or in a Folder

    Ofentimes when a disk space shortage alert pops up from a specific server, I’d like to know quickly which files are the largest that I might have a way to shrink or move or delete. Here are the two funtions that I use to immediately locate the full list of top N largest files at…

  • Powershell Function to Get Sizes of All Databases on an Instance

    Below is a function that I modified from one in Idera’s SQL Server Powershell Scripts toolset: [sourcecode language=”powershell”] #Function to get sizes of all DB’s hosted on an instance Function Get-SQLDBSizes { param ( [string]$InstaceName = "$(Read-Host ‘Please use format ServerName(IPAddress)\InstanceName’ [e.g. 127.0.0.1\instance])" ) begin { [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") } process { try { Write-Verbose "Connect to…

  • Powershell Function to Reboot a Computer with Warning Messages

    I am fully aware that a simple [sourcecode language=”powershell”]Restart-Computer "ServerName"[/sourcecode] command can easily do the trick, but it means doing this trick a bit too easy and too harzardly. I’d like to see my own function to promt up some warnings and confirmations before I actually do the reboot. I learned this lesson by typing…

  • T-SQL Stored Procedure to Get the Rough Row Count(s) of Specific Table(s)

    If you’d like to get the most accurate of row count of any table, select count(*) is still your BFF. However, in some situations, the speed of retrieving a rough estimate of the row count from a very large table is more emphasized upon. And hence here goes the script: [sourcecode language=”sql”] /* .Synopsis Stored…

  • T-SQL Stored Procedure to Get the Number of Connections and Login Names per DB

    [code language=”sql”] /* Script to get the number of connections and their login names on an instance Last modified by Nick Xu on 2014-03-05 Usage: EXEC sp__GetDBConnections ‘master’ EXEC sp__GetDBConnections */ –For generic setting USE master GO IF EXISTS(SELECT * FROM sysobjects WHERE name = ‘sp__GetDBConnections’ AND type = ‘P’) BEGIN DROP PROC sp__GetDBConnections END…

  • T-SQL Stored Procedure to Shrink the Log File of a Database

    This script is particularly useful when you are dealing with disk space limitations on dev or uat boxes when point-in-time recovery capabilities are not required and the simple recovery model is very much acceptable. I use this stored proc constantly to reduce the t-log file sizes of DB’s that are just restored from their PROD…