Category Archives: 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 … 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

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:

Posted in SQL Server | 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

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

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

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

Posted in SQL Server | Tagged , | Leave a comment

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

Posted in Database Connection, SQL Server | Tagged , , | 2 Comments