SQL Server Express Size Limit (1 Viewer)

[tmyers]

I haven't been able to find a clear answer to this, so I hope someone hear may know. I know that SQL Server Express database has a size limit of 10G but when that size is approached, can you just create another database within the same instance of SQL Server and start anew? So if I have 3 different databases within a single instance of SQL Server, all 3 could go until they each hit 10G or I would be done once all 3 collectively add up to 10G? I would assume its the former but cant seem to find out for sure.

 

[GPGeorge]

Each database is subject to the 10GB limit.

If your data grows beyond that limit, there are options, including what you suggest, i.e. adding a second database. That would come with most of the same problems you encounter trying to split an accdb BE into multiples, such as loss of Referential Integrity between tables in the different databases.

I can't image most small to medium sized relational database applications growing that large, though. Probably more of a theoretical problem, IMO. On the other hand, if your data grows that dramatically, it probably also indicates a growing operation and one of the licensed versions of SQL Server might be a logical choice as well. There is a cost to those licenses, of course, but in the greater scheme of things, that cost might be worth incurring if you need to handle that data.

And, of course, there are other free databases, such as PostgreSQL which advertise no limits on size. You'd have to investigate them to decide if they would be viable alternatives for you.

 

[tmyers]

To make sure I follow, so within a single SQL Server instance, I could have 3 different databases all of which could each be 10G for a total of 30G for that single server instance?
And it is more of a theoretical problem then practical right now but if it had been 10G overall, that would have allowed me to possibly push for something other than the Express version of SQL Server as I would hit that quite quickly if it was a collective total.

 

[GPGeorge]

30 GB or more on an instance, yes. I don't know what the limit on databases is. There must be one.

I think you may want to pitch this differently. Splitting data into multiple databases is probably much less than optimal in any circumstance, although one might consider it a stop-gap. Pricing is opaque to me, especially since I'm retired and no longer having to support clients.

What kind of data are you collecting now? Just out of curiosity, and partly because I'm not aware of any former clients remotely approaching the 10GB limit, how much data do you now have in an accdb? And what is the rate at which you accumulate data?

 

[GPGeorge]

I just ran across this blog post. Brent Ozar is one the most respected SQL Server experts in the world, btw.

 

[tmyers]

I bank sales, purchase order and vendor billing. The vendor billing is tiny and honestly could easily remain in Access if I wanted it to. My sales is currently at 1.4G after just under 3 years and still needs 2 and a half years of archived data added so it is already going to exceed the limits of a single Access file. The PO info isn't as bad as the sales data, but it isn't much better either.

To give an idea of accumulation, for the sales database (the PO is still being built), I add on average 5k records a day but I have 2 or 3 days a month where I add anywhere from 25-50k records.

Edit:
The sales is close to 30 columns wide and the PO will be ~18 columns.

 

[GPGeorge]

Specifications for ALL versions of SQL Server.

 

[CJ_London]

if you were to use mySQL instead of express, the limit is 2TB providing you are using a NTFS file system (pretty standard these days).

 

[GPGeorge]

I bank sales, purchase order and vendor billing. The vendor billing is tiny and honestly could easily remain in Access if I wanted it to. My sales is currently at 1.4G after just under 3 years and still needs 2 and a half years of archived data added so it is already going to exceed the limits of a single Access file. The PO info isn't as bad as the sales data, but it isn't much better either.

To give an idea of accumulation, for the sales database (the PO is still being built), I add on average 5k records a day but I have 2 or 3 days a month where I add anywhere from 25-50k records.

Edit:
The sales is close to 30 columns wide and the PO will be ~18 columns.

I see. Thanks. Again, to me, the idea that the cost of a license for SQL Server (roughly $1,000 for Standard Edition) is a deal-breaker for a business that size is not an easy sell. You know your own organization, though.

And there's always SQL Azure....

 

[tmyers]

I see. Thanks. Again, to me, the idea that the cost of a license for SQL Server (roughly $1,000 for Standard Edition) is a deal-breaker for a business that size is not an easy sell. You know your own organization, though.

And there's always SQL Azure....

I would LOVE to get my hands on Azure and learn with it, but that would be a hard no due to cost and restrictions from IT. The company uses Azure at the corporate level but not at the store level unfortunately.

I know of MySQL, but have never looked into it. I will do some research on it.

 

[Minty]

Azure isn't expensive if you buy the right options. you can host a sizeable database for about £20/month

 

[tmyers]

My boss considered not allowing a Power BI license for better reporting and it was like $5 a month

 

[Minty]

My boss considered not allowing a Power BI license for better reporting and it was like $5 a month

Ah - Oh well - it's probably considerably less than he spends on a single mobile phone for any member of staff...

 

[Isaac]

I also would not recommend assuming that Access's size and SQL Server size will be the same for the records. I could well be wrong and that is just an educated guess, not from expertise, but what is 1 GB in Access may not be 1 GB in SQL Server.

As George said, the SQL Server Express size is pretty generous and I've done many small business projects in it not coming remotely close to that size limit - PLUS you can add, as you mentioned, a different database for truly different data that doesn't need to be relationally connected and still include both in a single query easily - so with all that I doubt the size limit is an issue for most small business projects.

 

[tmyers]

I am going to give it a go and see how it turns out. I am going to keep the aspects their own individual database (one for sales, one for PO's etc).

I did also get in contact with our IT department and learned that our main systems database is currently sitting at just under 60 gigs but obviously has a little more data that I am pulling out and apparently it has nearly 1200 tables!

 

[Galaxiom]

Brent Ozar is one the most respected SQL Server experts in the world,

Brent's free Accidental dba course is a must do for anyone working with SQL Server. The tools provided are invaluable.

 

[GPGeorge]

Spoiler: etc

I am going to give it a go and see how it turns out. I am going to keep the aspects their own individual database (one for sales, one for PO's etc).

I did also get in contact with our IT department and learned that

our main systems database is currently sitting at just under 60 gigs

Spoiler: etc

but obviously has a little more data that I am pulling out and apparently it has nearly 1200 tables!

This "main systems database".... What is wrong with getting space for your needs on the server where that database is running?

 

[Velocity]

I haven't been able to find a clear answer to this, so I hope someone hear may know. I know that SQL Server Express database has a size limit of 10G but when that size is approached, can you just create another database within the same instance of SQL Server and start anew? So if I have 3 different databases within a single instance of SQL Server, all 3 could go until they each hit 10G or I would be done once all 3 collectively add up to 10G? I would assume its the former but cant seem to find out for sure.

you can create multiple databases within a single instance of SQL Server Express and each of those databases can be up to 10GB in size. This is according to Microsoft's documentation which states: "Each instance of SQL Server Express can contain up to 10 databases with a total combined size of 10GB".

 

[Minty]

@Velocity That's not correct according to the previously supplied link.
A single database can be up to 10 Gb, but you can have up to 32,767 databases per SQL instance.

 

[tmyers]

This "main systems database".... What is wrong with getting space for your needs on the server where that database is running?

IT does not allow anyone to touch our primary system. We are allowed to write queries in a custom interface to extract data, but that is all we can do. Everything else regarding that software/hardware is off limits.