Finding Size Information for the Content of your WSS 3.0 Webs/Lists

Today I had to hack out a T-SQL query to determine information regarding the size of indiviual lists and libraries inside of a single site collection (one content DB). I used this query and then pulled it into an Excel workbook and used pivot tables to aggregate the data.

USE [WSS_Content]
GO

SELECT
    [dbo].[Webs].[FullUrl]
   ,[dbo].[Lists].[tp_Title] AS "ListName"
   ,[dbo].[Docs].[DirName]
      ,[dbo].[Docs].[LeafName]
      ,[dbo].[Docs].[Size]
      ,[dbo].[Docs].[MetaInfoSize]
      ,[dbo].[Docs].[Version]
      ,[dbo].[Docs].[TimeCreated]
      ,[dbo].[Docs].[TimeLastModified]
      ,[dbo].[Docs].[MetaInfoTimeLastModified]
      ,[dbo].[Docs].[CheckoutUserId]
      ,[dbo].[Docs].[CheckoutDate]
      ,[dbo].[Docs].[ExtensionForFile]
  
  FROM [WSS_Content].[dbo].[Docs]
  INNER JOIN [WSS_Content].[dbo].[Webs] ON [dbo].[Webs].[Id] = [dbo].[Docs].[WebId]
  INNER JOIN [WSS_Content].[dbo].[Lists] ON [dbo].[Lists].[tp_ID] = [dbo].[Docs].[ListId]
  
  WHERE [dbo].[Docs].[Size] > 0     
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.stp')   
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.aspx')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.xfp')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.dwp')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%template%')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.inf')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.css')

PowerShell Script to Monitor the Status of a SQL Job

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$job = $server.JobServer.Jobs["Test Job"]
$now = Get-Date
do
{
 Start-Sleep -Seconds 1
 $job | select Name,CurrentRunStatus,LastRunDate
 $job.Refresh()
}
while($job.LastRunDate -lt $now)
$job | select Name,CurrentRunStatus,LastRunDate

PowerShell Scripts to Continuously Ping a Host and Give an Audible Alert

#Used to continuously ping a host and get an audible alert when it is back up.

function ContinuousPing-Beep
{
    param([string]$computer)
    $ping = new-object System.Net.NetworkInformation.Ping
    $result = $ping.Send($computer);
    if($result.Status -eq "Success")
    {
        Write-Host "Reply received from $computer" -Foreground green
        Write-Host `a;
    }
    else
    {
        do{$result = $ping.Send($computer);Write-Host "Reply from $computer. Destionation host unreachable." -Foreground red}
            until($result.Status -eq "Success")
            Write-Host "Reply received from $computer" -Foreground green
            Write-Host `a;
    }
}

function ContinuousPing-Voice
{
    param([string]$computer)
    
    $voice = new-object -com SAPI.SpVoice
    $ping = new-object System.Net.NetworkInformation.Ping
    $result = $ping.Send($computer);
    if($result.Status -eq "Success")
    {
        $voice.Speak("Reply received from $computer", 1)
    }
    else
    {
        do{$result = $ping.Send($computer);Write-Host "Reply from $computer. Destionation host unreachable." -Foreground red}
          until($result.Status -eq "Success")
       
        $voice.Speak("Reply received from $computer", 1)

    }
}

ContinuousPing-Beep "192.168.1.1"

ContinuousPing-Voice "192.168.1.1"

How to determin if you are logged in via Kerberos or NTLM on SQL Server

Simply run the following query.

SELECT
    c.session_id
   ,s.login_name
   ,c.auth_scheme
   ,c.net_transport
   ,s.host_name
   ,s.login_time
   
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s on s.session_id = c.session_id  

BDC in SharePoint 2010 Foundation for a Department Level Database

One big project we are workign on currently is using the BDC to surface our company's EDI project to end users in a familiar way to have them enter confirmation of new orders from clients quickly. Before we got too deep into this we were looking at various options and ways to experiment and learn the technology. One thing we did not have was a tracking system of our servers and their assigned IP addresses and assigned roles. Here is a diagram of the testing database that I designed and wrote.

The steps to create a fully CRUD enabled application that looks like a SharePoint liust but has all the benefits of being backed by a SQL database are as follows:

1. Design your database as you normally would.

2. Create a set of views that the users will access to see the data.

3. Use INSTEAD OF triggers to make the views updateable.

4. Create External Content types to connect to the SQL data.

5. Create External Lists to display the data in SharePoint 2010.

6. Add the correct permissions to the External Content Type in the SharePoint BDC management in Central Admin.

7. Test that you can add, update, and delete items in the lists.

8. Write a webpart to supply the users with a richer, easier to understand GUI.

In my next post I hope to detail the steps for adding a CRUD enabled Silverlight web part to work with the External Lists.

PBM and ExecuteWQL()

Another item of interest in studying for the SQL Server 2008 upgrade exam. This is a really interesting feature that could be combined with another monitoring system to give you a really complete view of your SQL servers every morning.

http://msdn.microsoft.com/en-us/library/aa394606(VS.85).aspx
http://msdn.microsoft.com/en-us/library/bb895209.aspx
http://sqlserverpedia.com/blog/sql-server-bloggers/executewql-in-policy-based-management/

SQL Server 2008 Extended Events

In my studies for the MP: DBA SQL Server 2008 I cam accross a topic that was not included in any of the books that I have been using. It's called Extended Events and is used in diagnosing things like dealocks and high CPU utilization. Read the MSDN article.