Purge Transaction Log File

My database file is growing and growing and growing. This is good...
The bad news, at least for storage limitations, is the Transaction log file
is growing significantly faster. At the present time my data file is around
150 MB and the Transaction log file is just under 10GB......
Do I need to let the Transaction log file grow or is there a way to prevent
the file from getting to large? Any suggestions on solving this problem are
greatly appreciated.
WB
you have to back up the transaction log to keep it from growing for ever.
OR Put your Database in "Simple Recovery Mode"
Greg Jackson
PDX, OR
|||Ta add to Greg's response, the proper database recovery model depends on
your recovery requirements. If your plan is to simply restore from your
last backup, you should use the SIMPLE model. Committed data will be
removed from the log automatically to keep your log size reasonable.
If you need to further minimize potential data loss, you need to use the
FULL or BULK_LOGGED recovery model and schedule regular log backups between
database backups. The log backups will provide the means for forward
recovery following a database restore and also remove committed data from
the log.
You can read more about recovery models in the Books Online.
Hope this helps.
Dan Guzman
SQL Server MVP
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>
|||I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>
|||When you do a log backup, the log file(s) will be emptied. It is not normal to do BACKUP LOG WITH
TRUNCATE_ONLY in such scenario (this will break your chain of log backups!!!) nor to shrink file log
file (http://www.karaszi.com/SQLServer/info_dont_shrink.asp). You should investigate *why* the log
keep growing despite your regular log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>
|||Thank you for all the input. I was able to backup the log file and then
shrink it down to under 100MB. The problem now is I can't set the max file
size to say 2GB because the file size is still 9GB. How can I prevent the
file from growing beyond 2GB?
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
file
> is growing significantly faster. At the present time my data file is
around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
prevent
> the file from getting to large? Any suggestions on solving this problem
are
> greatly appreciated.
> WB
>
|||"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes
> larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
Why do you have Full recovery model if you're truncating the logs? It
doesn't make any sense. Put the recovery model to simple and you will gest
the same result, but the cheaper way.
Best regards
Wojtek
|||I don't understand. You say you shrunk it down to 100MB. Then you say the file is still 9GB?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> Thank you for all the input. I was able to backup the log file and then
> shrink it down to under 100MB. The problem now is I can't set the max file
> size to say 2GB because the file size is still 9GB. How can I prevent the
> file from growing beyond 2GB?
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> file
> around
> prevent
> are
>
|||Yes, There are two listings for the transaction file.
1. Current size = ~ 9GB
2. Space used = ~100MB
I am referencing the details listed under Shrink Database -> files
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> I don't understand. You say you shrunk it down to 100MB. Then you say the
file is still 9GB?[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
file[vbcol=seagreen]
the[vbcol=seagreen]
news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
problem[vbcol=seagreen]
|||I see. I don't use the EM GUI much, but I assume it means that the file size is 9GB and it is only
used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might require a few BACKUP,
SHRINK, BACKUO, SHRINK iteration where you monitor the status between using DBCC LOGINFO. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"WB" <none> wrote in message news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Yes, There are two listings for the transaction file.
> 1. Current size = ~ 9GB
> 2. Space used = ~100MB
> I am referencing the details listed under Shrink Database -> files
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> file is still 9GB?
> file
> the
> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> problem
>