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 BUILTINAdministrators was by default a member of sysadmin. In 2008/2008R2 that login was not added by default, but NT AUTHORITYSYSTEM still is.
psexec -s -i cmd
create login [login] from windows
exec [sys].[sp_addsrvrolemember] 'login', 'sysadmin';
Powershell ISE is not installed by default on Windows Server 2008 R2
To install it, launch Powershell and enter the following commands:
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.
SELECT ‘kill ‘ + cast(spid as varchar)
WHERE dbid = DB_ID(‘dbname’)
SELECT ‘kill ‘ + cast(request_session_id as varchar)
WHERE resource_database_id = DB_ID(‘dbname’)
SELECT ‘kill ‘ + cast(session_id as varchar)
WHERE database_id = DB_ID(‘dbname’)
–KILL any processes connected to dbname
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption ‘[dbname] ‘, ‘single user’, ‘FALSE’;
ALTER DATABASE [dbname] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [dbname] SET MULTI_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [dbname] SET READ_WRITE WITH NO_WAIT