SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SP_Spaceused


SP_Spaceused

Author
Message
Jpotucek
Jpotucek
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4268 Visits: 1680
I have a question about how to interpret the results from sp_spaceused

I have a database (SQL Server 2008 R2) that is about to be moved from Development into Production and when I run sp-spaceused it appears to me that there is quite a bit (over 16gb)

database_name database_size unallocated space
MYDatabase 19705.00 MB 16326.98 MB

reserved data index_size unused
1819672 KB 883976 KB 924144 KB 11552 KB

My thought was to backup the database and then shrink it.

Any thoughts or suggestions?



Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61787 Visits: 17954
Why do you think you need to shrink it? Wouldn't you think that production will get a lot bigger than dev anyway?

http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jpotucek
Jpotucek
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4268 Visits: 1680
I do not anticiapate that the Database will grow alot from this point. They spent quite a bit of time loading Data (with the Vendors help) while in Development and went through 2 separate Front-end upgrades to fix some issues they were having in the process. Their original estimate on what the Production size would be was no more than 8-10 gb. I was just looking to get things cleaned up (space-wise) before moving it to production and I was looking for the most prudent way to go about doing that.



Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6474 Visits: 1439
I'm nott a big fan of shrinking a database, if you have enough space on production, maybe worth it to keep it as it is.




My blog

Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6474 Visits: 1439
To answer your question, you can find information about sp_spaceused here. When I'm checking disk usage by a database I usually use the disk usage standard report on SQL Server as it also shows the auto growth events.




My blog

psingla
psingla
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 1249
As there is lot of unallocated space then once you can shrink the file..
if it grows again then don't shrink file again.


Note:As specified by some geeks ,it is not advisable to shrink files.

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61787 Visits: 17954
Maybe another approach would be to create a new database and then script all the objects and transfer in the data? For the small amount of data it shouldn't take very long at all.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jpotucek
Jpotucek
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4268 Visits: 1680
Thank you all for your insight!!!! I will script out all object and move my data into a new Database rather than shrink and risk the fragmentation.



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