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

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

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

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.

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:

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"

#################################################################################

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