Monday, May 31, 2010

Mostly Recently Updated Table for SQL Server

I found a useful query for finding out the most recently accessed/updated table in a SQL Server database (I forgot the original resource). I used the script to find out a table back sceen of Team Foundation Server (TFS), so I could modify the content to configure a particular URL.

use TfsIntegration     
go
select
t.name
,last_user_update
,user_updates
,user_seeks
,user_scans
,user_lookups
,last_user_seek
,last_user_scan
,last_user_lookup
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()

SQL Server database hang in "in recovery" status

Problem. After a failed setting mirror operation (initiated from SQL Server Management Studio from GUI), SQL Server Database stay in "in recovery" status for prolonged period of time (overnight). The database is about 7GB in size. From the primary site, the database showed normal. From the secondary site, the database showed "in recovery".

There is little that can be done for the secondary database. One option is to stop the SQL services, rename the data files (.mdf & .ldf), start the SQL database again, drop the existing database, rename the data files back, and attached the data files with same database name. After that, we can try to restore database backup and set up mirror.

On a closer look from primary, it seems that the mirror is broken. However, from the output of some database operation in the secondary database, it seems that the mirror has not be broken yet. Further more, there is no entry in the event log to indicate the progress of the recovery operation.

Let's try this approach. From the primary site, to break the mirror run the following command.
ALTER DATABASE TfsVersionControl SET PARTNER OFF
After the command finished successfully, the database on the secondary site changed to (Restoring…) status immediately. The following operations are straight forward -- copy the full database backup and log, and restore with norecovery option, set mirror for the database.
This is the preferred approach, since only the problem db is touched, no need to re-set the mirror for all other databases.

There are occasional complains from the internet about this prolonged (or hanged) SQL Server database "in recovery" status. It may worth to take a look whether the database actually started the "recovery", or still in a previous status due to unknown reasons.

Script to Monitor ClearCase license

Here are the scripts mentioned in my previous post regarding monitoring ClearCase license usage.

re: http://doublepaddle.blogspot.com/2008/02/monitoring-clearcase-license-usage.html Apperently I am not good at following up my previous posts -- Sorry for the delay.


The collection script is as simple as this.

#!/usr/bin/sh
date >> /home/ccadmin/logs/license.log
/opt/rational/clearcase/bin/clearlicensegrep "Current active users" >> /home/ccadmin/logs/license.log

The script is scheduled in cron jobs for every 10 mintues. This script can be easily converted to a Windows batch file and added to scheduler.

Then a perl script is used to load the data and generate the report in excel format. The script can be added as part of the Windows scheduler, or run at the time when you want to view the report. The nice chart in the previous post is also part of the excel report.

#!/usr/bin/perl
#Auther: Li Qin
#Date: 2008.02.05
#Function: generate ClearCase license usage report in excel format

use Spreadsheet::WriteExcel;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; # die on errors...

$file = "C:\\Li\\SCM\\ClearCase\\report-licns.xls";
$workbook = Spreadsheet::WriteExcel->new("$file");
$sheet = $workbook->addworksheet("data");

my $format1 = $workbook->add_format();
$format1->set_bold();
$format1->set_size(15);
$format1->set_color('blue');
$format1->set_align('center');

my $format2 = $workbook->add_format();
$format2->set_bold();
$format2->set_size(11);
$format2->set_color('Black');
$format2->set_align('center');

my $format3 = $workbook->add_format();
$format3->set_size(11);
$format3->set_color('Black');
$format3->set_align('center');

$sheet->activate();
$sheet->merge_range('C2:G2', "ClearCase Usage Report", $format1);

$sheet->set_column(3, 2, 40);
$sheet->set_column(3, 3, 20);
$sheet->set_column(3, 4, 20);
$sheet->write(3, 2, "Date", $format2 );
$sheet->write(3, 3, "Usage", $format2 );
$sheet->write(3, 4, "Max", $format2 );

open(FILE,"license.log")  || die "couldn't open file a.out";
$row = 4;
my @user1, @user2;
$max = 35;
while ($date= ) {
chomp $date;

$line1 = ;
chomp $line1;
while ( !($line1 =~ m/Current/) ) {
$user1[$row-4] = 0;

$sheet->write($row+1, 2, $date, $format3);
$sheet->write($row+1, 3, $user1[$row-4], $format3);
if ( $date =~ m/Fri Feb 1 17/ ) {
$max = 40;
}
$sheet->write($row+1, 4, $max, $format3);
$row = $row +1;
$date = $line1;
$line1 = ;
chomp $line1;
}
$pos = rindex $line1, ":";
$user1[$row-4] = substr($line1,$pos+1);
$user1[$row-4]=$user1[$row-4]+0;

$sheet->write($row+1, 2, $date, $format3);
if ( $date =~ m/Fri Feb 1 17/ ) {
$max = 40;
}
$sheet->write($row+1, 3, $user1[$row-4], $format3);
$sheet->write($row+1, 4, $max, $format3);
$row = $row +1;
}
close(FILE);
$workbook->close;

# #create chart
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');

# open Excel file
my $Book = $Excel->Workbooks->Open("$file");
# select worksheet of data


$count = 6;
my $sheet = $Book->Worksheets("data");
my $value = $sheet->Cells($count,4)->{'Value'};
my $len = length($value);

while ( $len >0 ) {
$count = $count +1;
my $value = $sheet->Cells($count,4)->{'Value'};
$len = length($value);
#printf "At ($count, 4) the value is %s. length is %d\n", $value, $len;
}
print "last row is $count.";


$lastRow = $count-1;
my $Range = $sheet->Range("C6:E$lastRow");

my $Chart2 = $Excel->Charts->Add($sheet);
$Chart2->{ChartType} = xlLine;
$Chart2->SetSourceData({Source => $Range, PlotBy => xlColumns});

$Chart2->{HasTitle} = 1;
$Chart2->ChartTitle->{Text} = "ClearCase License Usage\n";
$Chart2->Axes(xlValue)->{HasTitle} = 1;
$Chart2->Axes(xlValue)->AxisTitle->{Text} = "License Usage";
$Chart2->Axes(xlCategory)->{HasTitle} = 1;
$Chart2->Axes(xlCategory)->AxisTitle->{Text} = "Date/Time";

$Chart2->{HasLegend} = 'False';
$Chart2->{Name} = "Chart";
$Chart2->{HasLegend} = 1;
$Chart2->Legend->{Position} = xlBottom;
$Chart2->SeriesCollection(1)->{Name} = "Usage";
$Chart2->SeriesCollection(2)->{Name} = "Max";

# save and clean up
$Book->Save;
$Book->Close;
# End of create charts

We have upgraded our monitoring using SQL Server Reporting Service, a nice-to-have.

Monday, May 17, 2010

Change check-in email notification to TFS Web Access

There are some very useful articles to change TFS email notifications to link to Team System Web Access:

Changing TFS emails to link to Team System Web
Changing TFS emails to link to Team System Web Part 2

The configurable links include:
- Work Item notification mails
- Build notification mails
- Check-in notification mails
- Mails sent by Team Explorer

However, for check-in notification mails, if your TFS web access is not installed as the default website, and contains a virtual directory as part of the URL, the tfsadminutil tool cannot process the URL correctly, at least not until TFS 2008 SP1. For example, if your TFS web access URL is http://myTFSserver:8080/tswa, tfsadminutil tool will set up the links as http://myTFSserver:8080/ instead.

Here we provide a workaround until tfsadminutil tool has that improved. The workaround requires direct modification of the TFS database. Please note that it is NOT recommended by Microsoft.

Prerequisite: TFS 2008 + SP1 + TFS web access + Hotfix KB957196

Step 1. run the command on the TFS server:
C:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools> tfsadminutil configureconnections /TSWAUri:http://myTFSserver:8080/tswa

Step 2. connect to TFS database through SQL Server Management Studio. Run the following query.
use TfsIntegration
go
select * from tbl_service_interface

output:
....
8 WorkItemEditor
http://myTFSserver:8080/wi.aspx
8 ChangesetDetail
http://myTFSserver:8080/cs.aspx
8 Difference
http://myTFSserver:8080/diff.aspx
8 ViewItem
http://myTFSserver:8080/view.aspx
....

Step 3. Modify the URLs in the table for the above items.
Update tbl_service_interface
Set url='http://myTFSserver:8080/tswa/wi.aspx'
Where name='WorkItemEditor'

Update tbl_service_interface
Set url='http://myTFSserver:8080/tswa/cs.aspx'
Where name='ChangesetDetail'

Update tbl_service_interface
Set url='http://myTFSserver:8080/tswa/diff.aspx'
Where name='Difference'

Update tbl_service_interface
Set url='http://myTFSserver:8080/tswa/view.aspx'
Where name='ViewItem'

Step 4. After the TFS process is recycled, your check-in email subscription will generate emails linking to TFS web access.