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