If anyone's curious, I ended up doing this. It gives me something, but I'm not really convinced that the data is good enough to be better than no data.
-- Set up table
-- Put this somewhere other than TempDB if you want it to persist across restarts
USE TempDB
DROP TABLE Sessions
CREATE TABLE Sessions (last_request_end_time datetime, login_name varchar(100), reads int,
writes int, cpu_time bigint)
-- Insert info about sessions from sys.dm_exec_sessions
INSERT INTO Sessions (last_request_end_time, login_name, reads, writes, cpu_time)
SELECT last_request_end_time, login_name, reads, writes, cpu_time
FROM sys.dm_exec_sessions
/*
In a loop, every 100ms insert data from sys.dm_exec_sessions where
last_request_end_time is greater than what has already been written
to the sessions table.
This data will be inaccurate in that very short-lived
sessions might never get counted, and long-running sessions will get
activity counted multiple times. As a result, you might get very different results
depending on what the WAITFOR delay is set to.
Optionally run this part as an Agent job.
*/
WHILE (2 > 1)
BEGIN
INSERT INTO Sessions (last_request_end_time, login_name, reads, writes, cpu_time)
SELECT last_request_end_time, login_name, reads, writes, cpu_time
FROM sys.dm_exec_sessions
WHERE last_request_end_time > (SELECT MAX(last_request_end_time) FROM Sessions)
WAITFOR DELAY '00:00:00.100';
END
-- After exiting the loop (stop Agent job, manually abort, etc) report on results
SELECT login_name,
SUM(reads) AS reads,
SUM(writes) AS writes,
SUM(cpu_time) AS cpu_time,
COUNT (login_name) AS session_count
FROM TempDB.dbo.Sessions
GROUP BY login_name
Here is some old code I wrote for SQL 2000. It still works on SQL 2005. You can use this to come up with a rough estimate of how much space you are using per document, then multiply that by your estimated number of documents.
select 'CatalogName' = left([name],30),
'Status' = case(FULLTEXTCATALOGPROPERTY ([name],'populatestatus'))
when 0 then 'Idle'
when 1 then 'Full population in progress'
when 2 then 'Paused'
when 3 then 'Throttled'
when 4 then 'Recovering'
when 5 then 'Shutdown'
when 6 then 'Incremental population in progress'
when 7 then 'Building index'
when 8 then 'Disk is full. Paused.'
when 9 then 'Change tracking'
else 'Unknown'
end,
'ItemCount' = FULLTEXTCATALOGPROPERTY (name,'itemcount'),
'IndexSize(MB)' = FULLTEXTCATALOGPROPERTY (name,'indexsize'),
'UniqueWords' = FULLTEXTCATALOGPROPERTY (name,'uniquekeycount'),
'ErrorLogBytes' = FULLTEXTCATALOGPROPERTY (name,'logsize'),
'Location' = left(s.path,50)
from sysfulltextcatalogs s
Best Answer
This sounds like a job for SP_BLITZ,SP_WHOISACTIVE etc. By Brent Ozar & Team.
https://www.brentozar.com/first-aid/
Edit, I'm not advertising for them (it's a free resource anyway). This is exactly what I've used for this exact scenario. 3rd party apps and their rogue developers killing the DB.
You might also want to limit them to using views/procedures that you write/have control of in order to get their data.
Edit2: And I just realized this is an old question that got bumped somehow.
My answer stands.