USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_what]    Script Date: 08/03/2009 07:50:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_what]
--    @loginame     sysname = NULL
as
/*
http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/31690/
By Andrew Stanton
*/
set nocount on

declare
    @retcode         int, @loginame  sysname
declare
    @sidlow varbinary(85),@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh int

declare
    @charMaxLenLoginName varchar(6),@charMaxLenDBName varchar(6),@charMaxLenCPUTime varchar(10),@charMaxLenDiskIO varchar(10),
    @charMaxLenHostName varchar(10),@charMaxLenProgramName varchar(10),@charMaxLenLastBatch varchar(10),@charMaxLenCommand varchar(10)
--------
select  @retcode         = 0      -- 0=good ,1=bad.
--------------------------------------------------------------

if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses

--------------------  Capture consistent sysprocesses.  -------------------

SELECT
  spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,
	convert(sysname, rtrim(loginame)) as loginname,spid as 'spid_sort', 
	substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch
      INTO    #tb1_sysprocesses
      from master.dbo.sysprocesses   (nolock)

--------Screen out any rows
   DELETE #tb1_sysprocesses
         where   lower(status)  = 'sleeping'
         and     upper(cmd)    IN (
                     'AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER'
		    ,'TASK MANAGER'
                                  )

         and     blocked       = 0 or spid <= 50
---set the column widths
UPDATE #tb1_sysprocesses set last_batch = DATEADD(year,-10,GETDATE()) 
	where last_batch IS NULL or last_batch = '01/01/1901 00:00:00' or last_batch < '01/01/1950'
update #tb1_sysprocesses set status = substring(status,1,10), program_name = substring(program_name,1,20)
ALTER TABLE #tb1_sysprocesses 
	ALTER COLUMN status varchar(10)
ALTER TABLE #tb1_sysprocesses 
	ALTER COLUMN program_name varchar(20)
--------Prepare to dynamically optimize column widths.
SELECT
            @charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,5)),
            @charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)),
            @charMaxLenCPUTime =  convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)),
            @charMaxLenDiskIO =  convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)),
            @charMaxLenCommand = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)),
            @charMaxLenHostName  = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)),
            @charMaxLenProgramName = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)),
            @charMaxLenLastBatch = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
      from
             #tb1_sysprocesses
      where
             spid >= 0 and spid <= 32767



--------Output the report.


EXECUTE(
'SET nocount off
SELECT   SPID          = convert(char(5),spid)
        ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END
        ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END
	,ActiveSeconds = DATEDIFF(ss,last_batch,getdate())
        ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
        ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')
        ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END
 	,BatchStart = CONVERT(varchar(8),last_batch,14)
	,Now = CONVERT(varchar(8),getdate(),14)
        ,LBDate = substring(last_batch_char,1,5)
	,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
	,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')
        ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
        ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
order by CAST(SPID as int)
      -- (Seems always auto sorted.)   order by SPID
SET nocount on')

drop table #tb1_sysprocesses
--return @retcode 

GO

/****** Object: StoredProcedure [dbo].[usp_BackupAllDatabases] Script Date: 07/31/2009 19:04:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_BackupAllDatabases]
@BackupTarget_FullPathToFolder varchar(4000)
, @UseLitespeed int = 0
as

set nocount on

declare @err int
declare @sql varchar(256)
declare @db_name varchar(128)
declare @BackupName varchar(255)

declare @RunDate varchar(255)
set @RunDate = replace(replace(convert(varchar(255), getdate() , 120), ' ', '_'), ':','')
---- select @RunDate

-- Make sure our path ends in a '\'…
set @BackupTarget_FullPathToFolder = ltrim(rtrim(@BackupTarget_FullPathToFolder))
if right(@BackupTarget_FullPathToFolder, 1) <> '\' set @BackupTarget_FullPathToFolder = @BackupTarget_FullPathToFolder + '\'

declare extract CURSOR Local FOR

select
[name]
from
sysdatabases
where
version <> 0
and DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
and DATABASEPROPERTYEX(name, 'IsInStandBy') = 0
and [name] <> 'tempdb'
and [name] = 'RCRC'
order by
case when [name] = 'master' then 'ZZZ' else [name] end desc

OPEN extract
FETCH NEXT FROM extract
INTO @db_name

WHILE (@@FETCH_STATUS = 0)
BEGIN

set @BackupName = @BackupTarget_FullPathToFolder + @db_name + '_' + @RunDate + '.bak'

if (@UseLitespeed = 1)
begin

-- LiteSpeed backup…
exec xp_backup_database @database = @db_name, @filename = @BackupName, @init = 1, @logging = 0, @with = 'SKIP', @with = 'STATS = 10'
end
else
begin
-- SQL Server Backup…
set @sql = 'backup database [' + @db_name + '] to disk = '''+ @BackupName + ''' with init'
exec(@sql)
end

select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))

-- Shrink Database
-- As per Microsoft, avoid shrinking / regrowing the tempdb
--set @sql = 'DBCC SHRINKDATABASE (N''' + @db_name + ''', 75)'
--exec(@sql)

FETCH NEXT FROM extract INTO @db_name
END

CLOSE extract
DEALLOCATE extract

GO

/****** Object: StoredProcedure [dbo].[usp_BackupAllTransactionLogs] Script Date: 07/31/2009 19:04:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_BackupAllTransactionLogs]
@BackupTarget_FullPathToFolder varchar(4000)
, @UseLitespeed int = 0
as
/*
exec [dbo].[usp_BackupAllTransactionLogs] 'e:\',0
*/

set nocount on

declare @err int
declare @sql varchar(256)
declare @db_name varchar(128)
declare @BackupName varchar(255)
declare @copy_cmd varchar(255)

declare @RunDate varchar(255)
set @RunDate = replace(replace(convert(varchar(255), getdate() , 120), ' ', '_'), ':','')
-- select @RunDate

-- Make sure our path ends in a '\'…
set @BackupTarget_FullPathToFolder = ltrim(rtrim(@BackupTarget_FullPathToFolder))
if right(@BackupTarget_FullPathToFolder, 1) <> '\' set @BackupTarget_FullPathToFolder = @BackupTarget_FullPathToFolder + '\'

create table #SimpleRecoveryModelDatabases (
dbname varchar(255)
)

-- Edit this as you see fit…
insert into #SimpleRecoveryModelDatabases(dbname) values ('model')
insert into #SimpleRecoveryModelDatabases(dbname) values ('msdb')
insert into #SimpleRecoveryModelDatabases(dbname) values ('ReportServer')
insert into #SimpleRecoveryModelDatabases(dbname) values ('ReportServerTempDB')
insert into #SimpleRecoveryModelDatabases(dbname) values ('hmail')

CREATE TABLE #BackupInProgress (
[SPID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BlkBy] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ActiveSeconds] [int] NULL ,
[DBName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Command] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BatchStart] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Now] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LBDate] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgramName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiskIO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

declare extract CURSOR Local FOR

select
[name]
from
sysdatabases
where
version <> 0
and DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
and DATABASEPROPERTYEX(name, 'IsInStandBy') = 0
and [name] not in (select * from #SimpleRecoveryModelDatabases)
and [name] not in ('tempdb','master')
and [name] = 'RCRC'
order by
[name]

OPEN extract
FETCH NEXT FROM extract
INTO @db_name

WHILE (@@FETCH_STATUS = 0) BEGIN

truncate table #BackupInProgress

insert into #BackupInProgress exec sp_what

if ((select count(1) from #BackupInProgress where DBName = @db_name and Command = 'BACKUP DATABASE') = 0) -- is the current db being backed up now?
begin

-- Check on recovery mode…
if @db_name in (select * from #SimpleRecoveryModelDatabases)
begin
set @sql = 'alter database [' + @db_name + '] set recovery simple'
exec(@sql)
select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))
end
else
begin
set @sql = 'alter database [' + @db_name + '] set recovery FULL' -- you can consider BULK LOGGED here as well.
exec(@sql)
select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))
end

-- Backup the transaction logs…
-- For TEMPDB (and other SIMPLE), truncate the log…
/* START: This is depricated in 2005 and unsupported in 2008, so comment this out if you are using SQL Server 2008 or newer… */
if DATABASEPROPERTYEX(@db_name, 'Recovery') = 'SIMPLE'
begin
set @sql = 'backup log [' + @db_name + '] with truncate_only'
exec(@sql)
select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))
end
else
/* END: This is depricated in 2005 and unsupported in 2008, so comment this out if you are using SQL Server 2008 or newer… */
-- For other logging databases, backup the log…
if DATABASEPROPERTYEX(@db_name, 'Recovery') <> 'SIMPLE'
begin

set @BackupName = @BackupTarget_FullPathToFolder + @db_name + '_log_' + @RunDate + '.bak'

if (@UseLitespeed = 1)
begin
-- LiteSpeed backup…
exec xp_backup_log @database = @db_name, @filename = @BackupName, @init = 1, @logging = 0, @with = 'SKIP', @with = 'STATS = 10'
end
else
begin
-- SQL Server Backup…
set @sql = 'backup log [' + @db_name + '] to disk = '''+ @BackupName + ''' with init'
exec(@sql)
end

select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))
end

end

-- Shrink Database
-- As per microsoft, avoid shrinking / regrowing the tempdb
--set @sql = 'DBCC SHRINKDATABASE (N''' + @db_name + ''', 75)'
--exec(@sql)

FETCH NEXT FROM extract INTO @db_name

END

CLOSE extract
DEALLOCATE extract

drop table #SimpleRecoveryModelDatabases

GO