spaceused

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » spaceused

13 posts, Page 1 of 2

1 2 »»

spaceused

Rate Topic

Display Mode

Topic Options

Author

Message

Pedro Reis-267786

Posted Friday, March 17, 2006 9:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, November 03, 2006 5:20 AM
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 ?

Post #266619

MVDBA

Posted Friday, March 17, 2006 9:32 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

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

Post #266628

SQLBill

Posted Friday, March 17, 2006 10:50 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:48 PM
Points: 3,223, Visits: 414

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

Post #266666

Pedro Reis-267786

Posted Monday, March 20, 2006 9:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, November 03, 2006 5:20 AM
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.

Post #267000

Mark Yelton

Posted Monday, March 20, 2006 12:34 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:45 PM
Points: 209, Visits: 324

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

Post #267040

SQLBill

Posted Tuesday, March 21, 2006 7:15 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:48 PM
Points: 3,223, Visits: 414

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

Post #267257

MVDBA

Posted Tuesday, March 21, 2006 8:00 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

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

Post #267282

Pedro Reis-267786

Posted Wednesday, March 22, 2006 8:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, November 03, 2006 5:20 AM
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.

Post #267611

MVDBA

Posted Wednesday, March 22, 2006 8:58 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

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

Post #267621

MVDBA

Posted Wednesday, March 22, 2006 9:01 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

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

Post #267623

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions