Free space per Filegroup

Hello,

[SQL2005]

Is there a sql script where I can find the free space and Used Space in GB, 
per Filegroup, for a database? It should report the consolidated usage of 
all files in the filegroup; if there is only one file in the filegroup then 
the script calculates for the single file.

tia,
AbbA 


0
Abba
3/29/2010 9:09:35 AM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
2006 Views

Similar Articles

[PageSpeed] 40

Hi

Just need to change the below from extents to GB and consolidate the 
filegroups.


create table #tmpspc 
(Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, 
FileName nchar(520))
insert #tmpspc EXEC ('dbcc showfilestats')

SELECT
g.name AS [FileGroupName],
s.name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(CASE s.type WHEN 2 THEN 0 ELSE tspc.UsedExtents*convert(float,64) END 
AS float) AS [UsedSpace]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and 
s.database_id = db_id() and (s.drop_lsn IS NULL)) AND 
(s.data_space_id=g.data_space_id)
LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id

drop table #tmpspc

Jon

"Abba" wrote:

> Hello,
> 
> [SQL2005]
> 
> Is there a sql script where I can find the free space and Used Space in GB, 
> per Filegroup, for a database? It should report the consolidated usage of 
> all files in the filegroup; if there is only one file in the filegroup then 
> the script calculates for the single file.
> 
> tia,
> AbbA 
> 
> 
> .
> 
0
Utf
3/29/2010 10:55:01 AM
I ended up using this:
SELECT  b.groupname AS 'File Group',Name, [Filename],
CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space 
(MB)],
CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS 
[Space Used (MB)],
CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) 
AS [Available Space (MB)]
FROM dbo.sysfiles a (NOLOCK)
JOIN sysfilegroups b (NOLOCK)
ON a.groupid = b.groupid
ORDER BY b.groupname

Thanks anyways, Jon!

"jgurgul" <jgurgul@discussions.microsoft.com> wrote in message 
news:AD8AB60A-D63F-4594-A09C-40FBA3461DB6@microsoft.com...
> Hi
>
> Just need to change the below from extents to GB and consolidate the
> filegroups.
>
>
> create table #tmpspc
> (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name 
> sysname,
> FileName nchar(520))
> insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> g.name AS [FileGroupName],
> s.name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(CASE s.type WHEN 2 THEN 0 ELSE tspc.UsedExtents*convert(float,64) END
> AS float) AS [UsedSpace]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and
> s.database_id = db_id() and (s.drop_lsn IS NULL)) AND
> (s.data_space_id=g.data_space_id)
> LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
>
> drop table #tmpspc
>
> Jon
>
> "Abba" wrote:
>
>> Hello,
>>
>> [SQL2005]
>>
>> Is there a sql script where I can find the free space and Used Space in 
>> GB,
>> per Filegroup, for a database? It should report the consolidated usage of
>> all files in the filegroup; if there is only one file in the filegroup 
>> then
>> the script calculates for the single file.
>>
>> tia,
>> AbbA
>>
>>
>> .
>> 


0
Abba
3/30/2010 8:01:40 AM
Reply:

Similar Artilces:

You a 7-Day FREE Trial
We Are Offering You a 7-Day FREE Trial to the internet's Hottest New Business Opportunity! If you missed out on the DOT COM boom, now is your chance to cash in on the massive and growing global demand in our $20 BILLION PER YEAR market. We'll show you how to create an income that will come to you for years and grow with each passing month. A SIMPLE, Fully-Automated, REJECTION-FREE Internet Marketing System! Finally, an EASY, proven, FREE system that will truly help anyone willing to follow our guidance succeed! Average People Are Making Money! Average people are putting their home...

One resource file per CDialog or VSS solution
Hi Is there any way that each Dialog has their own resource file ? I need that because I'm working on a big project having many dialog. Actually we are 4 programmers building interfaces. To share code we are using Visual Source Safe. The only problem is that we can't works on interface design at same time because of Resource ID conflict ! Is someone having a solution ? Best regards, Martin On Aug 28, 3:19=A0pm, Erakis <Era...@discussions.microsoft.com> wrote: > Hi > > Is there any way that each Dialog has their own resource file ? > > I need that because I&...

Multiple remit-to addresses per vendor
Is there a good way to handle multiple remit-to addresses per vendor? It seems like the only solution is to create one vendor per remit-to address (resulting in a bunch of vendors) or manually switching the remit-to address prior to printing checks (opening the door to more error). You can have an unlimited number of addresses for your vendors. Each address is titled with an address code which must be unique. On the vendor card you choose ONE address as the default Remit To but you can perform a remit to function to any address. cr >-----Original Message----- >Is there a good...

Pack and Go Disk Space Error
When I pack and go for a commerical printing service, I get an error message that I'm out of disk space. My publication is only a page but is fairly complex with graphics, charts, and text. I have 20GB of disk space free. Got any tips as on how to resolve this. If I creat a new document with only a little text and a couple small pictures, I am able to successfuly pack and go. Thank you! Bill, which version of Publisher are you using? If you have any BLENDS in your Publication, this can be an issue as the file sizes become humungous. -- After managing to set up OE-QuoteFix...

free dot interview questions information
..NET database dev questions http://freedownloadablebooks.blogspot.com/2008/03/net-database-dev-questions.html Some general quickies http://freedownloadablebooks.blogspot.com/2008/03/some-general-quickies.html ..NET WebDev interview questions - Part 1 http://freedownloadablebooks.blogspot.com/2008/03/net-webdev-interview-questions-part-1.html ..NET WebDev interview questions - Part 3 http://freedownloadablebooks.blogspot.com/2008/03/net-webdev-interview-questions-part-3.html Electronic engineer interview questions http://freedownloadablebooks.blogspot.com/2008/03/electronic-engineer-interview-...

freeing up space
If you delete an attachment or an image from a message, then save the changes, does that free up space in the store? On Wed, 22 Dec 2004 13:27:04 -0800, dlv <dlv@discussions.microsoft.com> wrote: >If you delete an attachment or an image from a message, then save the >changes, does that free up space in the store? Not enough to care about I would think. Unless you have a 10 GB attachment. ...

Missing Free/Busy Folder #2
I have read the other postings regarding the fix to the missing Free/Busy. When you follow the recommendations from the following KB articles: http://support.microsoft.com/default.aspx?scid=kb;en-us;284200 http://support.microsoft.com/default.aspx?scid=kb;en-us;275171 There is only one Administrative Group folder. Also the client has several folders created under the public folder. Will I need to reset their published folders under public? Also will there be any other information that I will need to rest after coping the new GUID ? Hi Michael, When we put in a new GUID we will dest...

Free books on Excel; Free Add-In
You may download the free books and software from http://www.vgupta.com (offer good till Dec 31 2003) Excel For Beginners Charting in Excel Excel- Beyond the Basics Managing & Tabulating Data in Excel Financial Analysis Using Excel Statistics Using Excel Software: "Excel Usability Enhancer" VJ VJ wrote: > You may download the free books and software from > http://www.vgupta.com (offer good till Dec 31 2003) > Software: "Excel Usability Enhancer" I note that the only significant installation instructions were:- "Double click to launch the add-in &q...

Don't add space between paragraphs of the same style
Hi, In Word, when defining a style, there is a paragraph format property titled "Don't add space between paragraphs of the same style". It's very convenient to add extra space before/after a block of paragraphs, such as a list. Is there any equivalent in Publisher or do I need to change the settings of each paragraph? Thanks. Raph ...

Any Links to Free Manuals for Word 2007?
Does anyone seem to know links to Free Good Quality manuals fro Office Word 2007? I did downlaod one from http://www.mousetraining.co.uk/ms-office-training-manuals.html but it doesn't include all the topics that I want to learn! Any help will be much appreciated! Amit http://office.microsoft.com/en-us/help/FX100485361033.aspx?pid=CL100605171033 Daddy "Rajeev Rawat" <rajeev.rawat1@googlemail.com> wrote in message news:b49a7fb1-2599-4529-8583-b03284aac736@y17g2000yqd.googlegroups.com... > Does anyone seem to know links to Free Good Quality manuals fro...

Site missing in System Folder
Hi, I have 7 sites in my Exchange 2000 organization. 4 of them was migrated from Exchange 5.5 to Exchange 2000, the other 3 are new. In system folders - schedule+ free busy of the 3 new sites I can only see the local site. On the other 4 sites I see only them selfs and none of the new sites. I think this is related to the problem that I have that people can't see each other's calendar when they make an appointment. Can someone help me solve this problem? Best reagards, S´┐Żnia Carvalho ...

Extend Published free/busy time, consequences.
Hello there. I am thinking of extending the published Free/Busy time in Outlook to a year or two into the future. But how about the consequences in doing so, as seen from the Exchange perspective. Before Exch 2007 this information was stored in a public folder, now in Exch 2007 the check is made directly in the calendar using OWA, so I guess that there should be no problem in doing so? Anyone got any info or best practice I can lean up against. Best regards Jesper. Free-busy data is pretty small so I doubt think you'll have any trouble. But, seriously, who...

Default Free/Busy Calendar time
I put a GPO in a few months ago to change the default free/busy time in outlook so that some of the Administrative Assistants could schedule things on their boss's calendars up to 4 months in advance. Since the time change/DST this gpo has stopped working. Has anyone seen this? The users haven't moved OU's, they haven't been upgraded, the GPO hasn't changed. If I'm posting this in the wrong group can someone point me the right way? ...

Free Manual
Oh By the way I downloaded the free Excel VBA manual from http://www.mousetraining.co.uk/training-manuals/Excel2003VBA.pdf but it doens't seem to provide me any answer for my specific query cheers Download Excel VBA Language Reference from Microsoft http://www.microsoft.com/downloads/details.aspx?familyid=2204a62e-4877-4563-8e83-4848dda796e4&displaylang=en -- Jacob "rraw75" wrote: > Oh By the way I downloaded the free Excel VBA manual from http://www.mousetraining.co.uk/training-manuals/Excel2003VBA.pdf but it doens't seem to provide me any answer for ...

Free e-books!
Hello all, I have update my e-books.. Please take a look: http://freebooks2007.blogspot.com ...

Text to columns, split at first space only
I have a 2500 line column of street addresses along the lines of (123 E Grand Maple). How can I split this column into two columns, first column only the numeric portion, second column only the street portion even though the street portion may contain spaces? For simplicity's sake I can assume that the first space in the address represents where the split should occur and that any subsequent spaces should be ignored. Assuming the addresses start in cell A2 =LEFT(A2,FIND(" ",A2)-1) =RIGHT(A2,LEN(A2)-FIND(" ",A2)) And drag down ... Regards Trevor "W...

Offline defrag with Eseutil.exe - Yes or No
I have an exchange 2003 server with 10% (6gb of 56) disk space left. This place has no quotas for mailboxes but I will put them in place. So far I have cleaned out an additional 6 gb of space. The exchange database files don't shrink I understand unless you offline defrag them but I don't think this is recommended unless absolutely needed. I see how the available space is reported in the event log which verifies the space I have cleaned out. I'm leaning on not doing an offline defrag as there is no other app running on this server. Do you think its safe to let this server ha...

Free\Busy Time Setting
Hi I have set up a number of Rooms as Resources. When we Schedule a meeting, although the Resource is set up to Auto-Reply, the Meeting is booked in the Resource Diary, but does not Show up in a subsequent Meeting Schedule, as busy, for 15 minutes. I have set up the Resources to update every minute, but this does not seem to help. How do I change the Refresh on the Exchange Server. If I book another Meeting with the Resource, it adds the last meeting into the View Group Schedule list, but of course the Meeting I have just created waits another 15 minutes. Thanks Allan ...

Spacing between lines manually adjusting them
How can I nudge, or move the lines of text closer together? I have tried the line spacing tool and changed the numbers, and all that seems to do is cut off part of the top of the text. I want some of the lines close together and some farther. Does Publisher 2007 have that ability. Thanks for any help. Didja try Format | Paragraph | Line Spacing ? also right click in the text box and there;s more adjustments in Format TExt Box "ccardiff" <ccardiff@discussions.microsoft.com> wrote in message news:813FF563-8CEE-4EBF-A7FC-4A7D10E0C9BD@microsoft.com... | How can I nudg...

Forcing Free/Busy to Free
This might sound like a silly question, but in Exch. 2003 is there a way to force a user's calendar as ALWAYS showing "Free" even when there is an event scheduled on that calendar? Essentially, what I am trying to do is to allow a resource calendar managed by AutoAccept to be able to double-book. Apparantly, the only way to do this is when AutoAccept sees the resource as being "Free", so that it will not automatically decline the meeting request. Thanks!! -sul@fiu.edu ...

Insert for free your website
Insert for free your website in the new search engine Free Pass http://www.free-pass.org ...

Published Free & Busy Information inconsistent
Envirnonmet=Windows 2000 SP4, E2K SP3+Sept 2003 rolled up hotfix, Outlook 2000 SR1 clients. I have recently deployed a group policy to force OL clients to publish 12 months of Free&Busy data to our organization, all this seems fine and all 4000 clients have the setting set to 12 months and update every 15 minutes, we have 7 servers and replicate the public folder to all servers. The data that is visible in the client meeting request window pane is very inconsistent with very few actuall displaying the 12 mths, some have upto 6 mths and some only 2, i have observed that this varies at times...

Free Signup-Free Marketing Tools-Enormous Paychecks
We got 'em! Powerful secrets, tips, tools, and techniques for turning small businesses into BIG paychecks Listen to this audio: http://www.byoaudio.com/play/WpmTgFBk Go to my website and lock your position in now! http://www.watchthemoneyonline.com ...

The line spacing of the last line in a paragraph is bigger?
How do I make all the line spacing in a paragraph equal? The last line in my print preview and printed documents always has a larger space above it than those within the paragraph. This just came up earlier this week. I'd check to make sure everything - including the carriage return at the end of the sentence - is the same size font. (I am one of those people who like to see the hidden characters.) -- JoAnn Paules MVP Microsoft [Publisher] "Merx27" <Merx27@discussions.microsoft.com> wrote in message news:9F721372-9275-476A-9026-B80942D89A87@microsoft.com... >...

spacing between words NOT between lines or paragraphs
I'm using Word 2007 on Window's Vista. How can I change the space between words? I am working on a template that will use various styles for difference sections. Is there a way to add some extra space between words when using the "Eurostile" font, size 14, condensed by 0.4. To my old eyes, the words are a little close together - any way to modify it? KJ You can type more than one space between words. On Feb 9, 3:16=A0pm, kj <k...@discussions.microsoft.com> wrote: > I'm using Word 2007 on Window's Vista. =A0How can I change the space betw=...