Archive for June 2008

Three scripts of mine at SQLServerCentral.com

June 12, 2008

Since I can’t publish them myself for thirty days (I did agree to the sites rules about re-publishing)…

Delete files older than N-days via T-SQL:

Script

Powershell “unload and archive table” script:

Script

SQL Server “Quick n dirty” Restore Generator:

Script

SMO & Connection Pooling (and how to avoid it).

June 12, 2008

First, I’d like to apologise for not being as active the last week or so. I caught some very nasty food poisining from a local restaraunt chain. I won’t give names but thier initials start with “Chipotles”. ;)

I’d like to discuss connection pooling with SMO…

In my process’ that I’ll be demonstrating with, I am performing a serial operation on a list of servers. So each script usually has two connections, one to the ‘main’ db server that gathers the list of servers to query. And connections to the servers in the list. I’ve found, through rudimentary testing that when doing serial operations like this, it’s more efficient for the computer running the script to not use pooled connections. The connection pooling will create about 25% more network connections and leave them for the pool on the server to manage. I was able to reduce the number of open connections by controlling the connections to the servers manually.

So, let’s demo how to control our connections explicitly using the last script as our example:

[System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.SqlServer.SMO”) | Out-Null
$SrvName = “YourServerName”
$DBName = “master”

$SrvObj = New-Object (”Microsoft.SqlServer.Management.SMO.Server”) $SrvName
$SrvObj.ConnectionContext.NonPooledConnection = “True” # Sulu, you have the Com.
$SrvObj.ConnectionContext.Connect() # Connect to the dbserver.
$DBObj = $SrvObj.Databases[$DBName]

$Query = “SELECT name FROM dbo.sysdatabases”

# Execute $Query and return a dataset
$Results = $DBObj.ExecuteWithResults($Query)

$SrvObj.ConnectionContext.Disconnect()

foreach ($Row IN $Results.Tables[0].Rows)
{ Write-Host $Row.Name }

Note: These lines will actually connect to the database, so make sure that you have the ConnectionContext.NonPooledConnection set right after you create your $SrvObj object.

$DBObj = $SrvObj.Databases[$DBName]
$Query = “Select name FROM dbo.sysdatabases”

Next up is a WMI based disk query that records information from the remote servers to a permanant table in the ‘main’ dbserver. I see this asked for in the forums all the time, and it’s a perfect segue into some more complex usage of Powershell & SMO.