Web Site/Service State

In researching using the application state in asp.net for a status page I came across a few other links that would provide helpful:

Session State Provider for sharing session state across servers.

Light weight.

Of course performance and good control dev should always be considered.

Sending lots of data to a web service

I've needed to create a web service to send a lot of data to a web service.

Configure Web Service to MTOM, or buffer the sending of a bytes array. Also consider performance.

SQL Server MemToLeave

If you find yourself running out of memory in linked server queries, or in SSIS, look at this article.

Found through StackOverflow Question:

“Linked server queries use memory from an area outside of the SQL Server buffer pool called memToLeave, which is used for servicing connection threads and now the sql clr amongs other things. On 32-bit it's 384 MB in size which can be inadequate for some scenarios.

If you need to adjust your SQL Server Memory configuration and in particular the memToLeave area you will find an explanation on the following blog…”

All SQL Server DB Infomation

Useful script provided here.

SELECT database_id ,

CONVERT(VARCHAR(25), DB.name) AS dbName ,

CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],

state_desc ,

(SELECT COUNT(1)

FROM sys.master_files

WHERE DB_NAME(database_id) = DB.name

AND type_desc = 'rows'

) AS DataFiles,

(SELECT SUM((size*8)/1024)

FROM sys.master_files

WHERE DB_NAME(database_id) = DB.name

AND type_desc = 'rows'

) AS [Data MB],

(SELECT COUNT(1)

FROM sys.master_files

WHERE DB_NAME(database_id) = DB.name

AND type_desc = 'log'

) AS LogFiles,

(SELECT SUM((size*8)/1024)

FROM sys.master_files

WHERE DB_NAME(database_id) = DB.name

AND type_desc = 'log'

) AS [Log MB] ,

user_access_desc AS [USER access] ,

recovery_model_desc AS [Recovery model] ,

CASE compatibility_level WHEN 60 THEN '60 (SQL Server 6.0)'WHEN 65 THEN '65 (SQL Server 6.5)'WHEN 70 THEN '70 (SQL Server 7.0)'WHEN 80 THEN '80 (SQL Server 2000)'WHEN 90 THEN '90 (SQL Server 2005)'WHEN 100 THEN '100 (SQL Server 2008)'END AS [compatibility level],

CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation DATE] ,

-- last backup

ISNULL(

( SELECT TOP 1 CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +' (' + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'

FROM msdb..backupset BK

WHERE BK.database_name = DB.name

ORDER BY backup_set_id DESC

)

,'-') AS [Last backup] ,

CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext] ,

CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose] ,

page_verify_option_desc AS [page verify OPTION] ,

CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only] ,

CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink] ,

CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto CREATE statistics],

CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto

UPDATE statistics] ,

CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],

CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]

FROM sys.databases DB

ORDER BY dbName ,

[Last backup] DESC,

NAME

Fixing exception messages on the .NET Compact Framework 3.5

Had a problem getting Compact Framework 3.5 and 2.0 displaying error messages, I asked the question on StackOverflow, which sent me here - I was suspicious, but it worked!

.Net Formatting Strings

Because I always forget - here are some useful links:

4GuysFromRolla

Standard Date & Time Format Strings

Custom Date & Time Format Strings

Whitespace issue in PowerShell Format Files

Looks useful - never used it, but I'm sure it will come in handy - Whitespace issue in PowerShell Format Files

Log4net Object Renderers in .NET

I had an annoying problem yesterday when trying to register a custom renderer for an object, it took up too much of my time, and I believe it is a bug in Log4net, but I sorted out a work around.

There are lots of resources and good tutorials for log4net, especially on adoAppenders, lossy logging. I recommend logging to a database and using L4NDash to keep an eye on it.

Serializing Exceptions for Web Services

I needed to create a remote logging web service, found a few good bits of info:

in .Net 3.5

Stack Overflow

Some more useful articles from Micro$oft

Tutorial Videos

Came across this today, pretty useful if you've got the time to watch them.

Everyone should know how to call WCF (& Web Services) Async.

50 Most Frequently Used UNIX / Linux Commands

Useful linux commands that I always forget after not using Linux for a while

SQL SERVER – Finding Last Backup Time for All Database

Useful script to find last backup time for all db's in SQL Server:

SELECT sdb.Name AS DatabaseName,

COALESCE(CONVERT(VARCHAR(20), MAX(bus.backup_finish_date), 120),'-') AS LastBackUpDateTime,

COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate

FROM sys.sysdatabases sdb

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

GROUP BY sdb.Name

Source: http://blog.sqlauthority.com/2010/11/04/sql-server-finding-last-backup-time-for-all-database/

NoSQL - get off the bandwagon

I've never been convinced with the NoSQL bandwagon, so we never got on it. It has it's place, but not if you want reporting etc. This is an interesting article

SSIS & Postgres using PGNP provider

We've been using SSIS for a while now to connect to Postgres for archiving our production database. Natively SSIS can't connect to Postgres, so we've been using a provider called PGNP (http://www.pgoledb.com/), it seemed a but buggy to begin with, but their support is excellent and it has come a long way in the last year!

Using SSIS proved to be much quicker and easier to maintain (after the initial learning curve) for ETL tasks and soon we'll be re-designing our data warehouse. Thanks to PGNP.

Imperfect IT: Automate SharePoint 2010 Farm Backups with Powershell

Found this useful. Had what seems to be a common problem, but not always obvious why your getting to problem - see this for more details.

# Location of spbrtoc.xml

$spbrtoc = "E:\Backups\spbrtoc.xml"

# Days of backup that will be remaining after backup cleanup.

$days = 3

# Import the Sharepoint backup report xml file

[xml]$sp = gc $spbrtoc

# Find the old backups in spbrtoc.xml

$old = $sp.SPBackupRestoreHistory.SPHistoryObject |

? { (

(

[datetime]::ParseExact($_.SPStartTime, "MM/dd/yyyy HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)

) -lt (get-date).adddays(-$days)

)

}

# get-date $_.SPStartTime) -lt (get-date) }

#$old | ? {write-host}

#write-host $old.SPBackupRestoreHistory.SPHistoryObject.SPStartTime

if ($old -eq $Null) { write-host "No reports of backups older than $days days found in spbrtoc.xml.`nspbrtoc.xml isn't changed and no files are removed.`n" ; break}

# Delete the old backups from the Sharepoint backup report xml file

$old | % { $sp.SPBackupRestoreHistory.RemoveChild($_) }

# Delete the physical folders in which the old backups were located

$old | % { Remove-Item $_.SPBackupDirectory -Recurse }

# Save the new Sharepoint backup report xml file

$sp.Save($spbrtoc)

Write-host "Backup(s) entries older than $days days are removed from spbrtoc.xml and harddisc."