Category: SQL Server

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

  • T-SQL Function to Return Current Timestamp as String

    Sometimes, a DBA will have to make adhoc backups and it is ideal that these backup files are named with proper timestamp info. The below function returns current date and time as a string fomartted in ‘yyymmdd_hhmmss’ style: [sourcecode language=”sql”] /* Script to create a function to get current timestamp in the format of YYYYmmdd_hhmmss;…

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

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

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

  • Mirroring Error: The Remote Copy of Database Does Not Exist

    Usually setting up a mirroring pair is a breeze but today I found out some possible overlook that my fellow DBA’s could also encounter on their jobs. Here are the common steps that I usually take once restoration on the mirrored side is properly done till the most recent LSN. Step 1: on mirrored partner,…

  • Workaround to Register an Instance Out of Your Domain in Central Management Server

    It sucks that Central Management Server (CMS) doesn’t support registering an instance by SQL Server authentication (and unfortunately in very DBA’s inventory, there are always a few servers that are out your normal domain but you still have to manage). Here is a workaround posted by David Samson on Brent Ozar’s blog post about how…

  • The alert for ‘time behind’ has been raised. The current value of ‘xyz’ surpasses the threshold ‘abc’.

    This is the strange error message that I encountered from a mirroring principal server on which an alert is set to report to DBA team if error number “32040” is detected.  The official Microsoft description of this error message can be found here at Microsoft’s website. And usually this error is reported only when unsent transactions…