sp_kill_user

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_kill_user]    Script Date: 04/24/2013 09:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_kill_user 'dw_user'
*/
ALTER proc [dbo].[sp_kill_user]
(
    @username nvarchar(1000) = 'dw_user'
)
as
begin

    SET NOCOUNT ON

    DECLARE @spid INT,
        @cnt INT,
        @sql VARCHAR(255)

    SELECT @spid = MIN(spid), @cnt = COUNT(*)
        FROM master..sysprocesses
        WHERE loginame = @username
        AND spid != @@SPID

    PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
   
    WHILE @spid IS NOT NULL
    BEGIN
        PRINT 'About to KILL '+RTRIM(@spid) 
        SET @sql = 'KILL '+RTRIM(@spid)
        EXEC(@sql) 
        SELECT @spid = MIN(spid), @cnt = COUNT(*)
            FROM master..sysprocesses
            WHERE loginame = @username
            AND spid != @@SPID 
        PRINT RTRIM(@cnt)+' processes remain.'
    END
END

SQL Server DB file Size

http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/

Truncate Mirrored Database Log File

http://sqlserverimpressions.blogspot.com/2009/02/truncate-mirrored-database-log-file.html

Table Partitioning on a varchar field

exec sp_who3
exec sp_whoisactive

drop partition scheme CoreCollectionPartitionScheme
drop PARTITION FUNCTION [SwimlaneRangePartitionFunction]


CREATE PARTITION FUNCTION [SwimlaneRangePartitionFunction] (nvarchar(100))
AS RANGE RIGHT FOR VALUES ('LA4PRDCLT101', 'LA4PRDCLT201', 'LA4PRDCLT301',
               'LA4PRDCLT401', 'LD4PRDCLT101', 'LD4PRDCLT102', 'LD4PRDCLT201');
              
CREATE PARTITION SCHEME CoreCollectionPartitionScheme
AS PARTITION SwimlaneRangePartitionFunction
TO ([primary],sl1,sl2,sl3,sl4,sl5,sl51,sl6);
GO              


drop table [user_demographics_partition_by_swimlane]
go
CREATE TABLE [dbo].[user_demographics_partition_by_swimlane](
    [user_id] [int] NULL,
    [swimlane] [nvarchar](100) NULL,
    [portal] [nvarchar](200) NULL,
    [status_id] [int] NULL,
    [user_type_id] [int] NULL,
    [gender] [varchar](100) NULL,
    [user_country] [varchar](100) NULL,
    [user_state] [nvarchar](50) NULL,
    [user_zipcode] [nvarchar](50) NULL,
    [birth_dt] [datetime] NULL,
    [hire_dt] [datetime] NULL,
    [term_dt] [datetime] NULL,
    [timezone] [int] NULL,
    [date_stamp] [datetime] NULL,
    [user_country_profile_base] [varchar](100) NULL,
    [user_state_profile_base] [nvarchar](50) NULL,
    [user_zipcode_profile_base] [nvarchar](50) NULL
) ON CoreCollectionPartitionScheme ([swimlane])

GO