SharePoint SQL Queries

My stash of useful SQL queries I use.  Particularly useful when doing migrations as tempdb files can grow out of hand and renaming, fixing databases are needed.

View the state and files for a database:

exec sp_helpdb [tempdb]

View the status of Transaction Log size and usage:

dbcc sqlperf(logspace)

Shrink a Transaction Log:

USE Test_Intranet_Content_Home;
GO

-- Truncate the log
ALTER DATABASE Test_Intranet_Content_Home
SET RECOVERY SIMPLE;
GO

-- Shrinks the log to number of Mb
DBCC SHRINKFILE (Test_Intranet_Content_Home_log, 1536);
GO

-- Reset the recovery model
ALTER DATABASE Test_Intranet_Content_Home
SET RECOVERY FULL;
GO

Rename Filenames (I often do this when migrating a content DB to a new farm):

-- Get the current database filenames
sp_helpdb Test_Technology_Content_Root
GO

-- Change Logical Filenames
 USE [Test_Technology_Content_Root]
ALTER DATABASE [Test_Technology_Content_Root]
      MODIFY FILE (NAME=N'SharePoint_PortalHome_Content', NEWNAME=N'Test_Technology_Content_Root')
ALTER DATABASE [Test_Technology_Content_Root]
      MODIFY FILE (NAME=N'SharePoint_PortalHome_Content_log', NEWNAME=N'Test_Technology_Content_Root_log')
GO

Fix URL Length Restrictions:

Recently I needed to migrate a content database to a new farm.  The client wished to have the root site collection in a new path, because 3 farms are being joined to live under a single web application and URL.  This requires a backup-spsite and restore-spsite to a new path.  It was failing, and the reason why is the old content database had document libraries that exceeded the 260 limit when restoring to the new longer URL.

Here is how to fix it, and honestly I run this against any content database before I attempt a restore-spsite to a new path.  Note: change the SELECT TOP 1000 to a larger integer to get more records if you find that you need more, I just like redcuing the results so I do not get 750,000+ items (which I did).

SQL Query:

USE [content_database]
SELECT
   CONCAT([DirName], N'/', [LeafName]) AS [FullRelativePath],
   LEN(CONCAT([DirName], N'/', [LeafName])) AS [Length]
FROM
   [dbo].[AllDocs]
ORDER BY
   [Length] DESC
GO

Comments

Popular posts from this blog

SharePoint Designer 2013 Approval Workflow with Comments

Change SharePoint server hostname and Web Application Names

The Timer Service Failed to Recycle