Even the faintest ink beats the strongest memory.

  • Powershell Function to Get SQL Server’s Errorlog Path

    This is a repost of Ed Wilson the ScriptingGuy’s awesome function: [sourcecode language=”powershell” wraplines=”false”] #Get the SQL Error Log Directory on a Server Function Get-ErrorLogPath { <# .Synopsis Returns the path to the SQL Error Log .Description This function returns the path to the SQL Error Log .Example Get-ErrorLogPath Returns the path to the SQL…

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

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

  • A Quick Tutorial for Auditing DML Actions in a Database

    I was recently asked to “capture” which account made a possible delete/truncate operation regularly against a table in a specific database. And I landed on this article through google and found it a very useful tutorial for setting up an audit trail to watch DML actions. The most important takeaway I got is that when…

  • Two Ways to Control Services on a Remote Computer in Powershell

    I am just making a note here about the two alternatives that I found via Google. The first one is on Hey! Scripting Guy’s blog post and the second one is here. To wrap up, I am summing up the below two functions: [sourcecode language=”powershell”] #Function to start a service on remote computer Function Start-Service2…

  • SQL Server Service Stuck in ‘Starting’ Status

    Bumped into this problem today with MSSQLSERVER service stuck at “starting” status. Here is the original article with illustrative screen shots, I am going a bit “roughly speaking” below: Open a command window by pressing windows key + R Type “sc queryex MSSQLSERVER” to get basic info about this service Identify the PID of this…

  • T-SQL Stored Procedure to Get the Number of DB Connections and Logins per Database

    [sourcecode language=”text” wraplines=”false”] /* Script to get the number of connections and their login names on an instance Last modified by Nick Xu on 2013-08-17 Usage: exec sp__GetDBConnections ‘master’ */ –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 Get the Time and Backup File Location of the Last Data Restore

    [sourcecode language=”sql” wraplines=”false”] /* .Synoposis Stored procedure to list the last data restore time AND backup file location for specific online user DB’s. .Last modified 2014-03-24 by Nick Xu .SQL Version 2005+ –Usage EXEC sp__GetlastDataRestoreTime @dbname = ‘DBADB’ GO EXEC sp__GetlastDataRestoreTime GO */ USE master GO /* –For generic setting IF EXISTS (SELECT * FROM…