Monthly Archives: March 2014

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 … Continue reading

Posted in Common Table Expression, Recursion, T-SQL Script, User Defined Function | Tagged , , | Leave a comment

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 … Continue reading

Posted in Recovery Model, SQL Server, T-SQL Script | Tagged | Leave a comment

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 … Continue reading

Posted in T-SQL Script | Tagged , | Leave a comment

Powershell Function to Get SQL Server’s Network Protocols

The output of the above function usually looks like this: ProtocolName ProtocolDisplayName ProtocolOrder sm Shared Memory 1 tcp TCP/IP 2 np Named Pipes 3 via VIA 0 Number ‘0’ means corresponding protocol is ‘disabled’, while the NO. ‘1’ has the … Continue reading

Posted in Database Connection, Powershell | Leave a comment

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 … Continue reading

Posted in Powershell | Tagged | Leave a comment

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:

Posted in Powershell | Tagged , | Leave a comment

Powershell Function to Reboot a Computer with Warning Messages

I am fully aware that a simple 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 … Continue reading

Posted in Powershell | Leave a comment

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 … Continue reading

Posted in SQL Server, T-SQL Script | Tagged , | Leave a comment

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

Posted in Database Connection, T-SQL Script | Tagged | Leave a comment

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 … Continue reading

Posted in SQL Server, T-SQL Script, Transaction Log | Tagged , | Leave a comment