Category: 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 note here for future reference. Some other useful T-SQL commands that involve the control of…

  • 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 backup folder location. [sourcecode language=”sql”] /* Script to create a function to get default backup…

  • 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 office. But somehow, some employees skip cardings or tailgate behind others. Therefore, the logging system…

  • 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.…

  • 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…

  • 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. [sourcecode language=”text” wraplines=”false”] WITH VAS_Summary AS ( SELECT Size = VAS_Dump.Size, Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0) WHEN 0 THEN 0 ELSE 1 END),…