x

available space in table?

Can I get the available space in a perticular table? If Yes, How?
more ▼

asked Apr 08 '12 at 03:07 PM in Default

mituljain9 gravatar image

mituljain9
30 4 4 6

Thanks John, Thanks Thomas, Thanks Jonathan
Apr 08 '12 at 04:07 PM mituljain9
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

sp_spaceused is what you need to use

USE [AdventureWorks]
GO

[sys].[sp_spaceused] @objname = N'humanresources.shift'
go
INSERT [HumanResources].[Shift]
       ( [ShiftID] ,
         [Name] ,
         [StartTime] ,
         [EndTime] ,
         [ModifiedDate]
       )
VALUES  ( 0 , -- ShiftID - tinyint
         N'' , -- Name - nvarchar(50)
         '2012-04-08 16:57:41' , -- StartTime - datetime
         '2012-04-08 16:57:41' , -- EndTime - datetime
         '2012-04-08 16:57:41'  -- ModifiedDate - datetime
       )
GO 2000


[sys].[sp_spaceused] @objname = N'humanresources.shift', @updateusage='true'

will show how the space used by a table changes when rows are inserted

before:

name    rows   reserved    data  index_size   unused
Shift   203     72 KB     8 KB    8 KB       56 KB

after:

name    rows   reserved    data  index_size   unused
Shift 2203 136 KB 80 KB 8 KB 48 KB
more ▼

answered Apr 08 '12 at 05:04 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

+1. I'm curious to know if that is what the OP was referring to. Thanks!
Apr 08 '12 at 05:43 PM JohnM
looks like it was ;)
Apr 08 '12 at 06:27 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Not sure that I quite understand - tables are not limited in any particular way, beyond those limits of the database storage engine itself. If you're interested in the limits that do affect SQL Server, I would suggest you start here: http://msdn.microsoft.com/en-us/library/ms143432.aspx

If you want to know how much space a table is using, I suggest having a look at how to run sp_spaceused - http://msdn.microsoft.com/en-us/library/ms188776.aspx
more ▼

answered Apr 08 '12 at 03:26 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

(comments are locked)
10|1200 characters needed characters left

No, I don't believe so.

The amount of available space for an individual table would be the available space that is in the database, furthermore, if the database is set to unrestricted growth, then the table would be free to grow until the database consumed all available disk on the hard drive.

Hope this helps!
more ▼

answered Apr 08 '12 at 03:28 PM

JohnM gravatar image

JohnM
6k 1 3 7

(comments are locked)
10|1200 characters needed characters left

is possible to free the space? was made a shrink on the files and release only 2GB what else can we do?

Space Reserved : 46.49GB Space Used : 20.26GB

Total Sapace Usage: 48739.13MB Data files Space usage: 47602.00MB Transaction Log Space usage: 11911.13MB

alt text

dbprod.png (101.6 kB)
more ▼

answered Aug 02 '12 at 04:28 PM

snevarez4 gravatar image

snevarez4
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x82
x21

asked: Apr 08 '12 at 03:07 PM

Seen: 756 times

Last Updated: Aug 02 '12 at 04:28 PM