How to determine % of free space

Ok I can't seem to find an answer to this question. I have a NDF file in a
second file group and want to know how much space it used or how much space
is free. Can I do this? If so how? I've just got to be missing it.
Thanks,
PaulWThis isn't documented, but easy to use:
DBCC SHOWFILESTATS
For 2005, there are also a bunch of dynamic management views, and I assume that one of them can give
you the answer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Widner" <paul_widner@.hotmail.com> wrote in message
news:uC3vnSltGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Ok I can't seem to find an answer to this question. I have a NDF file in a second file group and
> want to know how much space it used or how much space is free. Can I do this? If so how? I've
> just got to be missing it.
> Thanks,
> PaulW
>|||Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
extents, but not KB....
Paul Widner wrote:
> Ok I can't seem to find an answer to this question. I have a NDF file in a
> second file group and want to know how much space it used or how much space
> is free. Can I do this? If so how? I've just got to be missing it.
> Thanks,
> PaulW|||Sorry about that Tibor, I should have hit refresh! ;)
SGCSNA wrote:
> Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
> extents, but not KB....
>
> Paul Widner wrote:
> > Ok I can't seem to find an answer to this question. I have a NDF file in a
> > second file group and want to know how much space it used or how much space
> > is free. Can I do this? If so how? I've just got to be missing it.
> >
> > Thanks,
> > PaulW|||No problem. Happens to all of us, all of the time. Also, refresh might not help, as you might be
connected to a different news server than I am, and replication between the news server incurs a
delay. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SGCSNA" <sean.gorman@.computershare.com> wrote in message
news:1154540131.071946.269940@.m79g2000cwm.googlegroups.com...
> Sorry about that Tibor, I should have hit refresh! ;)
> SGCSNA wrote:
>> Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
>> extents, but not KB....
>>
>> Paul Widner wrote:
>> > Ok I can't seem to find an answer to this question. I have a NDF file in a
>> > second file group and want to know how much space it used or how much space
>> > is free. Can I do this? If so how? I've just got to be missing it.
>> >
>> > Thanks,
>> > PaulW
>|||Are those extents in 8k pages?
Paul
"SGCSNA" <sean.gorman@.computershare.com> wrote in message
news:1154540044.752066.187980@.b28g2000cwb.googlegroups.com...
> Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
> extents, but not KB....
>
> Paul Widner wrote:
>> Ok I can't seem to find an answer to this question. I have a NDF file in
>> a
>> second file group and want to know how much space it used or how much
>> space
>> is free. Can I do this? If so how? I've just got to be missing it.
>> Thanks,
>> PaulW
>|||An extent is 64K. A page is 8k or 8192 bytes. Pages go into extents.
Paul Widner wrote:
> Are those extents in 8k pages?
> Paul
> "SGCSNA" <sean.gorman@.computershare.com> wrote in message
> news:1154540044.752066.187980@.b28g2000cwb.googlegroups.com...
> > Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
> > extents, but not KB....
> >
> >
> > Paul Widner wrote:
> >> Ok I can't seem to find an answer to this question. I have a NDF file in
> >> a
> >> second file group and want to know how much space it used or how much
> >> space
> >> is free. Can I do this? If so how? I've just got to be missing it.
> >>
> >> Thanks,
> >> PaulW
> >

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
Jim
DELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:

> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim
|||That should be:

> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?
|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?
|||You told that the rows are about THE SAME, you didn′t mentioned something of
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?
|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
JimDELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:
> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim|||That should be:
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü�meyer" wrote:
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
> > There are two columns in the table1,
> >
> > ID|AccountName | ContactName |
> > 1 | ebay.com | Alex
> > 2 | ebay.com |
> >
> >
> > Look the second row, it is reduplicate info which is absolutely same with
> > the first row. So I'd like to delete the second row. How to use SQL to do
> > that?
> >
> > Cheers,
> > Jim|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||You told that the rows are about THE SAME, you didn´t mentioned something of
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
JimDELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:

> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim|||That should be:

> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches t
he
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
>|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||You told that the rows are about THE SAME, you didn′t mentioned something o
f
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?