Find Members of an NT Group

-- Find Members of DOMAIN belonging to a user group 'UserGroup'

exec master..xp_logininfo 'DOMAIN\UserGroup','members'
exec master..xp_logininfo 'DOMAIN\Administrators','members'

user connections ,Logical Connections, Threads, Logins , Logouts

select *
from sys.dm_os_performance_counters a
where a.object_name = 'SQLServer:General Statistics'
and counter_name in ('user connections ','Logical Connections','Logins/sec','Logouts/sec')

--Number of threads used with MSSQL 2005

select count(*) as 'Number of threads'
from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL';

Statements having High CPU usage on execution count

-- High CPU queries -- on execution count SELECT TOP 50 total_worker_time/execution_count AS avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats OUTER APPLY ( select dbid,objectid from ( select attribute,cast(value as int) as attvalue from sys.dm_exec_plan_attributes(plan_handle) where attribute in ('dbid','objectid') ) as depa PIVOT ( MAX(depa.attvalue) FOR depa.attribute IN ("dbid", "objectid") ) AS depapvt ) as depamain ORDER BY execution_count DESC;

Statements having High CPU Usage

select top 50 total_worker_time/execution_count as avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname,
(selecct substriung(text, statement_start_offset/2 + 1,
(case when statement_end_offset = -1 then len(covert(nvarchar(max), text)) * 2 else statement_end_offset nd - statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
outer apply
( select dbid,objectid from
( select attribute,cast(value as int) as attvalue
from sys.dm_exec_plan_attributes(plan_handle)
where attribute in ('dbid','objectid')
) as depa
pivot ( max(depa.attvalue) for depa.attribute
in ("dbid", "objectid") ) as depapvt ) as depamain
where execution_count > 4
order by [avg_cpu_cost] desc;

Drop column after checking regular constraints

-- use database
go
-- SQL 2005 and above
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_lib_drop_column]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_lib_drop_column]
GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
 /* exec sp_lib_drop_column 'constratintcheckhead','accountid','l' exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
-- testing
create table constratintcheckhead ( accountid int not null primary key ,clientid int not null ) create table constraintcheckdetails ( accountid int not null ,notesid int not null primary key (accountid,notesid) )
alter table constratintcheckhead add constraint DF_accountid default 0 for accountid
alter table constratintcheckhead add constraint CK_accountid check (accountid >= 0 )
alter table constratintcheckhead add constraint UQ_account Unique (accountid,clientid ) alter table constraintcheckdetails add constraint FK_constraintcheckdetails_constratintcheckhead foreign key (accountid) references constratintcheckhead (accountid)
create index IX_constratintcheckhead_clientid on constratintcheckhead(clientid) with fillfactor = 90
create index IX_constratintcheckhead_clientid_accountid on constratintcheckhead(clientid,accountid) with fillfactor = 90
drop table constratintcheckhead
drop table constraintcheckdetails
Description :
Lists/Drops Constartints and indexes for a column and drops the column Following constraints are checked only Default,Check, Foreign Key, Primary Key,Unique Key Indexes : Clustered or Non Clustered This will not check column is replicated or part of full text etc Only SQL 2005 and above WARNING: Once constaints are dropped , column will be dropped Assumes there are not more than 10 columns in an index

Usage : exec sp_lib_drop_column 'constratintcheckhead','accountid','l'
exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
Input Parameters : @tablename , @columnname ,@flag char(1) @flag -- 'l' -- list constraints only , 'd' -- drop column , 'b' -- list constraints and drop column
Output Parameters : None
Return Value : None
Record Set : None
 */

create procedure dbo.sp_lib_drop_column @tablename varchar(150), @columnname varchar(80) ,@flag char(1) = 'l'
as
set nocount on

if @flag not in ('l','d','b')
return
declare @slno int ,@maxslno int,@vchsql varchar(4000),@ci_name varchar(150),@ci_type char(1)
create table #temp_constraints( tablename varchar(150),type_desc varchar(150),ci_name varchar(150),columnname varchar(80),ci_type char(1), parentablename varchar(150),parentcolumname varchar(80),slno int identity(1,1) primary key)
declare @numbers table ( n int primary key )
insert into @numbers values (1)
insert into @numbers values (2)
insert into @numbers values (3)
insert into @numbers values (4) insert
into @numbers values (5)
insert into @numbers values (6)
insert into @numbers values (7)
insert into @numbers values (8)
insert into @numbers values (9)
insert into @numbers values (10)

 insert into #temp_constraints (tablename,type_desc,ci_name,columnname,ci_type,parentablename,parentcolumname )select object_name(parent_object_id) as tablename,type_desc,name as ci_name,
col_name(parent_object_id,parent_column_id) as columnname,'N' as ci_type ,'' as parentablename,'' as parentcolumname
from sys.default_constraints
where object_name(parent_object_id) = @tablename
and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name(parent_object_id) as tablename,type_desc,name as ci_name,col_name(parent_object_id,parent_column_id) as columnname, 'N' as ci_type ,'' as parentablename,'' as parentcolumname
from sys.check_constraints
where object_name(parent_object_id) = @tablename and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name( parent_object_id) as tablename, 'FOREIGN KEY' as type_desc, object_name(constraint_object_id) as ci_name,col_name(parent_object_id,parent_column_id) as columnname,'N' as ci_type,object_name( referenced_object_id) as parentablename, col_name(referenced_object_id,referenced_column_id) as parentcolumname
from sys.foreign_key_columns
where object_name( referenced_object_id) = @tablename
and col_name(referenced_object_id,referenced_column_id) = @columnname
union all
select object_name(i.object_id) as tablename, case when o.type = 'PK' then 'PRIMARY KEY' when o.type = 'UQ' then 'UNIQUE'else 'INDEX ' end as type_desc, i.name as ci_name,t.index_column_name as columnname, case when o.type in ('PK','UQ') then 'N' else 'I' end as ci_type,'' as parentablename, '' as parentcolumname
from sys.indexes i
left outer join sys.objects o
on o.parent_object_id = i.object_id
and i.name = o.name
cross apply
( select index_col(object_name(i.object_id),index_id,n) as index_column_name from @numbers where index_col(object_name(i.object_id),index_id,n) = @columnname ) t where object_name(i.object_id) = @tablename
----- if @flag in ('l','b')
select * from #temp_constraints order by slno
 if @flag = 'l'
return
set @slno = 0
select @maxslno = max(slno) from #temp_constraints
while @slno < @maxslno
begin 
    set @slno = @slno + 1
    select @tablename = tablename, @ci_name = ci_name, @ci_type = ci_type
    from   #temp_constraints where slno = @slno
   if @ci_type = 'N' set @vchsql = 'alter table ' + @tablename + ' drop constraint '   + @ci_name else if @ci_type = 'I' set @vchsql = 'drop index ' + @tablename + '.' + @ci_name
-- print @vchsql
 exec (@vchsql)
end
drop table #temp_constraints
return

Generate Sequence numbers on the fly

/* Generate sequence nos on the fly without using any tables
sql 2005 you can select from cte into #temp table and
use this result set
OR
you can use this with other tables in cross join or join .
100 is kept as the limit , since max recursion cannot exceed this value .
you can expand on this idea to generate higher value limit .
*/ -- upto 100 numbers

with cte (nos) as
( select 1 as nos union all select cte.nos + 1
from cte where nos < 100 )
select nos from cte order by nos -- upto 200 numbers
with cte (nos) as
( select 1 as nos union all select cte.nos + 1 from cte where nos < 100 )
select nos
from cte
union all
select nos + 100
from cte order by nos ;

-- upto 199 numbers -- alternative way

with cte (nos)
as
( select 1 as nos union all select cte.nos + 1
from cte where nos < 100
union all
select cte.nos + 100 from cte where nos < 100 )
select nos from cte order by nos ;

/* you can use select nos into #temp from cte to port into a temp table */

SQL Server Backups in brief

SQL Server backups in brief
----- Full Backup ----
Full Backup can be taken in all the recovery modes simple,bulk logged, full. .
Full Backup will not truncate the log.
 Full Back up is essential for all restorations.
 Full Backup is not enough for point-in-restoration.
Log backup is required.
 For database restore, if there are differential/log backups , restore of fullbackup should be done with NORECOVERY.

---------Differential Backup --------
 Differential Backup can be taken in all the recovery modes simple,bulk logged, full. . Differential Backup will have data after the last full backup and are cumulative.
ie If a full back up is taken at 2 AM.
differnetial back up taken at 3 AM -- This will have changed data between 2 AM to 3AM. differnetial back up taken at 4 AM -- This will have changed between 2 AM to 4 AM. . Differential Backup will not truncate the log.
 For database restore, if there are log backups ,
restore of differential backup should be done with NORECOVERY.

--------Log Backup -----------
Log Backup can be taken if the database recovery modes are bulk logged or full. .
Log backup will truncate the log after backup log command . (See exceptions in BOL) . Log backup will not truncate when no_truncate, copy_only.
When replication is enabled on the database,it will truncate the log after inactive portion is replicated. 
Truncating the log means providing the avialable space for reuse.
This will not reduce the size of log file. .
To reduce the log size DBCC SHRINKFILE should be used
Avoid shrinking log file which may create performance problems and fragmentation. .
Log backups are essential for point-in-time recovery .
In case of database corruption, try to take the current log backup to recover.
If this is possible , this log file will become the last log file for recovery.
 For restore of database , restore full backup, subsequent differential backup and all the logs WITH NORECOVERY except the last log backup .
Last log back up should be restored WITH RECOVERY
-------Other Information --------------

 It may be necessary to issue CHECKPOINT before any of the backups taken. CHECKPOINT is esential for log truncation .
set recovery interval option with sp_configure indirectly sets CHECKPOINT interval .
If log backup sequence of files are used for restoration with last full backup ,
log backup can be taken just before fullbackup and this can be discarded .
This will truncate the logfile. . shrinking file will not invalidate log sequence restoration. . Keep higher log and data file size and avoid autogrow as much as possible.
Check dbcc opentran . Long running transactions will fill the log file.
 Fullbackup with differential backup can be used in simple recovery mode.
But this is not point-in-time recovery.