question

mituljain9 avatar image
mituljain9 asked

available space in table?

Can I get the available space in a perticular table? If Yes, How?
tablespace
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks John, Thanks Thomas, Thanks Jonathan
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. I'm curious to know if that is what the OP was referring to. Thanks!
1 Like 1 ·
looks like it was ;)
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

snevarez4 avatar image
snevarez4 answered
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][1] [1]: /storage/temp/332-dbprod.png

dbprod.png (99.2 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.