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 |
|
GreatInca
Posting Yak Master
102 Posts |
 Posted - 2002-03-12 : 16:26:57
|
| I need to estimate how much time is left before running out of space in one of the arrays. Is there a Quick Way to find free space in a filegroup? Got write-heavy tables in one array and other tables on another. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-12 : 16:37:52
|
| The only one I'm aware of is sp_spaceused. You can use it on individual tables (pass the table name) or the entire database. There's no option for filegroups though, but checking each table in the filegroup will at least tell you how much space they use. |
 |
|
|
Jay99
468 Posts |
Posted - 2002-03-12 : 16:42:51
|
| Other than that you can view the taskpad in Enterprise Manager and look at the Space Allocated tab(?). This is just using SpaceAvailableInMB Property of the DBFile Object (SQL-DMO), so you could write your own custom app to monitor these . . .Jay |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-03-13 : 10:51:51
|
| Hey there,I KNOW THAT THE FOLLOWING MIGHT NOT BE CONSIDERED A QUICK WAY - because it isn't (first time round) , let's face it. However, when you're finished, it will be quick thereafter, and re-usable. If you put it all into a script or an SP, next time it's a 5 second job. Here goes:SYSPERFINFO in master has an entry per DB for the first 99 DB's on a server. (If you've got more than that, this won't work for you).The following will give you space used:select instance_name,cntr_valuefrom sysperfinfowhere object_name = 'SQLServer:Databases'and counter_name = 'Data File(s) Size (KB)'or counter_name = 'Log File(s) Size (KB)'You can do some playing around to check what drives the files are on (check out sysfilegroups, sysfiles and sysobjects for that, I think), and then do either compare against fixed values (let's face, total harddrive space isn't going to change on it's own), or else get some space available figures for the drives ( anyone know of an easier way than parsing the output of an xp_cmdshell 'dir c:', for the free space at the end, formatting the result into a number).That will give you (if you do some constructive summing of the data from the query above) space used by data files, space used by logs, and you should be able to extract/calculate total space available on the specific drives...I KNOW that some will say that the format of the MS system table may change with releases, but since I see this, and similar scripts, to be relatively little work compared to the time saved, re-doing them when (and if) SQL Server knew editions/pathces cause them to need changes shouldn't be that big a thing. Plus you learn more about SQL, and what it knows, and stores ...My 2 cents worth (2 South African cents = 0.18 Amercian cents - sigh ...)ciao |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-13 : 12:11:23
|
| Wanderer's solution is great as long as you only have one filegroup per database. I think the question was directed at a situation where there are multiple filegroups per database and you want to see how the free space is distributed.Jay |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-03-14 : 02:28:25
|
| Ooops ... my bad.Comes from reading too quickly, and making assumptions. And we all know where assumptions lead... |
|
|
|
|
|
|
|
|
QuickWay to find free space in a filegroup or file