Identify the size and document count of a single SharePoint Site

The following query is useful, if you have a SharePoint site which uses one content database and all websites hang off the one top url.  The script can be used to identify the total size of each site and the number of documents contained in the site.  

USE [<YourContentDatabaseName>]; — don’t forget the semi-colon!

with
folderhierachy as(
select f.fullurl, (((sum(cast(d.size as bigint))) / 1024) /1024) as sizeinMB, count(d.Id) as DocCount
from webs f, docs d, lists l
where
d.webid = f.id
and
l.tp_id = d.listid
and
d.size > 0
and
(d.LeafName NOT LIKE ‘%.stp’)
AND (d.leafname NOT LIKE ‘%.aspx’)
AND (d.leafname NOT LIKE ‘%.xfp’)
AND (d.leafname NOT LIKE ‘%.dwp’)
AND (d.leafname NOT LIKE ‘%template%’)
AND (d.leafname NOT LIKE ‘%.inf’)
AND (d.leafname NOT LIKE ‘%.css’)
group by f.FullUrl
)
select substring(fullurl,1,charindex(‘/’,fullurl)) as Url, sum(sizeinMB) as “Size In MB”, sum(DocCount) as “Document Count”
from folderhierachy
group by substring(fullurl,1,charindex(‘/’,fullurl))
order by Url, [Size In MB] desc;

 

Red Gate SQL Monitor Metric – New logins to the Sysadmin role.

Red Gate SQL Monitor Metric – New logins to the Sysadmin role.

Another one of my SQL Monitor custom metrics has been published by Red Gate.  This one generates an alert when the count of server principles who are added to the sysadmin server role changes.  It is very useful when multiple members of an IT team may have a DBA support function and are inclined to simple add users with sysadmin roles. This metric can be used to alert when this happens so it can be checked and verified in case it causes a security risk.

Weekly SQL: Filter on SP_WHO2 in a table.

This is a new section of my blog, where I plan to publish a SQL script on a weekly basis.  These will be scripts I either use on a daily basis or ones which I find particularly interesting and worth sharing.

This week, the following script is really useful for finding out who is connected to a particular database. It uses the SP_WHO2 special procedure and allows the data to be filtered.  I am not sure where I got this script from, but it is not mine, but I must use it every day:

Run this on the database you wish to query:

DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT *
FROM @Table
where dbname = db_name(db_id())

You can add to the where clause to filter on the different columns.

I also use this script if I am restoring a database and need to quickly kill of any users.  By adding the following extra code after the above, it will generate a the kill commands which I can copy and paste to a new query window:

–Generate a kill script
SELECT ‘kill ‘ + cast(spid as varchar(3))
FROM @table
WHERE dbname = db_name(db_id())

First Redgate SQL Metric published

First Redgate SQL Metric published

A while ago, my first Red Gate SQL metric was published for use with Red Gates excellent SQL Monitor software.  This metric allows you to be alerted when a transaction log grows over a defined threshold.  This can be particularly useful if a process is stopping the log from truncating or the transaction backups have not been running successfully.

SPN Setup for Double hop from Client (IE) to IIS 8 to SQL Server (2012)

The following is the required steps to set up kerberos authentication for a client to sql server.

This is a rough post, created purely for self reference.  A lot of this work is talked about at the following location http://blogs.msdn.com/b/psssql/archive/2011/02/21/sharepoint-adventures-using-kerberos-with-the-report-server.aspx

On IIS:

  • Open ISS Manager and go to application pools.
  • Set the correct application pool to use a dedicated user – right click and slect advanced settings. Change identity to the domain user required. If you’re not sure what application pool is the being used by the web app, stop them all and then start them individually, whilst refreshing the web app. When the correct one is used, the web app should work (somewhat!).
  • Next, go to the web application web site in IIS manager. Double click authentication and enable ASP.NET Impersonation (right click and select enable).
  • Right click on Windows Authentication and enable too.
  • Right click on Windows authentication and select Advance Settings and untick Enable Kernel-mode Authentication.
  • Click ok then right click on Windows Authentication and select providers.
  • Add Negotiate:Kerberos from the available providers drop down list.  Make sure this is the top of the list of enabled providers, above negotiate.

On a domain connected computer:

Add the SPN’s:

  • Open a cmd window and add an spn for the service account that the web app is running under. This is the domain account you set for the application pool.  This SPN should be for HTTP service for the URL of the server. In this case below the server name:
    setspn -A HTTP/WebserverName domain\domainuser
    i.e: setspn -A HTTP/SGBB1234 CONTOSO\WebAppServiceUser
  • Add the SPN for the Fully qualified domain name too, i.e.:
    setspn -A HTTP/SGBB1234.CONTOSO.UK.COM CONTOSO\WebAppServiceUser
  • Now check the spn is set for the SQL Server Service account. This is the account the SQL Server is running under:
    setspn -l SQLServerServiceAccount
  • If nothing is listed, then add the spn for the database instance using port number and instance name:
    setspn -A MSSQLSvc/ServerNameFQN:portnumber domain\SQLServiceAccount
    setspn -A MSSQLSvc/ServerNameFQN:InstanceName domain\SQLServiceAccount
    i.e.:
    setspn -A MSSQLSvc/DBServer.Contoso.UK.COM:1453 contoso\Account1
    setspn -A MSSQLSvc/DBServer.Contoso.UK.COM:Instance1 contoso\Account1

Once all is done, run the command setspn -X to check for any duplicates. Remove any duplicate SPN’s from the incorrect location.

Now setup Delegation:

Once the SPN’s are set then the delegation tab will be visible in AD for users:

  • Open AD Users and Computers and navigate to the domain user used to run the app pool web service.
  • Click on Delegation tab and select Trust this user for delegation any service, or to a specific service if more granulatrity required.
  • Do the same for the IIS server in AD too.

Once all the above is complete, restart IIS.

On the client pc, make sure no kerberos tickets are cached and causing a incorrect response.  Do this by opening a cmd window and typing “klist purge”. It should say tickets cleared.

Now do a ctrl + F5 refresh in IE, and the kerberos authentication should work!

Hopefully that is it!

Cannot complete this action. Please try again. SharePoint 2007 Default.aspx Problem

A user tried to access their site on a SharePoint 2007 server, but was unfortunately greated with the wonderful, unhelpful message of:

Cannot complete this action.  Please try again.

This error occurred when trying to access the default page of the site i.e.: http://Server/subsite/default.aspx

A quick search around the internet and it seems many people have had this same problem at some point, but no one could find a solution that wasn’t as drastic as deleting and creating the site, restoring the server or even trashing and rebuilding SharePoint!! On a live production system, this wasn’t practical and would require a bit of work to sort out!

I run one server which consists of a main site for my project, with sub sites for each team on that project.  The problem was occurring for just one team sub site.  With a little poking around, I found I could still navigate to all the sites content and its settings, via the parents Content and Structure options – (“Site Actions” –> “Manage Content and Structure”).

Using this navigation option, I went to the master page viewer and tried to upload the master.default page from my fail over server which was working perfectly.  This didn’t work and caused a new “File not Found” error when trying to view the home page of the site.  I restored the previous version to get back to square one.

With my only option looking to be to restore the subsite from the backup, I tried one last thing.  Within the Manage Content and Structure pages, I deleted the Default.aspx file from the problem site.  I then copied a default.aspx page from a known working sub site.

On refreshing the problem site, this solution worked fine and I was once again able to navigate to  the problem site with no errors.

I am still not sure why the original problem occurred, so if anyone has any ideas I would be most grateful.

Restarting a stalled workflow where the delayActivity has stopped responding

I released a number of changes to my projects workflows, making sure I had released them as a new dll version in the GAC.  The workflows use a DelayActivity to “sleep” for about four hours, before waking and checking if the due dates of the tasks involved are out of date.  If so, then an email is sent to the task owner.  After I released the new workflow version, new workflows instances would start fine, but after the four hour delay, an ominous error was found in the Workflow History and now email escalations occurred:

“Workflow Failed to run”.

Checking the ULS log, there was also the following error:

“OWSTIMER.EXE (0x07EC) 0x0E58 Windows SharePoint Services Workflow Infrastructure 98d8 Unexpected System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SharePoint.Workflow.SPWorkflowManager.RunWorkflowElev(SPWorkflow originalWorkflow, SPWorkflow workflow, Collection`1 events, SPRunWorkflowOptions runOptions)”

A quick Google search brought up this old Microsoft Blog post where a number of users where experiencing the same problems.  Although the bug has been fixed in later hotfixes and service packs (which my system already had), one of the causes stood out as the reason for my problem.  That is; when releasing new versions of code into the GAC, the timer service is still using old versions which are non existent and so it falls over.

One recommendation to prevent this problem is to restart the Timer service, which is fine, but it doesn’t fix the workflows already running which are experiencing the problem.  However, reading through the blog someone mentioned being able to view the status of the timer jobs in the content database using the following SQL command:

SELECT * FROM ScheduledWorkItems Where id = ’20EF2AC4-64D1-47E4-91C1-4E5F4BFC7CD4′ – where this is the problem workflow ID.

When you run this command, you will notice the DeliveryDate.  This is the time the system will wake up the workflow. There is also the column “InternalState” which for problem workflows seemed to always be set to “9″ and for ok workflows set to “8″.

Using a broken test workflow, I did some hacking around, and changed the internal state value of the problem workflow to 8, and set the DeliveryDate value to sometime shortly in the future.  I also, noted that the time originally in this field was 1 hour behind what I expected, and assume this is because I am running under GMT +1 timezone.  Therefore I set the DeliveryDate to be one hour behind the time in the future, or else I would be waiting another hour longer than I expected! Hope that makes sense!

I then restarted the Timer service on the SharePoint server and low and behold, the problem workflow shortly “woke up” and did its job as expected when the DeliveryDate time had passed.

I changed all the other problem workflows in the same way and they all came back to life with no problems.

I have now made sure I have amended my deployment procedures of new workflow versions to restart the timer service so this problem shouldn’t happen again.

Follow

Get every new post delivered to your Inbox.