SQL Server Database is too big!

[wengang]

I got an email from the web host today that my db is over the size limit.

They said it was 7000 MB.

I knew i didn't have that much data, so I went to look at the database.

The only info they give me is that the data file is 900 MB and the log file is 6000 MB.

I don't know enough about the inner workings of SQL Server to mess with it, but it sounds like the problem is the log file.

What can i do to clean it up and make the database smaller?

Thanks.

[jggtz]

Please read through all of these:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147587
http://sqlinthewild.co.za/index.php/...ing-databases/ http://www.sqlservercentral.com/articles/64582/

In my case I do the following :
1.- Full backup
2.- shrink

[wengang]

OK.
I don't know what to take away from this.
It sounds like shrinking the database is completely the wrong thing to do.

So, let me ask differently.
I have less than 1G of data in a SQL Server 2005 database and my allotted space is 1 Gb, but for some reason my database is being reported as 7 GB, 0.9 Gb Data file 6.1 Gb Log file.
The host emailed today to tell me that I need to get the size down or buy more space. The space sells by the GB and it isn't cheap. Also, I have no reason to expect the db will stop growing.

I need a solution to get the db smaller and keep it smaller.
Any ideas?

[CVMichael]

Change the recovery model to "Simple", then do a shrink.

To do that, right click on the database, go to properties, click Options, and you should see "recovery model" drop down there...

[dee-u]

Have a look at this also.

[mikeblas]

Sounds like you're not doing backups, and therefore never truncating the log file.

[wengang]

no i've never done a backup.
But there is a button on the webhost control panel that says "truncate log file".
I haphazardly clicked it after I posted here. I don't know what that did (aside from the self-evident).
I guess it takes time for the report to change the numbers.


OK. if anybody cares, here's the rest of the story.
I had 2 databases on different hosts.
One ran fine and everything was good, no issues. the other was always crashing and having connection problems. So I decided to move the bad server's database into the good one and have one db.
But the one I moved had about 6 million records in the main table, and it grows by thousands each week.
Still, a single record can't be more than about a hundred bytes of data, almost all numeric. So it shouldn't be nearly that big.

How will backing up help other than having a backup of the data, and does that automatically shrink the log file?

[wengang]

Update:
I just checked the host panel and it shows the log file is now 13 MB only, so I guess that truncate button works. But the report is still showing 7000 MB total size. I guess it just hasn't caught up.

Also, the data file is still showing as 932 MB.
My main data table has 6 million records and each record has about 15 fields, almost all numeric(10,3)
plus a datetime, an ID, and a varchar(10). I don't see how that adds up to 900 MB.
A second table has the same 15,000 records in it, though I occasionally delete some of them as they are no longer needed. These all have their data updated on a daily basis automatically.
The main table with the 6 million records has 50,000 or so records added weekly.
I guess this activity is growing the log file??
Also, how do I get the data file smaller?
I read the articles on shrinking and several people had very negative views about doing it.

[GaryMazzone]

Truncate log is not the same as shrink database. I would not shrink the data file by try to shrink the log file only. Ensure the the database recovery mode is Simple when you do this. Also leave it in simple mode.

[wengang]

Gary
I see that you have posted in a number of threads on this subject.
Why did you find it necessary to reindex the primary keys.
and just what is that or how is it done?
I saw that the procedure script you used was very long, not just shrinkdatabase
is all of that necessary?

If I have this right, it's 1. reindex primary keys (?) 2. set recovery to simple, 3. shrink the database.
Is that right?

Will shrink database do something to the primary keys or change data in some way? I definitely wouldn't want that. Also, why does shrinkdatabase have parameters? WOuldn't everybody want the database to be only as big as it has to be?

[GaryMazzone]

That was doing something else. I need to reclaim storage space from a table. The only way to do that was to reindex the primary key after I delete the column from the database. Doing that will redo all the leaf pages for the index (basickly the leaf pages for the table) to allow the recovery of the space. We were moving a large field (VARBINARY(MAX)) from one table to another database. Setting the recovery mode to simple will allow you to shrink (Truncate) the logfiles. When it is in Simple mode the log files will not get quite so large. I would not recommend the shirink database (unless you were in the position I was in before removing that large field). I would use shrink file and just do the log file.

[techgnome]

something to look at as well... open up the properties of your database, look in the sections where the mdf and log files & location are defined... and check your growth rate....the log files grow by percentages... while the mdf grows by Mb ... I've found this can cause the log file to grow by leaps and bounds if doing a lot of activity. I usually reset it to something more reasonable and grow them both by a set size. (typically 10 for data and somewhere between 5 & 10 for the log).

-tg

[wengang]

Right. but like i was saying, now that I've truncated the log file and it's small again, the data file is still 900 MB and that's way, way more than the actual amount of data that I have. So isn't that the file I should be shrinking?

[GaryMazzone]

No I would never shrink the actuall data files. They will only grow again in need, if the space is empty they new records would just use the already allocated space in the data file.

[FunkyDexter]

Truncating is not shrinking.

Truncating a file deletes information from it that is no longer needed but it doesn't change the size of the file. Think of it as pouring the contents out of a beer can.

Shrinking reduces the size of the file down to a minimum of the size of its contents. Think of it as crushing the beer can after you've emptied it.

What people are telling you to do is to truncate the file to reduce the size of its content and then shrink it to reduce its actual size. You need to do both.

It's important to note, though, that shrinking the file is a short term fix at best. If you stop there it's just going to grow again. So you need to investigate why the log file is growing so large in the first place.

The most likely explanation is that you've got your recovery mode set to Full. In this case SQL Server retains information about all the transactions that have gone through it since the last back up was taken. If that's the case then you either need to start taking regular back ups so that the log can truncate to a reasonable size or you need to change the recovery mode to Simple, in which case SQL Server will only bother to retain information about the pending transactions.

[wengang]

well i did both, i guess.
I used the web host's truncate button
then I open management studio and right clicked the database and clicked shrink files.
Then I entered 25% in the bottom field.
Later it shrunk the file some, but only by about 100 mb.
FUnny thing now, I'm getting a LOT more timeout errors than I ever got before.

[mikeblas]

You're getting timeout errors because shrinking the file does lots of I/O to moved data around in the file, compacting it towards the beginning of the file.

Ignoring the specifics of how SQL Server actually stores data, let's think of a simple example. Maybe you have a table that takes 10 pages:

[0][1][2][3][4][5][6][7][8][9]

Your file is obviously ten pages long. Let's say you delete all but the first page and the last page. SQL Server will end up marking the data as deleted -- the pages are free, but they're still part of the file. The file looks like this, where [.] is a free page:

[0][.][.][.][.][.][.][.][.][9]

The operating system file is still ten pages long, even though ten pages are still in use.

This might seem wasteful, and it is I suppose -- considering space. But it's best for performance as adding more data to the file will probably reuse existing pages in the file (which is fast) instead of asking the operating system to make the file larger (which can be slow).

Obviously, in this very simple example, you only have one block of space that's not in use. In a real database file, depending on the usage pattern the database has seen, you might have hundreds and hundreds of such chains in varying length.

Making the file shorter means taking each of those pages, moving them closer to the beginning of the file, thus trying to put all the free space at the end of the file and all the used data at the beginning. This is a lot of I/O to do, and since I/O is expensive, you're likely to run out of I/O capacity and end up getting timeouts on other queries.

The mechanisms SQL Server uses for storing data are much more complicated in practice, and I don't cover all the details in this explanation. But I think this abstraction suffices to explain the mechanism at play.

[wengang]

so the question now is, how do I put a stop to these timeouts. THey're still happening.

[CVMichael]

First of all, you did not show the data structure, types, and number or records for each table, so I cannot tell you what to improve from that perspective.

First you should improve the data structure, then you should improve your queries and make sure they are efficient.

If you think the DB is already improved to the MAX, then I think there is nothing else you can do to fix your problem. Next thing after that is make sure the hardware is adequate for a DB (SQL Server). For example lots of RAM and a good I/O hard drive.
I think the provider you are using don't have hardware good enough for your needs.

By the way Arvixe has unlimited hosting space...

[mikeblas]

Is the compaction job still running?

[FunkyDexter]

Later it shrunk the file some, but only by about 100 mb

That's because your file is still full. When you truncated the file there wasn't much information it could get rid of. Then when you shrunk the file you didn't see much change because there wasn't much spare space to get rid of.

In my previous post I wrote "you need to investigate why the log file is growing so large in the first place". The reason I wrote that was because it was important. It was so important, in fact, that I'm going to write it again with a few exclamation marks: you need to investigate why the log file is growing so large in the first place!!!

Carry out the following steps, in order:-
1. Take a full back up.
2. Truncate the log
3. Shrink the long

At this point the files will probably get significantly smaller. Then, as an added step to make sure you don't get into this state again:-
4. Look at the recovery mode on the database. It's almost certainly set to full. Change it to simple.




As for the timeouts, I have no idea, and anything anyone else posts at this stage is a guess at best because there could be a million different reasons and it might not be connected to shrinking the file. You need to do some investigation yourself first:-

1. Check which queries are timing out. If it's the same query all the time then you should examine those queries individually and performance tune them.
2. If it's all different queries then you've probably got a systemic problem. Disk IO is the biggest bottlenek so ask your provider if they've got any tools you can use to analyse this.

I very much doubt that the log file re-growing is the cause of your time outs. It would introduce a delay once at the moment it grew and then it would go back to behaving normally. It wouldn't give you the continuous timeouts you're describing.

[wengang]

ok
in case I wasn't being clear before,
there were no timeout issues before i took these several steps.
I only did this because the web host told me my db had grown over the allowed size.
the db was running fine and never had a single timeout.
The things i did are:
1. truncate the log file
2. shrink the database (by the procedure I mentioned above)
after that, I now regularly get timeouts all the time.

I don't think it would have anything to do with the data structure since I never made any changes to the data tables or anything else for that matter. I just did the two things above intending to make the file smaller, now I get frequent timeouts.

BTW, i haven't since been notified about the size issue, so for now, I'm assuming it is not a continuing problem.

[szlamany]

well i did both, i guess.
I used the web host's truncate button
then I open management studio and right clicked the database and clicked shrink files.
Then I entered 25% in the bottom field.
Later it shrunk the file some, but only by about 100 mb.
FUnny thing now, I'm getting a LOT more timeout errors than I ever got before.

You should have followed the link in post #5 from dee-u - that would have shrunk the log and that would never cause timeouts.

Shrinking your db and changing values on how it grows can cause problems.

Do you have a backup of the db before any changes were made so you can see original settings?

What the heck does the WEBSITE SHRINK-MY-DB button actually do?????

Can you talk to your remote DB with SSMS?

[wengang]

the control panel button said "Truncate Log File"
that was the first thing I did. I assume it truncated the log file.
Then the other thing was done in Management studio.
I right clicked on the database, went to Actions, and clicked on Shrink database.
That ran one time, now I get a lot of timeouts.
I don't have a backup from before.
If necessary, I could forfeit all my data. That wouldn't be the ideal solution. That's why i'm asking.
Is there any way to "undo" or just a standard series of actions that can be taken to ensure the db has all the proper settings and is working as it should?

[szlamany]

How did you create your initial database?

If you could duplicate those steps - even with a different DB name - you could see what MS offers for defaults and we can discuss where you might have strayed with adjustments in SSMS.

btw - having a large DATABASE is expected due to how sql partially fills buckets in the DB - so that future insertions and adjustments can be accomplished.

Shrinking the 7 GB log was a GOOD thing.

Touching a 800 MB DB was a BAD thing.