SharePoint Calculated Column – how to work around

Q: I work in Communications and we collect the news related to our department and send it out by email twice daily, once in the morning and once in the afternoon. I have set up a SharePoint library to group by Date, so it groups uploaded files by the date they were added, which is great. I was wondering if I could group the files under these dates by morning and afternoon (ie. before 12:00 and after 12:00) so by the time that they were added, so that they correspond with our morning and afternoon news alerts? Continue reading

Restore Project server databases and instances to another farm

As I said in my previous post, today we will see how can we move project instances to another farm which including all your project server databases.

Before we delete PWA databases from sql server we need to accomplish some tasks in the order:

  • Delete/remove all the pwa instances from project server provisioning center (PWA service app)
  • Connect to sql server analysis services and delete the respective pwa instance cube db’s.
  • Go to timer service job definition and remove any jobs related starts with  PWA*
  • Verify back with powershell commands below listed if you still have any orphaned PWA instances or timer jobs and remove them.
  1.   Open the SharePoint management shell and run these commands.$serviceapp = get-spserviceapplication | ? {$_.TypeName –like “*Project*”}
    This will get the project server service application
    $pwainstances = $serviceapp.Sitecollection

    $pwainstances | ft name, id
    This will list all PWA instances referenced in the config DB


    Repeat these steps for each PWA site to delete them individually

    $toberemoved = $pwainstances | ? {$_.Id –eq “a1a29814-983e-4cad-a730-9a80d40737f7”}
    Enter the instance ID from query results in the previous step
    This will confirm the instance you want to remove
    This will delete the instance

We can now go forward and backup the pwa db’s and restore them to this farm.  follow steps to restore db’s

Now comeback to Project Service Application and create a new PWA instance and provide the same PWA instance name which exists in your other farm (eg: http://spserver-webapp/PWA-name ) so PWA-name should be same as restoring farm pwa-name – *important*. Then provide the database names while creating the pwa instance as you restored from source farm. Once the pwa instance is created we need to create the PWA root site. Note: do not backup and restore the root site db from source farm, rather create a new db and assign max site count to 1. Basically we are storing each PWA instance root site separately for each PWA site.

Post Update tasks after the PWA instance and root site are created:

  • Open the PWA instance site and go to server settings -> manage users and add the service account or your admin account to the list or users and administrators group, as the current db does not contain the destination farm service account it will hold the source farm farm account.PWA-1
  • Then open up the “Basic Project Plan” under workflow and project detail pages –> Enterprise project Types. Open the Basic project plan and add the project detail pages as below.
  •  pwa-2
  • Either disable notifications E-mail settings or modify them to suit this farm. This setting under operational policies –> Alerts and Reminders
  • Go to OLAP database management which you can find under Database Administration. Edit the existing one listed and change the database server and instance details, give it a couple of seconds and click “build Now” and watch the status there it self as it refreshes.

We should be good, if every thing went well. Also follow the below links for further troubleshooting.


PowerShell Cmdlets to Upgrade Content Databases from SP 2010 to 2013

In this new article, I will summarize basic Powershell cmdelts we have available to upgrade SharePoint 2010 content databases to SharePoint 2013:

  • Before going into detail, you can list available cmdlets by executing the following PowerShell sentence in the SharePoint 2013 Administration Console:

Get-help *SPContentDatabase

  • After executing this sentence, you will have a list with all the cmdlets available:

  • From this list, the cmdlets we are interested in are the following: Test-SPContentDatabse, Upgrade-SPContentDatabase and Mount-SPContentDatabase.
  • Test-SPContentDatabase cmdlet allows you to identify any issue or customization you could take into account when upgrading a content database to SharePoint 2013: features installed in the SharePoint 2010 environment that should be in the SharePoint 2013 ones, language packs installed, etc. Below, you can find the general syntax for Test-SPContentDatabase.
Test-SPContentDatabase –Name -WebApplication

  • If the command execution detects issues in the content database, it will appear in the output window. For instance, a common issue is to have orphan objects in the content database.

  • Ugrade-SPContentDatabase cmdlet allows you to upgrade a content database that has some issues or upgrade it from one product build to another one. This command is used to only to resume a failed Upgrade. The syntax of this cmdlet is quite simple as you can see in the following script:
Upgrade-SPContentDatabase -Identity

  • If you execute this command in the SharePoint 2013 Administration Console, you will have to confirm that you want to start the upgrade process in the related database. If there are no problems, a warning message will be shown indicating that the database doesn’t need to be upgraded.
  • Finally, you have to execute the MountSPContentDatabase cmdlet in order to add a content database to an existing web application. Please note that only once you have fixed any existing problem found with the content database,  should you execute this command.  You are then ready to add to an existing web application. Mount-SPContentDatabase syntax is quite simple as you can see below:
Mount-SPContentDatabase -Name -WebApplication
And that’s all there is to know about upgrading from basic PowerShell cmdlets to SharePoint 2010 content databases.

Courtesy: Juan Carlos

Recover Sharepoint 2013 databases from suspect mode.

This Post courtesy to: 
Original Post from:

I restarted my SharePoint server, opened Central Administration and encountered the following error:
Server Error in ‘/’ Application
Runtime Error
Description: An application error occurred on the server.
01 - SharePoint 2013 Server Error in Application
In order to troubleshoot this issue I had to check couple of thing:

  • Make sure SQL Server services are up and running
  • Make sure the IIS application pools are started
  • Review Windows logs and gather more information about the server. I noticed the following event:

SQL Database ‘SharePoint_Config’ on SQL Server instance ‘C4968397007′ not found. Additional error information from SQL Server is included below.
Cannot open database “SharePoint_Config” requested by the login. The login failed. Login failed for user ‘DC07SQLSvc’.

02.1 - Windows logs
This event made me suspect something is wrong with my SQL Server. I opened SQL Server management studio and noticed that some of my most critical SharePoint databases are not accessible and set to suspect mode. 02 - SharePoint 2013 databases are in Suspect mode
What is a suspect mode in SQL Server database?
Suspect mode might be caused by many reasons like unavailable or corrupted database files, hardware failure etc.
Don’t worry! This situation is reversible.
Here’s a quick guide of how to recover your SharePoint databases from suspect mode:
Open your SQL Server management studio and execute the following queries one after another:

  • Run the following query. sp_resetstatus command will turn off suspect flag on the database.

EXEC sp_resetstatus ‘SharePoint_Config’;

After executing this query you’ll see the following warning. Don’t worry, this doesn’t mean you did something wrong.
04 - SQL Server reset status of database warning

  • The next step is to set the database to an Emergency mode. This can be done by executing following query:


After executing this query your database should look like this:
06 - SharePoint_config database is set to emergency mode
Once we set the database to an Emergency mode it temporarily becomes a Read Only database.

  • Execute the following query in order to check the logical and physical integrity of the objects in the database.

DBCC checkdb(‘SharePoint_Config’)

  • To complete the process, run the following queries:

SharePoint_Config SET SINGLE_USER
DBCC CheckDB (‘SharePoint_Config’, REPAIR_ALLOW_DATA_LOSS)
DBCC CheckDB (‘SharePoint_Config’)

Repeat this action for each one of the affected databases.
09 - Everything is back to track
I ran some basic tests to make sure my SharePoint server is working properly again, looks like everything is back to track.

January 29th, 2013 | Author:  | Filed under: CloudShareDev / TestSharePoint | Tags:  

Tools to Analyzing Perfmon logs

               Now that we talked about perfmon counters for SharePoint and project server in the last post, the other challenging task after collecting these counters was to analyze this data and make out some sense out of those huge log file.  There may be different ways analyze them by applying knowledge gained experience of such incidents, but not all the time we can rely on that when the counters are in large in number. The standard Perfmon interface tends to be messy when many counters are involved.

I have two recommendations:

1.      Manual PerfMon Analysis Workbook   — When you want a tool that would give you quick overview of the server without having to delve through a thick report or through a cluttered perfmon diagram with lots of counters. 

2.      PAL  –  Performance Analysis of Logs (PAL) Tool  – A proper tool which prepares a beautiful report and graphs based on the counters and it also needs a threshold file for the counter/application we need detailed analysis.

This tool is all you need to complete your analysis. While you are looking to analyze SharePoint Counters and see how your server fares to the best practice results or a standard expected results of a well performing one, you need to feed the threshold file for SharePoint and the dependent threshold files as well, like for SharePoint server you may need a system overview, IIS, SQL thresholds as well as the counters we normally monitor for SharePoint server will also include IIS, process and system hardware related.
You can get a clue by looking at the sample report which this tool prepares at this URL
Manual PerfMon Analysis Workbook – You get detailed and latest update by visiting codeplex link provided above.

This tool  gives you an overview over lots of counters that are collected using perfmon. This release is workable. Too upload the data to the workbook: read the manual below:
Manual Perfmon Analysis Workbook.docx

Perfmon Counter Templates – SharePoint Server, Project Server and SQL Server

               When we run into issues and need further investigation to troubleshoot the root cause we look up to getting Servers and Database statistics. Always I look forward to gather as much data from Performance Counters. These counters are specific to application and Operating system and as a SharePoint Admins we need to gather data which makes sense and the following counters had been very helpful for up in such instances.
Every Situation is different and we need to add counters which are necessary and apt for that situation and below are one which I use most of the time for SharePoint and Project Server and while working with Microsoft we got these templates to collect the counters. I also uploaded these templates which you can download at:
SharePoint Server and Project Server Counters:

Processor(*)% Processor Time
Network Interface(*)Bytes Total/sec
Network Interface(*)Current Bandwidth
SystemProcessor Queue Length
Processor(*)% Privileged Time
PhysicalDisk(*)Avg. Disk sec/Read
PhysicalDisk(*)Avg. Disk sec/Write
LogicalDisk(*)Avg. Disk sec/Read
LogicalDisk(*)Avg. Disk sec/Write
MemoryFree System Page Table Entries
MemoryPool Nonpaged Bytes
MemoryPool Paged Bytes
MemoryAvailable MBytes
Network Interface(*)Output Queue Length
Processor(_Total)% Processor Time
Processor(_Total)% Privileged Time
SystemContext Switches/sec
Process(*)Private Bytes
Process(*)Handle Count
Process(*)Thread Count
Process(*)% Processor Time
Process(*)Virtual Bytes
LogicalDisk(C:)Free Megabytes
Processor(*)% Interrupt Time
Process(*)Working Set
MemorySystem Cache Resident Bytes
Process(*)IO Data Operations/sec
Process(*)IO Other Operations/sec
LogicalDisk(*)Disk Transfers/sec
MemoryPages Input/sec
Paging File(*)% Usage
Paging File(*)% Usage Peak
ASP.NET Apps v2.0.50727(*)Request Execution Time
ASP.NET Apps v2.0.50727(*)Requests/Sec
ASP.NET Apps v2.0.50727(*)Requests In Application Queue
ASP.NET(*)Worker Process Restarts
SQLServer:Locks(*)Average Wait Time (ms)
SQLServer:Locks(*)Number of Deadlocks/sec
SQLServer:Access Methods(*)Full Scans/sec
SQLServer:General Statistics(*)User Connections
SQLServer:Cache Manager(*)Cache Hit Ratio
SharePoint Publishing Cache(*)Publishing cache flushes / second
SharePoint Publishing Cache(*)Publishing cache hit ratio
SharePoint Publishing Cache(*)Publishing cache misses / sec
SharePoint Foundation(*)Sql Query Executing time
SharePoint Foundation(*)Executing Sql Queries
SharePoint Foundation(*)Responded Page Requests Rate
SharePoint Foundation(*)Executing Time/Page Request
SharePoint Foundation(*)Current Page Requests
SharePoint Foundation(*)Reject Page Requests Rate
SharePoint Foundation(*)Incoming Page Requests Rate
SharePoint Foundation(*)Active Threads
.NET CLR Exceptions(*)# of Exceps Thrown / sec
Web Service(_Total)Connection Attempts/sec
.NET CLR Loading(*)Current appdomains
ASP.NET Applications(*)Request Execution Time
ASP.NET Applications(*)Requests Queued
.NET CLR Memory(*)# Bytes in all Heaps
ASP.NET Applications(*)Application Restarts
MemoryCommitted Bytes
MemoryCommit Limit
Memory% Committed Bytes In Use

SQL Server Perfmon Counters Template:

Network Interface(*)*
Paging File(*)*
SQLServer:Access Methods*
SQLServer:Backup Device(*)*
SQLServer:Broker Activation(*)*
SQLServer:Broker Statistics*
SQLServer:Broker/DBM Transport*
SQLServer:Buffer Manager*
SQLServer:Buffer Node(*)*
SQLServer:Buffer Partition(*)*
SQLServer:Catalog Metadata(*)*
SQLServer:Cursor Manager by Type(*)*
SQLServer:Cursor Manager Total*
SQLServer:Database Mirroring(*)*
SQLServer:Exec Statistics(*)*
SQLServer:General Statistics*
SQLServer:Memory Manager*
SQLServer:Plan Cache(*)*
SQLServer:Replication Agents(*)*
SQLServer:Replication Dist(*)*
SQLServer:Replication Logreader(*)*
SQLServer:Replication Snapshot(*)*
SQLServer:Replication Merge(*)*
SQLServer:SQL Errors(*)*
SQLServer:SQL Statistics*
SQLServer:User Settable(*)*
SQLServer:Wait Statistics(*)*


SharePoint logs and IIS logs backup/archive and Compress to a file

The data keeps on growing as the SharePoint Farm being adopted more into the company and customers. It brings more challenges in terms of keeping up with the pace of requests and data administration. We are going to address today one part of that problem which is managing SharePoint LOGS weather it is ULS, Diagnostic and Analytic logging. Also it includes IIS logs. To address this challenge I did wrote a small script to migrate these logs to a different directory and them zip these logs and move to a network storage or to another directory on the local hard drive. 

Some of the challenges to achieve this were 

·        There are IIS logs with same file names in different directories (mainly IIS logs) we need to maintain the directories structure to not miss any logs. 

·        The criteria to move these logs out is based on a number of day , like I need to move all the logs 15 or older away from the default logs folder or drive. 

·        Then zip these logs and add to same archive file as this script has to run daily and we have a single archive file on that machine and to maintain the zip name to reflect the server name to easy recognition on the network store. 

·        All this to be scripted in batch scripting and using a free compress utility (7z) command line utility to include compress in the script. 

·        Donwload 7z and install it to program files as we need to add this install path to environment variable PATH to use it in script directly 

REM Sharepoint ULS and IIS Log cleanup script (backup and compress)
REM Venu Madhav
REM 18/july/2013
REM Enter the file path for the ULS log directory path
SET log=D:LogsIIS
REM ENTER no of days old logs to be archived
SET days=15
REM Enter the location for the temporary logs backup and archive file location
SET backup=d:archive
REM sets the compress utility directory  to PATH variable
REM SET PATH=%Path%;C:Program Files7-Zip
REM copy the current logs directory structure
mkdir %backup%
echo D | xcopy %log% %backup% /t
REM copies all the *.log files matching to the criteria in root directory initially
forfiles -p "%log%" -m *.log -d %days% -c "cmd /c copy /Y @path  %backup%@file"
PUSHD %log%
FOR /F %%v IN ('dir/ad/b') DO forfiles -p "%log%%%v" -m *.log -d -%days% -c "cmd /c copy @path %backup%%%v@file"
REM creating a compress file with hostname if it already exists it appends these logs either in local directory or network location
for /F %%v in ('hostname') DO 7z a %backup%*
REM once the logs are compressed remove the log files meeting the criteria
forfiles -p "%log%" -m *.log -s -d -%days% -c "cmd /c del @path /F /Q"
REM removing the temporary folder after the log compressed and zipped
rmdir %backup% /S /Q