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

Disk Partition Alignment Best Practices for SQL Server

Bytes per Cluster for Data/Log/TempDB disks: 65536    (64K)

Bytes per Cluster for OS, Application, Pagefile disks: 4096   (default 4K)

Check using:

fsutil fsinfo ntfsinfo d:

Default offset since Windows Server 2008 has been aligned at 1024k, may need to align to 1024k for disks formatted prior to Server 2008.

Check using:

wmic partition get BlockSize, StartingOffset, Name, Index

or

diskpart

select disk 1

list part

http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

Clear Readonly Disk Attrib

Just got a spanky new VM for my squirrel server and the sysadmin took off for the holiday and left the drives offline.

Disk Management for whatever reason was no help, even running as Administrator.  The disks were flagged as readonly by policy and would not online or initialize via the GUI tool.

So break out the cmd window and get to DISKPARTying

DISKPART
select disk #
online disk
detail disk
attribute disk clear readonly
detail disk

Now can close and reopen Disk Management (diskmgmt.msc) and it can now initialize the disks :)



http://support.microsoft.com/kb/971436

SYSUTCDATETIMEOFFSET()

Why isn't there a

SYSUTCDATETIMEOFFSET() 

function for

TODATETIMEOFFSET(SYSUTCDATETIME(),'+00:00')