Category: Uncategorized

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/

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

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’

call PowerShell -NoLogo -NoProfile -NonInteractive -ExecutionPolicy “Bypass” -Command “& ‘pathtopscriptmyscript.ps1′” < NUL

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

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:Windowswinsxsamd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.1.7600.16385_none_c9392808773cd7dacleanmgr.exe

Windows Server 2008 R2 64-bit
C:Windowswinsxsamd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.1.7600.16385_en-us_b9cb6194b257cc63cleanmgr.exe.mui

Windows Server 2008 64-bit
C:Windowswinsxsamd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.0.6001.18000_en-us_b9f50b71510436f2cleanmgr.exe.mui

Windows Server 2008 64-bit
C:Windowswinsxsamd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.0.6001.18000_none_c962d1e515e94269cleanmgr.exe.mui

Windows Server 2008 32-bit
C:Windowswinsxsx86_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.0.6001.18000_en-us_5dd66fed98a6c5bccleanmgr.exe.mui

Windows Server 2008 32-bit
C:Windowswinsxsx86_microsoft-windows-cleanmgr_31bf3856ad364e35_6.0.6001.18000_none_6d4436615d8bd133cleanmgr.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%System32en-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

Scenario: On SQL 2008R2 server, Tempdb has unlimited growth max size and is in danger of filling disk.  

Need a quick way to set max size to current size to prevent further growth until can assess if there is an abnormal process taking up TempDB, what optimal size of TempDB should be, and/or disk space can be added.

SELECT ‘ALTER DATABASE [‘ + db_name(A.database_id) 
     + ‘] MODIFY FILE ( NAME = N”’ + A.name 
     + ”’, MAXSIZE = ‘ 
     + cast((B.size_on_disk_bytes/1024) as varchar) + ‘ KB)’
     , A.*
     , B.*
  FROM master.sys.master_files A
  JOIN sys.dm_io_virtual_file_stats( db_id(‘tempdb’),NULL) B
    ON A.file_id = B.file_id
 WHERE A.database_id = db_id(‘tempdb’)

High ASYNC_NETWORK_IO often caused by MS Access or MS Excel connections

SQL Service Aliases (example instance name INST01)

NT SERVICEMSSQL$INST01
NT SERVICESQLAgent$INST01
NT SERVICEMSOLAP$INST01