Database files free space (SQL Server 2005)

Dear friends

I need to report the amount of free space in each datafile of a database.

I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.

Please help.

Regards

Parviz

Take a look at the sp_spaceused text. You should be able to derive from there.

exec sp_helptext 'sp_spaceused'|||

Thanks for the fast reply, but please note that sp_spaceused returns the free space for the whole database files including the LOG file. I need the info fo each file in a database seperately.

If you have a look at summary reports for a database (SQL 2005 SSMS),you would see a table and a pie chart showng the status for each file.

Thanks again.

Parviz

|||Yup. sp_spacedused can be used to get the data file usage. You can then use 'dbcc sqlperf(logspace)' to get the log usage.|||

Here's some code, cribbed from a sqldbatips report designed to mimic the taskpad in SSMS. (http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx). All I did was add some code to show the amount free. Seems to work well. Posting here because this was one of the top results in Google when I went looking for code myself.

create table #data(Fileid int NOT NULL,

[FileGroup] int NOT NULL,

TotalExtents int NOT NULL,

UsedExtents int NOT NULL,

[Name] sysname NOT NULL,

[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,

LogSize numeric(15,7) NOT NULL,

LogUsed numeric(9,5) NOT NULL,

Status int NOT NULL)

insert #data exec('DBCC showfilestats with no_infomsgs')

insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from

(

select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]

from #data

union all

select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()

--order by [Type],[Name]

)a

order by [Type],[Name]

drop table #data

drop table #log

|||

This will work for you:

-- Individual File Size query

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *

FROM sys.database_files;

Database Designing...

Friends,
Who is responsible for the Design of Database? System Analyst, DBA, Databse Designer, Project Leader? Coz I am working as a System Analyst, but now desgining the Databse for the ERP package which I feel is another man's work. Confussed. Plz help me.
AnilI'll wait for the follow-up poll: Who gets blamed for a poorly designed database?

A: DBA
B: DBA
C: DBA
D: All of the above.|||you're kidding, right?

you have four people, and one of them is a Database Designer, and you're asking whose job it is to design the database?

answer: not the DBA

the DBA's job is to create the physical database from the finished database design

answer: not the project leader

the project leader's job is to talk to management

answer: not the systems analyst

the system analyst's job is to figure out what the users really want, not what they're asking for

let's see, have i missed anybody?|||Yeah r937 you missed someone. You missed the poor suckers that have to perform all of those roles. I'm stuck in that position right now. I know the most about the business processes, db design, dba work, java coding, object modeling, reporting progress to management, and managing interaction with the end users. It sux but it acts as great leverage when negotiating salary.|||I don't see any reason or advantage to splitting the DBA and Database Designer roles. It sounds like a recipe for turf wars to me.

Anybody?|||Originally posted by blindman
I'll wait for the follow-up poll: Who gets blamed for a poorly designed database?

A: DBA
B: DBA
C: DBA
D: All of the above.

You took the words right out of my keyboard :)|||you can't see any advantage to splitting the DBA and Database Designer roles?

whoa, you must be trollin

where to start?

how about: if the Database Designer designs the database, sufficient time and effort will be devoted to the understanding and identification of candidate keys based on business logic, whereas if the DBA does it, you get surrogate autonumber primary keys on every table and $deity knows what other similar crap

how's that?

;)|||Do you mean to say a DBA has no knowledge of Database design ...

Buzz ... You are wrong ... what we are talking about is an Integration of DBA and DD roles ... and that would be absolutely great if the person posseses enough DD and DBA knowledge .|||Well, in large shops it doesn't seem to fly. I'D LOVE TO:

- do the analysis
- do the design
- and do what I do on a daily basis (DBA stuff)

Turf wars is what we have here.

But the sad part is, that after the design is handed over to me to implement (mind you I am not invited to any of the meatings held by the project development team), I find all kinds of design issues in about 70% of the projects. At that point it's too late to take it back to the designer, or rather to the systems analyst, because success is declared and propagated all the way to the top, and I still love my job ;) So what am I left with? MAKE IT WORK!!! Recently the "designers" started listening to me a little more (I AM SHOCKED!) and started implementing any data access through a stored procedure. So, having a break like this, I have a luxury to re-write the procedure if it needs to be, and even alter table structures and relations, because the data is not directly accessed. It's a workaround that I found, but it keeps me occupied (not right now, it's boring here, everything works...)|||no, i do not mean to say the DBA has no knowledge of database design

that'd be silly, and i wouldn't say that

and who said we were talking about an integration of roles?

i thought we were talking about a0060162742357's situation where there is already two people, one of them a database designer and the other a DBA, and the question is, whose job is it to design the database?

duh...|||Database Designer
DBA
Project Leader
Programmer

hmmm .. these guys are there and still the system analyst is designing the db ... wierd|||Unfortunately it really doesnt matter who does it because the pooch usually gets screwed right from the beginning and then you have to spend the rest of your days working around a flawed design.

those magnificent bastards|||Originally posted by Ruprect
those magnificent bastards Do they come any other way ?!?!

-PatP|||How about all the "little" projects where the project manager says something like: "We only have two tables to hold our data, why don't we just dump them in the products database?"

Hmm. Would the fact that it is not product data have anything to do with that decision (or lack thereof)?

Sadly, we have no database design group here. All the developers are free to come up with their own designs, which I find out about later. And let me tell you, we have a creative bunch around here...

How are all of your shops made up? We only have project managers, programmers, and DBA.|||All very interesting comments.

So, in a split DBA/Designer environment, should the DBA be responsble only for pure "admin" functions, such as backups, index optimization, replication, etc?

I think it is absolutely essential that a database designer have good knowledge of database administration, though I don't think the reverse is necessarilly required.

I've only worked in small-to-midsize shops where I was the one-man show, so I'm curious about how duties are split in larger environments and how people keep from stepping on eachother's toes.|||The word is "micromanagement"...Or is it 2 words?|||or the client who says i want to bring these 900 various access 97 2000 and excel files into a sql server database

bottom line the only one who cares that the model is done right, is you.

it's like paul newman said
"only cream and bastards rise"|||Originally posted by blindman
All very interesting comments.

So, in a split DBA/Designer environment, should the DBA be responsble only for pure "admin" functions, such as backups, index optimization, replication, etc?

I think it is absolutely essential that a database designer have good knowledge of database administration, though I don't think the reverse is necessarilly required.

I've only worked in small-to-midsize shops where I was the one-man show, so I'm curious about how duties are split in larger environments and how people keep from stepping on eachother's toes.

I kind of disagree about the database administration piece. It would be impossible for me to be a good DBA if I didn't know about database design. I can only do so much with backups, recoveries, replication, etc.

How would I optimize the indexes and do performance tuning well though if I didn't understand database design. Half of performance tuning is knowing how proper design works and seeing where you can improve performance by applying indexes properly in the design, restructuring entities to be more efficient, and identifying weak code through analysis of the underlying structure.|||I don't disagree with you. I just feel that tweaking indexes falls within the realm of the DBA (though a designer has to plan for indexes as well). Indexes can be changed without modifying the structure or functionality of the schema, so I don't think of them as being the exclusive realm of the designer.|||Friends,
Thanx for ur suggestions n explanations. So Can I conclude that:

A system analyst : feeds the DD with user reqs and other high level flow diagrams.

A DD : models and designs the DB

A DBA (with the help of DD) : creates the Physical Database and Admins it, gives it PL

A PL : uses the info from SA and DBA+DD to develope the System(software)

A programmer : An OX

Am I Right?

Regards,

Anil,
the inexperienced|||This is a typical distribution of tasks, but I would argue that it has several flaws.

I think the Database Designer should create the physical database, though in a development environment. When it is done (or during development) the DBA should review it, and the DBA should be responsible for rolling it out to the production environment.

Also, the Database Designer should be involved with the application design from the start, including requirements gathering and functionality. I think a lot of problems occur when a system analyst and other parties gather requirements and design interfaces and then just hand the package off the the designer with the instructions to "build this". An allegory would be many of Frank Lloyd Wright's architectural designs, which may have been innovative and attractive, but were structurally unsound. The Database Designer should be a critical part of the development team.|||any competent database designer who finds himself or herself not included in the overall systems design and user requirements analysis will not stay in that job for long

Database Design Issue

Hello Friends,
I have to develop a database for website where Advertisements are displayed, Now i have to maintain number of clicks on an Advertisement on hourly basis so that report can be generated. That is number of click on Advertisement 789 between 10 AM and 11 AM.
What i am trying to do now is that i have a seperate tabel for clicks on Ad. where i store Ad_ID and nuber of clicks, and time duration.
Now my challenge is that is i store like that than for every ad there will be 24 rows per day. There can be as many as 1000 ads on that site so there will be 24*1000 rows added per day on that table alone.
Is there a better way of designing it. Because If the database becomes bulky it will take lots of time while retrieving the data.

thanks in advance
saddysanYou might consider putting this table in it's own database.
After each hour the rows for that row will be static so you could have an active table and an archive table. During slow times on the server copy the expired hours to the archive table to keep the active table small. This will give less likelyhood of corruption too.

You could make single rows with 24 fields for the hours but that wouldn't give much benefit and would cause more difficult coding.

Instead of updating the row you could insert an entry in a table then create the aggregate overnight.
This should cause less contention over locks as an update is relatively slow compared to insert.

Your current design will give around 8 million very small static rows per year which isn't too bad.
Just make sure that you create aggregate tables for any queries and don't try to query the active table.|||Do you really want data for each click? Think about what the final goal is. Maybe you can collect / evaluate the data as it comes in and store it in a summary row. Less data, more information--maybe.

--jfp|||Dumping data into a database is not nearly as recource-comsuming as reading data out from the database so you can easily fill it with plenty of rows without meeting any performance-problems. However, when you need your reporst on this you might want to look into some datawarehousing-techniques (i.e. look up OLAP in BOL). And I would also suggest that you only store Ad_ID and the date/time (GETDATE()) it was hit in the database, if you do that then you have all you need. Then you can aggregate the data into day-by-day and hour-by-hour reports say on a weekly or a monthly basis...

Database design

Hai friends,
I am asked to develop a project for a steel company for its warehouse processing. So I would like to know how to design a database for the warehouse keeping. Plz help me out to solve this problem.I would like to know how many fields are to be there in the different database and how to get the requirements that are required for warehousing.

Thanks in advance
raghulIt is practicaly impossible to suggest you anything without accessing the actual situation. how do you expect us to guide you without having any idea what is going on at your side.