I have a db that grew 8.2GB in one week and don't understand why.
There are three tables added to the db daily. I calculated the
spaceused by each of the three tables for a period of two weeks. The
tatal amount of data added to the db for the three daily tables over
the past two weeks was about 4MB yet the db grew approximately 8.2GB.
WHY?
Can someone please tell me what I should look at so that I can
understand what is going on? 4 1389
Hi
There are a lot of things that can cause the database to grow. Without
more details its hard to say what, here are a couple of things it could
be.
Is it your database or is it your transaction log that has grown? If
you have your database set to full recovery and you are not doing
transaction log backups that can cause a large amount of growth.
Another possibility, have you recently set up a maintenace plan or a
job that rebuilds some or all of your clustered indexes? If your
database is set to auto grow, it could have grown due to index
rebuilds. Do you have some large tables on your database?
Hope this helps
John
war_whee...@yah oo.com wrote: I have a db that grew 8.2GB in one week and don't understand why. There are three tables added to the db daily. I calculated the spaceused by each of the three tables for a period of two weeks. The tatal amount of data added to the db for the three daily tables over the past two weeks was about 4MB yet the db grew approximately 8.2GB. WHY?
Can someone please tell me what I should look at so that I can understand what is going on?
At a guess, you have the database in full recovery mode and you are not
backing up the transaction log - in this case, the log will keep
growing. If you don't need transaction log backups, then you can set
the recovery mode to simple, and MSSQL will truncate (empty) the log
periodically. See "Selecting a Recovery Model" in Books Online for the
implications of using simple mode.
Note that truncating the log doesn't shrink the physical file - you can
use DBCC SHRINKFILE for that. See "Shrinking the Transaction Log" in
Books Online.
If this doesn't help, I suggest you post some more details - which
version of MSSQL, what size are the data and log files for the
database, have you tried DBCC SHRINKDATABASE and SHRINKFILE etc.
Simon
Simon and John thanks for both of your replies. Also in response to
both of your assumptions, I am running a 'Simple' recovery model. My
data file is 47,678,488,576 bytes or 47.6GB while my tlog file is
524,288,000 bytes or 524.2MB. Two weeks ago I started to monitor the
growth of the database. As I previously mentioned, we add three tables
to the database daily. The tatal size of all of the tables over a two
week period (3 tables x 10 days) is approximately 4MB.
Given these numbers, I don't understand how the db size could increase
8.2GB in a week. The reason that I say in a week is because two weeks
ago I tracked the size of the db file on a daily basis. The db was
39,403,651,072 or 39.4GB. I wasn't feeling well last week so I didn't
track the database growth for a week. I starting tracking the
databases growth again yesterday and noticed the 8.2GB difference from
a week ago.
On another note, I do understand your suggestion(s) of shrinking the db
or the db file. My issue though is how can a db grow 8.2GB when only
4MB of data is added. Remember last week the db size was unchanged
with approx. 2MB of additional data while this week the file has grown
8.2GB with the same addition of 2MB.
The db contains 515 tables, but most of the tables are the daily tables
which do not grow. Only the current day's daily tables grow and then
are used for historical reference. The core of the db contains approx.
38 tables which are mostly static. If there is change in the core
tables it is insignificate compared to the growth of the daily tables.
I know that we might not be able to figure out exactly what is
happening. I am only trying to gain a better understanding of the way
databases can grow. Perhaps each of the tables or a significate number
of them outgrow their allotted space and increased their reserved
space. This might account for the physical growth???
Going on the above assumption I reran the spaceused sp and did notice
an increase in the reserved space for the tables of two weeks ago. My
tracking only goes back two week, but if the increase is applied by
515 tables this may explain the anomaly.
Any thoughts?
<wa*********@ya hoo.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. . Simon and John thanks for both of your replies. Also in response to both of your assumptions, I am running a 'Simple' recovery model. My data file is 47,678,488,576 bytes or 47.6GB while my tlog file is 524,288,000 bytes or 524.2MB. Two weeks ago I started to monitor the growth of the database. As I previously mentioned, we add three tables to the database daily. The tatal size of all of the tables over a two week period (3 tables x 10 days) is approximately 4MB.
Given these numbers, I don't understand how the db size could increase 8.2GB in a week. The reason that I say in a week is because two weeks ago I tracked the size of the db file on a daily basis. The db was 39,403,651,072 or 39.4GB. I wasn't feeling well last week so I didn't track the database growth for a week. I starting tracking the databases growth again yesterday and noticed the 8.2GB difference from a week ago.
On another note, I do understand your suggestion(s) of shrinking the db or the db file. My issue though is how can a db grow 8.2GB when only 4MB of data is added. Remember last week the db size was unchanged with approx. 2MB of additional data while this week the file has grown 8.2GB with the same addition of 2MB.
The db contains 515 tables, but most of the tables are the daily tables which do not grow. Only the current day's daily tables grow and then are used for historical reference. The core of the db contains approx. 38 tables which are mostly static. If there is change in the core tables it is insignificate compared to the growth of the daily tables.
I know that we might not be able to figure out exactly what is happening. I am only trying to gain a better understanding of the way databases can grow. Perhaps each of the tables or a significate number of them outgrow their allotted space and increased their reserved space. This might account for the physical growth???
Going on the above assumption I reran the spaceused sp and did notice an increase in the reserved space for the tables of two weeks ago. My tracking only goes back two week, but if the increase is applied by 515 tables this may explain the anomaly.
Any thoughts?
My best guess is that you have autogrow enabled (I don't believe you
mentioned if you do or not), and adding the small tables has resulted in it
kicking in, and increasing the file size by whatever percentage or absolute
size is set. If the database size seems to fluctuate up and down, that could
be because you also have autoshrink enabled.
If these options are set (note that autogrow is per file, but autoshrink is
per database), then you could set up a server-side trace on the
autogrow/autoshrink events to get an idea of when and how often they occur.
There's more information on how the options work here: http://support.microsoft.com/default...b;en-us;315512
Simon This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: baustin75 |
last post by:
Posted: Mon Oct 03, 2005 1:41 pm Post subject: cannot mail() in ie
only when debugging in php designer 2005
--------------------------------------------------------------------------------
Hello,
I have a very simple problem but cannot seem to figure it out. I have a
very simple php script that sends a test email to myself. When I debug
it in PHP designer, it works with no problems, I get the test email. If
|
by: Soren Jorgensen |
last post by:
Hi,
Following code should give the number of weeks in years 1998-2010 for a
Danish calendar (on a Danish box)
GregorianCalendar cal = new GregorianCalendar();
for(int i = 1998; i < 2010; i++)
{
DateTime date = new DateTime(i, 12, 31);
int week= cal.GetWeekOfYear(date,
|
by: Rigs |
last post by:
Hi,
I have a textbox with a Custom Validator that utilizes the OnServerValidate
method for that textbox. This works fine, however the method only executes
when data exists in that textbox after the Submit button is clicked. If I
click the submit button and no data exists in the textbox, the
OnServerValidate method does not fire.
I'd like the OnServerValidate method to either execute every time the Submit
button is clicked. I am...
|
by: Pietje puk |
last post by:
Hello,
Since im quite new to ASP.NET i wanted to ask you folks what the best
way is to create a WebForm for modifying 1 field from a record. The
manipulation of this field can be done by using dropdown list.
Previously i've written a lot of these page's by not using the
webcontrols, but now i've reached this magically moment that i want to
try these features. :D
|
by: David |
last post by:
Hi,
I have an order processing system in which I have an OrderDate and a
Completion Date
I want to print the difference in Weeks and Days between the two dates,
i.e. if the completion date is 3 weeks and 2 days after the order date,
I want to print : 3 Weeks and 2 Days, on the page.
Is this possible ?
| |
by: Matt |
last post by:
Hi All,
I have the following situation:
I admin a metrics database that includes an attendance piece that
collects hours worked. From this data I show overtime as any amount
of hours over 40 for a particular week.
To do this I have created a table with 4 fields ... Date, week, month,
year. This way I can join on the Date field and then group on year,
week and month. The problem is I must enter data a few times a year
|
by: Libber39 |
last post by:
Hi everyone,
Have a query on how to calculate the amount of weeks and days contained in a number in an access query. ie: the difference in days between 2 dates amounts to 17 days. I want to now calculate in the query the amount of weeks and days within the 17 days to show 2 weeks 3 days.
I can create the function within excel, (A1 as cell with the days within it) as:
=INT(A1/7) & " Weeks, " & MOD(A1,7) & " days"
but cant seem to...
|
by: Bull |
last post by:
Hi,
Is there a code that converts a whole number to Years and Weeks only?
Thanks,
Bull
|
by: Tommy Jakobsen |
last post by:
Hi.
Is there a method in .NET that takes "year" as an argument and returns the total
number of weeks in that year? For culture da-DK (Danish).
Thanks in advance.
Tommy.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |