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.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2012-12-25 : 14:45:00
|
| I want to track the size of every database's data file into a table so I can project future growth patterns.Does anyone have a solid technique, that can be ran programatically?Note - I don't the file size - that is how much space that's allocated. I want to find the amount of space actually used. If the data file were shrunk to minimum size, if that makes sense.Thanks for any tips.Jack |
|
|
v_yaduvanshi
Starting Member
11 Posts |
Posted - 2012-12-26 : 05:19:50
|
| u may check thisSelect db_name(),fileid,case when groupid = 0 then 'log file' else 'data file' end,name,filename, [file_size] = convert(int,round((sysfiles.size*1.000)/128.000,0)),[space_used] =convert(int,round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,0)),[space_left] =convert(int,round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,0))from dbo.sysfiles; |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2012-12-27 : 16:56:51
|
| Thank you v_ Your technique seesm to compute a size that is accurate.This will work if I hardcode for each known database name with a preceeding Use DatabaseX;I was hoping for a way of looping thru all databases in a stored procedure so it can automatically pick up any new databases.Thanks for your idea. Jack |
|
|
|
|
|
|
|
|
Reliable Method for Determining Data File Size