Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


spaceused


spaceused

Author
Message
Pedro Reis-267786
Pedro Reis-267786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 1

Hi guys!

My database is getting kind of big, so I have run sp_spaceused in my database to check the space allocated…

The results are as follows:

Database name database size Unallocated space

inquierosb 467.44 MB -136.90 MB

reserved data index size unused

617816 KB 326520 KB 49616 KB 241680 KB

How can i free up the 241680kb unused space?

What is the meaning of the unallocated space?

Is there a way to view which table are allocating more space in my database ?


MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
firstly it might be wise to run the following

use mydatabase
go
dbcc updateusage
go

that should make sure you get accurate sizing information
then run sp_spaceused again

to view which tables are using the most space you can do a few things

sp_spaceused 'tablename' should sho the individual table size.
there is also an undocumented command (i think it's called) sp_msforeachtable which can run the sp_spaceused command against each table

based on your questions i'd also say can you post the results of the command DBCC SQLPERF(LOGSPACE)

just in case...

MVDBA
SQLBill
SQLBill
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3453 Visits: 1017
You can shrink the database file size by using DBCC SHRINKFILE command. Check the BOL for the syntax. You have to use the actual file name. To check what it is, use Enterprise Manager, right click on the database. Select Properties. Go to the Data tab and you'll find the file name.

-SQLBill



Pedro Reis-267786
Pedro Reis-267786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 1

Thanks for your replies.

Bill,

Should i use DBCC SHRINKFILE with the TRUNCATEONLY argument to release the unused space ?

Mike,

About that interesting command (sp_MSforeachtable), it's not working, am i missing something ? :

Server: Msg 201, Level 16, State 4, Procedure sp_MSforeachtable, Line 0

Procedure 'sp_MSforeachtable' expects parameter '@command1', which was not supplied.


Mark Yelton
Mark Yelton
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 433

Pedro,

For the sp_MSforeachtable suggestion, use this:

exec sp_MSforeachtable 'exec sp_spaceused ''?'''

For the DBCC SHRINKFILE suggestion, it would be a good idea to do a ReIndex or IndexDefrag of your tables after your done. Some of your tables will get fragmented after running the SHRINKFILE.

HTH,

Mark


SQLBill
SQLBill
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3453 Visits: 1017
To use Truncate or not........

If you use truncate (with DBCC SHRINKFILE or with BACKUP LOG dbname WITH TRUNCATE_ONLY), you 'break' the backup chain and must start a new chain. That means that you must do a Full backup after you issue the truncate command. Truncate means that log and any after it cannot be applied to the FULL backup done before the truncate.

I have used truncate at times.....but I always do a full backup right after it.

-SQLBill



MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
DBCC SHRINKFILE WITH TRUNCATE_ONLY doesn't break the LSN (log sequence number) and cause you to lose your backup chain - all it does is remove the empty space from the file. If your tables have allocated space near the end of the file then you may not be able to release the space as you can only release file space down to the last active page in the file - but there may be free space in the file before this


BACKUP LOG WITH TRUNCATE_ONLY does break you LSN though, however this performs something completely different and won't claim space back - it will just flush the inactive portion of the log.

BACKUP LOG WITH TRUNCATE_ONLY has been deprecated and shouldn't be used - if your log files are growing set up a t-log backup job or set your databases to simple mode




a shrinkfile will not

MVDBA
Pedro Reis-267786
Pedro Reis-267786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 1

Thanks for you replies.

I have ran the update usage command and now the values are as folows:

Databasename database size unallocated space

InquieroSB 467.44 MB 43.65 MB

Reserved data index_size unused

432936 KB 365776 KB 53896 KB 13264 KB

I have some questions, can you can help me understand this:

- What is the diference between unallocated space and unused space

- In the event of running DBCC SHRINKFILE TRUNCATEONLY commend what is the space recovered (unallocated space or unused space ?)

- can I ran DBCC SHRINKFILE TRUNCATEONLY in multi-user mode , I mean can I ran it if there is someone using the database ?

- Can i recover some space by simple doing a reindex? What is the command that I can use - DBCC DBREINDEX or DBCC INDEXDEFRAG ?

Thanks.


MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
What is the diference between unallocated space and unused space

not sure if this is entirely accurate but here goes - unused is allocated space that has not been used by the objects yet - this can't be shrunk using a shrinkfile.


- In the event of running DBCC SHRINKFILE TRUNCATEONLY commend what is the space recovered (unallocated space or unused space ?)

unallocated - but not necessarilly all of it (in some cases none) - if you have allocated space (might be unused) at then end of the file then you won't reclaim any space back

the most you can reclaim from your database is 43Mb - but the next time you rdatabase grows due to autogrow 10% (if thats what you have it at) then it wuill grow by 46Mb - so don't run the shrink!!!

- can I ran DBCC SHRINKFILE TRUNCATEONLY in multi-user mode , I mean can I ran it if there is someone using the database ?

you can run it if other users are operating.

- Can i recover some space by simple doing a reindex? What is the command that I can use - DBCC DBREINDEX or DBCC INDEXDEFRAG ?

the re-index or defrag operations will consume more space during the excercise and then de-allocate the space (but your file may have grown during the operation)

INDEXDEFRAG can be used while users are on the system (sort of) and won't really claim you any space back - it'll just make the indexes run better - t-log useage is pretty small as it's many operations not one big drop and rebuild

REINDEX will create new indexes and drop the old ones - (beware you need space to hold 2 copies of the index + working space + space in your t-log file to do this) but you should reduce the index size (depending on the fill factor you specify)

MVDBA
MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
if i could suggest something-

can you run the command DBCC SQLPERF(LOGSPACE) and paste the results back

if you need to claim space back it might be wise to claim it back from the log and not from the data file which looks correctly set up.

MVDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search