Huge size blowout on Database

Question

Post reply

Huge size blowout on Database

RobJellyBean

Valued Member

Points: 59
More actions
June 3, 2008 at 10:26 pm

#191855

Hi,
We have a database running on SQL 2000 server. The database has been growing by about 15 - 20% per year and is currently around 11GB. We are doing some testing for an upgrade of the program that we use to manipulate the database, so a backup was taken of the production database and restored onto another SQL server in a development environment. The restore seems to have worked fine but when we started working on the database it began to grow abnormally (20% in a few days) and when applied the upgrade that we wanted to test, the size blew out to 232GB ! The software company that own the database program have restored the same backup onto their server and performed the upgrade with virtually no change to the database size.
I've tried doing the restore and upgrade several times using different backups and the result is always the same.
Does anyone have any ideas about what might be causing this problem and where I shoul look to fix it?
Thanks
Rob

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 1

maybe a delete trigger is not working?

Wilfred
The best things in life are the simple things

munireddybn-687270 Old Hand Points: 363 More actions · Answer 2

What is the value of file growth for data file and Transaction log of the database? If the value is more (by MB or by %) , it may cause abrupt increase in file size.

Thanks

MuniReddy

CrazyMan SSChampion Points: 12560 More actions · Answer 3

From what point the size is growing, mdf or ldf, since log files grows with differential backups, and check the recovery mode, change it to simple and then shrink the files, this will help, if you have to have recovery mode as full then use DBCC command to shrink files

Hope this helps

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

I had some similar problem and this was caused by one stored procedure kept committing and was in a loop and it therefore keep adding to the log - the log filled up in almost 5 minutes to 150 GIG.

The way i check this was to put on profiler and see what was running.

Hope that helps.

RobJellyBean Valued Member Points: 59 More actions · Answer 5

Thanks for your replys.

I don't know what a delete trigger is but I'll see if I can find out.

As far as the log size is concerned. It is big after the upgrade as a lot of transactions have taken place but I can reduce them by backup / shrink and this doesnot affect the size of the database itself.

1.gif = production database

2.gif = test system

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

The software company that own the database program have restored the same backup onto their server and performed the upgrade with virtually no change to the database size.

I've not seen such a thing... same backup blows out your machine but not the vendor's. Are you guys using the exact ame tape as the vendor? I'm thinking either someone pulled your tape too early or it has a corruption.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 7

I have seen this issue a couple of times I have been able to get to the root cause on any occasion.

It has not been frequent enough for me to worry but I know it is scary to see those size explotion. Now If I retry the restore all goes back to the "normal" expected sizes.

* Noel

RobJellyBean Valued Member Points: 59 More actions · Answer 8

Hi and thanks for your comments.

Calrification: The vendor and I are using exactly the same backup file to do the restore onto separate SQL 2000 servers. The files are not on tapes.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

That kind of narrows it down to something on the server... are they loading in a different recovery mode than you are? Do you have different server or db settings somewhere else. I know, I know... I'm not being much help... sorry. I'm pretty much out of tricks on this one if it's not something like that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

odyoda Newbie Points: 1 More actions · Answer 10

A few quick questions:

1) When you restore, what Recovery Mode are you using? (what's the vendor using?)

2) What is your AutoGrow set to?

3) What page size are you using?

odyoda