sp_spaceused

Question

sp_spaceused

Bhavin Patel-257492

SSC Eights!

Points: 917
More actions
July 1, 2007 at 9:03 pm

#119666

Here's the result of sp_spacesused.
Is it good practise to delete the unallocated space? How to prevent this from happening, so that there are not empty space?
database_size      unallocated space
-------------------------------------
6320.25 MB         944.38 MB
reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
5503864 KB         5386776 KB         104224 KB          12864 KB

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Do you have a maintenance plan to backup this database if so there is a option to remove unused space in the database in that plan. Else you should script a dbcc shrinkdatabase ot dbcc shrinkfile and schedule it to run every night or weekly.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 2

Do not shrink databases. Period. Fragmentation both at the database level and the file level is the result. There are execeptions, of course, but in general, do NOT shrink databases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

As what Jeff says you can shrink the data file only if the data file has more than 25% of space free. But there is no problem in shrinking the log file of the database but remeber to keep a minimum space in the file for transactional operations.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Bhavin Patel-257492 SSC Eights! Points: 917 More actions · Answer 4

My DBSize is around 7GB and unused space is around 1gb in this case should I shrink it?

sugesh,

"but remeber to keep a minimum space in the file for transactional operations." what file?

my maintenance plan is not working and from what I read it wont work right until I apply SP2, is it right?

This is what I have in my plan

Monthly

re-buildindex

update index

update statistic

check database integrity

logbackup

database backup

Daily at night: logbackup and database backup

Any suggestion please?

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 5

Hi Patel,

Wat Sugesh is trying to tell is that u better ensure to hav @least some space in tran log,coz in case u perform sum bulk insert operations,the tran log will grow drastically.....inorder to avoid it u can hav sumthing around 1gb of log space.....if u hav very less log space and perform bulk operations log file will try to grow as specified......and mite cause performance probs

Further its not that maintenance plan will not work with SP1,it will work...but there r few bugs which r still in SP1 so its advisable to go 2 SP2

[font="Verdana"]- Deepak[/font]

Bhavin Patel-257492 SSC Eights! Points: 917 More actions · Answer 6

Hi deepak,

My data size is around 7GB and unused space is around 1gb in this case should I shrink it? This is not the size of the logs, this is database itself

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 7

then need not shrink it.......if the unused or free space in log or data file is more than 25% u can goahead with shrinking else not required.......

[font="Verdana"]- Deepak[/font]

Bhavin Patel-257492 SSC Eights! Points: 917 More actions · Answer 8

Thanks everyone for your help. It was quick and very helpful.

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 9

You ALWAYS want free space in your data files. This is so as you add data, it has a place to go. The only time you'd want 0% free space is when you have a READ ONLY database that will not change.

Bhavin Patel-257492 SSC Eights! Points: 917 More actions · Answer 10

This is my maintenance plann, do I need to change any thing to make it better

This is order in which I ran it Monthly