Category Archives: T-SQL Script

Best Way to Set up a Server Side Trace

Accidentally bumped into this tutorial about setting up server side trace. By using SQL profile and scripting our the trace definition, you’d save a lot of work by defining various events that you’d like to trace. I am taking a … Continue reading

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

T-SQL Function to Get the Default Backup Folder

Again, DBA’s are often faced with adhoc backup tasks that shall dump relevant data or log to the existing backup location for all other data/log dumps. Quick way to do so will invovling concatenating dynamic sql commands with the default … Continue reading

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

An Example of Using LEAD and LAG Functions from SQL Server 2012

I recently encountered a technical interview question and it was something like this: A company has its door access logging system and the company policy dictates that every employee’s card-in/card-out records shall reflect his/her true presence in or absence from … Continue reading

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

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

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

T-SQL Script to Check MemToLeave’s Size

This below script is sourced from here. It checks not only the current size of MemToLeave area and also the size of the largest available memory block out of buffer pool. And definition of MemToLeave: MemToLeave is virtual address space … Continue reading

Posted in T-SQL Script | Leave a comment