Powershell Mount and dismount content database
While working on the scripts for dismounting all content database for a DR situation found SPContentdatabase do not display OFFLINE databases. So had to work around to get the offline content database for each web application and disable it.
Code
Puts OFFLINE AND DISMOUNT all attached DBs
get-spwebapplication -identity webappURL | foreach {$SPContentDB = $_.ContentDatabases}
$SPContentDB | ForEach {Set-SPContentDatabase -Identity $_ -status disabled}
$SPContentDB | ForEach {Dismount-SPContentDatabase $_ -confirm:$false}
Found another interesting blog where it mentioned putting SharePoint content database offline is no the best practice rather putting the sitecollection max count to current number of sitecollections also avoids creating new sitecollection for the specified content database.
MOUNT DATABASE
mount-spcontentdatabase “DBName” -DatabaseServer “DBServer” -Webapplication webappURL
IncludeExceptionDetailInFaults Error message Sharepoint 2010
The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs.
To get the actual error:
INETMGR –> SharePoint Web Services –> Right Click Explore Security Token ServiceApplication –> Edit Web Config and add
<serviceDebug httpHelpPageEnabled=”true” includeExceptionDetailInFaults=”true” />
below <serviceThrottling maxConcurrentCalls=”65536″ maxConcurrentSessions=”65536″ maxConcurrentInstances=”65536″ />
SharePoint quota email has empty data
When SharePoint site collection exceeds the quota limit it sends the email to the owner. But in our environment the email has URL to _layouts/usage.aspx where the data are all empty. But the web analytic report has all data populated.
After research found out the there are 2 timer jobs for the usage analysis. 1) for data gathering and other for data processing. For some reason our processing time job email was disabled.
In Central Administration, on the Home page, click Monitoring.
On the Monitoring page, in the Reporting section, click Configure usage and health data collection.
On the Configure usage and health data collection page, in the Usage data collection section, enable usage data collection by selecting the Enable usage data collection text box.
In the Event Selection section, select the events to log by selecting the check box next to the events in the Events to log list
In the Usage data collection settings section, type the path of the folder you want usage and health information to be written to in the Log file location box. The path that you specify must exist on all farm servers.
Type the maximum disk space for the logs in gigabytes (between 1 and 20 GB) in the Maximum log file size box.
In the Health data collection section, select the Enable health data collection check box. To change the collection schedules, click Health Logging Schedule. A list of timer jobs that collect health data is listed. Click any of the timer jobs to change its schedule, or disable that timer job.
ref:msdn
But the main part was on Log Collection Schedule Click the log colletion schedule it will take to the page with 2 timer jobs that I mentioned before. Activate both the jobs and run it.
Now _layouts/usage.aspx will have valid data in it.
Query for Sharepoint information
Query for Sharepoint information
T-SQL Queries to get MOSS and WSS information:
- Top 100 documents in terms of size (latest version(s) only):
- Top 100 most versioned documents:
- List of unhosted pages in the SharePoint solution:
- List of top level WSS sites and their total size, including child sites in the portal:
- List of portal area and total number of users:
- List of top level and sub sites in the portal and the number of users:
- List of all portal area:
- List of the total portal area:
- List of all top level and sub sites in the portal:
- List of the total top level and sub sites in the portal:
- List of all list/document libraries and total items:
- List of document libraries and total items:
- List of image libraries and total items:
- List of announcement list and total items:
- List of contact list and total items:
- List of event list and total items:
- List of all tasks and total items:
- List of all InfoPath form library and total items:
- List of generic list and total items:
Applies To:
- Microsoft SharePoint Portal Server 2003
- Microsoft Windows SharePoint Services
- Microsoft® ASP. NET 1.x, 2.x
- Microsoft® Visual Studio® .NET 2003
- Top 100 most versioned documents:
SELECT TOP 100 Webs.FullUrl As SiteUrl, Webs.Title ‘Document/List Library Title’, DirName + ‘/’ + LeafName AS ‘Document Name’,
COUNT(Docversions.version)AS ‘Total Version’, SUM(CAST((CAST(CAST(Docversions.Size as decimal(10,2))/1024
As decimal(10,2))/1024) AS Decimal(10,2)) ) AS ‘Total Document Size (MB)’, CAST((CAST(CAST(AVG(Docversions.Size) as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) AS ‘Avg Document Size (MB)’ FROM Docs INNER JOIN DocVersions ON Docs.Id = DocVersions.Id
INNER JOIN Webs On Docs.WebId = Webs.Id INNER JOIN Sites ON Webs.SiteId = SItes.Id WHERE Docs.Type <> 1 AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’) AND (LeafName NOT LIKE ‘%.xfp’) AND (LeafName NOT LIKE ‘%.dwp’) AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’) AND (LeafName NOT LIKE ‘%.css’)
GROUP BY Webs.FullUrl, Webs.Title, DirName + ‘/’ + LeafName ORDER BY ‘Total Version’ desc, ‘Total Document Size (MB)’ desc
- List of unhosted pages in the SharePoint solution:
select Webs.FullUrl As SiteUrl, case when [dirname] = ” then ‘/’+[leafname] else ‘/’+[dirname]+’/’+[leafname]
end as [Page Url], CAST((CAST(CAST(Size as decimal(10,2))/1024 As decimal(10,2))/1024) AS Decimal(10,2))
AS ‘File Size in MB’ FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id where [type]=0 and [leafname] like (‘%.aspx’)
and [dirname] not like (‘%_catalogs/%’) and [dirname] not like (‘%/Forms’) and [content] is not null
and [dirname] not like (‘%Lists/%’) and [setuppath] is not null order by [Page Url];
- List of top level WSS sites and their total size, including child sites in the portal:
select FullUrl As SiteUrl, CAST((CAST(CAST(DiskUsed as decimal(10,2))/1024 As decimal(10,2))/1024) AS Decimal(10,2))
AS ‘Total Size in MB’ from sites Where FullUrl LIKE ‘%sites%’ AND fullUrl <> ‘MySite’ AND fullUrl <> ‘personal’
- List of portal area and total number of users:
select webs.FullUrl, Webs.Title, COUNT(WebMembers.UserId) As ‘Total User’
from Webs INNER JOIN WebMembers ON Webs.Id = WebMembers.WebId Where fullurl NOT like ‘%sites%’
AND fullUrl <> ‘MySite’ AND fullUrl <> ‘personal’ Group BY webs.FullUrl, Webs.Title Order By ‘Total User’ desc
- List of top level and sub sites in the portal and the number of users:
select webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) As ‘Total User’
from Webs INNER JOIN WebMembers ON Webs.Id = WebMembers.WebId where fullurl like ‘%sites%’
AND fullUrl <> ‘MySite’ AND fullUrl <> ‘personal’ Group BY webs.FullUrl, Webs.Title Order By ‘Total User’ desc
- List of all portal area:
select Webs.FullUrl As [Site Url], Title AS [Area Title] from Webs
Where fullurl NOT like ‘%sites%’ AND fullUrl <> ‘MySite’ AND fullUrl <> ‘personal’
- List of the total portal area:
select COUNT(*)from Webs Where fullurl NOT like ‘%sites%’ AND fullUrl <> ‘MySite’ AND fullUrl <> ‘personal’
- List of all top level and sub sites in the portal:
select Webs.FullUrl As [Site Url], Title AS [WSS Site Title] from webs
where fullurl like ‘%sites%’ AND fullUrl <> ‘MySite’ AND fullUrl <> ‘personal’
- List of the total top level and sub sites in the portal:
select COUNT(*) from webs where fullurl like ‘%sites%’ AND fullUrl <> ‘MySite’ AND fullUrl <> ‘personal’
- List of all list/document libraries and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], case tp_servertemplate when 104 then ‘Announcement’ when 105 then ‘Contacts’
When 108 then ‘Discussion Boards’ when 101 then ‘Docuemnt Library’ when 106 then ‘Events’ when 100 then ‘Generic List’
when 1100 then ‘Issue List’ when 103 then ‘Links List’ when 109 then ‘Image Library’ when 115 then ‘InfoPath Form Library’
when 102 then ‘Survey’ when 107 then ‘Task List’ else ‘Other’ end as Type, tp_title ‘Title’, tp_description As Description,
tp_itemcount As [Total Item] from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate IN (104,105,108,101, 106,100,1100,103,109,115,102,107,120) order by tp_itemcount desc
Note: the tp_servertemplate field can have the following values: - 104 = Announcement
- 105 = Contacts List
- 108 = Discussion Boards
- 101 = Document Library
- 106 = Events
- 100 = Generic List
- 1100 = Issue List
- 103 = Links List
- 109 = Image Library
- 115 = InfoPath Form Library
- 102 = Survey List
- 107 = Task List
- List of document libraries and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 101 order by tp_itemcount desc
- List of image libraries and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 109 — Image Library order by tp_itemcount desc
- List of announcement list and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 104 — Announcement List order by tp_itemcount desc
- List of contact list and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 105 — Contact List order by tp_itemcount desc - List of event list and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 106 — Event List order by tp_itemcount desc
- List of all tasks and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 107 — Task List order by tp_itemcount desc - List of all InfoPath form library and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 115 — Infopath Library order by tp_itemcount desc
- List of generic list and total items:
select case when webs.fullurl = ” then ‘Portal Site’ else webs.fullurl end as [Site Relative Url],
webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 100 — Generic List order by tp_itemcount desc
Filter Content Query Web Part
Add the SharePoint 2010 content query webpart. Edit filter properties:
[PageQueryString : <Query string name >]
[PageFieldValue: <field display name>]
eg:
Load Balancing Incoming Email in SharePoint 2007/2010
Ref:
After a recent bout of teaching and testing folks, it became obvious that we are somewhat lacking in our information about some of the finer points of configuring incoming email in SharePoint 2007. So, below are some notes that I made around some of the SMTP and Exchange configuration steps that are necessary to get things working well.
Items 1 through 3 are applicable exclusively to routing incoming mail, and are all about configuring the IIS SMTP Service that you would typically run on one of your SharePoint web front end servers. Remember you configure the SMTP Service in Windows Server 2003 by using the IIS Manager snap-in. It also assumes that you have Exchange 2007 in your organization and a Send Connector already configured for an Address Space that a) includes the name of the server (like wfe1.foo.local) and the network smart host mapping in that connector is configured to send it to the same server. For example, the Send Connector would have an Address Space of wfe1.foo.local and a smart host configured on the network tab of wfe1.foo.local.
1. In the Access…Relay Restrictions settings you can restrict relay to ONLY the Exchange server that is going to route messages to the SharePoint farm, and deny all others. You can do the same for Access…Connection Control.
2. In Delivery…Advanced, if you DON’T have to use the same delivery name in Masquerade domain that is configured in the SharePoint Incoming Email Settings in Central Admin (like @wfe01.foo.local). Masquerade domain just allows you to have a different domain name displayed on outgoing messages from the SMTP server (like non delivery messages).
3. In Delivery…Advanced Smart host, you can leave that field blank and incoming messages are still delivered.
4. If you are load balancing email between multiple SharePoint servers running the IIS SMTP service, it can beVERY difficult to get working. Assume for example that you have two WFEs in the farm, and their server names are wfe1.foo.local and wfe2.foo.local. You want to use both of them for routing incoming mail so you have some high availability for incoming email in the farm. Also assuming you have Exchange 2007 SP1 in your organization and that’s what your users use for their email server.
To begin with, you need some load balancing mechanism in place in the farm. You should already have this if you have multiple web front end servers. For purposes of our conversation we’ll assume you have NLB on your web front ends and they use a Virtual IP (VIP) of 10.10.10.100.
Next, you need an alias for those SMTP servers; that’s the first part of the name that will become part of the document library email address. Lets assume you want to use “mossmail” as your SMTP servers email alias. That means that the email addresses for your document libraries will be something likeLibraryName@mossmail.foo.local (since in our example our local domain is called ‘foo.local’). We now have all of the information we need to configure our servers.
Start with DNS – create a Host (A) record called mossmail in the foo.local zone; give it an address of 10.10.10.100. Now any requests for mossmail.foo.local will go to the VIP address of 10.10.10.100, and NLB will be responsible for passing the request to either wfe1 or wfe2. NOTE: for routing email internally only, you DO NOT need an MX record.
In Exchange, we need a Send Connector. The Send Connector is where we configure a set of mail addresses that Exchange should look for, and the server(s) it should send those messages to. In this case we’ll create a new Send Connector and in the Address Space we’ll add a new SMTP address space of mossmail.foo.local. In the Network settings for the connector, we’ll configure it to route the mail to following smart hosts: mossmail.foo.local. So at this point if a user opens up Outlook and sends a message toLibraryName@mossmail.foo.local, that message is sent to Exchange. Exchange looks at the Send Connector and sees that it should route messages for mossmail.foo.local to a “server” called mossmail.foo.local. It looks up that address in DNS, gets the VIP of 10.10.10.100 back and sends the SMTP message to that address. NLB picks up the request and routes it to either wfe1 or wfe2.
The final piece of the configuration happens on the the web front end servers running the SMTP service – in our case, wfe1 and wfe2. First, you need to make sure the SMTP service is configured to start automatically. Next, you’ll open up the IIS Manager snap-in, expand it so you can see the Default SMTP Virtual Server, right-click on it and select Properties. Click on the Access tab and then click on the Relay button. In the dialog that opens up, enter the IP address of the Exchange server that is going to be routing SMTP messages to the web front end. It is a best practice to limit the ability to relay only to servers that you trust. So, click the top radio button that says Only the list below, click the Add… button, and type in the IP address of the Exchange Server. Click OK to close out the Relay dialogs and save those changes. Note that you can also make the same change using the Connection button. It will just further insulate you from exposure if you think you need it. Next, click on the Delivery tab, and then the Advanced… button. This next step is not strictly required, but it is a good practice in terms of keeping your naming schemes consistent from an SMTP perspective. In the Fully-qualified domain name edit box, type mossmail.foo.local. Click the OK button to save those changes, then click the OK button to close out the Properties dialog. Finally, the last thing needed is a domain alias, so that the SMPT server knows that it is responsible for routing email for the mossmail.foo.local domain. In the IIS Manager snap-in, expand the Default SMTP Virtual Server tree and click on the Domains object. Right-click on it and select the New…Domain… menu. A wizard will start up; click the Alias radio button and then the Next button. Type mossmail.foo.local in the Name edit field then click the Finish button. Finally, YOU MUST restart the SMTP service for these changes to take effect.
The rest of the configuration takes place in SharePoint’s Central Admin…Incoming Email Settings now. While I won’t walk through that step-by-step, I will just say that in the E-mail server display address edit field, type in mossmail.foo.local when you are saving your changes. Now, create a new or mail enable an existing document library, give it an email address, and send it a message with an attachment. You should hopefully be good to go at this point.
5. For outgoing mail, like when you create an alert and you receive the alert confirmation email, it doesn’t use any settings on the local SMTP servers. Instead it relies entirely upon the SMTP server name that was configured in the Central Admin Outgoing email settings.
So the net is pretty simple:
For incoming emails, you can plug in the name of a single server (like img5.foo.local) or a load balanced name (like mossmail.foo.local). Each one of those servers (either the single server or all of the servers in the load balanced name mossmail.foo.local) need to have their SMTP server configured and running. The setup is extensive for a load balanced SMTP server scenario, but very quick and easy for a single server.
Pasted from <http://blogs.technet.com/b/speschka/archive/2009/05/10/load-balancing-incoming-email-in-sharepoint-2007.aspx>
SharePoint Live 