Daily Service Requests Report SCSM 2012

Daily Service Requests report is something which is used by service delivery managers and there is no out of box reports in SCSM to process this. 

Below SQL query will give you the count of following Service Requests assigned to Support Engineer's:

·         Active

·         Completed

·         Closed

Run this against DWDataMart database as per Microsoft’s suggest no query should be run directly on a transactional database in this case it ServerManager Database.

Select

 distinct userdim.DisplayName as AssignedToUser , count ( case ServiceRequest.status

when 'ServiceRequestStatusEnum.Active' THEN 'Active' end )  as Active,

 count ( case ServiceRequest.status

when 'ServiceRequestStatusEnum.Completed' THEN 'Completed' end )  as Completed,

Count ( case ServiceRequest.status

when 'ServiceRequestStatusEnum.Completed' THEN 'Closed' end )  as Closed

From    ServiceRequestDim ServiceRequest JOIN   

WorkItemDim workitem on ServiceRequest.EntityDimKey = workItem.EntityDimKey JOIN   WorkItemAssignedToUserFactvw assignedtouser

on workitem.WorkItemDimKey = assignedtouser.WorkItemDimKey JOIN   

UserDimvw userdim on assignedtouser.WorkItemAssignedToUser_UserDimKey = userdim.UserDimKey

 Where    assignedtouser.DeletedDate is null and ServiceRequest.createddate>= CONVERT(varchar(8), GETDATE()-1, 112)

 group by userdim.DisplayName

Output:

Assigned To

Active

Completed

Closed

Support Engineer A

2

0

0

Support Engineer B

1

0

0

Support Engineer C

1

0

0

Support Engineer D

2

0

0

 Please note this query will only pull info for Service requests

Missing server side dependencies SharePoint 2010

There is this known issue where you see Missing Server Side dependencies in the Health Analyzer in a clean install environment of SharePoint 2010. There are some blogs where they have Powers hell to sort this out but I found the Feature Admin Clean Up Tool from codeplex easy to use.

It’s an executable file and when you run, you just have to select the Web App and click on Find Faulty Feature to remove it.

Repeat the process for the other Web App’s if you’ve:

Link:  http://featureadmin.codeplex.com/releases/view/74732

Delete All versions from a Document Library in SharePoint 2010

The issue we had was the database size was growing at alarming rate which caused some serious concerns over SAN allocation.  The Size of Site was 140 GB and Document Library (Document Set) was 100 GB. We figured out based on a historical analysis of DB growth , there was an increase of  60 GB in 2-3 weeks of time for a sub site which was alarming.

This happened as versioning was enabled but no major versioning limit was set. That allows too many versions to be created in SharePoint based on the software boundaries. A bespoke application housing LOB Process and a bespoke timer solution somehow created too many versions as the limit was not set.

Run this Power Shell Command to change the number of version to be retained:

get-spWeb http://portal.contoso.com/Sites/Test/Sub-Test

ForEach-Object {ForEach($_.list in $_.Lists){If($_.EnableVersioning -eq $True){if($_.Title -eq "Shared Documents"){Write-host "List:"$_.Title "- List ID"$_.Id;$_.MajorVersionLimit = 4;$_.Update();ForEach($_.item in

$_.items){$_.item.URL;$_.update()}}}}}

Replace the URL with your Site URL and Shared Documents with the Document Library  Name you want

Change MajorVersionLimit to the number you require in my case I changed it to 4.

When that’s done, Connect to the corresponding Database Server via SQL Server Management Studio

Shrink the database by re-organizing the indexes, refer to DB maintenance guide before performing this on technet.

This released around 110 GB and we now have the Site Collection Size as 30 GB.

The Storage Metrics was showing incorrect numbers as We had June 2011 , August 2011 CU resolves this issue but the file size on Database was correct

Run this in Power Shell to get the size of all Site Collections to verify the numbers

Get-SPSite | select url, @{label="Size in MB";Expression={$_.usage.storage/1MB}} | Sort-Object -Descending -Property "Size in MB" | ConvertTo-Html -title "Site Collections sort by size" | Set-Content SiteCollection.html

Thanks to Todd and some other references this helped immensely, saved my day. 

Important part of governance is to define versioning , though we have the versioning defined on all other site collections, for this one it wasn't as the necessity was due to bespoke application, timer job , but this has been changed now after experiencing this issue. Now we have defined versioning on this site collection too.

Cannot delete orphaned Web Analytics Database in SharePont 2010.

Well, I started by running the following power shell script but this does the job only for Content Databases apparently.

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq
'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null) {
 Write-Host "Loading SharePoint Powershell Snapin"
 Add-PSSnapin "Microsoft.SharePoint.Powershell"
}
Get-SPDatabase | Where{$_.Exists -eq $false} | ForEach {$_.Delete()}
 
Got this error

Exception calling "Delete" with "0" argument(s): "An object in the SharePoint a
dministrative framework, "WebAnalyticsWarehouseDatabase Name=WebAnalyticsServiceApplication_ReportingDB", could not be deleted because other objects depend on it.  Update all of these dependants to point to null or different objects and retry this operation.  The dependant objec
ts are as follows: WebAnalyticsServiceApplication Name=Web Analytics Service Application"

There was some suggestion to delete the GUID from the configuration database using this but that leaves your environment in unsupportable state as this command has to be executed under the guidance / by MS Support Engineers some say so

Get-spdatabase |fl > C:\SPDatabase.txt
stsadm -o deleteconfigurationobject  -id *GUID*

I didn't want to leave the environment in unstable state I think no one does :). 

For me the ultimate resolution was to delete the Web Analytics Service Application  without the staging and reporting databases. I did disable the timer jobs before deleting the Web Analytics Service Application without the databases.

Health Analysis Job (Daily, Web Analytics Data Processing Service, Any Server)
Health Analysis Job (Daily, Web Analytics Web Service, Any Server)
Microsoft SharePoint Foundation Usage Data Processing
Microsoft SharePoint Foundation Usage Data Import
Microsoft SharePoint Foundation Site Inventory Usage Collection 


Issue resolved. This worked for me in my environment.

Now re-created the Web Analytics Service Application and associated the same with the old Web Analytics Staging and Reporting databases. I think the best way is to troubleshoot issues like this is sometimes you have to do things clean in fact more often.

The operating system returned error 21

The operating system returned error 21 (failed to retrieve text for this error. Reason: 15105) to SQL Server during a read at offset 0x00000000156000 in file 'D:\Data\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. (Microsoft SQL Server, Error: 823)

I got this error on the secondary database server which wasn’t in use thankfully. We had an issue with our SAN and as a result, the system database Master got corrupted when SAN came back.

After performing the following steps, my issue was resolved.

·         Start > Control Panel > Program and Features

·         Select SQL Server 2008 R2 (64 bit)

·         Click on Uninstall/Change

·         I selected Repair Option and followed the on-screen instructions.

Thankfully issue was resolved but I would suggest before you run this make sure you have good backups in place.

There is a brilliant article from Microsoft which talks about this at great length but I preferred the above approach as it didn’t have any other databases but if you’ve any other non-system database follow these steps here

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx