June 3, 2008 at 10:26 pm
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
June 4, 2008 at 2:50 am
maybe a delete trigger is not working?
Wilfred
The best things in life are the simple things
June 4, 2008 at 3:25 am
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
June 4, 2008 at 3:47 am
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
June 4, 2008 at 8:04 am
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.
June 4, 2008 at 2:40 pm
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
June 4, 2008 at 3:39 pm
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
June 4, 2008 at 3:49 pm
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
June 4, 2008 at 4:10 pm
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.
June 4, 2008 at 6:11 pm
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
June 5, 2008 at 12:37 pm
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply