Create a SQL server Install folder on a shared drive: Use this when you need to deploy a CD to multiple users. a good example would be developers that need a sql server developer copy for their local desktop.

The SQL Server 2005 installation uses the following two folders:
Servers
Tools

These two folders must be under the same level of a folder or the root folder of a drive. The names of these folders must be exactly Servers and Tools. The Servers folder contains all the files that are required to install major SQL Server 2005 components, such as database engine. The Tools folder contains tools components and Books Online for SQL Server 2005. 

To copy the files from the SQL Server 2005 installation CDs to a location and then install SQL Server 2005 from that location, follow these steps: 

Note In this example, the D:\SQLServer2005 folder is the location to which you want to copy the files from the SQL Server 2005 installation CDs.

In the D:\SQLServer2005 folder, create the following two subfolders:

Servers
Tools 

Copy all the files from the SQL Server 2005 installation CD that is named Servers to the D:\SQLServer2005\Servers folder.
Copy all the files from the SQL Server 2005 installation CD that is named Tools to the D:\SQLServer2005\Tools folder.
Open the D:\SQLServer2005\Servers folder, and then double-click Setup.exe to start the SQL Server 2005 Setup program.

What Users are accessing the server

sp_who / sp_who2

Statistics

exec sp_updatestats                                                                           -- update all tables in a database with new statistics 

update statistics tblcustomers                                                            -- update statistics for the single table tblcustomers.

update statistics tblcustomers  IX_tblcustomers_custid                  -- update statistics for a single index

dbcc showcontig (tblcustomers)                                                         -- show the contigous space on the tblcustomers  table.

dbcc showcontig ('tblcustomers') with tableresults, all_indexes      --show statistics for all indexs in the table

dbcc updateusage ('MyDatabase','tblcustomers')                            --:Reports and corrects pages and row count inaccuracies in the catalog views.           

How to replicate a single article  

Add the article to the publication.
Execute sp_refreshsubscriptions at the publisher.
Start the snapshot agent.
Although a complete snapshot will be created only the new article will be added to the subscription so minimal downtime.
When the snapshot is finished the new article will be added to the subscription.              
 

Kill non replication transactions

Hard remove replication from a database.

sp_dboption TAZ_MRKT,'publication',false

 Un-commited transactions

dbcc opentran(xstore) with tableresults                                               --Shows open Transactions that may be preventing logs from clearing.

ACTIVE DIRECTORY

 Add a link to Active Directory

sp_addlinkedserver 'ADSI','Active Directory Services 2.5','ADSDSOObject','adsdatasource'

go

 Query Active Directory

select useraccountControl, sAMAccountName "ADUser", name "Name", title "Title",
case userAccountControl when 512 then 'Enabled'
                                                                                                                                when 514 then 'Disabled'
                                                                                                                                when 4096 then 'Computer'
                                                                                                                                when 66048 then 'Password Never Expires'
                                                                                                                                when 262656 then 'Smart Card'
                                                                                                                                else sAMAccountName
                                                                                                                                END "Status", 
company "Company", manager "Manager", department "Department", telephonenumber "Telephone", facsimileTelephoneNumber "FAX",
mobile "Mobile", homephone "HomePhone", sn "LastName",
displayname "DisplayName", l "City", st "State", postalcode "Zip", adsPath "Path"
from
openquery(adsi, 'select department, manager, company, userAccountControl, mobile, homephone, profilePath, userPrincipalName, userWorkstations,  adsPath, facsimileTelephoneNumber, givenName, sAMAccountName,
displayName, name, sn, streetaddress, postalcode, l, st, title, telephoneNumber 
from ''LDAP://SERVERNAME'' )

 Mass grant / revoke permissions: don’t want to type all those grants for a single account here's a script go help.  

declare
@name varchar(50),
@xtype varchar(50),
@SQL varchar(250)
 
declare cur_objects cursor fast_forward
for
select name, xtype from sysobjects nolock
where xtype in ('P','U','V','IF','TF','FN')
--where xtype = 'FN'
and ((left(name,3) <> 'sys') and (Left(name,3) <> 'dt_')) -- Avoid SQL server procedures
BEGIN
open cur_objects
fetch next from cur_objects
into @name, @xtype
while @@fetch_status = 0
 BEGIN
 if (@xtype = 'P' or @xtype = 'IF' or @xtype = 'TF' or @xtype = 'FN')
      BEGIN
     set @SQL = 'grant execute on ' + @name + ' to [useraccount]'
      EXEC(@SQL)
      print @SQL
      END
 if @xtype = 'U' or @xtype = 'V'
      BEGIN
    --set @SQL = 'revoke all on ' + @name + ' to "[useraccount]"'
      set @SQL = 'grant select on ' + @name + ' to [useraccount]'
      EXEC(@SQL)
      print @SQL
      END
 fetch next from cur_objects
 into @name, @xtype
 END
close cur_objects
deallocate cur_objects
END

Script all NonClustered Indexes: Use this to create a script of all non-clustered indexes on a database. this is really useful in replication in a scenario where you do not wish to replicate the indexes as articles. You can copy / paste the output into another query window on a different server and execute.
 
declare @TableName as varchar(50), @SavedTableName as varchar(50),
@IndexName as varchar(50), @SavedIndexName as varchar(50),
@ColumnName as varchar(30), @SavedColumnName as varchar(400),
@Seq as integer,
@Count as integer,
--@Columns as varchar(250),
@IndexScript as varchar(800)

declare readAllIndexes cursor read_only for
select o.name "Table", i.name "index",  c.name "column", c1.colid "seq"
 from
            sysindexes i, syscolumns c, sysobjects o, syscolumns c1
      where
            o.id = c.id
            and o.id = i.id
            and o.status >= 0
            and c.name = index_col (o.name,i.indid, c1.colid)
            and c1.colid <= i.keycnt
            and (i.status  & 0x60) <> 0x60
            and c1.id = o.id
                and i.status in (0,2,256,4098,2097152,2113552,16400,32770,2097154,2101250,16777216,16781314)
            order by o.name, I.name, c1.colid, c.name  --crdate, category, table_name, i.name, c1.colid

create table #NewIndexes
(IndexScript varchar(400))

set @count = 0
open ReadallIndexes
fetch next from ReadAllIndexes
    into @TableName, @IndexName, @ColumnName, @Seq
set @SavedIndexName = @IndexName
while @@fetch_status = 0
    BEGIN
    if @SavedIndexName = @IndexName
        BEGIN
        set @count = @count + 1
        select @SavedTableName = @TableName, @SavedIndexName = @IndexName -- , @SavedColumnName = @ColumnName
        if @Count = 1
            BEGIN
            set @SavedColumnName = ' [' + @ColumnName + '] ASC'
            END
        ELSE
            BEGIN
            set @SavedColumnName = @SavedColumnName +  ', [' + @ColumnName + '] ASC'
            END
        END
    ELSE
        BEGIN
        set @IndexScript = 'IF EXISTS (SELECT * from sys.indexes where object_id = OBJECT_ID(N''[dbo].[' + @SavedTableName + ']'') AND name = N''' + @SavedIndexName + ''')'
        print @IndexScript
        set @IndexScript = 'Drop INDEX [' + @SavedIndexName + '] ON [dbo].[' + @SavedTableName + '];'
        print @IndexScript
        set @IndexScript = 'CREATE NONCLUSTERED INDEX [' + @SavedIndexName + '] ON [dbo].[' + @SavedTableName + '](' +
            @SavedColumnName + ')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [SECONDARY];'       
--         insert into @NewIndexes
--             (IndexScript)
--         values
--             (@IndexScript
        print @IndexScript
        set @IndexScript = ' '
        print @IndexScript

        select @SavedTableName = @TableName, @SavedIndexName = @IndexName -- , @SavedColumnName = @ColumnName
        set @SavedColumnName = ' [' + @ColumnName + '] ASC'
        --set @count = 0
        END
    fetch next from ReadAllIndexes into @TableName, @IndexName, @ColumnName, @Seq
    END
Close ReadAllIndexes
drop table #NewIndexes
deallocate ReadAllIndexes