Space

----Log File(s) Used Size (KB)---
select instance_name
,cntr_value 'Log File(s) Used Size (KB)'
from sys.dm_os_performance_counters
where counter_name = 'Log File(s) Used Size (KB)'
and instance_name like '%%'

---Check total log space taken up by all DB--
select sum(cntr_value)
from sys.dm_os_performance_counters
where counter_name = 'Log File(s) Used Size (KB)'
and instance_name like '%%'


----Data File(s) Used Size (KB)---
select instance_name
,cntr_value 'Data File(s) Size (KB) '
from sys.dm_os_performance_counters
where counter_name = 'Data File(s) Size (KB) '
and instance_name like '%%'
order by 'Data File(s) Size (KB) '

---Check total data space taken up by all DB---
select sum(cntr_value)
from sys.dm_os_performance_counters
where counter_name = 'Data File(s) Size (KB) '
and instance_name like '%%'


SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info

--TempDB analysis for space--
USE tempdb
GO
EXEC sp_spaceused

--The following should give you some clues as to which table(s) consume most of the space in the data file(s) -- this will help you narrow down any transactions that are either taking a long time or repeatedly being left in limbo:
USE tempdb
GO

SELECT name
FROM tempdb..sysobjects

SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC

--If you can't shrink the log, it might be due to an uncommitted transaction. See if you have any long-running transactions with the following command:

DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb')

--Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with 'SPID (Server Process ID) : '). Use that in the following:

DBCC INPUTBUFFER()

--This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine if you want to end this process with:

KILL

-------DATABASE : Tablename ROWCOUNTS----------------------------
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC



--The first thing you can do is simply compare the difference between the timestamp BEFORE your query, and the timestamp AFTER. For example:

DECLARE @a DATETIME, @b DATETIME
SET @a = CURRENT_TIMESTAMP

DECLARE @i INT
SET @i = 0
WHILE @i < 10000
BEGIN
SET @i = @i + 1
END
SET @b = CURRENT_TIMESTAMP
SELECT DATEDIFF(MS, @a, @b)


--You can achieve similar results by running SQL Profiler, setting appropriate filters, and watching the Duration column as your query runs.

--Finally, you can alter the above code slightly so that you see all of the durations on the messages tab of Query Analyzer:

SET STATISTICS TIME ON
-- query here
SET STATISTICS TIME OFF


/*To find the number of unallocated pages in kb,we can use the sys.dm_db_file_space_usage DMV as follows: */
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*8) AS [free space in KB]
FROM sys.dm_db_file_space_usage

select a.session_id
, b.login_name
FROM sys.dm_exec_connections a ,sys.dm_exec_sessions b
WHERE a.session_id=b.session_id