Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KBTry running DBCC UPDATEUSAGE(0) and report back the space used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.12
9.250.171.65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:

> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,

Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB
Try running DBCC UPDATEUSAGE(0) and report back the space used.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.129.250.171. 65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:

> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,

Database growing unexpectedly

Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KBTry running DBCC UPDATEUSAGE(0) and report back the space used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns977AEC59B7F29gurbaohotmailcom@.129.250.171.65...
Hi all,
Running sql2000 ee SP4 on win2003.
I have a small database which havent been inserted/updated since it was
created 6 months ago. No DDL changes. This database was about 1,5 Mb for
the first months, but have started to grow. The size is now 30 Mb and it
is growing with about 2 Mb a month. It is set to autogrow with 10%.
Can someone explain this growth?
name rows reserved
form_lang 1 56 KB
grid_expr 0 0 KB
GridViewCategory 0 0 KB
GridViewDefinition 0 0 KB
hierarchy_tables 0 0 KB
invitation 0 0 KB
iteration 1 24 KB
lookup 0 0 KB
lookup_lang 0 0 KB
PanelistCredit 0 0 KB
quota 1 56 KB
response_control 2 104 KB
response1 0 0 KB
SampleLog 0 0 KB
weightmodel 0 0 KB
weightmodelform 0 0 KB|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:#noh#6kPGHA.1532
@.TK2MSFTNGP12.phx.gbl:
> Try running DBCC UPDATEUSAGE(0) and report back the space used.
>
Hi,
seems the first output from
USE MyDb; EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]'
was missing some tables...
I ran UpdateUsage and the sp_spaceused again and found the reason for the
growth.
Case closed :-)
Thanks,sql

Database Growing

I have a SQL Server that seems to be growing at rapid rates dialy. One day
it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb. I'm
afraid that by tomarrow the server will be out of space. What can explain
this sudden increase in space and how can I resolve this issue.
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:4F81DCAC-89B4-475D-B884-531431CC713E@.microsoft.com...
>I have a SQL Server that seems to be growing at rapid rates dialy. One day
> it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb.
> I'm
> afraid that by tomarrow the server will be out of space. What can explain
> this sudden increase in space and how can I resolve this issue.
Check out your transaction log. It may just need to be backed up.
Other culprits.. Find out if you have any runaway processing going on? Is
there bad logic in a while loop somewhere that inserts records?
Do you know which table(s) are the ones growing so quickly?
Rick Sawtell
MCT, MCSD, MCDBA
|||George Schneider wrote:
> I have a SQL Server that seems to be growing at rapid rates dialy.
> One day it had 18 GB free, the next it had 10gb, today its down to
> about 3.5 gb. I'm afraid that by tomarrow the server will be out of
> space. What can explain this sudden increase in space and how can I
> resolve this issue.
Do you do backups? If not, it may be your transaction log growing beyond
control.
robert
|||Hi
Pls read this article
http://www.sql-server-performance.co...e_settings.asp
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:4F81DCAC-89B4-475D-B884-531431CC713E@.microsoft.com...
>I have a SQL Server that seems to be growing at rapid rates dialy. One day
> it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb.
> I'm
> afraid that by tomarrow the server will be out of space. What can explain
> this sudden increase in space and how can I resolve this issue.
|||I'm not even sure if we keep tranaction logs. But no they are not part of
the database backups. Where would the tranaction logs be. How would
backuping up the tranaction logs resolve this? Could this be something the
maintenace plans relating to organization
"Robert Klemme" wrote:

> George Schneider wrote:
> Do you do backups? If not, it may be your transaction log growing beyond
> control.
> robert
>
|||George Schneider wrote:
> I'm not even sure if we keep tranaction logs.
? Your SQL Server database definitely has them. And you should not
manually remove them.

> But no they are not
> part of the database backups.
If you do a full backup, space in tx logs is marked free and then you can
shrink them.

> Where would the tranaction logs be.
Usually stored along with the data files but they can be everywhere you
put them.

> How would backuping up the tranaction logs resolve this? Could this
> be something the maintenace plans relating to organization
I suggest you read the details in BOL. You probably need to beef up your
understanding of how SQL Server works.
Kind regards
robert
|||You should determine if in fact it is the transaction log or the data that is
growing.
I'll assume you are in SQL Server 2000.
Open Enterprise Manager, right click each database that you are running on
this machine and select properties.
Under the database properties, you can view the side of the data file(s) and
the transaction log.
If it is in fact the transaction log that is growing so fast. The basic
strategy is to backup the transaction and the you can run dbcc shrinkfile on
it. I will post a link or 2 at the end of this post for more information.
On going you have several choices that are covered in the links I will
provide.
1) Change the database recovery model to simple. Not typically recommended
for production environments. This will solve your problem of a growing
transaction log, but will make your system more vulnerable to data loss.
Since when you have a failure, you will have to revert to your last backup.
Not usually okay for production.
2) Add transaction log backup and maintenance to your maintenance plans.
See attached links.
Recovery Models:
http://www.sqlskills.com/resources/S...HAChapter9.pdf
Reclaiming Space (and some other good links):
http://www.aspfaq.com/show.asp?id=2471
Transaction Log Backups
http://msdn.microsoft.com/library/de...kprst_565v.asp
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"George Schneider" wrote:
[vbcol=seagreen]
> I'm not even sure if we keep tranaction logs. But no they are not part of
> the database backups. Where would the tranaction logs be. How would
> backuping up the tranaction logs resolve this? Could this be something the
> maintenace plans relating to organization
> "Robert Klemme" wrote:
|||I ran a backup of the transaction log of one of the db that was getting out
of control and had it saved to a remote location. How will his cause the
size oft he log file to shrink? Everything is still the same size.
"Ryan Powers" wrote:
[vbcol=seagreen]
> You should determine if in fact it is the transaction log or the data that is
> growing.
> I'll assume you are in SQL Server 2000.
> Open Enterprise Manager, right click each database that you are running on
> this machine and select properties.
> Under the database properties, you can view the side of the data file(s) and
> the transaction log.
> If it is in fact the transaction log that is growing so fast. The basic
> strategy is to backup the transaction and the you can run dbcc shrinkfile on
> it. I will post a link or 2 at the end of this post for more information.
> On going you have several choices that are covered in the links I will
> provide.
> 1) Change the database recovery model to simple. Not typically recommended
> for production environments. This will solve your problem of a growing
> transaction log, but will make your system more vulnerable to data loss.
> Since when you have a failure, you will have to revert to your last backup.
> Not usually okay for production.
> 2) Add transaction log backup and maintenance to your maintenance plans.
> See attached links.
> Recovery Models:
> http://www.sqlskills.com/resources/S...HAChapter9.pdf
> Reclaiming Space (and some other good links):
> http://www.aspfaq.com/show.asp?id=2471
> Transaction Log Backups
> http://msdn.microsoft.com/library/de...kprst_565v.asp
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "George Schneider" wrote:
|||Transaction log backup doesn't shrink the size of the file, it only make the space therein available
for re-use. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for information on how to
shrink the file size.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:7888F7E0-4831-4820-B2B0-6B72B9D63C2C@.microsoft.com...[vbcol=seagreen]
>I ran a backup of the transaction log of one of the db that was getting out
> of control and had it saved to a remote location. How will his cause the
> size oft he log file to shrink? Everything is still the same size.
> "Ryan Powers" wrote:
|||George Schneider wrote:[vbcol=seagreen]
> I ran a backup of the transaction log of one of the db that was getting out
> of control and had it saved to a remote location. How will his cause the
> size oft he log file to shrink? Everything is still the same size.
> "Ryan Powers" wrote:
Now when you seem to have the logfile growth a bit more in control, you
should maybe look for the cause to why it suddenly grows. If the
database has been running for some time without problems and then
suddenly the log file grows out of control, it sounds like something is
not right. If you have some maintenance jobs running that e.g. reindex
the database and you've loaded a big amount of data, that could cause
your logfile to grow everytime you run the reindex job.
As one of the other posters mentioned, it could also be a script change
or application update that are causing the sudden growth.
The logfile growth and logfile size might not be a problem as such, but
it's a problem if you don't know why it grows and how to keep it in
control. Remember that backing up the log and shrinking the logfile
might not help a lot if the system really needs that size of logfile to
e.g. do a reindexing. That's also what you can read in Tibors article.
Regards
Steen

Database Growing

I have a SQL Server that seems to be growing at rapid rates dialy. One day
it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb. I'm
afraid that by tomarrow the server will be out of space. What can explain
this sudden increase in space and how can I resolve this issue."George Schneider" <georgedschneider@.news.postalias> wrote in message
news:4F81DCAC-89B4-475D-B884-531431CC713E@.microsoft.com...
>I have a SQL Server that seems to be growing at rapid rates dialy. One day
> it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb.
> I'm
> afraid that by tomarrow the server will be out of space. What can explain
> this sudden increase in space and how can I resolve this issue.
Check out your transaction log. It may just need to be backed up.
Other culprits.. Find out if you have any runaway processing going on? Is
there bad logic in a while loop somewhere that inserts records?
Do you know which table(s) are the ones growing so quickly?
Rick Sawtell
MCT, MCSD, MCDBA|||George Schneider wrote:
> I have a SQL Server that seems to be growing at rapid rates dialy.
> One day it had 18 GB free, the next it had 10gb, today its down to
> about 3.5 gb. I'm afraid that by tomarrow the server will be out of
> space. What can explain this sudden increase in space and how can I
> resolve this issue.
Do you do backups? If not, it may be your transaction log growing beyond
control.
robert|||Hi
Pls read this article
http://www.sql-server-performance.com/database_settings.asp
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:4F81DCAC-89B4-475D-B884-531431CC713E@.microsoft.com...
>I have a SQL Server that seems to be growing at rapid rates dialy. One day
> it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb.
> I'm
> afraid that by tomarrow the server will be out of space. What can explain
> this sudden increase in space and how can I resolve this issue.|||I'm not even sure if we keep tranaction logs. But no they are not part of
the database backups. Where would the tranaction logs be. How would
backuping up the tranaction logs resolve this? Could this be something the
maintenace plans relating to organization
"Robert Klemme" wrote:
> George Schneider wrote:
> > I have a SQL Server that seems to be growing at rapid rates dialy.
> > One day it had 18 GB free, the next it had 10gb, today its down to
> > about 3.5 gb. I'm afraid that by tomarrow the server will be out of
> > space. What can explain this sudden increase in space and how can I
> > resolve this issue.
> Do you do backups? If not, it may be your transaction log growing beyond
> control.
> robert
>|||George Schneider wrote:
> I'm not even sure if we keep tranaction logs.
? Your SQL Server database definitely has them. And you should not
manually remove them.
> But no they are not
> part of the database backups.
If you do a full backup, space in tx logs is marked free and then you can
shrink them.
> Where would the tranaction logs be.
Usually stored along with the data files but they can be everywhere you
put them.
> How would backuping up the tranaction logs resolve this? Could this
> be something the maintenace plans relating to organization
I suggest you read the details in BOL. You probably need to beef up your
understanding of how SQL Server works.
Kind regards
robert|||You should determine if in fact it is the transaction log or the data that is
growing.
I'll assume you are in SQL Server 2000.
Open Enterprise Manager, right click each database that you are running on
this machine and select properties.
Under the database properties, you can view the side of the data file(s) and
the transaction log.
If it is in fact the transaction log that is growing so fast. The basic
strategy is to backup the transaction and the you can run dbcc shrinkfile on
it. I will post a link or 2 at the end of this post for more information.
On going you have several choices that are covered in the links I will
provide.
1) Change the database recovery model to simple. Not typically recommended
for production environments. This will solve your problem of a growing
transaction log, but will make your system more vulnerable to data loss.
Since when you have a failure, you will have to revert to your last backup.
Not usually okay for production.
2) Add transaction log backup and maintenance to your maintenance plans.
See attached links.
Recovery Models:
http://www.sqlskills.com/resources/SQLServerHAChapter9.pdf
Reclaiming Space (and some other good links):
http://www.aspfaq.com/show.asp?id=2471
Transaction Log Backup
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"George Schneider" wrote:
> I'm not even sure if we keep tranaction logs. But no they are not part of
> the database backups. Where would the tranaction logs be. How would
> backuping up the tranaction logs resolve this? Could this be something the
> maintenace plans relating to organization
> "Robert Klemme" wrote:
> > George Schneider wrote:
> > > I have a SQL Server that seems to be growing at rapid rates dialy.
> > > One day it had 18 GB free, the next it had 10gb, today its down to
> > > about 3.5 gb. I'm afraid that by tomarrow the server will be out of
> > > space. What can explain this sudden increase in space and how can I
> > > resolve this issue.
> >
> > Do you do backups? If not, it may be your transaction log growing beyond
> > control.
> >
> > robert
> >
> >|||I ran a backup of the transaction log of one of the db that was getting out
of control and had it saved to a remote location. How will his cause the
size oft he log file to shrink? Everything is still the same size.
"Ryan Powers" wrote:
> You should determine if in fact it is the transaction log or the data that is
> growing.
> I'll assume you are in SQL Server 2000.
> Open Enterprise Manager, right click each database that you are running on
> this machine and select properties.
> Under the database properties, you can view the side of the data file(s) and
> the transaction log.
> If it is in fact the transaction log that is growing so fast. The basic
> strategy is to backup the transaction and the you can run dbcc shrinkfile on
> it. I will post a link or 2 at the end of this post for more information.
> On going you have several choices that are covered in the links I will
> provide.
> 1) Change the database recovery model to simple. Not typically recommended
> for production environments. This will solve your problem of a growing
> transaction log, but will make your system more vulnerable to data loss.
> Since when you have a failure, you will have to revert to your last backup.
> Not usually okay for production.
> 2) Add transaction log backup and maintenance to your maintenance plans.
> See attached links.
> Recovery Models:
> http://www.sqlskills.com/resources/SQLServerHAChapter9.pdf
> Reclaiming Space (and some other good links):
> http://www.aspfaq.com/show.asp?id=2471
> Transaction Log Backups
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "George Schneider" wrote:
> > I'm not even sure if we keep tranaction logs. But no they are not part of
> > the database backups. Where would the tranaction logs be. How would
> > backuping up the tranaction logs resolve this? Could this be something the
> > maintenace plans relating to organization
> >
> > "Robert Klemme" wrote:
> >
> > > George Schneider wrote:
> > > > I have a SQL Server that seems to be growing at rapid rates dialy.
> > > > One day it had 18 GB free, the next it had 10gb, today its down to
> > > > about 3.5 gb. I'm afraid that by tomarrow the server will be out of
> > > > space. What can explain this sudden increase in space and how can I
> > > > resolve this issue.
> > >
> > > Do you do backups? If not, it may be your transaction log growing beyond
> > > control.
> > >
> > > robert
> > >
> > >|||Transaction log backup doesn't shrink the size of the file, it only make the space therein available
for re-use. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for information on how to
shrink the file size.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:7888F7E0-4831-4820-B2B0-6B72B9D63C2C@.microsoft.com...
>I ran a backup of the transaction log of one of the db that was getting out
> of control and had it saved to a remote location. How will his cause the
> size oft he log file to shrink? Everything is still the same size.
> "Ryan Powers" wrote:
>> You should determine if in fact it is the transaction log or the data that is
>> growing.
>> I'll assume you are in SQL Server 2000.
>> Open Enterprise Manager, right click each database that you are running on
>> this machine and select properties.
>> Under the database properties, you can view the side of the data file(s) and
>> the transaction log.
>> If it is in fact the transaction log that is growing so fast. The basic
>> strategy is to backup the transaction and the you can run dbcc shrinkfile on
>> it. I will post a link or 2 at the end of this post for more information.
>> On going you have several choices that are covered in the links I will
>> provide.
>> 1) Change the database recovery model to simple. Not typically recommended
>> for production environments. This will solve your problem of a growing
>> transaction log, but will make your system more vulnerable to data loss.
>> Since when you have a failure, you will have to revert to your last backup.
>> Not usually okay for production.
>> 2) Add transaction log backup and maintenance to your maintenance plans.
>> See attached links.
>> Recovery Models:
>> http://www.sqlskills.com/resources/SQLServerHAChapter9.pdf
>> Reclaiming Space (and some other good links):
>> http://www.aspfaq.com/show.asp?id=2471
>> Transaction Log Backups
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
>> HTH
>> --
>> Ryan Powers
>> Clarity Consulting
>> http://www.claritycon.com
>>
>> "George Schneider" wrote:
>> > I'm not even sure if we keep tranaction logs. But no they are not part of
>> > the database backups. Where would the tranaction logs be. How would
>> > backuping up the tranaction logs resolve this? Could this be something the
>> > maintenace plans relating to organization
>> >
>> > "Robert Klemme" wrote:
>> >
>> > > George Schneider wrote:
>> > > > I have a SQL Server that seems to be growing at rapid rates dialy.
>> > > > One day it had 18 GB free, the next it had 10gb, today its down to
>> > > > about 3.5 gb. I'm afraid that by tomarrow the server will be out of
>> > > > space. What can explain this sudden increase in space and how can I
>> > > > resolve this issue.
>> > >
>> > > Do you do backups? If not, it may be your transaction log growing beyond
>> > > control.
>> > >
>> > > robert
>> > >
>> > >|||George Schneider wrote:
> I ran a backup of the transaction log of one of the db that was getting out
> of control and had it saved to a remote location. How will his cause the
> size oft he log file to shrink? Everything is still the same size.
> "Ryan Powers" wrote:
>> You should determine if in fact it is the transaction log or the data that is
>> growing.
>> I'll assume you are in SQL Server 2000.
>> Open Enterprise Manager, right click each database that you are running on
>> this machine and select properties.
>> Under the database properties, you can view the side of the data file(s) and
>> the transaction log.
>> If it is in fact the transaction log that is growing so fast. The basic
>> strategy is to backup the transaction and the you can run dbcc shrinkfile on
>> it. I will post a link or 2 at the end of this post for more information.
>> On going you have several choices that are covered in the links I will
>> provide.
>> 1) Change the database recovery model to simple. Not typically recommended
>> for production environments. This will solve your problem of a growing
>> transaction log, but will make your system more vulnerable to data loss.
>> Since when you have a failure, you will have to revert to your last backup.
>> Not usually okay for production.
>> 2) Add transaction log backup and maintenance to your maintenance plans.
>> See attached links.
>> Recovery Models:
>> http://www.sqlskills.com/resources/SQLServerHAChapter9.pdf
>> Reclaiming Space (and some other good links):
>> http://www.aspfaq.com/show.asp?id=2471
>> Transaction Log Backups
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
>> HTH
>> --
>> Ryan Powers
>> Clarity Consulting
>> http://www.claritycon.com
>>
>> "George Schneider" wrote:
>> I'm not even sure if we keep tranaction logs. But no they are not part of
>> the database backups. Where would the tranaction logs be. How would
>> backuping up the tranaction logs resolve this? Could this be something the
>> maintenace plans relating to organization
>> "Robert Klemme" wrote:
>> George Schneider wrote:
>> I have a SQL Server that seems to be growing at rapid rates dialy.
>> One day it had 18 GB free, the next it had 10gb, today its down to
>> about 3.5 gb. I'm afraid that by tomarrow the server will be out of
>> space. What can explain this sudden increase in space and how can I
>> resolve this issue.
>> Do you do backups? If not, it may be your transaction log growing beyond
>> control.
>> robert
>>
Now when you seem to have the logfile growth a bit more in control, you
should maybe look for the cause to why it suddenly grows. If the
database has been running for some time without problems and then
suddenly the log file grows out of control, it sounds like something is
not right. If you have some maintenance jobs running that e.g. reindex
the database and you've loaded a big amount of data, that could cause
your logfile to grow everytime you run the reindex job.
As one of the other posters mentioned, it could also be a script change
or application update that are causing the sudden growth.
The logfile growth and logfile size might not be a problem as such, but
it's a problem if you don't know why it grows and how to keep it in
control. Remember that backing up the log and shrinking the logfile
might not help a lot if the system really needs that size of logfile to
e.g. do a reindexing. That's also what you can read in Tibors article.
Regards
Steen

Database Growing

I have a SQL Server that seems to be growing at rapid rates dialy. One day
it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb. I'
m
afraid that by tomarrow the server will be out of space. What can explain
this sudden increase in space and how can I resolve this issue."George Schneider" <georgedschneider@.news.postalias> wrote in message
news:4F81DCAC-89B4-475D-B884-531431CC713E@.microsoft.com...
>I have a SQL Server that seems to be growing at rapid rates dialy. One day
> it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb.
> I'm
> afraid that by tomarrow the server will be out of space. What can explain
> this sudden increase in space and how can I resolve this issue.
Check out your transaction log. It may just need to be backed up.
Other culprits.. Find out if you have any runaway processing going on? Is
there bad logic in a while loop somewhere that inserts records?
Do you know which table(s) are the ones growing so quickly?
Rick Sawtell
MCT, MCSD, MCDBA|||George Schneider wrote:
> I have a SQL Server that seems to be growing at rapid rates dialy.
> One day it had 18 GB free, the next it had 10gb, today its down to
> about 3.5 gb. I'm afraid that by tomarrow the server will be out of
> space. What can explain this sudden increase in space and how can I
> resolve this issue.
Do you do backups? If not, it may be your transaction log growing beyond
control.
robert|||Hi
Pls read this article
http://www.sql-server-performance.c...se_settings.asp
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:4F81DCAC-89B4-475D-B884-531431CC713E@.microsoft.com...
>I have a SQL Server that seems to be growing at rapid rates dialy. One day
> it had 18 GB free, the next it had 10gb, today its down to about 3.5 gb.
> I'm
> afraid that by tomarrow the server will be out of space. What can explain
> this sudden increase in space and how can I resolve this issue.|||I'm not even sure if we keep tranaction logs. But no they are not part of
the database backups. Where would the tranaction logs be. How would
backuping up the tranaction logs resolve this? Could this be something the
maintenace plans relating to organization
"Robert Klemme" wrote:

> George Schneider wrote:
> Do you do backups? If not, it may be your transaction log growing beyond
> control.
> robert
>|||George Schneider wrote:
> I'm not even sure if we keep tranaction logs.
? Your SQL Server database definitely has them. And you should not
manually remove them.

> But no they are not
> part of the database backups.
If you do a full backup, space in tx logs is marked free and then you can
shrink them.

> Where would the tranaction logs be.
Usually stored along with the data files but they can be everywhere you
put them.

> How would backuping up the tranaction logs resolve this? Could this
> be something the maintenace plans relating to organization
I suggest you read the details in BOL. You probably need to beef up your
understanding of how SQL Server works.
Kind regards
robert|||You should determine if in fact it is the transaction log or the data that i
s
growing.
I'll assume you are in SQL Server 2000.
Open Enterprise Manager, right click each database that you are running on
this machine and select properties.
Under the database properties, you can view the side of the data file(s) and
the transaction log.
If it is in fact the transaction log that is growing so fast. The basic
strategy is to backup the transaction and the you can run dbcc shrinkfile on
it. I will post a link or 2 at the end of this post for more information.
On going you have several choices that are covered in the links I will
provide.
1) Change the database recovery model to simple. Not typically recommended
for production environments. This will solve your problem of a growing
transaction log, but will make your system more vulnerable to data loss.
Since when you have a failure, you will have to revert to your last backup.
Not usually okay for production.
2) Add transaction log backup and maintenance to your maintenance plans.
See attached links.
Recovery Models:
http://www.sqlskills.com/resources/...rHAChapter9.pdf
Reclaiming Space (and some other good links):
http://www.aspfaq.com/show.asp?id=2471
Transaction Log Backups
http://msdn.microsoft.com/library/d... />
t_565v.asp
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"George Schneider" wrote:
[vbcol=seagreen]
> I'm not even sure if we keep tranaction logs. But no they are not part of
> the database backups. Where would the tranaction logs be. How would
> backuping up the tranaction logs resolve this? Could this be something th
e
> maintenace plans relating to organization
> "Robert Klemme" wrote:
>|||I ran a backup of the transaction log of one of the db that was getting out
of control and had it saved to a remote location. How will his cause the
size oft he log file to shrink? Everything is still the same size.
"Ryan Powers" wrote:
[vbcol=seagreen]
> You should determine if in fact it is the transaction log or the data that
is
> growing.
> I'll assume you are in SQL Server 2000.
> Open Enterprise Manager, right click each database that you are running on
> this machine and select properties.
> Under the database properties, you can view the side of the data file(s) a
nd
> the transaction log.
> If it is in fact the transaction log that is growing so fast. The basic
> strategy is to backup the transaction and the you can run dbcc shrinkfile
on
> it. I will post a link or 2 at the end of this post for more information.
> On going you have several choices that are covered in the links I will
> provide.
> 1) Change the database recovery model to simple. Not typically recommende
d
> for production environments. This will solve your problem of a growing
> transaction log, but will make your system more vulnerable to data loss.
> Since when you have a failure, you will have to revert to your last backup
.
> Not usually okay for production.
> 2) Add transaction log backup and maintenance to your maintenance plans.
> See attached links.
> Recovery Models:
> http://www.sqlskills.com/resources/...rHAChapter9.pdf
> Reclaiming Space (and some other good links):
> http://www.aspfaq.com/show.asp?id=2471
> Transaction Log Backups
> http://msdn.microsoft.com/library/d...>
rst_565v.asp
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "George Schneider" wrote:
>|||Transaction log backup doesn't shrink the size of the file, it only make the
space therein available
for re-use. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for in
formation on how to
shrink the file size.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:7888F7E0-4831-4820-B2B0-6B72B9D63C2C@.microsoft.com...[vbcol=seagreen]
>I ran a backup of the transaction log of one of the db that was getting out
> of control and had it saved to a remote location. How will his cause the
> size oft he log file to shrink? Everything is still the same size.
> "Ryan Powers" wrote:
>|||George Schneider wrote:[vbcol=seagreen]
> I ran a backup of the transaction log of one of the db that was getting ou
t
> of control and had it saved to a remote location. How will his cause the
> size oft he log file to shrink? Everything is still the same size.
> "Ryan Powers" wrote:
>
Now when you seem to have the logfile growth a bit more in control, you
should maybe look for the cause to why it suddenly grows. If the
database has been running for some time without problems and then
suddenly the log file grows out of control, it sounds like something is
not right. If you have some maintenance jobs running that e.g. reindex
the database and you've loaded a big amount of data, that could cause
your logfile to grow everytime you run the reindex job.
As one of the other posters mentioned, it could also be a script change
or application update that are causing the sudden growth.
The logfile growth and logfile size might not be a problem as such, but
it's a problem if you don't know why it grows and how to keep it in
control. Remember that backing up the log and shrinking the logfile
might not help a lot if the system really needs that size of logfile to
e.g. do a reindexing. That's also what you can read in Tibors article.
Regards
Steen

Database Design Assistance

I have 2 tables that will be created from flat files and recreated on a
daily basis with 300,000+ records and growing daily (tables will be
truncated and re-loaded via a batch process because we do not "own" the
data). I would like to extract the address information from these
tables and create a simple database design that will be relational and
efficient (there are additional Customer and Account fields but these
are the fields currently of interest).
Customer [table name]
TaxID [PK]
Addr1
Addr2
City
State
Zip
Country
MAddr1
MAddr2
MCity
MState
MZip
MCountry
Account [table name]
AccountNumber [PK]
TaxID
SecTaxID
Addr1
Addr2
City
State
Zip
Country
Every Customer in Customer table will have 2 addresses per TaxID,
Mailing and Physical even if they are the same. Every AccountNumber
will have exactly 1 address (Mailing) associated with it. So, if you
are a customer with 2 accounts, you would have 1 record in the customer
table (with both address and Mailing address populated) and 2 records
in the Account table. All 4 addresses could be the same or unique.
Mailings will EITHER go out at the Account level OR the Customer level
so I'm not sure if it is beneficial to combine the addresses into one
table or just create some sort of Account address table and another
customer address table but then i get one-to-one relationships and I'm
not sure how beneficial that is. This is how the data will be received
from the vendor and there isn't any leeway there. Any assistance that
could be provided would be greatly appreciated.Hi
If you do not store all the addresses as received you will probably end up
with a data cleansing excercise to make sure that you are not duplicating th
e
same address that is input slightly differently. Moving the address out to
another table will be a good idea if you have occassions to retrieve the
customer/account information without the address details. I would start with
a normalised design and if performance is being severely compromised you can
then de-normalise.
John
"jasonl22@.yahoo.com" wrote:

> I have 2 tables that will be created from flat files and recreated on a
> daily basis with 300,000+ records and growing daily (tables will be
> truncated and re-loaded via a batch process because we do not "own" the
> data). I would like to extract the address information from these
> tables and create a simple database design that will be relational and
> efficient (there are additional Customer and Account fields but these
> are the fields currently of interest).
> Customer [table name]
> TaxID [PK]
> Addr1
> Addr2
> City
> State
> Zip
> Country
> MAddr1
> MAddr2
> MCity
> MState
> MZip
> MCountry
> Account [table name]
> AccountNumber [PK]
> TaxID
> SecTaxID
> Addr1
> Addr2
> City
> State
> Zip
> Country
> Every Customer in Customer table will have 2 addresses per TaxID,
> Mailing and Physical even if they are the same. Every AccountNumber
> will have exactly 1 address (Mailing) associated with it. So, if you
> are a customer with 2 accounts, you would have 1 record in the customer
> table (with both address and Mailing address populated) and 2 records
> in the Account table. All 4 addresses could be the same or unique.
> Mailings will EITHER go out at the Account level OR the Customer level
> so I'm not sure if it is beneficial to combine the addresses into one
> table or just create some sort of Account address table and another
> customer address table but then i get one-to-one relationships and I'm
> not sure how beneficial that is. This is how the data will be received
> from the vendor and there isn't any leeway there. Any assistance that
> could be provided would be greatly appreciated.
>|||so you import it exactly as it comes from your source. that is life,
and how it is done. .
from there, you have an account table, that ONLY has accountnumber,
taxid, and maybe sectaxid depending on what the heck sectaxid is.
you will have an "address table." it will have primary key of
Taxid,Addresstype
AddressType
TaxID
Addr1
Addr2
City
State
Zip
You will have an addresstype table.
it will have
addresstype (M or P)
AddressDescr (Either "Mailing or Physical)|||Jason,
I'd go for something like
Customer [table name]
TaxID [PK]
Physical_address_id
-- both could point to the same row
mailing_address_id
SInce one and the same addres can be both physical and mailing, I'd
rather not have address_type column in address table|||my answer was more normalized......

Database Design Assistance

I have 2 tables that will be created from flat files and recreated on a
daily basis with 300,000+ records and growing daily (tables will be
truncated and re-loaded via a batch process because we do not "own" the
data). I would like to extract the address information from these
tables and create a simple database design that will be relational and
efficient (there are additional Customer and Account fields but these
are the fields currently of interest).
Customer [table name]
TaxID [PK]
Addr1
Addr2
City
State
Zip
Country
MAddr1
MAddr2
MCity
MState
MZip
MCountry
Account [table name]
AccountNumber [PK]
TaxID
SecTaxID
Addr1
Addr2
City
State
Zip
Country
Every Customer in Customer table will have 2 addresses per TaxID,
Mailing and Physical even if they are the same. Every AccountNumber
will have exactly 1 address (Mailing) associated with it. So, if you
are a customer with 2 accounts, you would have 1 record in the customer
table (with both address and Mailing address populated) and 2 records
in the Account table. All 4 addresses could be the same or unique.
Mailings will EITHER go out at the Account level OR the Customer level
so I'm not sure if it is beneficial to combine the addresses into one
table or just create some sort of Account address table and another
customer address table but then i get one-to-one relationships and I'm
not sure how beneficial that is. This is how the data will be received
from the vendor and there isn't any leeway there. Any assistance that
could be provided would be greatly appreciated.Hi
If you do not store all the addresses as received you will probably end up
with a data cleansing excercise to make sure that you are not duplicating the
same address that is input slightly differently. Moving the address out to
another table will be a good idea if you have occassions to retrieve the
customer/account information without the address details. I would start with
a normalised design and if performance is being severely compromised you can
then de-normalise.
John
"jasonl22@.yahoo.com" wrote:
> I have 2 tables that will be created from flat files and recreated on a
> daily basis with 300,000+ records and growing daily (tables will be
> truncated and re-loaded via a batch process because we do not "own" the
> data). I would like to extract the address information from these
> tables and create a simple database design that will be relational and
> efficient (there are additional Customer and Account fields but these
> are the fields currently of interest).
> Customer [table name]
> TaxID [PK]
> Addr1
> Addr2
> City
> State
> Zip
> Country
> MAddr1
> MAddr2
> MCity
> MState
> MZip
> MCountry
> Account [table name]
> AccountNumber [PK]
> TaxID
> SecTaxID
> Addr1
> Addr2
> City
> State
> Zip
> Country
> Every Customer in Customer table will have 2 addresses per TaxID,
> Mailing and Physical even if they are the same. Every AccountNumber
> will have exactly 1 address (Mailing) associated with it. So, if you
> are a customer with 2 accounts, you would have 1 record in the customer
> table (with both address and Mailing address populated) and 2 records
> in the Account table. All 4 addresses could be the same or unique.
> Mailings will EITHER go out at the Account level OR the Customer level
> so I'm not sure if it is beneficial to combine the addresses into one
> table or just create some sort of Account address table and another
> customer address table but then i get one-to-one relationships and I'm
> not sure how beneficial that is. This is how the data will be received
> from the vendor and there isn't any leeway there. Any assistance that
> could be provided would be greatly appreciated.
>|||so you import it exactly as it comes from your source. that is life,
and how it is done. .
from there, you have an account table, that ONLY has accountnumber,
taxid, and maybe sectaxid depending on what the heck sectaxid is.
you will have an "address table." it will have primary key of
Taxid,Addresstype
AddressType
TaxID
Addr1
Addr2
City
State
Zip
You will have an addresstype table.
it will have
addresstype (M or P)
AddressDescr (Either "Mailing or Physical)|||Jason,
I'd go for something like
Customer [table name]
TaxID [PK]
Physical_address_id
-- both could point to the same row
mailing_address_id
SInce one and the same addres can be both physical and mailing, I'd
rather not have address_type column in address table|||my answer was more normalized......