Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SP_Spaceused Expand / Collapse
Author
Message
Posted Monday, July 08, 2013 10:04 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:37 AM
Points: 743, Visits: 1,535
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?



Post #1471261
Posted Monday, July 08, 2013 10:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1471267
Posted Monday, July 08, 2013 1:31 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:37 AM
Points: 743, Visits: 1,535
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.


Post #1471340
Posted Tuesday, July 09, 2013 5:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, January 24, 2014 7:02 AM
Points: 3,066, Visits: 1,413
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
Post #1471548
Posted Tuesday, July 09, 2013 5:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, January 24, 2014 7:02 AM
Points: 3,066, Visits: 1,413
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
Post #1471551
Posted Tuesday, July 09, 2013 5:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 583, Visits: 963
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 | MCSA SQL Server 2012
Post #1471553
Posted Tuesday, July 09, 2013 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1471601
Posted Tuesday, July 09, 2013 8:46 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:37 AM
Points: 743, Visits: 1,535
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.


Post #1471665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse