Useful SQL Queries for SharePoint Practitioners:

The SQL queries listed below can be very useful for SharePoint Farm administrators to manage/administer the SharePoint content databases .

Note: The queries mentioned below are not specific only to SharePoint databases and can be used with any SQL database.

1.To get the total number of space utilized by all the SharePoint databases in SQL server:

SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace
FROM master.sys.master_files

2.To get the name of all the SharePoint databases in a SQL instance :

Select * from Sys.Databases

3.To get the total number of space utilized by all the SharePoint databases in SQL server:

SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace

FROM master.sys.master_files

4.To find the space used by a SharePoint DB and its free size :

*Replace MY_DB with the concerned database name

use “MY_DB”
exec sp_spaceused

5.To find the size consumed by SharePoint Databases individually in SQL Server:

SELECT DB_NAME(database_id) AS DatabaseName,

 

Name AS Logical_Name,

 

Physical_Name, (size*8)/1024 SizeMB

 

FROM sys.master_files

6.To get the total number of SharePoint databases in the SQL server:

select  * from sys.databases

or

select  COUNT(*) from sys.databases

7.To find the path for SQL Server error logs:

sp_readerrorlog

8.To get the total number of site collections in a Web application :

select  count(*) as ‘Total Site Collection’ from sites

Note: Point to the content database hosting that site collection and run this query

9.To get the total number of sites in a web application :

select count(*) from Webs

Note: Point to the content database hosting that site collection and run this query

10.To get the Site Title and Site ID :

select Title as ‘Site title’,FullUrl, SiteId as ‘Site Collection Id’ from Webs order by SiteId

11.To get the number of sites under each site collection in a web application :

select SiteId, count(*) as ‘Total Sub Sites’ from Webs inner join Sites on Sites.Id = Webs.SiteId group by SiteId

Note: Point to the content database hosting that site collection and run this query

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s