SQL Query: Set All Dbs AutoGrowth



The following are recommendations to proactively manage the growth of data and log files:

When possible, increase all data files and log files to their expected final size, or periodically increase these at set periods, for example, every month or every six months, or before rollout of a new storage-intensive site such as during file migrations.

Enable database autogrowth as a protective measure to make sure that you do not run out of space in data and log files. Consider the following:


You must factor in the performance and operations issues associated with using autogrowth. For more information, see Considerations for the “autogrow” and “autoshrink” settings in SQL Server.

Default Settings

The default settings for a new database are to grow by 1 MB increments. Because this default setting for autogrowth results in an increases in the size of the database, do not rely on the default setting. Instead, use the guidance provided in Set SQL Server options.


Set autogrowth values to a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be.

NoteNote:  Use care when you set the autogrowth feature for SharePoint databases. If you set a database to autogrowth as a percentage, for example at a 10-percent (%) growth rate, a database that is 5 GB grows by 500MB every time that a data file has to be expanded. In this scenario, you could run out of disk space. or the db could be 100gb and growth would be 10gb every time the file needed space

Consider for example, a scenario where content is gradually increased, say at 100MB increments, and autogrowth is set at 10MB. Then suddenly a new document management site requires a very large amount of data storage, perhaps with initial size of 50 GB. For this large addition, growth at 500 MB increments is more appropriate than 10MB increments.

For a managed production system, consider autogrowth to be merely a contingency for unexpected growth. Do not use the autogrow option to manage your data and log growth on a day-to-day basis. Instead, set the autogrowth to allow for an approximate size in one year and then add a 20 percent margin for error. Also set an alert to notify you when the database runs low on space or approaches a maximum size.

Maintain a level of at least 25 percent available space across drives to accommodate growth and peak usage patterns. If you add drives to a RAID array or allocate more storage to manage, monitor capacity closely to avoid running out of space. Setting autogrowth to use MB verse %  along with changing the autogrowth MB size will create less fragmentation than using the defaults, this SQL Query makes it easier to modify a large number of Dbs.

-- Query to Set File AutoGrowth

    DB_NAME(mf.database_id) database_name,
    mf.name logical_name,
    CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, size) / 128)) [file_size_MB],
    CASE mf.is_percent_growth
        WHEN 1 THEN 'Yes'
        ELSE 'No'
    END AS [is_percent_growth],
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
        WHEN 0 THEN CONVERT(VARCHAR, mf.growth / 128) + ' MB'
    END AS [growth_in_increment_of],
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(DECIMAL(20, 2), (((CONVERT(DECIMAL, size) * growth) / 100) * 8) / 1024)
        WHEN 0 THEN CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, growth) / 128))
    END AS [next_auto_growth_size_MB],
    CASE mf.max_size
        WHEN 0 THEN 'No growth is allowed'
        WHEN -1 THEN 'File will grow until the disk is full'
        ELSE CONVERT(VARCHAR, mf.max_size)

    END AS [max_size],
FROM sys.master_files mf
ORDER BY database_name


Download: Set_AutoGrowth4AllDbs.sql



SQL Query: Move TempDb Files to separate LUNs



Tempdb Multiple Files

One of the important issues when hosting multiple Content Dbs with multiple terabytes data is to ensure that you have created a TempDB with additional files of the same size and once created move teach of the files to their own Volume.

Do NOT use ISCSI for SQL Dbs

In this case we are using a NetApp SAN with Fiber Chanel HBAs as iSCSI does NOT meet our SQL performance requirements. I Microsoft provides multiple whitepapers against using iSCSI for SQL Dbs. Also, I have seen performance degradation using iSCSI once the ContentDBs reach about 50GB in total size. Using Fiber Chanel I have been able to host multiple terabytes of ContentDbs without any degradation in performance.


This server has 2 PROCS with 24 Cores, and 128GB of RAM with tempdb having 1 MDF, 2 NDF, and 1 LDF files on separate LUNS.. We used this script to move the original tempdb files to the SAN and then once provisioned to move the NDF files to separate LUNs.

NAME = Tempdb file Logical Name

FILENAME = the New path with existing filename where you want the file moved




Screenshot of OLD Locations


Screenshot NEW Locations

-- Query to Move tempDB

    physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

USE master;

MODIFY FILE (NAME = tempdev, FILENAME = 'K:\MSSQL\Data\tempdb.mdf');

MODIFY FILE (NAME = templog, FILENAME = 'L:\MSSQL\Data\templog.ldf');

    physical_name AS CurrentLocation,

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');


Note: you will need to restart the SQL Server Instance to have the move take affect


Download SET_TempDBLocation.ps1



PowerShell: Run IISReset on All Servers in your farm at the same time




One of the many things scripts are good for in general  is making repetitive tasks easier and the results more consistent. PowerShell takes it to another level with its intuitive cmdlets . I find it much easier run a script from my laptop or log into a single server rather than using MSTSC to login to every server in the farm, I am working in a SharePoint environment of 25 servers, so it wouldn’t definitely be a drag..




PowerShell Script

<#    IIS-Reset.ps1
Run IISReset on Multiple Servers #>

#Specify servers in an array variable
[array]$servers = "Server1","Server2","Server3","Server4"

#Step through each server in the array and perform an IISRESET
foreach ($server in $servers)
    Write-Host "Restarting IIS on server $server..."
    IISRESET $server /noforce
    Write-Host "IIS status for server $server"
    IISRESET $server /status
Write-Host IIS has been restarted on all servers

Download http://1drv.ms/1ZjF889



del.icio.us Tags: PowerShell,IISReset,SharePoint

PowerShell: Upgrade WAC – your Office Web Apps Farm

Like most folks who upgrade their SharePoint 2013 farms after they have applied the latest SharePoint 2013 CUs to the SharePoint side of the environment they will usually still have 2 WAC servers and at least 3 WFM servers left to apply and configure updates. This may depend on release of the updates as the Service Bus and Workflow manager updates do not coincide with the monthly delivery of SharePoint 2013 CUs.




The reason for this post is to make it easy for me (not to forget) updating the WAC Servers / Office Web App Farm. Updating the Office Web App farm is somewhat unique in that you remove the farm prior to the installing the CU then create a new farm after the cumulative update installed





PowerShell Script

# Update-WAC.ps1
# Add July 2015 CU

Import-Module -Name OfficeWebApps 

# Review the Current State of the Office Web App Environment
cmd /c pause 

# Remove OfficeWebAppMachine prior to installing Cumulative Update
cmd /c pause

# Install the Cummulative Update while paused

# Configure Office WebApp Farm after installing the updates
# If using HTTP remove comment below
# New-OfficeWebAppsFarm -InternalURL "http://wac.contoso.com" -AllowHTTP -EditingEnabled

New-OfficeWebAppsFarm -InternalURL "https://wac.contoso.com" -ExternalURL "https://wac.contoso.com" -CertificateName "wac.contoso.com" -EditingEnabled
cmd /c pause

# Open IE to test and ensure the New OfficeWebApps Farm is configured
$ie = New-Object -ComObject InternetExplorer.Application
$ie.Visible = $true
If successful your browser will open and will look the example below



Download http://1drv.ms/1JCmcKm



Metalogix Content Matrix 7.3.x – Bugs


SharePoint 2013 and Metalogix Content Matrix

SharePoint is mission-critical and users demand availability. Content Matrix allows you to migrate SharePoint by site collection, site, list, library, business unit, or department with zero downtime. Run old and new farms in parallel and test and re-arrange as often needed. Along with the Re-Organizer feature empowers your site collection owners and users, to manage SharePoint sites, content and metadata on an ongoing basis to keep in sync with business needs. All of my clients love this tool as it makes their environments more flexible and now with Re-Organizer we are enabling the site owners to more easily manage their site structure.





Error on Installation of Content Matrix 7.3.0002

During the installation of the Online edition of Content Matrix version 7.3.0002 while installing Metalogix Extensions Web Services you will see the error "MD5 check failed for the current file to be staged, the file may be corrupt" in the screenshot to the left. However, the installation will continue and complete. Also, if you go to the location in your AppData Folder and copy the "Metalogix SharePoint Extensions Web Service Setup.msi" it will run locally without any issues. The error appears to be related to the msi not matching the online manifest





Error Copying List Views Content Matrix 7.3.xxxx

Unfortunately, the installation error is not the only issue. The infamous "Object Reference not set to an instance of an object" error occurs when you attempt to copy list views. However, if you copy the complete list all views are included in the copy. In looking at the error details it appears to be caused by a dialog box, this makes sense as it never creates the job or writes to any log.








Error Copying Sites Lists or Library’s  Content Matrix Organizer 7.3.xxxx

The last symptom was when attempting to copy / move anything  using the Reorganize from the Widget Drop Down or the Ribbon of the list…


We initially found and reported these errors on 12/18/2015..

At first we thought incorrectly the error only occurred after an upgrade from an earlier version of Content Matrix, after further testing we found the issues existed in all 7.3.x versions whether installed as part of an upgrade or installed in a pristine environment. As always Metalogix Content Matrix is our favorite tool for SharePoint Migrations whether on-prem or online and Metalogix support is very helpful.  The only resolution at this time is to revert to a previous version Metalogix Content Matrix version 7.2.0017







SharePoint 2013 Unable to Open Documents from Ribbon when WOPI is Configured and Library Default open behavior for browser-enabled documents is Open in the client application

It appears to be an IE issue when clicking on the New Document Drop down from the ribbon using fiddler4 there are not any results returned, probably jscript or jquery … I am running IE11 and I have tested IE10 which exhibits the same behavior. I haven’t tested on earlier versions yet.

However, if you open with Mozilla the Microsoft Office is blocked until you Allow Mozilla to open Office Documents and then Default open behavior for browser-enabled documents works as expected. In other words when the library is set to open ion the browser (via WOPI) the documents open in the browser, when set to open in the client application, the docs open in the client as expected..

1. Click On Allow



2. Then choose to Allow remember


3. Then choose OK,



4. Click on New Document one more time to open from the ribbon



Note: this only occurs if you have WAC installed and configured WOPI with SharePoint. Also, you only have to go to library settings to set the Default open behavior for browser-enabled documents: to Open in the client application, you don’t have to set the site collection… I will resolve as time permits




Embed a PowerPoint Presentation into a SharePoint 2013 Page with Multiple Farms

It seems like everyone NothingButSharePoint and Microsoft Office and Microsoft Bogs states that you can not embed unless your using Office 365 (this may have been due to using SharePoint 2010). There are many more post and articles that require you to use Onedrive.. There are probably many better ways to sharing Content using WAC with multiple farms and SharePoint On-Prem and I would enjoy hearing about how you have solved this issue

Embed a PowerPoint Presentation from a source SharePoint Farm and render the presentation in a target SharePoint Farm using CEWP


1. Go to http://focalpoint.cotoso.com and search for PPTX

2. Use the Drop Down, Choose embedded information, and Copy All

3. Go to http://learning.fabricam.com

5. Add Content Editor WebPart


6. Click Inside the WebPart Choose the Orange Insert, Click Embed Code


7. Insert (Paste CTRL+V) the Code you copied from http://focalpoint.contoso.com


8. Then Click the Insert button,

9. Next Edit the WebPart, and change the Width to 540px, then click OK


10. Click Check-in, then Publish this Draft


Note: The Site Collection Feature – Cross-Farm Site Permissions is activated on both Farms, the cross-farm site permissions feature to allow internal SharePoint applications to access websites across farms.



SharePoint Saturday UTAH 2015

Register now!

Registration is still open. See our SPSEvents site for registration, session, and speaker information.


You can’t have SPS without #ShareSki, especially here in Utah! ShareSki is the day before SharePoint Saturday (Friday, Feb 27) on The Greatest Snow on Earth® at Snowbird! Meet at the Gadzoom lift between 9:00 and 9:45. We’ll be taking runs off this lift–returning to it–until 9:45, at which time we can’t guarantee a meetup. If you miss us at Gadzoom or loose us, we’ll have lunch in the Creekside Cafe and Grill at 12:00. Please arrive no later than 12:10 so we can get everyone through checkout in one shot. Go to snowbird.com to purchase your tickets online for $80 (save 16%).


#SharePint is the après-event social of SharePoint Saturday. This gathering is primo for networking and socializing with fellow SharePoint connoisseurs. SharePint will be held at Red Rock Brewing Co., 254 South 200 West, Salt Lake City, starting approximately 6:30.


Special thanks to our sponsors for making this event possible!






Ivan Sanders
SharePoint MVP / MCT


clip_image001 clip_image002 clip_image003 clip_image004 clip_image005

Technorati Tags: SharePoint Saturday UTAH,Business Intelligence,Event

RSA Web Agent 7.1.3 SharePoint WFEs Authentication with Multiple IP Addresses



If you are unable to authenticate the after installation and configuration of the RSA WebAgent, you have downloaded the agent from EMC http://www.emc.com/security/rsa-securid/rsa-authentication-agents/iis-7-1.htm,  followed the installation instructions.

Configuration and Errors

  1. You have created and copied the sdconf.rec file to %windir%/system32 and to the C:\Program files\RSA Security\RSAWebAgent
  2. When attempting to authenticate you receive “100:Access denied. The RSA ACE/Server rejected the Passcode. Please try again.”
  3. The application Event viewer  there is an Event ID: 1012, Source: ACEClIENT “Multihomed host detected: Primary IP assumed xxx.xxx.xxx.xxx” and this is not the IP Address you are using for your Extended Web App.

The secureid file will not be created and you will not be able to authenticate



RSA Rulesimage

Whenever there are multiple IP Addresses (Multi homed Servers) on a Web Server and the Default Address  is not the primary Addresses used for RSA Authentication . The RSA Agent will NOT create the SecureID file on first authentication and the request will timeout…

  1. If the SharePoint server is configured as the WFE, and you are installing the RSA WebAgent to secure external access, ensure the Local System account has read/write access for the following registry key: HKLM\Software\SDTI\ACECLIENT. This ensures that SharePoint WFE is able to write the secret to the registry.
  2. If the SharePoint WFE  has multiple IP Addresses, you will need to explicitly configure the network adapter address through which SharePoint WFE connects to the RSA Authentication Manager for authentication or authentication will fail. To do this, create a new String Value PrimaryInterfaceIP in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\SDTI\AceClient\.
  3. Double Click on  HKEY_LOCAL_MACHINE\SOFTWARE\SDTI\AceClient\PrimaryInterfaceIP 
  4. Add the IP Address as the value data.  The value specified must match that set in the agent host record.


Don’t forget SharePoint Saturday in Utah next Saturday


SharePoint administrators, end users, architects, developers, and other professionals that work with Microsoft SharePoint Technologies will meet for:

SharePoint Saturday Utah on February 28th 2015 at the University of Utah David Eccles School of Business located at 1655 Campus Center Dr. Salt Lake City, UT 84112.







Ivan Sanders
SharePoint MVP / MCT

Blog: http://blogs.msmvps.com/ivansanders/

clip_image002 clip_image004 clip_image006 clip_image007 clip_image009 clip_image010

Metalogix Content Matrix PowerShell Copy Jobs “Failed to Find the main assembly”

If you have upgraded from a previous version of Metalogic Content Matrix SharePoint edition or have multiple Content Matrix editions (SharePoint, Fileshare, Blog) deployed you may receive the error “Failed to find the main application assembly” when attempting to run any of your PowerShell Copy Job Scripts. If you are not running the Metalogix copy jobs in PowerShell you will not be affected by this issue.



The resolution that worked for me was to uninstall Metalogix Content Matrix Consoles, Content Organizer and the Content Matrix WSPs. Open a command prompt in admin mode and run Gacutil /lr > E:\Apps\gacutil.txt to pipe the contents of the GAC to a txt file to review. In one environment I found two assemblies were not being removed Metalogix.System.Commands.dll and Metalogix.SharePoint.Adapters.StoragePoint.dll. The setup uses Installutil and was unable uninstall the two assemblies listed previously.

From the open command prompt run the followingimage

  • gacutil /u Metalogix.System.Commands.dll
  • gacutil /u Metalogix.SharePoint.Adapters.StoragePoint.dll

substitute any assemblies you found for the ones I used as necessary


Download gacutil as part of the Microsoft Windows SDK for Windows 7 http://www.microsoft.com/en-us/download/details.aspx?id=8279. I spoke with Bill Wilcock the Western Regional Support Manager for Metalogix and “Metalogix has determined it is definitely the GAC assemblies and the issue should be resolved in the next hotfix release coming out in a week or two.” 

As I mentioned above, If you are not running the Metalogix copy jobs in PowerShell you will not be affected by this issue. But if your not running the copy jobs in PowerShell you should be; the jobs run at least 2x faster and you can set up a schedule in Task Scheduler to run your incremental jobs daily. This means that when its time to cut over,its very anticlimactic as the site admins, department admins, and end users have verified their content has been successfully migrated, they have time to get used to the new look and feel, and started using the new features prior to Go Live.





Blog: http://blogs.msmvps.com/ivansanders/

clip_image001[4] clip_image002[4] clip_image003[4] clip_image004[4] clip_image005[4] clip_image006


See you in Helsinki March 14th for SharePoint Saturday

Next Page »