How to determine the free space in a datafile

Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||for data files look at
dbcc showfilestats
This is undocumented dbcc command.
It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.
For log files
look at
dbcc sqlperf(logspace)
Regards
Amish shah
http://shahamishm.tripod.com
*** Sent via Developersdex http://www.codecomments.com ***|||Uri Dimant schrieb:

> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you[vbcol=seagreen]
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...|||Amish Shah schrieb:

> for data files look at
> dbcc showfilestats
> This is undocumented dbcc command.
> It shows total size and used size in extents.
> If you multiply it by 64 you will get size in KB.
> For log files
> look at
> dbcc sqlperf(logspace)
>
> Regards
> Amish shah
> http://shahamishm.tripod.com
> *** Sent via Developersdex http://www.codecomments.com ***
Thank you Amish,
that really does the job but how do i get this information into a
table?
like:
select into table ( dbcc showfilestat )
thats what i would like for capacity planning and charting.
Thank you very much, Ulli|||Hi Ulli
You have to create your own table. By looking at the output of dbcc
showfilestats you can determine the number of columns and an appropriate
datatype.
Once you have the table created, you can do the following:
INSERT INT MyFileStatsTable
EXEC ('DBCC showfilestats')
HTH
Kalen Delaney, SQL Server MVP
<ulli77@.web.de> wrote in message
news:1154272978.472934.27330@.b28g2000cwb.googlegroups.com...
> Amish Shah schrieb:
>
>
> Thank you Amish,
> that really does the job but how do i get this information into a
> table?
> like:
> select into table ( dbcc showfilestat )
> thats what i would like for capacity planning and charting.
> Thank you very much, Ulli
>

How to determine the free space in a datafile

Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||Uri Dimant schrieb:
> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I'm trying to figure out the amount of free space in the datafiles but
> > since sys.dm_db_file_space_usage works only for temp, I dont know which
> > table to join on sys.database_files or sys.master_files.
> >
> > Can anyone help me please?
> >sql

How to determine compiled proc size?

How do I determine the amount of server memory a stored procedure (or any
other object) takes up?http://www.sql-server-performance.com/rd_data_cache.asp gives some
information regarding querying the syscacheobjects system table and what
each row in the table means. You should be able to use that to derive
the number of pages of memory used with many kinds of server objects.
Good luck,
Tony Sebion
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:

> How do I determine the amount of server memory a stored procedure (or any
> other object) takes up?|||There's also an undocumented DBCC command: DBCC MEMOBJLIST but it requires
starting the service with the T3654 flag and it doesn't show what's in AWE
memory.
"Tony Sebion" wrote:

> http://www.sql-server-performance.com/rd_data_cache.asp gives some
> information regarding querying the syscacheobjects system table and what
> each row in the table means. You should be able to use that to derive
> the number of pages of memory used with many kinds of server objects.
> Good luck,
> Tony Sebion
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:
>
>

How to delete a big amount of data

Hi, suppose i have 3 tables

main table

CREATE TABLE table1 (
[t1ID] [int] primary key ,
[t1Name] nvatchar(50) NOT NULL,
.....................

)

and connections tables (t1ID is a foreign key in all connection tables)

CREATE TABLE tbl2 (
[t1ID] [int] not null,
[gID] [int] not null
PRIMARY KEY (t1ID, gID)
)

CREATE TABLE tbl3 (
[t1ID] [int] not null,
[pID] [int] not null
PRIMARY KEY (t1ID, pID)
)

************************************************** *****

t1ID CLISTERED INDEX
in othre tables each primary key is clustered.

gID and pID are keys from other table: gtable, ptable

Now in case one row was deletes from table1 i need to delete all t1ID's
from the connection tables (each of connection may have e.g. 200000 rows).
If u use delete on cascade it's take a lot of time, i want to delete let's
say 1000 rows till all irrelevant rows will be deleted.

How To accomplish this??
Thanks.

--
Message posted via http://www.sqlmonster.comakej via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> Now in case one row was deletes from table1 i need to delete all t1ID's
> from the connection tables (each of connection may have e.g. 200000 rows).
> If u use delete on cascade it's take a lot of time, i want to delete let's
> say 1000 rows till all irrelevant rows will be deleted.

Well, if you have FK constraints from the connections tables to the
main table, you cannot delete the connection from that table until all
references from the connection tables are gone. So while you could batch
things like:

SET ROWCOUNT 10000
WHILE EXISTS (SELECT * FROM tbl2 WHERE tl1D = @.id_to_delete)
DELETE tbl2 WHERE tlID = @.id_to_delete
SET ROWCOUNT 0

I can't really see that it will help you.

You could of course drop the foreign-key constraint, and the start some
background process that deletes the rows in the other table, but that's
a pretty wild thing to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, your suggestion is helpful however how can implement it,
i guess by job that must define, so when this job will be start and who
will start it??

Also i can remove all these relationship, only the ID's will be stay, now
each time when t1ID will be deleted i will put this t1ID in some pemanent
table suppose [toDelete] and from now i need to start job or jobs that
let's say every 10 minut will remove 1000 rows from each connection table
or from first connection table after this from second and so on, in the end
i need to remove this t1ID. However i'm new in sql and i don't really know
how to implement it. Maybe u reject this and have more efficient suggestion.

Thanks

--
Message posted via http://www.sqlmonster.com|||Any ideas ???

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> Thanks Erland, your suggestion is helpful however how can implement it,
> i guess by job that must define, so when this job will be start and who
> will start it??
> Also i can remove all these relationship, only the ID's will be stay,
> now each time when t1ID will be deleted i will put this t1ID in some
> pemanent table suppose [toDelete] and from now i need to start job or
> jobs that let's say every 10 minut will remove 1000 rows from each
> connection table or from first connection table after this from second
> and so on, in the end i need to remove this t1ID. However i'm new in sql
> and i don't really know how to implement it. Maybe u reject this and
> have more efficient suggestion.

Well, since I don't like dropping foreign-key constraints, I would first
look into the situation a little closer. It seems that you have a
"connection" and you have 200000 rows added for that connection that
you want to drop. Maybe there is reason to consider why all those rows
were added in the first place?

This may be a stupid question, but pleaes keep in mind that since I know
nothing about your business, I have to try some stabs in the dark.

But if you truly want an asynchronous delete, I would set up a in SQL
Agent that runs with some frequency and which deletes orphaned rows.
There could be a trigger on the table, so that when a row is deleted,
the job is started, but frankly, I would actually skip that step. This
job could be devised so that it deletes all orphans it can find, possibly
batched with SET ROWCOUNT. Or it could be written so that it stops after
some time, and take remaining oprhans on the next time, depending on
how you want to spread the load.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp