Shrink the log file

select name,recovery_model_desc from sys.databases
GO
Alter database YourDatabaseName Recovery simple
GO
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Multi-Valued Parameter function for SSRS

CREATE FUNCTION charlist_to_table

(@list ntext,

@delimiter nchar(1) = N',')

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

SET @textpos = 1

SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2

SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0

BEGIN

SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))

INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)

SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

SET @pos = charindex(@delimiter, @tmpstr)

END

SET @leftover = @tmpstr

END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),

ltrim(rtrim(@leftover)))

RETURN

END

GO


----------------------------------------------------------------------

simple one

-----------------------------------------------------------------------

CREATE FUNCTION dbo.fn_MVParam(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @VALUES TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @VALUES(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

Size of db

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/81fd5ec9-ce0f-4c2c-8ba0-6c483cea6c75.htm

Lock T-sql Queries

SELECT
SessionID = s.Session_id,
resource_type,
DatabaseName = DB_NAME(resource_database_id),
request_mode,
request_type,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
nt_domain,
nt_user_name,
s.status,
last_request_start_time,
last_request_end_time,
s.logical_reads,
s.reads,
request_status,
request_owner_type,
objectid,
dbid,
a.number,
a.encrypted ,
a.blocking_session_id,
a.text
FROM
sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN
(
SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
) a ON s.session_id = a.session_id
WHERE
s.session_id > 50

order by SessionId desc
----------------------------------------------------------------------------------

Current Statements

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
SELECT * FROM ::fn_get_sql(@Handle)







----------------------------------------------------------------------------------

select l.request_session_id,s.login_name,s.[host_name],s.[program_name],l.resource_type as 'Lock Type',db_name(l.resource_database_id) as 'Database',object_name(p.[object_id],l.resource_database_id) as 'Object Name',l.request_mode AS 'LockMode',l.request_status AS 'LockStatus'
,l.request_reference_count as
'aprxmt # of times the same requestor has requested this resource'
from sys.dm_tran_locks as l INNER JOIN sys.partitions as p
ON l.resource_associated_entity_id = p.hobt_id
INNER JOIN sys.dm_exec_sessions as s
ON l.request_session_id = s.session_id
where l.request_session_id > 50

Enable CDC

declare @rc int
exec @rc = sys.sp_cdc_enable_db
select @rc
-- new column added to sys.databases: is_cdc_enabled
select name, is_cdc_enabled from sys.databases

CDC wrapper creation t-sql

DECLARE @wrapper_functions TABLE (
function_name sysname,
create_script nvarchar(max));

INSERT INTO @wrapper_functions
EXEC sys.sp_cdc_generate_wrapper_function;

DECLARE @create_script nvarchar(max);
DECLARE #hfunctions CURSOR LOCAL fast_forward
FOR
SELECT create_script FROM @wrapper_functions;

OPEN #hfunctions;
FETCH #hfunctions INTO @create_script;
WHILE (@@fetch_status <> -1)
BEGIN
EXEC sp_executesql @create_script
FETCH #hfunctions INTO @create_script
END;

CLOSE #hfunctions;
DEALLOCATE #hfunctions;

log file deletion with vbs

Option Explicit
On Error Resume Next
Dim oFSO, oFolder, sDirectoryPath
Dim oFileCollection, oFile, sDir
Dim iDaysOld

'' Specify Directory Path From Where You want to clear the old files

sDirectoryPath = "C:\ETL\LOGFiles"

'' Specify Number of Days Old File to Delete

iDaysOld = 0

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sDirectoryPath)
Set oFileCollection = oFolder.Files

For each oFile in oFileCollection

If LCase(Right(Cstr(oFile.Name), 3)) = "log" Then

If oFile.DateLastModified < (date() - iDaysOld) Then
oFile.Delete(True)
End If

End If
Next

Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing

Schema

select 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name from sys.objects o
Inner join sys.schemas s on o.schema_id = s.schema_id
where s.name= 'dbo' and o.Type = 'U' order by o.Name--(o.Type = 'A' Or o.Type = 'P' Or o.Type = 'V')

select 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name from sys.objects o
Inner join sys.schemas s on o.schema_id = s.schema_id
where s.name= 'dbo' and o.Type = 'P' and o.name <> 'sysdiagrams' order by o.Name--(o.Type = 'A' Or o.Type = 'P' Or o.Type = 'V')

select o.name tablename,s.name schemaname from sys.objects o left join sys.schemas s on o.schema_id =s.schema_id
where o.type ='U' and o.name <> 'sysdiagrams' and s.name not in ('cdc','dbo')

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order by table_name
declare @tablename Char(50)
SET @tablename='ACH'
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@tablename

ALTER SCHEMA Process TRANSFER dbo.MonthlyTickler

select s.Name , o.Name from sys.objects o
Inner join sys.schemas s on o.schema_id = s.schema_id
where o.Type = 'P'

Purged multiple tables.

create a cursor with all the table Names to be purged

OPEN CurDeleteRec;
FETCH CurDeleteRec INTO @TableName
WHILE (@@fetch_status <> -1)
BEGIN

select @numrec = 0
select @Query1 = 'select @numrec1 = COUNT(*) from TabletoPurge where created < @purgetodate1'
SELECT @Query1 = REPLACE(@Query1, 'TabletoPurge', @TableName)
SET @ParmDefinition1 = '@numrec1 INT OUTPUT, @purgetodate1 DATETIME'

-- print @Query1

EXEC sp_executesql @Query1, @ParmDefinition1, @numrec1 = @numrec OUTPUT, @purgetodate1 = @purgetodate

select @Query2 = 'delete from TabletoPurge where created < @purgetodate2'
SELECT @Query2 = REPLACE(@Query2, 'TabletoPurge', @TableName)
SET @ParmDefinition2 = '@purgetodate2 DATETIME'

--print @Query2

EXEC sp_executesql @Query2, @ParmDefinition2, @purgetodate2 = @purgetodate

insert datapurgelog
(
created ,
tablename ,
Numberofrecord
)
values (getdate(),
@TableName,
@numrec)

FETCH CurDeleteRec INTO @TableName
END
CLOSE CurDeleteRec;
DEALLOCATE CurDeleteRec;

YTD / MTD - SSRS expression

Expression I use to change my parameter default values. These could be written many different ways and actually use VB in the customer code are but I wrote them this way if you’re not a VB expert.

Month over month

=DateAdd("m",-1,Today)

MTD

=DateAdd("d",-(Day(today)-1), Today)

Week over week

=DateAdd("ww",-1, Today)

or

=DateAdd("d",-7, Today)

WTD

=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

Year over Year

=DateAdd("yyyy",-1, Today)

YTD

=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)

Notice in the YTD and WTD expression I’m calling a DateInterval function that is actually a Visual Basic function but does not require you do add anything to the custom code section of your report.

More @
http://www.bidn.com/blogs/DevinKnight/ssis/381/ssrs-commonly-required-date-expressions