SQL - Size of Database and Logs

One morning I noticed the usage on the drives of a SQL server increased by a lot from the previous day. I noticed because part of the morning checks was to, errrrrr, check the diskspace on said server.

Previous posts will show which script I used on a daily basis to check this.

What I wanted to do next was to find which DB and Logs were taking the space up (this particular server had Logs with DBs on the same drive).

The first one script I came across was:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files

--For specific Databases
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO

From the Blog sqlauthority.

It gave me the information I needed, or thought I needed.

When checking the tempdb it turns out that this was the problem - it had grown quite large and the only safe way to clear down a tempdb is go into single-user mode or when all tempdb activity is stopped. See TechNet article.Alternatively restart the instance.......

I found some other scripts to use (sorry can't remember where I found them to pass on their names and links!). Script1 I found at a number of sites.

Script1
SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
ORDER BY d.name

Script2
--SQL Server 2000
select
fileID
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where db_name(dbid) = 'master'

--SQL Server 2000
select
fileID
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where filename like '%.ldf%'

--SQL Server 2005/2008
SELECT DB_NAME(database_id) AS DBName,
Name AS LogicalName,
Physical_Name, (size*8)/1024 SizeMB
Physical_Name, ((size*8)/1024)/1024 SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Master'
GO