Posts

Quick Inventory of SharePoint databases

Inventory SharePoint databases

Often I have to quickly analyze SharePoint databases; both Content DBs as well as the full set of existing databases. This could be to see which of the databases are still a part of the farm. Here’s how to quickly see which Content DBs are mounted in the Farm:

get-spcontentdatabase

For the more general set of all databases associated with the farm, it is nice to know the type of each database. Here’s a quick query to get that for you:

Get-SPDatabase | select name, type

Happy farming!

Sync DB grows indefinitely

Sync DB is a database that is a part of the User Profile Service related to Active Directory synchronization.   Unfortunately it tends to grow in an unconstrained fashion.  SharePoint has no built-in cleanup mechanism.  The table that grows without bounds is “InstanceData”.  The followed Stored Procedure should be first run in waves, so as not to overload the transaction logs or tempDB.  Running on at least a monthly basis is recommended.

The following is a report that shows monthly growth in rowcount:

SELECT COUNT ([lastUpdated]) as countOfInstancedata, month ([created]) as month, YEAR([created]) as year
       FROM [Sync DB].[dbo].[InstanceData]
  group by month ([created]), YEAR ([created]) order by YEAR ([created]), month ([created])

Here’s the stored procedure:

USE [Sync DB]
GO
CREATE SCHEMA FIM
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [fim].[TruncateInstanceData]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @truncationTime datetime;
SET @truncationTime = DATEADD(day, -1, GETUTCDATE());
DELETE FROM [dbo].[InstanceData]
WHERE ([created] < @truncationTime)
END

Alternatively, I’ve been running this loop to gradually delete rows.  You can adjust the loop number, delete size (rowcount) and delay to taste:

EXEC sp_spaceused N'dbo.Instancedata';
 -- Declare local variables
DECLARE @NumberOfLoops AS int;
SET @NumberOfLoops = 500;
DECLARE @CurrentLoop AS int;
SET @CurrentLoop = 0
WHILE @CurrentLoop < @NumberOfLoops BEGIN
set rowcount 10000
delete from [Sync DB].[dbo].[InstanceData] 
where [Sync DB].[dbo].[InstanceData].created <CONVERT(DATETIME,'2012-02-01 00:00:00', 102)
WAITFOR DELAY '00:00:01:00';
SET @CurrentLoop = @CurrentLoop + 1;END
-- Check space used by table after we are done
EXEC sp_spaceused N'dbo.BigLoggingTable';

A more generic WHERE clause I’ve successfully used is:
WHERE [Sync DB1].[dbo].[InstanceData].created <= DATEADD(day, -60, GETUTCDATE())