SQL Server 2k database size

[meckeard]

Hi All,

I got an email from my hosting company stating that I was exceeding my alloted limit for my SQL Server database.

After reviewing the data, I determined that there wasn't a lot there.

The hosting company does not include the transaction log as part of the size.

I emailed them and they told me that the data does not matter, that it's the size of the columns. And that I should reduce my column size in order to reduce my database size. ??????

I am a little confused by this. I use varchar as my data type as I was under the impression that it only used the amount of space equal to the data. So, if my column was varchar(100) and I had a value that was 30 characters in length, it would only take up 30 characters. Is this true?

Any other additional comments would be appreciated.

Thanks,
Mark

 

[harebrain]

They're probably warning you that the width of a row can't exceed the 8192 byte page size. (8192 includes some bytes of overhead: I can't remember exactly what's available to you, but 8000 bytes is safe.)

Yes, yes, attributes defined as varchar only occupy the number of bytes each individual attribute needs, but you're looking at it the wrong way. (And over-estimating "just to be safe" is a sloppy substitute for analysis: take a good look at your real needs.)

Let's get back to the 8000 byte limit: if the sum of the length of the attributes in a table exceeds 8000 or so, you have set yourself up for potential data loss. Any row that is bigger than the (page size - overhead) will be truncated.

You must have an outstanding hosting company that would provide you with this level of preventive help.

 

[SonOfEmidec1100]

You are correct the varchar data type will use 30 chars plus a couple for the length indicator. The database size depends on several factors, there is normally a minimum size and the number and type of objects will effect the size. But unless you have a huge schema this is normally small in comparison to the data.

If your limit is the database size it should relate to the size of the data file on disk, how the schema is structured is irrelevant.

I would ask for a more detailed explanation.

 

[meckeard]

If I were to change some of my datatypes to help reduce the size, is there anything that I should know ahead of time?

For example, if I go from int to smallint, I notice that I get a warning that some data may be lost. Is this normal? Does SQL always warn you before it converts or only when it detects there is a problem?

Thx.

 

[SonOfEmidec1100]

This is a normal warning that data MAY be lost. You wont have a problem if all the values are small enough to fit into a smallint. With int/smallint the change will be aborted if any values actually dont fit. However with varchar fields the data is truncated.

 

[harebrain]

Personally, the last thing I'd look at changing (especially without examining values) is integer attributes.

Do some analysis on your data, for crying out loud! Do you NEED that varchar(100) or would a varchar(30) suffice? Don't just guess, FIGURE IT OUT.

And, after the analysis is done, if you just can't shorten any of your attributes enough to make a difference, create a second table that will have a natural join relationship with the first table, and move some of your attributes from table 1 to table 2: no data loss.

 

[SQLSister]

Agree with son of Emidac, the schema you use is none of their business, what should matter to them is the size of the file. Suppose I have big fields but will only have a few records, that take ups less space than if I have small fields and millions of records, so why would they care at all about the size of your fields? It's a puzzlement.


Talk to them some more and get more information. I would consider moving to a differnt provider.

 

[harebrain]

I'm surprised at you, SQLSister! Look, meckeard is obviously quite inexperienced with SQL Server. As foreign as the concept might be, his provider might actually be trying to help him before:

- he screws up royally
- they tell him they can't help him
- he gets mad and wrongly blames them

Ever hear, An ounce of prevention is worth a pound of cure?

 

[harebrain]

From the horse's mouth (MSDN):

Q: When I create a table, I get the following 2714 error message: 

Warning: The table '%.*ls' has been created but its maximum row size (%d) exceeds the maximum numberof bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. 
The table creation succeeds and data can be inserted without problems. What did this error message mean?

A: This error message indicates that you have variable length columns in your table (such as nvarchar or varbinary) and that the total maximum length of all the columns adds up to more than 8060 bytes. You can still insert rows into the table provided that the total length of the data in each row does not exceed 8060 bytes. However, if the data does exceed 8060 bytes, the insertion fails with the following error message:

Server: Msg 511, Level 16, State 1, Line 5
Cannot create a row of size <rowlength> which is greater than the allowable maximum of 8060.
The statement has been terminated.

Depending on the latch-up with the provider, they might see this message while the end-user, creating the offending tables, does not. Get it?

 

[meckeard]

harebrain,

Well, I'm not inexperienced with SQL. I was looking for feedback on the prompt you get from SQL when changing the datatype on a field.

I already checked my data and there are quite a few fields that I alloted a large varchar to that do not really need it. My client thought they would need up to the max 8000 characters but never used it.

The hosting company (IMHO) is the best. They do not cut off your database once it reaches a certain size, but they do email you when you pass the limit. Hence the start of my tread.

I will take everyone's advice into consideration and make some changes to my database. And use this as a good learning tool when creating my next one.

Thanks all.