x

available space in table?

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

more ▼

asked Apr 08, 2012 at 03:07 PM in Default

avatar image

mituljain9
30 4 4 7

Thanks John, Thanks Thomas, Thanks Jonathan

Apr 08, 2012 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, 2012 at 05:04 PM

avatar image

Fatherjack ♦♦
43.7k 79 97 117

+1. I'm curious to know if that is what the OP was referring to. Thanks!

Apr 08, 2012 at 05:43 PM JohnM

looks like it was ;)

Apr 08, 2012 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, 2012 at 03:26 PM

avatar image

ThomasRushton ♦♦
39.7k 20 47 52

(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, 2012 at 03:28 PM

avatar image

JohnM
12.3k 3 7 14

(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, 2012 at 04:28 PM

avatar 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.

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:

x109
x25

asked: Apr 08, 2012 at 03:07 PM

Seen: 1106 times

Last Updated: Aug 02, 2012 at 04:28 PM

Copyright 2016 Redgate Software. Privacy Policy