much ado about NOLOCK

In most cases it does not cause problems and in some cases may improve performance of the query.

If the query includes tables that are extremely volatile (data changing very rapidly) then there is a chance of a “dirty read” where an older version of the record is read while that record is currently being in process of being updated by another transaction.

Contrary to the name of the query hint (NOLOCK) it does not completely eliminate locking caused by the query, but it does reduce it.

I don’t recommend putting it arbitrarily on every query by default -- something I’m guilty of myself :)  

Instead recommend using it when other optimization strategies are not practical, the risk of dirty reads is acceptable, and it actually makes a difference in the speed the query returns results.

https://www.brentozar.com/archive/2011/11/theres-something-about-nolock-webcast-video/

https://www.brentozar.com/archive/2013/02/7-things-developers-should-know-about-sql-server/

https://www.brentozar.com/archive/2015/03/top-3-mistakes-t-sql-developers-make/

Change DB Owner to SA

SELECT SUSER_SNAME(A.owner_sid) "current_owner"
     , A.name "database_name"
     , 'ALTER AUTHORIZATION ON DATABASE::[' + A.name + '] TO [sa]; '
     + 'USE [' + name + ']; CREATE USER [' + SUSER_SNAME(A.owner_sid) + '] FOR LOGIN [' + SUSER_SNAME(A.owner_sid) + '] WITH DEFAULT_SCHEMA=[dbo]; '
     + 'ALTER ROLE [db_owner] ADD MEMBER [' + SUSER_SNAME(A.owner_sid) + '];' 
--   + 'EXEC sp_addrolemember N''db_owner'', N''' + SUSER_SNAME(A.owner_sid) + ''';' 
       "cmd"
  FROM sys.databases A
 WHERE SUSER_SNAME(A.owner_sid) NOT IN ( 'sa')
   AND A.[state] = 0
 ORDER BY A.name

Enable AD module for PowerShell in Windows Server 2012R2

To enable AD module for PowerShell, fire up Server Manager and go to Features->Remote Server Administration Tools -> Role Administration Tools -> AD DS and AD LDS Tools -> Active Directory module for Windows PowerShell

TSQL to control SQL Server Agent

To Start the sql server agent,
EXEC master.dbo.xp_ServiceControl 'START', 'SQLServerAgent'

To Stop the sql server agent,
EXEC master.dbo.xp_ServiceControl 'STOP', 'SQLServerAgent'

To Check the status of the sql server agent,
EXEC master.dbo.xp_ServiceControl 'QueryState', 'SQLServerAgent'

Calling Powershell script from Control-M

call PowerShell -NoLogo -NoProfile -NonInteractive -ExecutionPolicy "Bypass" -Command "& '\\path\to\pscript\myscript.ps1'" < NUL

Execute Powershell scripts at command line

Powershell’s default setting does not allow for execution of scripts

C:\>powershell                           # enter powershell
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\> Get-ExecutionPolicy              # check what the rights are
Restricted
PS C:\> Set-ExecutionPolicy RemoteSigned # reset the rights
PS C:\> Get-ExecutionPolicy              # make sure the change took

Now can execute scripts at command line

Enable Disk Cleanup on Windows Server 2008 / 2008 R2

In order to use cleanmgr.exe you’ll need to copy two files that are already present on the server, cleanmgr.exe and cleanmgr.exe.mui. Use the following table to locate the files for your operating system.

Windows Server 2008 R2 64-bit
C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.1.7600.16385_none_c9392808773cd7da\cleanmgr.exe

Windows Server 2008 R2 64-bit
C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.1.7600.16385_en-us_b9cb6194b257cc63\cleanmgr.exe.mui

Windows Server 2008 64-bit
C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.0.6001.18000_en-us_b9f50b71510436f2\cleanmgr.exe.mui

Windows Server 2008 64-bit
C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.0.6001.18000_none_c962d1e515e94269\cleanmgr.exe.mui

Windows Server 2008 32-bit
C:\Windows\winsxs\x86_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.0.6001.18000_en-us_5dd66fed98a6c5bc\cleanmgr.exe.mui

Windows Server 2008 32-bit
C:\Windows\winsxs\x86_microsoft-windows-cleanmgr_31bf3856ad364e35_6.0.6001.18000_none_6d4436615d8bd133\cleanmgr.exe

Once you’ve located the files move them to the following locations:
Cleanmgr.exe should go in %systemroot%\System32.
Cleanmgr.exe.mui should go in %systemroot%\System32\en-US.

You can now launch the Disk cleanup tool by running Cleanmgr.exe from the command prompt.
Disk Cleanup can now be run by entering Cleanmgr.exe into a command prompt, or by clicking Start and typing Cleanmgr into the Search bar.

https://technet.microsoft.com/en-us/library/ff630161(WS.10).aspx