Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.
yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
David Gugick
Quest Software
www.quest.com
|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want to
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fit
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can find
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and after
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
[vbcol=seagreen]
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally, a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the field.
> So, your example is not actually accurate in how big a row of the index (and
> it is further dependent on whether the index is clustered or non-clustered)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> will also show you if there is fragmentation in those pages and extents. In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can find
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.co...umented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
[vbcol=seagreen]
> I totally understand and am aware of the points mentioned. The main reason
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and after
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some where,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:

Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+
4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
David Gugick
Quest Software
www.quest.com|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want t
o
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fi
t
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field
.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can fin
d
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and afte
r
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
[vbcol=seagreen]
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will
fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally,
a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the fie
ld.
> So, your example is not actually accurate in how big a row of the index (
and
> it is further dependent on whether the index is clustered or non-clustered
)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up.
It
> will also show you if there is fragmentation in those pages and extents.
In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can f
ind
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
>|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.c...cumented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
[vbcol=seagreen]
> I totally understand and am aware of the points mentioned. The main reaso
n
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to
do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and af
ter
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some wher
e,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:
>

Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
--
David Gugick
Quest Software
www.quest.com|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want to
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:
> yodarules wrote:
> > Hey Guys,
> >
> > I would like to enumerate all the indexes in a table and find the
> > size of the index, when I mean the size not the total space occupied
> > but the size of each row of the index. Eg: Table EMP has id bigint,
> > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > would be used in later calculating the required size based on number
> > of records to be inserted.
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fit
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can find
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:
> yodarules wrote:
> > Hey Guys,
> >
> > I would like to enumerate all the indexes in a table and find the
> > size of the index, when I mean the size not the total space occupied
> > but the size of each row of the index. Eg: Table EMP has id bigint,
> > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > would be used in later calculating the required size based on number
> > of records to be inserted.
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and after
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally, a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the field.
> So, your example is not actually accurate in how big a row of the index (and
> it is further dependent on whether the index is clustered or non-clustered)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> will also show you if there is fragmentation in those pages and extents. In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can find
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
> > yodarules wrote:
> > > Hey Guys,
> > >
> > > I would like to enumerate all the indexes in a table and find the
> > > size of the index, when I mean the size not the total space occupied
> > > but the size of each row of the index. Eg: Table EMP has id bigint,
> > > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > > would be used in later calculating the required size based on number
> > > of records to be inserted.
> >
> > See "Estimating the Size of a Table with a Clustered Index" in BOL.
> >
> > --
> > David Gugick
> > Quest Software
> > www.quest.com
> >|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
> I totally understand and am aware of the points mentioned. The main reason
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and after
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some where,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:
> > A couple of thoughts on your question.
> >
> > 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> > extents. Based on the fill factor and size of your index SQL Server will fit
> > as many rows from your index within the page as it can.
> > 2) A non-clustered index always contains a reference to the key of the
> > clustered index or a pointer to a place in the heap if no clustered index
> > exists, in addition to the data in the non-clustered index. Additionally, a
> > varchar field does not always take up all 8 bytes. Varchars are variable
> > length and only stored with the bytes taken up by the value within the field.
> > So, your example is not actually accurate in how big a row of the index (and
> > it is further dependent on whether the index is clustered or non-clustered)
> > would be.
> >
> > I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> > use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> > will also show you if there is fragmentation in those pages and extents. In
> > SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> > dm_db_index_physical_stats also provides values for min,avg and max record
> > size. Which might be what you are looking for. I don't know if you can find
> > that data for SQL 2000.
> >
> > The article in BOL that David pointed you to is a good illustration of how
> > that calculation would be done manually for a particular table and its
> > indexes.
> >
> > HTH
> >
> > --
> > Ryan Powers
> > Clarity Consulting
> > http://www.claritycon.com
> >
> >
> > "David Gugick" wrote:
> >
> > > yodarules wrote:
> > > > Hey Guys,
> > > >
> > > > I would like to enumerate all the indexes in a table and find the
> > > > size of the index, when I mean the size not the total space occupied
> > > > but the size of each row of the index. Eg: Table EMP has id bigint,
> > > > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > > > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > > > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > > > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > > > would be used in later calculating the required size based on number
> > > > of records to be inserted.
> > >
> > > See "Estimating the Size of a Table with a Clustered Index" in BOL.
> > >
> > > --
> > > David Gugick
> > > Quest Software
> > > www.quest.com
> > >

Find table/indexes size

Hi, guys!
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:
> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>|||Thank you all! Just what I needed!
FireStarter

Find table/indexes size

Hi, guys!
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.
look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.
|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:

> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>
|||Thank you all! Just what I needed!
FireStarter

Find table/indexes size

Hi, guys!
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:

> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>|||Thank you all! Just what I needed!
FireStarter

find space (MS SQL 2005)

how can I find a space into a string ? (for MS SQL 2005)
SELECT name FROM tbNames WHERE name LIKE '% % '
is not working
thank youwhat do you mean it's not working? it should

can you give an example of a name with a space in it where it doesn't work?|||I dont understand ... now it works

thanks r937|||sounds like a heisenbug|||Usually when that happens to me it is caused by an id-10-t error.

-PatP|||... heisenbugthank you so much, what a gorgeous word, that's brilliant

:)

pat, see A Truly ID-iotic Design (http://worsethanfailure.com/Articles/A_Truly_ID-iotic_Design.aspx)

Edit: oh hai, it r down atm, plz try again later, kthxbye|||do you mean it can happen time to time with no reason ?|||it's not my creation. there are several related ones:

http://en.wikipedia.org/wiki/Heisenbug

find backup directory

Hello!
MSQL2000:
I want to make db-backup (maintenance) but haven't enough space on the
discs that are physically in the machine. I have enough on the network, but
i can't choose an network-drive, when
i want to specify the backup directory.

What to do?
/Bjrn"bdjensen" <b.d.jensenMAPS_ON@.gmx.net> wrote in message news:<3fb22d61$0$95067$edfadb0f@.dread11.news.tele.dk>...
> Hello!
> MSQL2000:
> I want to make db-backup (maintenance) but haven't enough space on the
> discs that are physically in the machine. I have enough on the network, but
> i can't choose an network-drive, when
> i want to specify the backup directory.
> What to do?
> /Bjrn

Enterprise Manager only displays the local drives, but you can just
type in the UNC path: \\Server\Share\Folder.

Simon|||Create a backup device in enterprise manager. You can backup to network
locations this way.|||"bdjensen" <b.d.jensenMAPS_ON@.gmx.net> wrote in message
news:3fb22d61$0$95067$edfadb0f@.dread11.news.tele.d k...
> Hello!
> MSQL2000:
> I want to make db-backup (maintenance) but haven't enough space on the
> discs that are physically in the machine. I have enough on the network,
but
> i can't choose an network-drive, when
> i want to specify the backup directory.

Note, as others have said, you can use UNC paths. However, SQL Server Agent
must be running in a user context that can map those drives w/o human
intervention.

> What to do?
> /Bjrn