Using stsadm to stop the osearch service will blat all the indexes:
In command prompt try running:
taskkill.exe /F /IM mssearch.exe /T
– this will terminate all mssearch.exe processes and their dependencies and then restart the mssearch.exe service again ( this might take a couple of minutes – you can check in Task manager when the service is up again).
If the above does not resolve the issue, then reboot the index server
Saturday, 6 March 2010
PORTS 56737 AND 56738
OPEN FIREWALL PORTS 56737 AND 56738
Submitted Request to Open firewall ports 56737 and 56738 in Approval Environment to enable Office Server web service to communicate between Index and WBEs and WFEs. Hoping this will resolve 'Server Error' warnings in Search Admin
Also:
From SCA >> Operations >> Services on Server:
I am unable to open the Office SharePoint server Search Configuration page on WFE01
When I try and open this I get the error below:
Also unable to open for WFE02 and WFE03.
Can open for the Index server
After getting the ports 56737 and 56738 openeing between the WBEs and WFEs:
Now the ports have been opened I am picking up WFE01 and WFE02 but the recently added WFE03 is still in an error state
Can browse to the web service on WFE01 over http on 56737:
I can browse to the web service on WFE01 over https on 56738:
Same behaviour when browsing to WFE02
Can browse over http on 56737 to WFE03 but on https over 56738 it cannot connect:
To do this:
Save the existing certificate which you can then use for the rollback:
Before using SelfSSL you can save the existing Certificate -> Open IISManager -> Select the Properties of the Office Server Web Services WebSite -> select the Directory Security tab -> select Server Certificate -> select Export the current certificate to a .pfx file and follow the next screens to save the certificate
Stop the Office SharePoint Services Search service. To do this, follow these steps:
Click Start, click Run, type cmd , and then click OK.
At the command prompt, type net stop osearch, and then press ENTER.
Type exit to exit the command prompt.
Download and install the IIS 6 Resource Toolkit (http://www.microsoft.com/downloads/details.aspx?FamilyID=56FC92EE-A71A-4C73-B628-ADE629C89499) to a workstation / server.
Copy the SelfSSL tool to the server WFE03. Click Start, click Run, type cmd , and then click OK.
Navigate to the location of the IIS 6.0 Resource Kit Tools (default location is: C:\Program Files\IIS Resources\SelfSSL)
Create and assign a new certificate using the selfssl tool with the following parameters:
At the command prompt, type
selfssl /s:951338967 /v:9999 /P:56738
-951338967 = the ID of the Office Server Web Services site on a 64bit server (for 32bit servers it is: 1720207907)
-9999 = new certificate will be valid for 9999 days ( 28 years )
-56738 = SSL port for the Office Server Web Services website.
Start the Office SharePoint Services Search service. To do this, follow these steps:
At the command prompt, type net start osearch, and then press ENTER.
Type exit to exit the command prompt.
Submitted Request to Open firewall ports 56737 and 56738 in Approval Environment to enable Office Server web service to communicate between Index and WBEs and WFEs. Hoping this will resolve 'Server Error' warnings in Search Admin
Also:
From SCA >> Operations >> Services on Server:
I am unable to open the Office SharePoint server Search Configuration page on WFE01
When I try and open this I get the error below:
Also unable to open for WFE02 and WFE03.
Can open for the Index server
After getting the ports 56737 and 56738 openeing between the WBEs and WFEs:
Now the ports have been opened I am picking up WFE01 and WFE02 but the recently added WFE03 is still in an error state
Can browse to the web service on WFE01 over http on 56737:
I can browse to the web service on WFE01 over https on 56738:
Same behaviour when browsing to WFE02
Can browse over http on 56737 to WFE03 but on https over 56738 it cannot connect:
So, next step is to use the SelfSSL tool to reinstall the Office Server Web Services Certificate
To do this:
Save the existing certificate which you can then use for the rollback:
Before using SelfSSL you can save the existing Certificate -> Open IISManager -> Select the Properties of the Office Server Web Services WebSite -> select the Directory Security tab -> select Server Certificate -> select Export the current certificate to a .pfx file and follow the next screens to save the certificate
Stop the Office SharePoint Services Search service. To do this, follow these steps:
Click Start, click Run, type cmd , and then click OK.
At the command prompt, type net stop osearch, and then press ENTER.
Type exit to exit the command prompt.
Download and install the IIS 6 Resource Toolkit (http://www.microsoft.com/downloads/details.aspx?FamilyID=56FC92EE-A71A-4C73-B628-ADE629C89499) to a workstation / server.
Copy the SelfSSL tool to the server WFE03. Click Start, click Run, type cmd , and then click OK.
Navigate to the location of the IIS 6.0 Resource Kit Tools (default location is: C:\Program Files\IIS Resources\SelfSSL)
Create and assign a new certificate using the selfssl tool with the following parameters:
At the command prompt, type
selfssl /s:951338967 /v:9999 /P:56738
-951338967 = the ID of the Office Server Web Services site on a 64bit server (for 32bit servers it is: 1720207907)
-9999 = new certificate will be valid for 9999 days ( 28 years )
-56738 = SSL port for the Office Server Web Services website.
Start the Office SharePoint Services Search service. To do this, follow these steps:
At the command prompt, type net start osearch, and then press ENTER.
Type exit to exit the command prompt.
The result in Search Admin:
How to Enable BLOB Caching in MOSS 2007
ENABLE BLOB CACHING
BLOB Caching is a feature that serializes large objects to disk on the Web Front Ends to avoid database round-tripping. BLOB Caching would prove beneficial for those who work with large file sizes as this improves page delivery time as cache stores files on front-end server and reduces database traffic. To enable BLOB Caching you need to edit the web.config file and changes will be applied to all site collections within the web application.
• Open web.config file of the Establishments Web Application.
• In the web.config file, find the line starting: 'Blobcache location = '
• Change enabled parameter, from "false" to "true"
• Replace the section:
path="\.(gif
jpg
png
css
js)$"
with:
path="\.(gif
jpg
jpeg
jpe
jfif
bmp
dib
tif
tiff
ico
png
wdp
hdp
css
js
asf
avi
flv
m4v
mov
mp3
mp4
mpeg
mpg
rm
rmvb
wma
wmv)$"
• This means that when enabled on Live the size of the Blob cache on the WFes must be monitored. If the size exceeds 10GB then the Blob size will have to be increased
• Save the file, and close the file
• IISReset to enable the cache (MS state this is not necessary)
• The account used to manage the files is the account used as the App Pool ID (svcmossappid) – this folder must have ACLs that grant full privilege to this account (Modify rights)
BLOB Caching is a feature that serializes large objects to disk on the Web Front Ends to avoid database round-tripping. BLOB Caching would prove beneficial for those who work with large file sizes as this improves page delivery time as cache stores files on front-end server and reduces database traffic. To enable BLOB Caching you need to edit the web.config file and changes will be applied to all site collections within the web application.
• Open web.config file of the Establishments Web Application.
• In the web.config file, find the line starting: 'Blobcache location = '
• Change enabled parameter, from "false" to "true"
• Replace the section:
path="\.(gif
jpg
png
css
js)$"
with:
path="\.(gif
jpg
jpeg
jpe
jfif
bmp
dib
tif
tiff
ico
png
wdp
hdp
css
js
asf
avi
flv
m4v
mov
mp3
mp4
mpeg
mpg
rm
rmvb
wma
wmv)$"
• This means that when enabled on Live the size of the Blob cache on the WFes must be monitored. If the size exceeds 10GB then the Blob size will have to be increased
• Save the file, and close the file
• IISReset to enable the cache (MS state this is not necessary)
• The account used to manage the files is the account used as the App Pool ID (svcmossappid) – this folder must have ACLs that grant full privilege to this account (Modify rights)
Configuring Infopath forms in a MOSS 2007 farm
DETAILING HOW TO PUBLISH INFOPATH FORMS AND DOCUMENTING THE CONFIGURATION STEPS REQUIRED ACROSS THE FARM:
1 - Install enterprise features in SharePoint SCA
2 - Activate enterprise features in SCA – site settings – site collection features
3 - Activate Enterprise features in Site Collection (may need to deactivate then reactivate)
4 - Create browser-enabled forms/re-publish forms
5 - In the Form Library – make sure that in Settings à Advanced settings à In the Browser-enabled documents field make sure that Display as a web page is selected
1 - Install enterprise features in SharePoint SCA
2 - Activate enterprise features in SCA – site settings – site collection features
3 - Activate Enterprise features in Site Collection (may need to deactivate then reactivate)
4 - Create browser-enabled forms/re-publish forms
5 - In the Form Library – make sure that in Settings à Advanced settings à In the Browser-enabled documents field make sure that Display as a web page is selected
Sunday, 28 February 2010
First install of SharePoint 2010
HOW I GOT SHAREPOINT 2010 RC INSTALLED:
Deployed W2k8 R2 onto 2 virtual servers on the same LAN both with Internet connectivity
Server 1 will be the DC
Server 2 will be the SharePoint Server
Server 1 had 1GB RAM and 2 had 2Gb RAM (Install failed at Creating Sample Data in the PSConfig Wizard when it only had 1GB RAM – recommended is 4GB RAM)
Server 1 and 2 have 20GB disk space (After installed SharePoint on Server2 I immediately had a low disk space warning even though there was still 6GB on the drive. The warning was – Availabe disk space is less than 5 times the value of Physical memory. This does not provide enough room for a full memory dump with continued operation. It could also cause problems with the Virtual Memory swap file). Recommended is 40GB
Ran Windows Updates on both
Required permissions:
In order to install SharePoint Server 2010, the logon account that you are using on the virtual machine must be a member of:
• The local Administrators group on the virtual machine
• The SQL Server dbcreator fixed server role
• The SQL Server securityadmin server role
On Server 1 – run DCpromo and create a new domain 2010.net
On Server 2 – join to domain
You can install SharePoint on a DC but some extra config is required:
Start Windows PowerShell with the Run as administrator option and run the following commands to enable deployment on a domain controller:
$acl = Get-Acl HKLM:\System\CurrentControlSet\Control\ComputerName
$person = [System.Security.Principal.NTAccount]"Users"
$access = [System.Security.AccessControl.RegistryRights]::FullControl
$inheritance = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit"
$propagation = [System.Security.AccessControl.PropagationFlags]::None
$type = [System.Security.AccessControl.AccessControlType]::Allow
$rule = New-Object System.Security.AccessControl.RegistryAccessRule($person, $access, $inheritance, $propagation, $type)
$acl.AddAccessRule($rule)
Set-Acl HKLM:\System\CurrentControlSet\Control\ComputerName $acl
On the virtualization server, create an installation point that contains the SharePoint Server software or provide media, such as an ISO image, that can be accessed from the virtual machine.
On Server 2 – run the SharePoint.exe and run the Microsoft SharePoint 2010 Products Preparation Tool. This tool checks for required products and updates. (Must have internet connection for this to succeed)
Run the Setup Wizard
Interestingly when the Wizard closes the Template Selection Page opens (_layouts/templatepick.aspx) prompting you to create a site in the default SharePoint Web App
From the Start menu - browse to Central Admin.
TIPS:
If you are prompted for your user name and password, perform the following procedures:
• Add the SharePoint Central Administration Web site to the list of trusted sites
• Disable Internet Explorer Enhanced Security settings
If you receive a proxy server error message, perform the following procedure:
• Configure proxy server settings to bypass the proxy server for local addresses
Deployed W2k8 R2 onto 2 virtual servers on the same LAN both with Internet connectivity
Server 1 will be the DC
Server 2 will be the SharePoint Server
Server 1 had 1GB RAM and 2 had 2Gb RAM (Install failed at Creating Sample Data in the PSConfig Wizard when it only had 1GB RAM – recommended is 4GB RAM)
Server 1 and 2 have 20GB disk space (After installed SharePoint on Server2 I immediately had a low disk space warning even though there was still 6GB on the drive. The warning was – Availabe disk space is less than 5 times the value of Physical memory. This does not provide enough room for a full memory dump with continued operation. It could also cause problems with the Virtual Memory swap file). Recommended is 40GB
Ran Windows Updates on both
Required permissions:
In order to install SharePoint Server 2010, the logon account that you are using on the virtual machine must be a member of:
• The local Administrators group on the virtual machine
• The SQL Server dbcreator fixed server role
• The SQL Server securityadmin server role
On Server 1 – run DCpromo and create a new domain 2010.net
On Server 2 – join to domain
You can install SharePoint on a DC but some extra config is required:
Start Windows PowerShell with the Run as administrator option and run the following commands to enable deployment on a domain controller:
$acl = Get-Acl HKLM:\System\CurrentControlSet\Control\ComputerName
$person = [System.Security.Principal.NTAccount]"Users"
$access = [System.Security.AccessControl.RegistryRights]::FullControl
$inheritance = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit"
$propagation = [System.Security.AccessControl.PropagationFlags]::None
$type = [System.Security.AccessControl.AccessControlType]::Allow
$rule = New-Object System.Security.AccessControl.RegistryAccessRule($person, $access, $inheritance, $propagation, $type)
$acl.AddAccessRule($rule)
Set-Acl HKLM:\System\CurrentControlSet\Control\ComputerName $acl
On the virtualization server, create an installation point that contains the SharePoint Server software or provide media, such as an ISO image, that can be accessed from the virtual machine.
On Server 2 – run the SharePoint.exe and run the Microsoft SharePoint 2010 Products Preparation Tool. This tool checks for required products and updates. (Must have internet connection for this to succeed)
Run the Setup Wizard
Interestingly when the Wizard closes the Template Selection Page opens (_layouts/templatepick.aspx) prompting you to create a site in the default SharePoint Web App
From the Start menu - browse to Central Admin.
TIPS:
If you are prompted for your user name and password, perform the following procedures:
• Add the SharePoint Central Administration Web site to the list of trusted sites
• Disable Internet Explorer Enhanced Security settings
If you receive a proxy server error message, perform the following procedure:
• Configure proxy server settings to bypass the proxy server for local addresses
MOSS 2007 Search Config and SSP Admin site access issues
PURPLE CONFIGURATION
Today I was called in to investigate why Search was not working at all for end users in one of our Purple environments
SESSION DB ISSUES
First off – I couldn’t even connect to the SSP Admin site
I was getting the dreaded Server N Application screen:
Turns out the default Sessionstate value in the web.config
sessionState mode="SQLServer" timeout="60" allowCustomSqlDatabase="true" partitionResolverType="Microsoft.Office.Server.Administration.SqlSessionStateResolver, Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
Had been replaced by a custom value:
sessionState mode="SQLServer" timeout="20" allowCustomSqlDatabase="true" sqlConnectionString="PurpleSessionDB"
I found that reverting to the original sessionstate value completely resolves this issue
HOSTS FILE
So I got onto the SSP Admin site – the Hosts file on the Index server was a mess of REMs – this is how I configured it so that WFE04 (172.28.91.3) is the dedicated crawl server:
127.0.0.1 localhost
127.0.0.1 ssp.school.klp.pri
172.28.91.3 mysite.school.klp.pri
172.28.91.3 WebApp1.school.klp.pri
172.28.91.3 WebApp2.school.klp.pri
NOTES ON CONFIGURING A DEDICATED FRONT-END WEB SERVER FOR CRAWLING
Front-end Web servers are used by Microsoft Office SharePoint Server 2007 to crawl content in SharePoint sites in your server farm
A front-end Web server is any server in your farm that is running the Windows SharePoint Services Web Application service
Content that is external to the farm is accessed directly by the index server, without the help of a front-end Web server
The index server sends requests to each front-end Web server in the farm. These requests include the start address from which to start crawling, what file types and how many items to get. The front-end Web servers get the requested content from the SharePoint sites in the farm and forward that content to the index server for indexing
Recommended to use a dedicated front-end Web server if crawling a server farm that contains more than 500 gigabytes (GB) of content
Can specify any front-end Web server in your farm that is not part of a network load balanced cluster
Must ensure that the Windows SharePoint Services Web Application service is running on the front-end Web server that you want to use for crawling content
For best performance, the recommendation is to configure the index server as the dedicated front-end Web server for crawling if the index server has the memory capacity for both roles
By using the same server as both the index server and dedicated front-end Web server this eliminates the need for the index server to send requests to a different server when crawling content. Consequently this boosts crawl performance and reduces the overall traffic on the network
WHEN NOT TO CONFIGURE A DEDICATED FRONT-END WEB SERVER FOR CRAWLING
Another application is running on the index server
• Doing so might mean that those applications (such as the Excel Calculation service) can no longer communicate with other servers in the farm
• If other applications are running on the index server - must first move those applications to another application server before configuring a dedicated front-end Web server for crawling.
The index server is not also the query server. Configuring a dedicated front-end Web server for crawling can disable the index server's ability to propagate the index to another server
TWO WAYS TO CONFIGURE A DEDICATED FRONT-END WEB SERVER FOR CRAWLING
1. Use the Configure Office SharePoint Server Search Service Settings on server page in Central Administration.
2. Update the Hosts file directly (see Purple environment notes above)
When crawling content - Office SharePoint Server 2007 reads the Hosts file to determine whether to use all front-end Web servers or a dedicated server for crawling content in your server farm
When all front-end Web servers are used for crawling content in your server farm, the Hosts file contains only the loopback address of the index server, as shown in the following example:
127.0.0.1 localhost
When you use the Configure Office SharePoint Server Search Service Settings on server page in Central Administration to select a dedicated front-end Web server for crawling, the SharePoint timer service writes one or more entries to your Hosts file, depending on the number of Web applications that exist in your server farm.
• These entries include the names of the Web applications in your farm and the IP address of the server that you selected as the dedicated front-end Web server for crawling
• In some cases, the timer service writes the incorrect IP address to your Hosts file
o Things that cause the timer server to add an incorrect IP address to the Hosts file include:
The server that you specified as your dedicated front-end Web server for crawling has multiple IP address assigned to one or more network cards.
Network load balancing is being used in your server farm
When you use the Configure Office SharePoint Server Search Service Settings on server page in Central Administration to specify a dedicated front-end Web server for crawling, you cannot change the Hosts file manually if the timer service adds the wrong IP address. This is because the timer service repeatedly overwrites the entries in the Hosts file every few minutes. If this occurs, you must remove the Host file entries made by the timer service and use the Configure Office SharePoint Server Search Service Settings on server page in Central Administration to specify that all front-end Web servers are used for crawling
CONFIGURE A DEDICATED FRONT-END WEB SERVER FOR CRAWLING BY EDITING THE HOSTS FILE DIRECTLY
Before you can configure the Hosts file, you must collect the following information:
• The base or host header name of each Web application in your farm that you will crawl.
• IP address of the server you want to use as the dedicated front-end Web server for crawling
Today I was called in to investigate why Search was not working at all for end users in one of our Purple environments
SESSION DB ISSUES
First off – I couldn’t even connect to the SSP Admin site
I was getting the dreaded Server N Application screen:
Turns out the default Sessionstate value in the web.config
sessionState mode="SQLServer" timeout="60" allowCustomSqlDatabase="true" partitionResolverType="Microsoft.Office.Server.Administration.SqlSessionStateResolver, Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
Had been replaced by a custom value:
sessionState mode="SQLServer" timeout="20" allowCustomSqlDatabase="true" sqlConnectionString="PurpleSessionDB"
I found that reverting to the original sessionstate value completely resolves this issue
HOSTS FILE
So I got onto the SSP Admin site – the Hosts file on the Index server was a mess of REMs – this is how I configured it so that WFE04 (172.28.91.3) is the dedicated crawl server:
127.0.0.1 localhost
127.0.0.1 ssp.school.klp.pri
172.28.91.3 mysite.school.klp.pri
172.28.91.3 WebApp1.school.klp.pri
172.28.91.3 WebApp2.school.klp.pri
NOTES ON CONFIGURING A DEDICATED FRONT-END WEB SERVER FOR CRAWLING
Front-end Web servers are used by Microsoft Office SharePoint Server 2007 to crawl content in SharePoint sites in your server farm
A front-end Web server is any server in your farm that is running the Windows SharePoint Services Web Application service
Content that is external to the farm is accessed directly by the index server, without the help of a front-end Web server
The index server sends requests to each front-end Web server in the farm. These requests include the start address from which to start crawling, what file types and how many items to get. The front-end Web servers get the requested content from the SharePoint sites in the farm and forward that content to the index server for indexing
Recommended to use a dedicated front-end Web server if crawling a server farm that contains more than 500 gigabytes (GB) of content
Can specify any front-end Web server in your farm that is not part of a network load balanced cluster
Must ensure that the Windows SharePoint Services Web Application service is running on the front-end Web server that you want to use for crawling content
For best performance, the recommendation is to configure the index server as the dedicated front-end Web server for crawling if the index server has the memory capacity for both roles
By using the same server as both the index server and dedicated front-end Web server this eliminates the need for the index server to send requests to a different server when crawling content. Consequently this boosts crawl performance and reduces the overall traffic on the network
WHEN NOT TO CONFIGURE A DEDICATED FRONT-END WEB SERVER FOR CRAWLING
Another application is running on the index server
• Doing so might mean that those applications (such as the Excel Calculation service) can no longer communicate with other servers in the farm
• If other applications are running on the index server - must first move those applications to another application server before configuring a dedicated front-end Web server for crawling.
The index server is not also the query server. Configuring a dedicated front-end Web server for crawling can disable the index server's ability to propagate the index to another server
TWO WAYS TO CONFIGURE A DEDICATED FRONT-END WEB SERVER FOR CRAWLING
1. Use the Configure Office SharePoint Server Search Service Settings on server page in Central Administration.
2. Update the Hosts file directly (see Purple environment notes above)
When crawling content - Office SharePoint Server 2007 reads the Hosts file to determine whether to use all front-end Web servers or a dedicated server for crawling content in your server farm
When all front-end Web servers are used for crawling content in your server farm, the Hosts file contains only the loopback address of the index server, as shown in the following example:
127.0.0.1 localhost
When you use the Configure Office SharePoint Server Search Service Settings on server page in Central Administration to select a dedicated front-end Web server for crawling, the SharePoint timer service writes one or more entries to your Hosts file, depending on the number of Web applications that exist in your server farm.
• These entries include the names of the Web applications in your farm and the IP address of the server that you selected as the dedicated front-end Web server for crawling
• In some cases, the timer service writes the incorrect IP address to your Hosts file
o Things that cause the timer server to add an incorrect IP address to the Hosts file include:
The server that you specified as your dedicated front-end Web server for crawling has multiple IP address assigned to one or more network cards.
Network load balancing is being used in your server farm
When you use the Configure Office SharePoint Server Search Service Settings on server page in Central Administration to specify a dedicated front-end Web server for crawling, you cannot change the Hosts file manually if the timer service adds the wrong IP address. This is because the timer service repeatedly overwrites the entries in the Hosts file every few minutes. If this occurs, you must remove the Host file entries made by the timer service and use the Configure Office SharePoint Server Search Service Settings on server page in Central Administration to specify that all front-end Web servers are used for crawling
CONFIGURE A DEDICATED FRONT-END WEB SERVER FOR CRAWLING BY EDITING THE HOSTS FILE DIRECTLY
Before you can configure the Hosts file, you must collect the following information:
• The base or host header name of each Web application in your farm that you will crawl.
• IP address of the server you want to use as the dedicated front-end Web server for crawling
SQL QUERY: COUNT THE NUMBER OF UNIQUE MEMBERS OF SHAREPOINT GROUPS:
SQL QUERY: COUNT THE NUMBER OF UNIQUE MEMBERS OF SHAREPOINT GROUPS:
The query checks if the SharePoint specific tables exist in the DB scheme otherwise the DB will be skipped.
No rows returned can also mean that you don’t have more than (in this case) 800 users per group defined – the query as it is only outputs problematic groups with more than 800 members:
DBAs can execute the query a second time and change the value 800 to e.g. 10 to see an output.
CREATE TABLE ##UsersPerGroup
(
[Counts] bigint NULL,
[GroupTitle] nvarchar(255) NULL,
[siteID] uniqueidentifier null,
[servername] nvarchar(100) null,
[content_DB] nvarchar(100) null
)
go
CREATE TABLE #DBNamesLL
(
DatabaseName VARCHAR(800),
RecStatus INT Default 0
)
DECLARE @cmdStr NVARCHAR(2000)
DECLARE @dbName VARCHAR(500)
INSERT INTO #DBNamesLL (DatabaseName)
SELECT [Name] FROM sys.databases where state_desc = 'online' ORDER by [Name] ASC
WHILE EXISTS
(SELECT * FROM #DBNamesLL WHERE RecStatus=0)
BEGIN
SELECT TOP 1 @DbName=DatabaseName
FROM #DBNamesLL
WHERE RecStatus=0
SELECT @cmdStr = N'USE ' + quotename(@dbName, '[') + N';'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES with (NOLOCK) WHERE TABLE_NAME = ''namevaluepair'')'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'BEGIN'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'INSERT INTO ##UsersPerGroup'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'SELECT COUNT(m.MemberId) as [Counts], g.Title as [GroupTitle], g.SiteId,'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + '(@@servername) [servername], (select db_name(dbid) from master..sysprocesses with (NOLOCK) where spid=@@SPID) [Content_DB]'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'from GroupMembership m with(nolock)'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'join Groups g with(nolock) on m.GroupId = g.ID and m.SiteId = g.SiteId'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'group by g.Title, g.SiteId'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'HAVING COUNT(m.MemberId) > ''800'''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'END'
EXEC sp_executesql @Cmdstr
UPDATE #DBNamesLL
SET RecStatus=1
WHERE RecStatus=0
AND DatabaseName=@DbName
END
select Counts AS 'Count', GroupTitle, servername AS ServerName, content_DB AS ContentDB, siteID AS SiteID from ##UsersPerGroup order by counts desc
DROP TABLE #DBNamesLL
Drop table ##UsersPerGroup
The query checks if the SharePoint specific tables exist in the DB scheme otherwise the DB will be skipped.
No rows returned can also mean that you don’t have more than (in this case) 800 users per group defined – the query as it is only outputs problematic groups with more than 800 members:
DBAs can execute the query a second time and change the value 800 to e.g. 10 to see an output.
CREATE TABLE ##UsersPerGroup
(
[Counts] bigint NULL,
[GroupTitle] nvarchar(255) NULL,
[siteID] uniqueidentifier null,
[servername] nvarchar(100) null,
[content_DB] nvarchar(100) null
)
go
CREATE TABLE #DBNamesLL
(
DatabaseName VARCHAR(800),
RecStatus INT Default 0
)
DECLARE @cmdStr NVARCHAR(2000)
DECLARE @dbName VARCHAR(500)
INSERT INTO #DBNamesLL (DatabaseName)
SELECT [Name] FROM sys.databases where state_desc = 'online' ORDER by [Name] ASC
WHILE EXISTS
(SELECT * FROM #DBNamesLL WHERE RecStatus=0)
BEGIN
SELECT TOP 1 @DbName=DatabaseName
FROM #DBNamesLL
WHERE RecStatus=0
SELECT @cmdStr = N'USE ' + quotename(@dbName, '[') + N';'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES with (NOLOCK) WHERE TABLE_NAME = ''namevaluepair'')'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'BEGIN'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'INSERT INTO ##UsersPerGroup'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'SELECT COUNT(m.MemberId) as [Counts], g.Title as [GroupTitle], g.SiteId,'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + '(@@servername) [servername], (select db_name(dbid) from master..sysprocesses with (NOLOCK) where spid=@@SPID) [Content_DB]'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'from GroupMembership m with(nolock)'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'join Groups g with(nolock) on m.GroupId = g.ID and m.SiteId = g.SiteId'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'group by g.Title, g.SiteId'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'HAVING COUNT(m.MemberId) > ''800'''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'END'
EXEC sp_executesql @Cmdstr
UPDATE #DBNamesLL
SET RecStatus=1
WHERE RecStatus=0
AND DatabaseName=@DbName
END
select Counts AS 'Count', GroupTitle, servername AS ServerName, content_DB AS ContentDB, siteID AS SiteID from ##UsersPerGroup order by counts desc
DROP TABLE #DBNamesLL
Drop table ##UsersPerGroup
SharePoint 2007 SP2
INTRODUCTION:
SP2 provides the groundwork for an eventual upgrade to the next major version of SharePoint Products and Technologies.
It’s important to note that the SP2 releases also incorporates previous major updates, including Service Pack 1 (SP1), the Infrastructure Update, and all of the cumulative update packages up to and including those released in February 2009.
Service Pack 2 (SP2) provides full support for Windows Server 2008 and Internet Information Services (IIS) 7, extended browser support, new operations and properties for the Stsadm command line tool, improved functionality for existing features, and hot fixes for known issues.
Read-Only Content Databases
While performing some maintenance tasks, you may need to mark content databases as read-only. On farms that are running Office SharePoint Server 2007 with SP2 or Windows SharePoint Services 3.0 with SP2, the site collection associated with a read-only content database is set to be read-only automatically, and the user interface is modified so that users cannot perform tasks that require writing to the database. Previously, the user interface was not modified, and users received error messages when they attempted to perform operations that wrote information to the content database.
Performance and Availability Improvements
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 include several fixes and enhancements designed to improve performance, availability, and stability in your server farms.
Database Integrity
Index fragmentation is a major cause of performance degradation in Microsoft® SQL Server™ databases. This problem is addressed in Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 with a new, enhanced version of the Database Statistics timer job. Previously, the sole responsibility of the Database Statistics job was to update query optimization statistics for content databases. As of SP2, the job also rebuilds content database indexes every time it runs. This automatic index rebuild helps to stop fragmentation and maintain database performance.
The Database Statistics job uses the online index rebuild feature of SQL Server 2005 and should run without any impact on performance for content databases of up to the recommended size limit of 100 GB.
Content Deployment and Site Variations
Office SharePoint Server 2007 SP2 includes several enhancements that improve the performance and stability of the content deployment and variations features. In particular, Office SharePoint Server 2007 SP2 adds a new operation to the Stsadm command-line tool that enables a SharePoint administrator to scan sites that use the variations feature for errors. The operation reports on errors encountered and, where possible, fixes the underlying issues. For example, the operation can find and repair missing peer relationships and corrupted entries in the internal variations relationships list. The Office SharePoint Server 2007 SP2 enhancements also provide a more manageable way to handle the propagation of large quantities of variation content.
Other Stsadm Enhancements
In addition to the site variation enhancements, SP2 includes other new and extended commands for the Stsadm command-line tool. Most notably, SP2 adds Stsadm operations and parameters that ease the removal of orphaned objects from SharePoint databases. Orphaned objects—typically sites and site collections without corresponding entries in the configuration database—posed challenges in previous upgrades. Other additions include a operation that you can use to tune various aspects of search query processing and the preupgradecheck operation, which this paper discusses in a subsequent section.
New Stsadm Commands and Parameters:
http://stsadm.blogspot.com/2009/05/new-stsadm-commands-and-parameters-with.html
http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?List=56f96349%2D3bb6%2D4087%2D94f4%2D7f95ff4ca81f&ID=127
Excel Services and InfoPath Forms Services
SP2 includes a broad range of fixes and enhancements that improve the performance, security, stability, and usability of Excel Services. SP2 makes it easier to configure Excel Web Access Web Parts on new sites. Several rendering, calculation, and security issues have been resolved.
SP2 also offers a range of improvements for users of InfoPath Forms Services. Performance enhancements in SP2 reduce both the memory requirements and the page load times for large browser-rendered forms. SP2 also improves the browser rendering of various controls, such as the Cannot be blank red asterisk and the rich text field.
Improved Interoperability
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 introduce substantial improvements to the way that SharePoint Products and Technologies integrate with other products and platforms. In particular, SP2 offers support for a broader range of Web browsers. SP2 also provides enhancements to user experience with forms authentication.
Improved User Experience with Forms Authentication
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2 offer a substantially improved client integration experience for forms-based authentication users. Previously, forms-based authentication provided a poor experience when users attempted to use the client integration features of Windows SharePoint Services 3.0 or Office SharePoint Server 2007; the client application would repeatedly prompt users for credentials whenever they executed a command in a document. To avoid this, administrators were advised to disable SharePoint client integration features in forms-based authentication environments.
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2, together with the SP2 updates for Microsoft Office Client applications, provide a much smoother user experience and make client integration a viable option for forms-based authentication users. Users of Microsoft Windows Vista™ must install Vista SP2 to take advantage of the improved user experience.
Browser Support
Web browser support in SharePoint Products and Technologies is divided into two levels. Level 1 Web browsers provide an optimal user experience for all SharePoint sites, through the use of advanced Microsoft ActiveX® control features. Level 2 Web browsers support all of the basic features of SharePoint Products and Technologies, including read and write functionality and site administration tasks.
As of SP2, Level 1 browser support is extended to include Microsoft Internet Explorer 8. Level 2 browser support is extended to include Firefox 2 and Firefox 3.
Getting Ready for SharePoint Products and Technologies "14"
As well as improving performance, availability, and stability, Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2 provide the groundwork for future major releases of SharePoint Products and Technologies. In particular, SP2 includes a new Stsadm operation that can help you to prepare your SharePoint deployment for an upgrade to SharePoint Products and Technologies "14".
The preupgradecheck operation
The Stsadm preupgradecheck operation can be used to scan your server farm to establish whether it is ready for upgrade to SharePoint Products and Technologies "14". It identifies issues that could present obstacles to the upgrade process. It also checks for SharePoint Products and Technologies "14" system requirements, including the presence of Microsoft® Windows Server® 2008 and a 64-bit machine, and provides feedback and best practice recommendations for your current environment, together with information on how to resolve any issues that the tool discovers.
After installing SP2, scanning your server farm by using preupgradecheck gives you the benefit of time to resolve any potential issues in your SharePoint environment, ensuring a smooth and painless upgrade to SharePoint Products and Technologies "14".
This operation can be run on your Office SharePoint Server 2007 farm to report on farm and server data
• Servers and total amount of content
• Search configuration
• Alternate access mappings
• Features
• Site definitions
• Language packs
It will also expose potential issues, including:
• Large lists
• Data orphans
• CAML views and CAML content types
• Modified content databases that have improper schemas
• Missing upgrade dependencies
Output into an .html file
Process is read-only.
Can run it often, not only before upgrading but also just to check the general health of your environment
After Updating to SP2 the Microsoft.SharePoint.portal.dll version number: 12.0.6421.1000
To keep all files in a SharePoint installation up-to-date, the following sequence is recommended.
1. Service Pack 2 for Windows SharePoint Services 3.0 and language packs
2. Service Pack 2 for Office SharePoint Server 2007 and language packs
3. Bugfix 971620 (which fix the trial issue, this fix will be included in the next round of cumulative update)
4. June Cumulative Update package for Windows SharePoint Services 3.0
5. June Cumulative Update package for Office SharePoint Server 2007
The version of content databases should be 12.0.6510.5000 after successfully applying these updates.
RISK MITIGATION:
- Detail the planned work and dates
- Confirm that MOSS DBs are defragmented
- Confirm that no maintenance tasks happening on MOSS instances for the weekend
- Confirm that MOSS Backups will not be running during the update window
- Forward the Service Call details to Tech Lead
- Confirm Disk space on each server (at least 1.5GB)
- File system backup – ensure that this will not be running during the update window
- Are the MOSS boxes virtual? Can we back them up?
- Stop all search and user profile activity
- Copy binaries to local location in advance as this can take time!
- Backup web.configs
- Check redirect is in place
DEPLOYMENT PLAN
In Summary: -
• Deploy WSS SP2
• Deploy MOSS SP2
• Deploy Bugfix
• Deploy WSS June CUs
• Deploy MOSS CUs
To assist with this, please use the template plan as below, customizing as appropriate for your environment.
SP2 provides the groundwork for an eventual upgrade to the next major version of SharePoint Products and Technologies.
It’s important to note that the SP2 releases also incorporates previous major updates, including Service Pack 1 (SP1), the Infrastructure Update, and all of the cumulative update packages up to and including those released in February 2009.
Service Pack 2 (SP2) provides full support for Windows Server 2008 and Internet Information Services (IIS) 7, extended browser support, new operations and properties for the Stsadm command line tool, improved functionality for existing features, and hot fixes for known issues.
Read-Only Content Databases
While performing some maintenance tasks, you may need to mark content databases as read-only. On farms that are running Office SharePoint Server 2007 with SP2 or Windows SharePoint Services 3.0 with SP2, the site collection associated with a read-only content database is set to be read-only automatically, and the user interface is modified so that users cannot perform tasks that require writing to the database. Previously, the user interface was not modified, and users received error messages when they attempted to perform operations that wrote information to the content database.
Performance and Availability Improvements
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 include several fixes and enhancements designed to improve performance, availability, and stability in your server farms.
Database Integrity
Index fragmentation is a major cause of performance degradation in Microsoft® SQL Server™ databases. This problem is addressed in Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 with a new, enhanced version of the Database Statistics timer job. Previously, the sole responsibility of the Database Statistics job was to update query optimization statistics for content databases. As of SP2, the job also rebuilds content database indexes every time it runs. This automatic index rebuild helps to stop fragmentation and maintain database performance.
The Database Statistics job uses the online index rebuild feature of SQL Server 2005 and should run without any impact on performance for content databases of up to the recommended size limit of 100 GB.
Content Deployment and Site Variations
Office SharePoint Server 2007 SP2 includes several enhancements that improve the performance and stability of the content deployment and variations features. In particular, Office SharePoint Server 2007 SP2 adds a new operation to the Stsadm command-line tool that enables a SharePoint administrator to scan sites that use the variations feature for errors. The operation reports on errors encountered and, where possible, fixes the underlying issues. For example, the operation can find and repair missing peer relationships and corrupted entries in the internal variations relationships list. The Office SharePoint Server 2007 SP2 enhancements also provide a more manageable way to handle the propagation of large quantities of variation content.
Other Stsadm Enhancements
In addition to the site variation enhancements, SP2 includes other new and extended commands for the Stsadm command-line tool. Most notably, SP2 adds Stsadm operations and parameters that ease the removal of orphaned objects from SharePoint databases. Orphaned objects—typically sites and site collections without corresponding entries in the configuration database—posed challenges in previous upgrades. Other additions include a operation that you can use to tune various aspects of search query processing and the preupgradecheck operation, which this paper discusses in a subsequent section.
New Stsadm Commands and Parameters:
http://stsadm.blogspot.com/2009/05/new-stsadm-commands-and-parameters-with.html
http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?List=56f96349%2D3bb6%2D4087%2D94f4%2D7f95ff4ca81f&ID=127
Excel Services and InfoPath Forms Services
SP2 includes a broad range of fixes and enhancements that improve the performance, security, stability, and usability of Excel Services. SP2 makes it easier to configure Excel Web Access Web Parts on new sites. Several rendering, calculation, and security issues have been resolved.
SP2 also offers a range of improvements for users of InfoPath Forms Services. Performance enhancements in SP2 reduce both the memory requirements and the page load times for large browser-rendered forms. SP2 also improves the browser rendering of various controls, such as the Cannot be blank red asterisk and the rich text field.
Improved Interoperability
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server SP2 introduce substantial improvements to the way that SharePoint Products and Technologies integrate with other products and platforms. In particular, SP2 offers support for a broader range of Web browsers. SP2 also provides enhancements to user experience with forms authentication.
Improved User Experience with Forms Authentication
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2 offer a substantially improved client integration experience for forms-based authentication users. Previously, forms-based authentication provided a poor experience when users attempted to use the client integration features of Windows SharePoint Services 3.0 or Office SharePoint Server 2007; the client application would repeatedly prompt users for credentials whenever they executed a command in a document. To avoid this, administrators were advised to disable SharePoint client integration features in forms-based authentication environments.
Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2, together with the SP2 updates for Microsoft Office Client applications, provide a much smoother user experience and make client integration a viable option for forms-based authentication users. Users of Microsoft Windows Vista™ must install Vista SP2 to take advantage of the improved user experience.
Browser Support
Web browser support in SharePoint Products and Technologies is divided into two levels. Level 1 Web browsers provide an optimal user experience for all SharePoint sites, through the use of advanced Microsoft ActiveX® control features. Level 2 Web browsers support all of the basic features of SharePoint Products and Technologies, including read and write functionality and site administration tasks.
As of SP2, Level 1 browser support is extended to include Microsoft Internet Explorer 8. Level 2 browser support is extended to include Firefox 2 and Firefox 3.
Getting Ready for SharePoint Products and Technologies "14"
As well as improving performance, availability, and stability, Windows SharePoint Services 3.0 SP2 and Office SharePoint Server 2007 SP2 provide the groundwork for future major releases of SharePoint Products and Technologies. In particular, SP2 includes a new Stsadm operation that can help you to prepare your SharePoint deployment for an upgrade to SharePoint Products and Technologies "14".
The preupgradecheck operation
The Stsadm preupgradecheck operation can be used to scan your server farm to establish whether it is ready for upgrade to SharePoint Products and Technologies "14". It identifies issues that could present obstacles to the upgrade process. It also checks for SharePoint Products and Technologies "14" system requirements, including the presence of Microsoft® Windows Server® 2008 and a 64-bit machine, and provides feedback and best practice recommendations for your current environment, together with information on how to resolve any issues that the tool discovers.
After installing SP2, scanning your server farm by using preupgradecheck gives you the benefit of time to resolve any potential issues in your SharePoint environment, ensuring a smooth and painless upgrade to SharePoint Products and Technologies "14".
This operation can be run on your Office SharePoint Server 2007 farm to report on farm and server data
• Servers and total amount of content
• Search configuration
• Alternate access mappings
• Features
• Site definitions
• Language packs
It will also expose potential issues, including:
• Large lists
• Data orphans
• CAML views and CAML content types
• Modified content databases that have improper schemas
• Missing upgrade dependencies
Output into an .html file
Process is read-only.
Can run it often, not only before upgrading but also just to check the general health of your environment
After Updating to SP2 the Microsoft.SharePoint.portal.dll version number: 12.0.6421.1000
To keep all files in a SharePoint installation up-to-date, the following sequence is recommended.
1. Service Pack 2 for Windows SharePoint Services 3.0 and language packs
2. Service Pack 2 for Office SharePoint Server 2007 and language packs
3. Bugfix 971620 (which fix the trial issue, this fix will be included in the next round of cumulative update)
4. June Cumulative Update package for Windows SharePoint Services 3.0
5. June Cumulative Update package for Office SharePoint Server 2007
The version of content databases should be 12.0.6510.5000 after successfully applying these updates.
RISK MITIGATION:
- Detail the planned work and dates
- Confirm that MOSS DBs are defragmented
- Confirm that no maintenance tasks happening on MOSS instances for the weekend
- Confirm that MOSS Backups will not be running during the update window
- Forward the Service Call details to Tech Lead
- Confirm Disk space on each server (at least 1.5GB)
- File system backup – ensure that this will not be running during the update window
- Are the MOSS boxes virtual? Can we back them up?
- Stop all search and user profile activity
- Copy binaries to local location in advance as this can take time!
- Backup web.configs
- Check redirect is in place
DEPLOYMENT PLAN
In Summary: -
• Deploy WSS SP2
• Deploy MOSS SP2
• Deploy Bugfix
• Deploy WSS June CUs
• Deploy MOSS CUs
To assist with this, please use the template plan as below, customizing as appropriate for your environment.
Wednesday, 24 February 2010
CONFIGURING ULS LOGGING ON NON-DEFAULT DRIVE VIA POWERSHELL SCRIPT
CONFIGURING ULS LOGGING ON NON-DEFAULT DRIVE VIA POWERSHELL SCRIPT:
Here is a Powershell script to set the ULS Logging to a different drive. The script takes 2 parameters - the first is the log file location, the second is the number of log files to create:
Here is a Powershell script to set the ULS Logging to a different drive. The script takes 2 parameters - the first is the log file location, the second is the number of log files to create:
Param([string]$logDirectory, [int]$number_of_log_files)#################################################################################
$description =@'
This script moves the trace logs to a specified directory and specifies the number of logfiles to maintain
'@
$scriptname = $MyInvocation.MyCommand.Name
$scriptpath = $MyInvocation.MyCommand.Path
$transcriptFile = "C:\Install\Logs\Powershell_$scriptname.log"
Start-Transcript $transcriptFile -append -force
Write-Output "Starting Script: $scriptpath"
Write-Output $description
$error.Clear()
$erroractionpreference="Stop"
$ReportErrorShowExceptionclass=$true
$ReportErrorShowInnerexception=$true
$ReportErrorShowSource=$true
$ReportErrorShowStackTrace=$true
trap {
$errorRecord = $error[0]
Write-Output "ErrorRecord:"
$errorRecord
Format-List * -Force
Out-Default
Write-Output "CategoryInfo:"
$errorRecord.CategoryInfo
Format-List * -Force
Out-Default
Write-Output "ErrorDetails:"
$errorDetails = $errorRecord.ErrorDetails
Write-Output "Message:"
$errorDetails.Message
Out-Default
Write-Output "RecommendedAction:"
$errorDetails.RecommendedAction
Out-Default
Write-Output "FullyQualifiedErrorId:"
$errorRecord.FullyQualifiedErrorId
Out-Default
Write-Output "InvocationInfo:"
$errorRecord.InvocationInfo
Format-List * -Force
Out-Default
Write-Output "TargetObject:"
$errorRecord.TargetObject
Format-List * -Force
Out-Default
Write-Output "Exception:"
$Exception = $errorRecord.Exception
for ($i = 0; $Exception; $i++, ($Exception = $Exception.InnerException))
{$Exception
Format-List * -Force
Out-Default}
Stop-Transcript
;break
}
[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
New-Item $logDirectory -type directory -Force
$directoryInfo = New-Object System.IO.DirectoryInfo($logDirectory)
$directorySecurity = $directoryInfo.getaccesscontrol()
$fileSystemAccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule("$env:COMPUTERNAME\WSS_WPG",[System.Security.AccessControl.FileSystemRights]::FullControl,[System.Security.AccessControl.AccessControlType]::Allow)
#$fileSystemAccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule("dev\administrator",[System.Security.AccessControl.FileSystemRights]::FullControl,[System.Security.AccessControl.AccessControlType]::Allow)
$directorySecurity.AddAccessRule($fileSystemAccessRule)
$directoryInfo.SetAccessControl($directorySecurity)
$diagnosticsService = [Microsoft.SharePoint.Administration.SPDiagnosticsService]::Local
$diagnosticsService.LogLocation = $logDirectory
$diagnosticsService.LogCutInterval = $number_of_log_files
$diagnosticsService.Update()
Restart-Service "SPTrace"
Monday, 15 February 2010
SQL Query to check for large lists
To check for big lists (lists with itemcount > 1500):
create table ##Largelists
(
[listID] uniqueidentifier NULL,
[siteID] uniqueidentifier null,
[webid] uniqueidentifier null,
[counts] bigint NULL,
[fullurl] nvarchar(255) NULL,
[dirname] nvarchar(255) null,
[tp_title] nvarchar(255) NULL,
[servername] nvarchar(100) null,
[content_DB] nvarchar(100) null
)
go
CREATE TABLE #DBNamesLL
(
DatabaseName VARCHAR(800),
RecStatus INT Default 0
)
DECLARE @cmdStr NVARCHAR(2000)
DECLARE @dbName VARCHAR(500)
INSERT INTO #DBNamesll (DatabaseName)
SELECT [Name] FROM sys.databases where state_desc = 'online'
ORDER by [Name] ASC
WHILE EXISTS (SELECT * FROM #DBNamesLL
WHERE RecStatus=0)
BEGIN
SELECT TOP 1 @DbName=DatabaseName
FROM #DBNamesLL
WHERE RecStatus=0
SELECT @cmdStr = N'USE ' + quotename(@dbName, '[') + N';'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES with (NOLOCK) WHERE TABLE_NAME = ''namevaluepair'')'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'BEGIN'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'INSERT INTO ##largelists'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'select a.siteid, a.webid, a.listid, count(a.id) [counts], c.fullurl, a.dirname, b.tp_title,'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + '(@@servername) [servername], (select db_name(dbid) from master..sysprocesses with (NOLOCK) where spid=@@SPID) [Content_DB]'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'from alldocs as a with (nolock) inner join'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'alllists as b with (nolock) on a.listid=b.tp_id inner join'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'webs as c with (nolock) on b.tp_webid=c.id'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'group by a.listid, c.fullurl, b.tp_title, a.siteid, a.webid, a.dirname'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'HAVING count(a.id) > ''1500'''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'END'
EXEC sp_executesql @Cmdstr
UPDATE #DBNamesLL
SET RecStatus=1
WHERE RecStatus=0
AND DatabaseName=@DbName
END
select WebID, SiteID, ListID, tp_Title, DirName, Counts, content_DB, servername from ##largelists order by counts desc
DROP TABLE #DBNamesLL
Drop table ##largelists
create table ##Largelists
(
[listID] uniqueidentifier NULL,
[siteID] uniqueidentifier null,
[webid] uniqueidentifier null,
[counts] bigint NULL,
[fullurl] nvarchar(255) NULL,
[dirname] nvarchar(255) null,
[tp_title] nvarchar(255) NULL,
[servername] nvarchar(100) null,
[content_DB] nvarchar(100) null
)
go
CREATE TABLE #DBNamesLL
(
DatabaseName VARCHAR(800),
RecStatus INT Default 0
)
DECLARE @cmdStr NVARCHAR(2000)
DECLARE @dbName VARCHAR(500)
INSERT INTO #DBNamesll (DatabaseName)
SELECT [Name] FROM sys.databases where state_desc = 'online'
ORDER by [Name] ASC
WHILE EXISTS (SELECT * FROM #DBNamesLL
WHERE RecStatus=0)
BEGIN
SELECT TOP 1 @DbName=DatabaseName
FROM #DBNamesLL
WHERE RecStatus=0
SELECT @cmdStr = N'USE ' + quotename(@dbName, '[') + N';'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES with (NOLOCK) WHERE TABLE_NAME = ''namevaluepair'')'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'BEGIN'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'INSERT INTO ##largelists'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'select a.siteid, a.webid, a.listid, count(a.id) [counts], c.fullurl, a.dirname, b.tp_title,'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + '(@@servername) [servername], (select db_name(dbid) from master..sysprocesses with (NOLOCK) where spid=@@SPID) [Content_DB]'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'from alldocs as a with (nolock) inner join'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'alllists as b with (nolock) on a.listid=b.tp_id inner join'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'webs as c with (nolock) on b.tp_webid=c.id'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'group by a.listid, c.fullurl, b.tp_title, a.siteid, a.webid, a.dirname'
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'HAVING count(a.id) > ''1500'''
SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'END'
EXEC sp_executesql @Cmdstr
UPDATE #DBNamesLL
SET RecStatus=1
WHERE RecStatus=0
AND DatabaseName=@DbName
END
select WebID, SiteID, ListID, tp_Title, DirName, Counts, content_DB, servername from ##largelists order by counts desc
DROP TABLE #DBNamesLL
Drop table ##largelists
Subscribe to:
Posts (Atom)
SharePoint Information Architecture Diagram
Here is the template I use for Information Architecture designs. It's built using Mindjet and I flesh the nodes out with the low level d...
-
Introduction This document is intended to highlight possible causes of poor indexing performance on SharePoint 2007 farms as well as p...
-
SharePoint 2010 Remote BLOB Storage (RBS) Contents Differences between LOCAL and REMOTE FILESTREAM Providers . 3 EBS . 4...
-
Here is the template I use for Information Architecture designs. It's built using Mindjet and I flesh the nodes out with the low level d...