Some of you may remember I posted about having made a change which unexpectedly doubled the size of a table in the database. (I changed one field from char(30) to char(60) )
Based on some of the advice given I have worked out that I could make the following changes…
Change the char(60) field to varchar(60)
trim that field in all records using replace([field],’ ‘,’’). (doing so with a filter, so I’m not doing millions of records all in one go!)
The import process includes spaces so where the field contains a character ‘1’. it actually also contains 59 spaces… (used the underbar to illustrate spaces in the example below)
I know that SQL space management can be complicated. Even if I make the above changes will the database bother to re-adjust its size? Or would I need to run a ‘shrink database’
There are 2 measures of size…the actual space used by the data, and the space used by the file on the disk. If you make that change, the space used by the table will be reduced, and so the amount of free space in the DB will increase (though you will not see this instantaneously unless you run some allocation checks). the file size will not change, just the percentage of unallocated space in the file.
To reducee the actual size of the physical file on the disk you will have to manually shrink the database.
Ok I’ve made the two changes (the data type, then remove the spaces from the records) and the filesize of the table actually increased.
And then I did shrink database right click->Tasks->Shrink->File then ‘release unused space’
And the datbase has been reduced to roughly the size it was before I made today’s changes. So at this point the changes don’t seem to have made much difference at all.
I’ll check again tomorrow, but would this suggest this database has been set up in some weird unexpected way? That it doesn’t properly manage the space or something?
That was more or less my question. My solution to large DB files has typically been to buy more disk space since they more or less give away hard drives nowadays. And I’ve worked with some pretty big DBs in my day.
My main concern is disk space. The doubling of the DB size was totally unexpected and I’d rather ‘fix’ it than have to go to the IT dept with my tail between my legs and say “I made the database double in size. I can haz more spase naou perty pleez!”
Having said that there is enough space for now. But still. I’d like to know a) how/why it grew. and b) how to un-grow it. After all I think that now, if anything, it should take up even less space than it did when I originally began to mess.
I say that because originally it was a char(30) who’s average length of data was about 15, so I’ve removed the white space from eight years’ worth of data, and changed the datatype to varchar from char so that the size of each record reflects what’s actually in it rather than the data type (so a record where that field contains ‘1’ will take up as much space as one char rather than sixty chars)
edit: At one point in the past my boss expressed willingness to pay for me to go on a dbadmin course. That fell by the wayside. I think I should broach the issue.
“Release unused space” only releases semi-contiguous space at the end of the file. What you want to do is reorganize the DB, there should be an option for somethig of that sort.
Also, if you used Management studio to edit the table, be aware that it probably copied all of the contents to a new table instead of just altering the existing one. That’s why it seemed to use so much more space.
The SQL Server service doesn’t automatically reposition every column in every record in the table to account for this change, as it isn’t necessary, as far as the engine is concerned. If you truly want its size to be minimized, you can create a table with the desired structure, copy the data to this new table, deal with any indexes, permissions, and other objects associated with that table. Then verify that the new table is perfect, drop the old table, and rename the new one to finalize the replace. Once all of that is done, the table will be smaller (at least based on my recollection of your description of it), and you can shrink the database as well, if desired.
The problem is that there are a lot of potential issues you might run into that, with your current level of experience, might be downright scary, and you’ll also temporarily increase the database size quite a bit.
Don’t tell IT that you double its size, just tell them that if you’re going to be a DBA, you might as well get used to asking for space.
Optimist: The glass is half full
Pessimist: The glass is half empty
The DBA: 50% of capacity? Get me more space NOW!!!
OK. My apologies. It shows how much I know. I could see that there was one column created as ‘indexed’ in the table design and it wasn’t the column in question, so I assumed it wasn’t indexed.
But it seems like in some way all the columns are indexed.
