Sladescross's Blog

Blogging about Sharepoint related stuff

SharePoint Proxies and the Topology Service and Load Balancing March 26, 2013

As part of the constructor for the BDC proxy, and for any Service Application Proxy utilizing SharePoint’s built-in load balancer system, a load balancer for the individual proxy is constructed and stored in a persisted field within the proxy object. This line is copied from within the .ctor method of BdcServiceApplicationProxy in .NET Reflector:

this.loadBalancer = new SPRoundRobinServiceLoadBalancer(serviceApplicationAddress);

As described, SPConnectedServiceApplication is a child persisted object of a Topology Service Application Proxy in the Hierarchichal Object Store (i.e. the Objects table in the Configuration Database). One way to see a list of SPConnectedServiceApplication objects and view their persisted state is to run the following query against the store (here, SPS2010_ConfigDB is the name of my farm’s configuration database):

,CAST([Properties] ASXML) ASProperties
FROM [SPS2010_ConfigDB].[dbo].[Objects] WITH (NOLOCK)
WHERE [ClassId] LIKE’FE65EF27-73F5-47C3-B23E-3D4FE5E10079′

If you wanted to determine the SPConnectedServiceApplication objects associated with a given Topology Service Application Proxy (i.e. their parent), you could determine the ID of the proxy (e.g. via (Get-SPTopologyServiceApplicationProxy).ApplicationProxies), and then run this query, replacing <ID from proxy> appropriately:

,CAST([Properties] AS XML) AS  Properties
FROM [SPS2010_ConfigDB].[dbo].[Objects] WITH (NOLOCK)
WHERE [ParentId] LIKE’<ID from proxy>’

With both of these queries, the Properties column will be a clickable XML document. Click it to open the serialized, persisted form of the object. For SPConnectedServiceApplication objects there will be a m_ApplicationAddresses field containing a list of endpoint Uris.

If you’d like to watch what happens when a service instance is removed on the publishing farm, run the SQL query above to see the current state of the SPConnectedServiceApplication and its list of endpoints. Then, stop a service instance for the service application and run the AddressesRefresh job on the consuming farm (Start-SPTimerJob job-spconnectedserviceapplication-addressesrefresh). After waiting about 20 seconds for the job to complete, run the SQL query again; you will see that the SPConnectedServiceApplication’s version has been updated and that the stopped service instance has been removed from its list of endpoints. Voíla! the Topology Service at work.


SharePoint Server 2010 Search Architecture March 16, 2013—-search-server-2010-and-fast-search—search-architecture.aspx

depositing each URL of each content  item in the MSSCrawlQueue table

a new process under Msssearch.exe  named Mssdmn.exe is spawned, and it is under this process that the  content source is both enumerated and then crawled

In Search 2010, the  number of crawl components across multiple servers can be increased as  the workload increases, with automatic distribution of addresses, or if  desired, specific addresses assigned to specific crawl components  through Host Distribution Rules.

The current propagation information is stored in the  Search_Service_Application database in the MSSPropagationTasks table, and the MSSPropagationLog table keeps records of past events and the query  components populate the MSSPropagationTaskCompletions table in response. The MSSPropagationErrors table will reflect any current deficiency, and the information there is entered every 10 minutes in a warning level event on the search admin  component’s server.

Since no single query component holds the complete index, the Query Processor service must manage disbursing the queries and processing the multiple results lists returned. This is accomplished, using a  round-robin load-balanced method, by one of the servers running the Search Query and Site Settings service (an Internet Information Services [IIS] service).



Various TroubleShooting For SharePoint Fixes August 20, 2012

Filed under: Fix,Sharepoint,Sharepoint SQL,Troubleshooting — sladescross @ 10:32 am

stsadm -o execadmsvcjobs

stsadm.exe -o deleteconfigurationobject -id <objectId>

SELECT     Id, Name, Status, Properties

FROM       Objects

WHERE (Name LIKE ‘SharePoint – 80′)

Stsadm –o osearch –action stop
Run iisreset / noforce
stsadm –o osearch – action start


Sharepoint 2010 SQL Maintenance July 21, 2011

Database maintenance white paper.

Database specifics for Sharepoint 2010



SQL Compile Error and Sharepoint Queries July 19, 2011

Gateway and tp_RowOrdinal



User Info Table Deletes Using Code November 3, 2010

Filed under: Sharepoint Security,Sharepoint SQL,User,UserInfo Table — sladescross @ 8:06 pm

The best approach to delete the orphaned AD users from the User Information List, you should write a custom code and remove the user using the SPWeb.SiteUsers API. This will delete the users from the top level site collection. If you are inheriting the site membership in the sub sites, deleting users from the top level site will delete the users from the sub site making sure deleted users are not available in the sub site’s people picker. If you have broken the site membership inheritance, you have to manually write a extra code to delete the users using the SPWeb.Users API.


This still leaves the user in the UserInfo table flagged as deleted tp_Deleted = 24.


Sharepoint 2010 SQL For SPField When Compressed August 13, 2010

Filed under: Sharepoint SQL,tCompressedString — sladescross @ 1:30 pm


Figured it out.

Skip the first 12 bytes based on, then skip another 2 bytes based on, then use System.IO.Compression.DeflateStream to decompress the remaining bytes and voila — readable text.

Of course this means you’ll need to use some .Net code for this — it can’t be done in raw TSql.

There is a company called Sharpest Tool Software that has a product called SPViews that generates SQL Views that do this for you. They put their 2010 beta version out there and it is working for me. Then you don’t have to deal with the compressed string or trying to parse the list XML.

Powershell for tCompressedString.


Sharepoint SQL For Documents

Filed under: Sharepoint SQL — sladescross @ 1:18 pm

– Fields

Each SharePoint site will have the data content of various types of data and queries, we also obtained data using the query as follows:

select tp_title from alllists
select tp_fields, tp_contenttypes from alllists

Retrieving Data Format Content

Once we see the list of data content in a SharePoint site, then we can also see the data format of the data content is, eg we want to see the data format Product_Price. Then we can also see the data format via a query. The format of data content is listed on the label AllList on the column tp_Fields and tp_ContentTypes. Tp_Fields column provides information on the field what they have on the data content. Meanwhile, the columns contain information tp_ContentTypes type daytanya. Mendaatkan following query to format the data content Product_Price:

select tp_fields, tp_contenttypes from alllists where tp_title = ‘Product_Price’

– Documents

select * from [dbo].[AllUserData] where master.dbo.fn_varbintohexstr(tp_ContentTypeId) not like ’0×0120%’ and master.dbo.fn_varbintohexstr(tp_DeleteTransactionId) is null and tp_IsCurrent = 1

Viewing Data Deleted

Any data or documents from the list will be stored by SharePoint to AllUserData in the table. If you want to get the data that are currently active, then we must add a filter, namely:

tp_iscurrent = 1 and tp_deletetransactionid = 0

When we want to get the data off, but the status is deleted, then we add a filtering as follows:

tp_iscurrent = 1 and tp_deletetransactionid 0

Each data item is removed from the document list or the value of the transaction will be recorded by SharePoint. For example, we have the data that are currently active and the status of the erased portal CMS Product_Price as follows:

select tp_id, tp_dirname, nvarchar3, nvarchar4, float1, tp_deletetransactionid from alluserdata
where tp_dirname like ‘cmdb / lists / product_price%’ and tp_iscurrent = 1 and tp_deletetransactionid 0


Sharepoint SQL For File Size June 12, 2010

Filed under: Sharepoint SQL,Sharepoint Tables,Size,SQL Document Size — sladescross @ 9:36 pm

select [DirName],[LeafName],
CAST((CAST(CAST(Size as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) AS ‘Size in MB’
FROM [WSS_Content_Intranet].[dbo].[AllDocs]
DirName Like ‘%1 Salisbury Sq 8 Salisbury Crt%’

select [filename],
sum(CAST((CAST(CAST(filesize as decimal(38,2))/1024 As
decimal(38,2))/1024) AS Decimal(38,2))) AS ‘Size in MB’
from (
select dirname + ‘/’ + leafname as [filename],
size as filesize,
from alldocs
select d.dirname + ‘/’ + d.leafname as [filename],
v.size as filesize,
from alldocs d
inner join alldocversions v on d.siteid = v.siteid
and =
) as results
inner join webs s on s.siteid = results.siteid
and = results.webid
where (filesize is not null
and filesize > 0)
and ExtensionForFile not like ‘%aspx%’ — Not Include Certian File Types
group by
order by 2 desc



Get every new post delivered to your Inbox.

Join 63 other followers