Donnerstag, 1. Dezember 2011

COUNT and SIZE of all docs in your SP2010 contend DB

There are a lot of use cases and scenarios you have to know the size and the count of the docs in you SharePoint content db. You can find it out in different ways, using the PowerShell , the Search (with a query like "%% -aspx -bdc3 -file") or 3th party tools. Another ways, and of cause a really smart one, is querying directly the SP contend db. The query if fired with a NOLOCK hint, so the load impact on your SharePoint system is really low.
The following scripts are written for SharePoint 2010:
--"count" and "size" of all docs in your SP2010 contend DB
--run script against your SharePoint 2010 wss_Content..... db
SELECT
COUNT(*) AS 'Count',
(SUM(CONVERT(bigint,Size))/1024) AS 'Size in kbyte',
((SUM(Size)/1024)/COUNT(*)) AS 'AVG Size per Doc. in kbyte'
from dbo.AllDocs WITH(NOLOCK)
WHERE ID IN
(
SELECT tp_DocID FROM dbo.AllUserData WITH(NOLOCK)--comment this clause out if you also want include the SP systemfiles
)
AND Size is NOT NULL
AND Extension != '000'

--"count" and "size" of all docs in your SP2010 contend DB group by fileextension
--run script against your SharePoint 2010 wss_Content..... db
SELECT
COUNT(ID) AS 'Count',
(SUM(CONVERT(bigint,Size))/1024) AS 'Size in kbyte', --covert because of an "int" overflowrisk in a lager db
Extension AS 'Filetype',
((SUM(Size)/1024)/COUNT(*)) AS 'AVG Size per Doc. in kbyte'
from dbo.AllDocs WITH(NOLOCK)
WHERE ID IN
(
SELECT tp_DocID FROM dbo.AllUserData WITH(NOLOCK) --comment this clause out if you also want include the SP systemfiles
)
AND Size is NOT NULL
AND Extension != '000'
GROUP BY
Extension
ORDER BY 'Size in kbyte' DESC

1 Kommentar: