August 4, 2011 at 9:38 am
I need to do some capacity planning for a SQL Server database used by my .NET app.
What's the best way to go about doing this? If I go to SSMS and right-click the database I see the following information:
Size = 27.81 MB
Space Available = 0.40MB
When I backup the database to a bak file the bak file size is only about 4MB. Does the backup process really compress the database size that much?
I think SQL Server allocates more space for a database as the database grows so the database doesn't use more space than it requires. Is this why the database size is 28 MB even though the bak file size is only 4MB? But this doesn't seem to explain why Space Available would only be 0.40MB.
I need to find out at what point my SQL Server database will run out of space. I guess this involves understanding the max amount of space the server will auto-allocate for the database and making sure that the server is configured to auto-allocate the max amount of space. Can anyone provide some guidance on how to accomplish this?
August 4, 2011 at 10:17 am
Take a look at the next section down (Files). You should see a filegroup PRIMARY. What does the autogrowth column show? Something like "By 1MB, unrestricted growth"? That means that it will automatically autogrow the size by 1MB as needed until there is no more disc space. The piece you were looking at is the amount of space remaining in the currently allocated space. Hope that helps.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2011 at 10:42 am
SQL 2008 can do backup compression. Are you using that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply