MSDE Database Size Restrictions 1

[dougcoulter]

Hello everyone. I am trying to determine the size of an MSDE database. In another thread, a couple of system stored procedures were recommended, but I still have a couple of questions.

From my research, it appears that each individual database within an MSDE installation is limited to 2GB - as opposed to the cumulative database size - is this correct? Also, it seems like the limitation is based on the data file only (mdf), not including the log file - is this correct?

I have tried to simulate reaching the 2GB barrier by simply creating a new database with a data file with a space allocation of 3GB - MSDE allows me to do this, so I am assuming the barrier is reached when the data within the allocated space reaches 2GB.

I first implemented the sp_spaceused stored procedure on the this 3GB test database (called Sample), and it returns a database_size of 3001.00 MB and unallocated space of 2999.48 MB. The second row of information: reserved = 536 KB, data = 152 KB, index_size = 280 KB, and unused = 104 KB. Based on this information, what would tell me how close I am to the 2GB threshold?

Similarly for the sp_helpdb stored procedure, it returns a db_size column, which for the Sample database is 3001.00 MB.

Can anyone offer any insights? Again, what I would like to do is to check on a nightly basis how close a database is to the 2GB barrier, and perform some tasks say if it is within 80% of 2GB.

Thanks!

 

[timscronin]

2 is the limite

 

[dougcoulter]

Thanks for the response timscronin - what do you mean that 2 is the limit? Do you mean that 2GB is the MSDE limit?

 

[SemperFiDownUnda]

Like I said in the other post use the sp_helpdb to determine the size. Let the database grow automatically.
When you archive the data make sure you shrink the DB.

Then you just have to worry about the database size. The size does not include the log file.

From books online -
"The size of a database cannot exceed 2 GB when using the SQL Server 2000 Desktop Engine or the Microsoft Data Engine (MSDE) 1.0."

Instead of diving into the system just stick with the main database size. Should be good enough for your intended purpose.


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[dougcoulter]

Thanks for the response Wayne!

I did try the sp_helpdb stored procedure, but it seems to only provide the allocated space of the database. When I created a test database within MSDE (called Sample), I allocated 3GB to it (which MSDE allowed me to do). I then ran the sp_helpdb stored procedure and it returned 3GB as the database size. Two things concern me - the first is why did MSDE allow me to create a 3GB database. The second is based on my assumption that the MSDE limits the actual data in the database, not the allocated amount of space. If this is true then sp_helpdb only returns the allocated space (since I had not inserted any data in to the Sample database).

Am I missing something here?

 

[SemperFiDownUnda]

Don't allocate 3gb. Allocate 512meg. Let the database grow as it needs. When you archive shrink the database down so its allocation size is only as big as the size of the data.


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[dougcoulter]

Thanks again Wayne - I am still unclear as to determining WHEN to archive. I need to somehow detect when the database reaches 80% of its capacity. I only allocated 3GB in an attempt to simulate what happens when MSDE exceeds 2GB.

 

[SemperFiDownUnda]

you can simulate the database growing by inserting a bunch of data.

I imagine this is going to be a scheduled process so just check when the db is over 1.6 gig.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[dougcoulter]

Wayne -

I can definitely setup a scheduled job to check the size of the database in question - but again, I am not sure which stored procedure to use that will tell me how much space has been used.

Thanks -

Doug

 

[SQLBill]

I use the procedure sp_spaceused and then subtract unallocated from data. That gives me the size of the database with data and indexes. (I use this for my SQL Server 2000 Enterprise Edition database to give me daily/weekly/monthly/yearly growth figures).

-SQLBill

 

[dougcoulter]

Thanks SQLBill.

I decided to run some tests to see how MSDE behaves upon reaching the 2GB limit. I created a new database, allocated 1000 MB with a 10% Auto Grow. I then created one table with two columns - the first an auto numbering BIGINT, the second a CHAR with length of 8000. According to some baseline calculations, I figured I should reach 2GB with 262,144 rows of data. I inserted groups of records at several intervals, and after each insertion exceuted sp_spaceused and captured the results. This is what I found:

After DB Creation
-----------------
database_size = 1001.00 MB
unallocated space = 999.48 MB
reserved = 536 KB
data = 152 KB
index_size = 280 KB
unused = 104 KB
database_size - unallocated space = 1.52 MB

After Table Creation
-----------------
database_size = 1001.00 MB
unallocated space = 999.30 MB
reserved = 720 KB
data = 264 KB
index_size = 296 KB
unused = 160 KB
database_size - unallocated space = 1.70 MB

After 100,000 Rows Inserted
---------------------------
database_size = 1001.00 MB
unallocated space = 217.26 MB
reserved = 801528 KB
data = 797680 KB
index_size = 3672 KB
unused = 176 KB
database_size - unallocated space = 783.74 MB

After 200,000 Rows Inserted
---------------------------
database_size = 1611.56 MB
unallocated space = 44.48 MB
reserved = 1603672 KB
data = 1596400 KB
index_size = 7064 KB
unused = 208 KB
database_size - unallocated space = 1567.08 MB

After 375,000 Rows Inserted
---------------------------
database_size = 3139.69 MB
unallocated space = 199.34 MB
reserved = 3009896 KB
data = 2996720 KB
index_size = 13000 KB
unused = 176 KB
database_size - unallocated space = 2940.35 MB

The surprising thing is that MSDE never balked upon exceeding 2GB... ?? I checked the table and all of the data appears to be in tact. I also started questioning the version of SQL Server (although I am running it from XP Pro, so it must be MSDE right?) I also checked the @@version from Query Analyzer and it indicated that I was running the Developer Edition (8.00.760).

Can anyone offer any explanation as to why MSDE continues to accept data beyond 2GB?

 

[dougcoulter]

Sorry for the previous long winded reply. I was concerned that the Developer Edition installed on XP Pro was the reason I did not encounter the 2GB barrier, so I performed the same test on another XP Pro box running the Desktop Engine, and I did receive an error upon reaching 2GB. In fact, I was not even able to provide an initial database size > 2048 MB (which is what I would have expected).

So this leads me to the question of what is the database size limit for the developer edition running on XP Pro? I cannot seem to find it on the Microsoft website.

Thanks again!

 

[SQLBill]

This is from Microsoft's SQL web site:

Developer Edition:
Scalability:
Up to 32 Processors
Up to 64 GB of Memory
Maximum Database Size 1,048,516 terabytes


Reference:

http://www.microsoft.com/sql/evaluation/overview/default.asp

-SQLBill

 

[dougcoulter]

Thanks again SQLBill - I was looking for that exact link, but no luck. Well that certainly explains the behavior I witnessed initially .

Once I recognized that I was not running my simulation with the Desktop Edition, I switched to another PC which I verified was running MSDE. I created a new database with the same table (i.e.: 2 fields, one BIGINT as an Autonumber and one CHAR(8000)). I proceeded to insert records and upon reaching 248,263, I received an error message indicating that the Primary Filgroup was full - excellent. These are the statistics at that point from the sp_spaceused stored procedure:

After 248,262 Rows Inserted
---------------------------
database_size = 1949.81 MB
unallocated space = 0.34 MB
reserved = 1995232 KB
data = 1986352 KB
index_size = 8720 KB
unused = 160 KB
database_size - unallocated space = 1949.47 MB

My first question is did I receive the error because MSDE was attempting to grow the database size another 10%, which would have pushed me over the 2GB limit?

Now, my biggest question surrounds the next chain of events. I then created a separate database with the same (empty) table. I then performed a select into from the full table to the empty table for 100,000 rows, followed by deleting the 100,000 rows from the previously full table. I then ran sp_spaceused:

After Deleting 100,000 Rows
---------------------------
database_size = 4059.13 MB
unallocated space = 784.79 MB
reserved = 1191960 KB
data = 1186376 KB
index_size = 5360 KB
unused = 224 KB
database_size - unallocated space = 3274.34 MB

It appears that deleting these rows did not "free" up any space. In fact the database_size more than doubled! I then performed a shrink database command:

After Shrinking the Database
----------------------------
database_size = 1554.69 MB
unallocated space = -7.90 MB
reserved = 1597784 KB
data = 1590504 KB
index_size = 7072 KB
unused = 208 KB
database_size - unallocated space = 1562.59 MB

I was kind of surprised to see that the database_size was not reduced even more. After all, 100,000 rows represents 40% of capacity. Is this because of the transaction log? (This test database is set to run in Simple Recovery mode.) After moving the remaining rows, resulting in an empty table and performing another shrink:

After Deleting Remaining Rows and Shrinking
-------------------------------------------
database_size = 1004.13 MB
unallocated space = 602.88 MB
reserved = 406648 KB
data = 404400 KB
index_size = 2032 KB
unused = 216 KB
database_size - unallocated space = 401.25 MB

The database_size seems reasonable, since shrinking the database will not shrink it below the initial size which was 1000 MB.

 

[SQLBill]

My first question is did I receive the error because MSDE was attempting to grow the database size another 10%, which would have pushed me over the 2GB limit?

Yes, and that's an issue with auto-growing by percentage. I always use by MB.

See that -7.90?
unallocated space = -7.90 MB

That's an indication the numbers aren't correct, you can't have negative disk space. Sometimes you need to reset the stats (sp_updateusage). I always run that command before running sp_spaceused, especially if I've expanded/shrunk the database.

Also, I believe that the transaction log, when SIMPLE is selected, still exists but shrinks/truncates upon CHECKPOINT. The checkpoint may not have been met between the time you initially deleted the rows and the time you deleted the rest.

-SQLBill

 

[dougcoulter]

Thanks again SQLBill - great information .

 

[SemperFiDownUnda]

Sorry I wasn't more clear with what I was saying

You create the database and set it to grow by a given amount, based upon the growth rate (if your db grows by 20 meg a day I'd have it grow by 20meg a pop).

You have your nightly process (scheduled task) grab the physical size of the database. Since your database grows only 20meg a pop it will always be within that much of the actual size.

At a given unit (you said 1.6g) you archive off your data delete your data and shrink the database. This will compact the data. If you archive 1 gig of data your database size should be about 600meg after shrinking.

Databases can be set up to shink automatically too if you want. Look up "Auto shrink".




Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[xtendscott]

I want to hear how his test goes to try and reach the "Maximum Database Size 1,048,516 terabytes"