How see Present size accupied in Data and Log file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How see Present size accupied in Data and Log file

Author

Topic

sponguru_dba
Yak Posting Veteran

93 Posts

Posted - 2006-12-04 : 01:29:16

Hi all

I have a Database, with automatic file growth of Datafile is 10% and logfile growth is 5%

but i want see exactly what is present occupied size data file and as well as log file

Using "select * from sysfile" present size of Data file but not present data occupied size

SreenivasaRao
Bangalore,INDIA

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-04 : 01:42:58

check out the sp_spaceused sproc and see if what you need is inside the sp

--------------------
keeping it simple...

sponguru_dba
Yak Posting Veteran

93 Posts

Posted - 2006-12-04 : 01:52:46

Thanks Jen

SreenivasaRao
Bangalore,INDIA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-04 : 11:12:00

I believe this is what you are looking for:


select
	[DB File Name] =
	convert(nvarchar(40),Name),
	[Size MB] =
	-- Divide by 128 to convert from pages to MB
	convert(decimal(10,2),round(size/128.0000,2)),
	[Used MB] =
	convert(decimal(10,2),round(fileproperty(name,'SpaceUsed')/128.0000,2))
from
	sysfiles
order by
	fileid

Results:


DB File Name                             Size MB      Used MB      
---------------------------------------- ------------ ------------ 
master                                   14.25        13.63
mastlog                                  2.00         .79

(2 row(s) affected)

CODO ERGO SUM

dewacorp.alliances

452 Posts

Posted - 2006-12-04 : 14:37:02

Thanks a lot Michael ... I tried to find this script and I couldn't find. Don't realise that you can use fileproperty.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources