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