Some disk discoveries

I’ve been using SQLQueryStress to benchmark a test query against a VM SharePoint database. I used the Docs View and selected the top 101 where size > 100

SELECT TOP 101 FROM Docs WHERE Size > 100

The original time taken was 23 seconds

The disk was 64% fragmented and after defragging, the query time was 9 seconds. That’s a 60% improvement without touching the database.

So, onto the database and all the indexes are defragged and updated. This took the time down to 8 seconds (12% quicker)

Now for a couple of experiments.

SpaceSniffer

SpaceSniffer running on the parent – 3 minutes

SpaceSniffer completed and looking for changes on parent – 9 seconds

SpaceSniffer doesn’t affect the performance of the SQL after it finished scanning but during the scan the performance was terrible.

Compressing files on Guest

I’m compressing IIS log files from the last year and running the same query at the same time. This expanded the query time to 8 minutes.

SQL Service Pack 4

First thing, it’s hard to find and second is there is no update for SQL Express (currently). I will have to reinstall SQL Express to do the update.

Can’t install service pack 4

Synchronizing VM with the undo file

The VM has been running since November and has a 23Gb undo file. I’ll remove this and see if it makes any difference.

It did. Took the total load time down to 4 seconds. from 23 at the start.

Wednesday

Moving Sharepoint config database location

http://sharepointsherpa.com/2008/04/18/sharepoint-2007-move-content-and-configuration-databases/

The SharePoint Sherpa blog has the outline of how to run this task;

**** BACKUP FIRST AND PRACTICE THIS IN A TEST ENVIRONMENT ****

stsadm -o deleteconfigdb
stsadm –o setconfigdb -connect –databaseserver target_SQL_server -databasename databasename -farmuser your_farm_account -farmpassword your_farm_password

But….. this didn’t work for me. I then fixed the firewall on SQL Server 2008 R2 and it worked. Yay

Tuesday

Litespeed has an error: Could not find xpsls.dll. This is the dll for extended stored procedures. Just uninstall and reinstall to fix it. The device seems to have been removed when I upgraded to R2.

Dashboards

New dashboard is required for a project and an existing one has broken. Both are critical, both are important, both need to be working. There is one of me.

Friday

Monty has three bells now.

Internet access has returned after I checked the proxy settings in internet explorer to discover my computer was set to be it’s own proxy on port 81. Strange.

Dynamic Hyperlinks in Reporting Services

=”javascript:void(‘window.open(https://intranet/sites/Apps/IR/Lists/Injury/DispForm.aspx?ID=” + CSTR(Fields!ID.Value) & “‘));”

This will open the list based on the value in the ID field. ID has to be converted to a string otherwise it’ll give a compile warning but will still load.

Tuesday

Monty now has five bells attached to his collar. Great for scaring birds, not so great for getting to sleep. Whenever he moves, anywhere in the house, you can hear him.

I still have no internet access in Windows 7 thanks to Bonjour from Apple (I think).

Sharepoint is running SP2 and seems to be happy. I can’t connect to it because I have no internet access.

The high CPU issue occurred again on Monday but it was a Bank Holiday so I know now the issue is automated and nothing to do with normal users. Still don’t know where to start.

Reviewing Spotlight Alerts

Quest’s Spotlight on SQL Server is a tool for monitoring SQL Server and Windows Servers. One of the features is the automatic sending of emails whenever various alert conditions are met and sending them to a distribution group or just me. Some days I receive hundreds of them and put them all in one folder with the intention of looking through and finding patterns. Of course, I don’t get around to doing this so I wrote this Powershell script to help;

Powershell script to visit a folder in Outlook, parse out the relevant columns and write it to a CSV file.

#Set up the connection to Outlook

$olFolderInbox = 6

$outlook = new-object-com outlook.application;

$ns = $outlook.GetNameSpace("MAPI");

$inbox = $ns.GetDefaultFolder($olFolderInbox)

#Set the path to the Spotlight Folder

$msgs = $inbox.Folders.Item("Automatic Messages").Folders.Item("From Me").Folders.Item("Spotlight")

$counter = 1

$items = $msgs.items

#Go through each email and process based on the rules

foreach ($item in $items) {
$status = "Processing item {0} of {1}" -f $counter,$items.Count

Write-Progress "Processing items" $status -PercentComplete ($counter / $items.Count * 100)
$b = $item.Body

#These two lines remove the new line carriage return

$b = $b.Replace('

',',')

#Tidy up the empty columns

$b = $b.Replace(', ',',')

$b = $b.Replace(',,',',')

$b = $b.Replace(',,,',',')

$b = $b.Replace(',,',',')

#These will contain the events

$b = $b.Replace('Connection: ','')

$b = $b.Replace('Time: ','')

$b = $b.Replace('Severity: ','')

#The emails come from my account so I need to remove my signature from the result

$b = $b.Replace(',<My Signature from outlook>,','')

$b | Out-File "C:\PS\spotlight emails.csv" -Append

$counter++

}

The CSV can then be altered in Excel using Data > Text to Columns and setting a comma as the delimiter. The results can appended to an existing Excel log of processed emails for long term pattern analysis.

Unusually high CPU every Sunday afternoon

Why?

Actually it’s now Tuesday. Seems to be every 6 and a half days so the problem moves through the week over the months.

Steady start

It’s February, what have I achieved?

I can create simple SQL Server 2008 Reports

I have a system sending perfmon data to a BLG file, relogging it to CSV then importing it into a datamart

Deleted data

A large number of rows have been deleted from the server. How can I find who did it?

I have looked at the default trace but this doesn’t show row deletions just object deletions.

Update – I just put the data back. I know it happened between 02/12/2009 and 03/12/2009 but other than no info.

I’m now running a regular script to give me the row counts of the tables in the database and comparing them against the last run. If there has been a large change I can see before it’s too late.

Database cannot be opened

 Database ‘[name]‘ cannot be opened due to inaccessible files or insufficient memory or disk space.

I had this error on two databases on a dedicated SQL Server with enough memory, disk space and no faults. The obvious thing it to check the error log but this only showed SQL detected an issue and ran DBCC Checkdisk several times. Stuck at this point, I connected to the server and tried renaming the database file by appending a 1 to the end of the filename. It allowed me to do this to the suspect database but no to the others telling me the disk had a fault with the file.

select databasepropertyex(‘[Name]‘,’STATUS’) returned SUSPECT.

Scan disk, antivirus and disk surface checks completed, I ran ALTER DATABASE ‘[Name]‘ SET ONLINE to bring it back online with no errors.