Even the faintest ink beats the strongest memory.

  • Error “Alter failed for Server ‘[servername]’ “in maintenance plan’s “check database integrity task”

    This error happened in one of the SQL2005 SP4 instances I am managing. The root cause of this issue is that the integrity check task runs statement ‘EXEC sys.sp_configure N’user options’, 0 RECONFIGURE‘ in the background and this statement conflicts with another “allow update” (with value of 1) setting to system catalogs. To put it another way, if…

  • A very interesting paragraph from a Microsoft KB article about NT AUTHORITY\SYSTEM and BUILTIN\Administrators logins

    The original article is here and the quoted text is as follows: The NT AUTHORITY\SYSTEM account The NT AUTHORITY\SYSTEM account is also granted a SQL Server login. The NT AUTHORITY\SYSTEM account is provisioned in the SYSADMIN fixed server role. Do not delete this account or remove it from the SYSADMIN fixed server role. The NTAUTHORITY\SYSTEM…

  • How to become NT AUTHORITY\SYSTEM on Windows systems

    As a DBA, you will bump into situations to trouble shoot weird things under this account. And this article details the way how to do it beautifully. Just before you follow the steps, you have to install a toolset from Microsoft to run all the commands that are needed in that tutorial. Also don’t forget…

  • T-SQL Script to Backup All Databases in an Instance

    /* This script make full backups of all user databases and three system databases in a specific instance. All backup files will named as “db_name_current_date.bak”. Watch out that you have to set up the correct backup directory in the @path parameter. Author: Ning Xu Date: 22-09-2010 Applied version: 2000, 2005, 2008, 2008 R2 */ DECLARE…

  • Remove Full Duplicates by Using row_number() Function and Common Table Expression

    This blog article is inspired by another very informative one which explains really well the meaning of the new row_number() function introduced since SQL2005. Since the original article did not give a full answer to how to delete multiple fully duplicated rows in a table, I am putting my code here for the test. The good…

  • Script that fixes the “sql server agent service won’t start” problem

    /* This script solves the “sql server angent service won’t start” problem. Usually if you are pretty sure that the service account running sqlserveragent has sufficient rights and this service still refuses to start up, it is most of the time becuase of either the misconfigured “Agent XPs” or inaccessible sql server agent errorlog file.…

  • Shortcuts to Microsoft MMC Snap-In’s

    Certificates certmgr.msc Indexing Service ciadv.msc Computer Management compmgmt.msc Device Manager devmgmt.msc Disk Defragmenter dfrg.msc Disk Management diskmgmt.msc Event Viewer eventvwr.msc Shared Folders fsmgmt.msc Group Policy gpedit.msc Local Users and Groups lusrmgr.msc Removable Storage ntmsmgr.msc Removable Storage OperatorRequests ntmsoprq.msc Performance perfmon.msc Resultant Set of Policy rsop.msc Local Security Settings secpol.msc Services services.msc Windows Management Infrastructure (WMI)…

  • T-SQL Script that Collects Version, Edition, sp, Authentication and Collation Info

    /* This script collects version, edition, sp, authentication and collation info from the whole list of instances in the central management servers Author: Ning Xu Last Modified Date: 2014-01-21 Applied version: 2000, 2005, 2008, 2008 R2, 2012 */ select (case substring(cast(serverproperty(‘productversion’) as varchar), 1, 4)  when ‘8.00’ then ‘2000’  when ‘9.00’ then ‘2005’  when ‘10.0’…