Recovering access

Local admins can get into instance when sysadmin access has been lost by starting the server in single user mode.   Here is how recover a sysadmin account without having to restart.  Prior to 2008 BUILTIN\Administrators was by default a member of sysadmin.   In 2008/2008R2 that login was not added by default, but NT AUTHORITY\SYSTEM still is.

psexec -s -i cmd

sqlcmd

create login [login] from windows
go

exec [sys].[sp_addsrvrolemember] 'login', 'sysadmin';
go

Powershell ISE

Powershell ISE is not installed by default on Windows Server 2008 R2

To install it, launch Powershell and enter the following commands:

Import-Module ServerManager
Add-WindowsFeature PowerShell-ISE

Rebuild Indexes and Update Statistics

Rebuild Indexes, then update statistics.

An index rebuild also updates index statistics with 100% sample rate.

After an index rebuild, only statistics not updated by the index-rebuild need to be updated.

An index re-org/defrag does not update statistics

In SQL Server 2005+ sp_updatestats only updates the statistics that need to be updated.

How to get out of SINGLE_USER mode

USE [master] 
GO

     SELECT 'kill ' + cast(spid as varchar)
       FROM master.sys.sysprocesses
      WHERE dbid = DB_ID('dbname')
GO

     SELECT 'kill ' + cast(request_session_id as varchar)
       FROM   sys.dm_tran_locks
      WHERE resource_database_id = DB_ID('dbname') 
GO

     SELECT 'kill ' + cast(session_id as varchar)
       FROM sys.dm_exec_requests 
      WHERE database_id = DB_ID('dbname')
GO

--KILL any processes connected to dbname



SET DEADLOCK_PRIORITY HIGH
GO
exec sp_dboption '[dbname] ', 'single user', 'FALSE';
GO
ALTER DATABASE [dbname] SET MULTI_USER WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [dbname] SET READ_WRITE WITH NO_WAIT
GO