Eric Jochens on SharePoint

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_Co…