Is there any way to make SQL database size small?

[XYZ500]

Keeping backups in compressed zip format is not an issue, but the database size itself is around 2GB which is quite big. Is there any way to make the size small?

[ozzy47]

Well, what is it in the DB that is making it that size?

Are you storing attachments in the db? if so, move them to the file system and that will free up space.

Same goes for avatars etc. Move everything you can into the file system.

[Max Taxable]

Two Gig ain't big.

[ozzy47]

It's not bad depending on the host, and what it is that is making it that size.

[XYZ500]

Well, what is it in the DB that is making it that size?

Are you storing attachments in the db? if so, move them to the file system and that will free up space.

Same goes for avatars etc. Move everything you can into the file system.

How to find out if attachments and avatars are stored in DB? And how to move them to file system?

[ozzy47]

ACP --> Attachments --> Attachment Storage Type

You still did not answer what it is in your DB that is making it that size.

[Max Taxable]

There are many 100s of BIG boards with millions of posts and 10s of thousands of avatars stored that have much larger databases than 2GB. What makes you think you need to reduce yours?

[ozzy47]

Really don't matter, if he/she is storing the avatars and attachments in the DB, by moving them to the file system, will not only reduce the DB size, it will also help with the sites speed. :)

[Max Taxable]

Really don't matter, if he/she is storing the avatars and attachments in the DB, by moving them to the file system, will not only reduce the DB size, it will also help with the sites speed. :)No doubt about that. It's one of your own basic tips for speeding up your vBulletin. (http://ozzmodz.com/showthread.php/2580-Speed-Up-Your-Site-Within-The-ACP)

I'm just curious to know what makes him think it is too big. Sounds like something someone with very little knowledge, would tell him. Someone like, a web host call center puke, for example. Someone trying to blame their own performance issues on the customer.

Newsflash for the OP - the entire DB isn't ever called. Just the parts of it the scripts need, at the time they need it.

[XYZ500]

ACP --> Attachments --> Attachment Storage Type

You still did not answer what it is in your DB that is making it that size.

I see this when I go to ACP --> Attachments --> Attachment Storage Type. http://prntscr.com/5bpgqv

I believe this means that the attachments are being stored in the DB. Should I hit the "go" button?

In answer to your question, I do not exactly know what is being stored in the DB that is making up this size. There are 1million+ posts and around 70k users.

I am not really a technical expert so I have to pretty much ask my host to do everything server related for me (e.g, restoring DB backup) and then they whine that its going to take a lot of time because 2GB is a VERY BIG size. So that's why I was just wondering if I could do anything to reduce the size.

Max_Taxable, I believe I have answered to your post as well here.

[ozzy47]

Yes you can do that for your attachments and avatars.

For attachments, https://www.vbulletin.com/docs/html/main/attachment_storage_db_to_fs1

For avatars, http://www.vbulletin.com/docs/html/userpics_db2fs

TBH if your host is crying about the size of the DB, you need to get a better host.

[Dave]

Also sometimes when you uninstall plugins they don't delete all tables and data off the database. You should check each table in the database and see if they were ever used by an old mod.

[ozzy47]

This is true, but I would not attempt that unless you know exactly what you are doing. :)

[kh99]

It's probably the attachments, but if you have phpMyAdmin you can go to the list of tables and sort them by size (by clicking on the 'size' column), then if you see any other tables that are large and it's not obvious what it is, it would give you something to look in to.

Also if you move attachments to the file system, that's something that you'd have to back up separately. If you lost everything you could download the vb files and restore your database, but if you didn't have a backup of the attachments and avatars files they would be lost.

[Lynne]

I'd just like to add that before you do *anything* to your database, take a database backup. If you move the attachments out of the database and something goes wrong, you will want to have that database backup!

[ozzy47]

I would have to say you are right, attachments can eat a good amount of DB space. :)