Auto-shrink all Database logs

One of the key differences between an OLTP system and a Data Warehouse, is that a DW can be completely truncated and reloaded from the OLTP, since it is purely an aggregation and reporting area. So while Log files are important for OLTP, you don't need have heavy logging on the DW. I always set my DW's to "Simple". However, even in "Simple" mode some logging does occur, and log files grow over time. Not all my customers have the luxury of a DBA, and in order to help them out, I have written this script to automatically shrink log files.

Some notes before you use it:

• You must have the necessary permissions on the databases
• Change the "SomeNameFilter" in the code to filter out only databases you want to filter. 

EXEC sp_MSForEachDB
' use ?;
declare @SQL varchar(2000);
select @SQL = ''DBCC SHRINKFILE ('' + name + '')''
from sys.database_files
where type = 1
and name like ''SomeNameFilter%'';

if @SQL <> ''''
begin;
exec (@SQL);
end '

Enjoy!!

Setting up POP3 and SMTP Relays with Exchange 2010

Wow, recently had SO much fun struggling with Exchange 2010, to set up a POP3 connectors for external mailboxes and an SMTP relay for an internal SQL mail server. The challenge was, that when I could relay internal mail to external, my POP mailboxes fell over, and vice versa...In the end, I had to delete all the connectors Exchange created by default, and set them up manually.

It all started when I was trying to send a SQL mail using CDO. I kept getting "-2147220977" error code, which translates into "0x8004020FL - The server rejected one or more recipient addresses" (Convert the code to hex, and look at TechNet for the meaning). When I rewrote my code to C# (.Net), it popped up with "5.7.1 Unable to relay". So, at this point, I was sure SMTP was the problem.

I deleted all the SMTP connectors, and recreated one for outgoing routing, and it worked... Or so I thought... even though the mail goes out, I now could not download any mail from POP3 mailboxes. When I started the POP3 download, Event log had an error event :"Cannot connect to the SMTP server 'localhost' on port 25. The error code was 0x800ccc0f. Verify that the Microsoft Exchange Transport service is running and that the Exchange receive connectors are properly configured"



Googling did not help much, as it seems not a lot of people have had this problem before, so out of desperation, I took to my trusty whiteboard, and designed what I wanted it to do (I know, such a developer thing to do, but hey, it worked). I came up with an action plan, executed it, and lo and behold, it worked.

This is what I did...

1. In the Exchange Management Console, go to Server Configuration, Hub Transport, and delete ALL receive connectors

Now let the fun start:

1. Using the Wizard, Create a new Connector, called "POP3 Mailbox Connector". Set the intended use to "Custom" and click on next
2. At Local Network Settings, remove the default, and add specific IP 127.0.0.1
   . For the FQDN, use the FQDN of the Exchange server, and click on next - This will tell the server which server to send the incoming mail to.
3. At Remote Network Settings, remove the default, and add "127.0.0.1". This will tell the server which machine will be doing the POP3 pickups. Click on next, and run through the confirmation screens (New, Finish)

At this point the connection is set, but because of security not set, it can't do anything

4. Double click on the new connection
5. Go to Authentication, and enable Basic Authentication only. This will allow the POP connector to send credentials to the remove POP mailbox server
6. Click on Permission Groups, and set Anonymous Users and Exchange Servers. This will let the current server connect to the boxes, either through the NW Server account or through and Exchange account
7. Click on Apply, and the POP3 component is set.


Now for the SMTP relay

1. Using the Wizard, Create a new Connector, called "SMTP Relay Connector". Set the intended use to "Custom" and click on next
2. At Local Network Settings, remove the default, and add the specific IP address of the exchange server
   for the FQDN, use the FQDN of the Exchange server, and click on next - This will tell the server which server to send the incoming mail to.
3. At Remote Network Settings, remove the default, and add the IP of the server you want to send emails from. Click on next, and run through the confirmation screens (New, Finish)

At this point the connection is set, but because of security not set, it can't do anything

4. Double click on the new connection
5. Click on Permission Groups, and set Exchange Servers. This will let the current server allow connections from the sending server
6. Go to Authentication, and enable Transport Layer Security (TLS) and Externally Secured. This will allow the server to receive the internally generated emails, and get the response from external domains
7. Click on Apply, and the SMTP component is set.

Now test, and there ya go... Hopefully this will make your searching simpler than what I had to go through to get it to work :)

Dynamic SQL Kill statements

Hi all

Like me, I am sure you get miffed when you do testing, and you need to restore a db to a specific backup point, only to get the message " database "Cannot restore database "xxx" because it is currently in use". Then it’s back to running the tedious "sp_who, kill " queries (or using the management tool).

Below is a script I wrote that you can use to kill all the connections to a list of DB's.

You'll notice the variable in one of the first lines, named " @List_Of_Databases". To use the script, simply type the name of the DB's you want to disconnect, separating with a comma. Then run it... It will kill all processes running against those DB's listed. Then you can drop/restore the db to your heart's content.

One word of warning - I have seen that, if SSIS is in "execution" mode (whether it is completed or not), and you run the script, it dies a horrible, painful death.

Enjoy...
/* START OF SCRIPT */


use master
go
Set NoCount on
go
declare @List_Of_Databases varchar(200)
-- List of databases to disconnect
/**********************************************************
User types the list of DB's here
***********************************************************/
select @List_Of_Databases =
'DB1, DB2, DB3, DB4'




--Parse the list to eliminate case problems
Select @List_Of_Databases = UPPER(RTRIM(LTRIM(@List_Of_Databases)))
-- Table to store DB's
Create table #DatabaseList
(
DBNAME varchar(200)
)
--Build the query to get the list of DB's
select @List_Of_Databases = 'select name from sysDatabases where UPPER(RTRIM(LTRIM(Name))) in (' + char(39) + replace(replace(replace(@List_Of_Databases,' ,',','),', ',','),',',char(39) + ',' + char(39)) + char(39) + ')'
--Get the DB's that match the names
Insert into #DatabaseList
exec (@List_Of_Databases)
-- Temp table to store SPID's in use
Create table #Table
(
RowID int identity(1,1)
,SPID int
,UserName varchar(200)
,DBName varchar(200)
,LoginTime datetime
,LastRun datetime
,Program varchar(200)
)
--Get the SPID's
Insert into #Table
(
SPID
,UserName
,DBName
,LoginTime
,LastRun
,Program
)
select spID
,LogiName
,SD.Name
,Login_time
,Last_Batch
,Program_Name
from master.dbo.sysProcesses SP
join master.dbo.sysDatabases SD
on SP.dbID = SD.dbID
join #DatabaseList DB_List
on SD.Name = DB_List.DBNAME
Where spID <> @@SPID
declare @RowID int
,@MaxRows int
,@SQL varchar(200)
,@SPID int
,@UserName varchar(200)
,@DBName varchar(200)
,@LoginTime datetime
,@LastRun datetime
,@Program varchar(200)
-- Set for Loop
Select @RowID = isnull(min (RowID),1)
,@MaxRows = isnull(max (RowID),0)
from #Table
--Perform Loop
While @RowID <= @MaxRows
begin
-- Get process details
Select @SPID = SPID
,@UserName = UserName
,@DBName = DBName
,@LoginTime = LogInTime
,@LastRun = LastRun
,@Program = Program
from #Table
where RowID = @RowID
-- Build query to kill process
Select @SQL = 'kill ' + convert(varchar(20),@SPID)
-- Kill process & show message
exec (@SQL)
Print 'Killed process details' + char(13)
+ '======================' + char(13)
+ 'Process ID: ' + convert(varchar(20),@SPID) + char(13)
+ 'UserName: ' + @UserName + char(13)
+ 'DataBase: ' + @DBName + char(13)
+ 'User Login time: ' + convert(varchar(50),@LoginTime,100) + char(13)
+ 'Last Query run by user: ' + convert(varchar(50),@LastRun,100) + char(13)
+ 'User Interface: ' + @Program + char(13) + char(13)
-- Iterate loop
select @RowID = min(RowID)
from #Table
Where RowID > @RowID
end
if @RowID is not null -- No rows were processed
begin
print 'No processes were killed'
end
-- Clean up
Drop table #Table
drop table #DatabaseList
dbcc FREEPROCCACHE WITH NO_INFOMSGS

/* END OF SCRIPT */