Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilsonWhat recovery model is the database in? the log is not truncated by a db bac
kup. So if full recovery
model, you either have to do regular transaction log backups or set the data
base to simple recovery
model.
Also, see for general considerations. http://www.karaszi.com/SQLServer/in...r />
hrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.ph
x.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
just[vbcol=seagreen]
the[vbcol=seagreen]
>sql

Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilsonWhat recovery model is the database in? the log is not truncated by a db backup. So if full recovery
model, you either have to do regular transaction log backups or set the database to simple recovery
model.
Also, see for general considerations. http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> > At one client site (running SQL Server 2000), I have a log file that
just
> > grows & grows. The maintenance plan performs a full backup nightly and
the
> > AutoShrink option is checked.
> >
> > I can run a script to forcibly shrink the thing, but the automated DB
> > maintenance should be handling this.
> >
> > What else can I check?
> >
> > Thanks.
> >
> > dwilson
> >
> >
>

Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilson
What recovery model is the database in? the log is not truncated by a db backup. So if full recovery
model, you either have to do regular transaction log backups or set the database to simple recovery
model.
Also, see for general considerations. http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>
|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>
|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
just[vbcol=seagreen]
the
>

Log full

Hi! Please help with the following:
Our disk space is limited. I set the database recovery model to
"FULL", and backup transaction log every hour between business hours.
But the disk is always full because the transactions log growth. Do
you think increase the frequency of transaction log backup or backup
transaction 24 hours instead of only business hours will solve the
problem?
Thanks!!"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0310270725.45bf4877@.posting.google.c om...
> Hi! Please help with the following:
> Our disk space is limited. I set the database recovery model to
> "FULL", and backup transaction log every hour between business hours.
> But the disk is always full because the transactions log growth. Do
> you think increase the frequency of transaction log backup or backup
> transaction 24 hours instead of only business hours will solve the
> problem?

I think you're approaching this from the wrong direction. Your transaction
log should be backed up as often as your business rules dictate and the
diskspace sized appropriately.

You say "between business hours" I assume you mean during? (that logically
makes more sense, but what you wrote could be read as from 5:00 PM to 9:00
AM instead when presumably your log isn't really growing.)

I'd say your best bet is to purchase more disk space.

You also mention the disk is always full. Generally your database files are
far larger than your transaction log files, especially if you are doing
transaction backups on a regular basis. In addition a transaction log back
up does NOT shrink the physical disk file, only removes unused portions. So
it might be that your transaction log is far larger than it should be (say
one day you skipped transaction log backups and it swelled to 8 times its
normal size, now it's normally 8x bigger than it needs to be.)

Hope that helps.

> Thanks!!|||"Saiyou Anh" <wangc@.alexian.net> wrote in message
news:a51d3ca7.0310270725.45bf4877@.posting.google.c om...
> Hi! Please help with the following:
> Our disk space is limited. I set the database recovery model to
> "FULL", and backup transaction log every hour between business hours.
> But the disk is always full because the transactions log growth. Do
> you think increase the frequency of transaction log backup or backup
> transaction 24 hours instead of only business hours will solve the
> problem?

I think you're approaching this from the wrong direction. Your transaction
log should be backed up as often as your business rules dictate and the
diskspace sized appropriately.

You say "between business hours" I assume you mean during? (that logically
makes more sense, but what you wrote could be read as from 5:00 PM to 9:00
AM instead when presumably your log isn't really growing.)

I'd say your best bet is to purchase more disk space.

You also mention the disk is always full. Generally your database files are
far larger than your transaction log files, especially if you are doing
transaction backups on a regular basis. In addition a transaction log back
up does NOT shrink the physical disk file, only removes unused portions. So
it might be that your transaction log is far larger than it should be (say
one day you skipped transaction log backups and it swelled to 8 times its
normal size, now it's normally 8x bigger than it needs to be.)

Hope that helps.

> Thanks!!

log files problem

Hi All,
I have problem with log files on my databases, they grow so much that I have
errors when I backup and restore the data.
The best way that I found to fix this is to detach the db, delete the log
file and attach the data again.
How can I do this programmatically?
Tks in advance
JohnnyThe best way to fix this is to manage the log properly so that it
doesn't grow. If you aren't performing Log backups then set Simple
Recovery mode. On an OLTP system you should not need to allow log files
to grow (disable autogrow) and you should avoid shrinking at all.
David Portas
SQL Server MVP
--|||You can truncate the log file to 1KB using the DBCC command , so do it in a
periodical basis
"JFB" wrote:

> Hi All,
> I have problem with log files on my databases, they grow so much that I ha
ve
> errors when I backup and restore the data.
> The best way that I found to fix this is to detach the db, delete the log
> file and attach the data again.
> How can I do this programmatically?
> Tks in advance
> Johnny
>
>|||Look at "recovering databases-SQL Server" in BOL.
If you don't need transaction logs, put the DB in simple recovery mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
> a
> periodical basis
> "JFB" wrote:
>|||Why? Periodically shrinking has divantages: http://www.karaszi.com/SQLServer/in...dont_shrink.asp
JFB's problem is probably that db is in full recovery mode and periodic log
backups are not
performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
a
> periodical basis
> "JFB" wrote:
>

Log Files - How To Clean Up

I had received a message that my log file is full and it do not enable to me to do a database backup before free up disk space. How do i clean up de log file (_log.ldf)?check 'dbcc shrinkfile' in BOL,

dbcc shrinkfile('db_log',size,truncateonly)|||Thanks, mallier, but my _log.ldf is on another disk driver under mssql\data folder. After executing the command you wrote, i got a message 'not found in the system files'|||run this query to get the log name in ur db


select *from sysfiles
-- u will get log name from name column with extension '_Log'

--replace that name in 'db_Log'|||ok, mallier, excuse me again, but the _log.ldf is 18GB great yet. Some system table was updated after the last command you wrote, but i need clean that _log.ldf to free up space in the disk drive.|||put the database in simple recoverry mode. Checkpoint the database. Backup the database. open QA. run "sp_helpdb (dbname). Find the file number for the log. execute dbcc shrinkfile(<file number for the log.).

Log Files

I have two questions regarding log files.
1) If I perform a full transaction log backup, does the file get reduced in
size?
2) Is it possible to have incomplete transctions in this file? If so, how
do I get rid of them?> 1) If I perform a full transaction log backup, does the file get reduced
in size?
No. It frees up space within the file, if possible. Partly, the reason is
that if the log file got that big once, it will get that big again. So,
rather than suffer the performance hit of shrinking the file just to grow it
again, it leaves it as is.
See DBCC SHRINKFILE

> 2) Is it possible to have incomplete transctions in this file?
Yes. Run DBCC OPENTRAN

> If so, how do I get rid of them?
Well, you can identify them by SPID, and then KILL them manually. But make
sure it is a transaction you can afford to kill.
A

Log Files

I have two questions regarding log files.
1) If I perform a full transaction log backup, does the file get reduced in size?
2) Is it possible to have incomplete transctions in this file? If so, how do I get rid of them?
> 1) If I perform a full transaction log backup, does the file get reduced
in size?
No. It frees up space within the file, if possible. Partly, the reason is
that if the log file got that big once, it will get that big again. So,
rather than suffer the performance hit of shrinking the file just to grow it
again, it leaves it as is.
See DBCC SHRINKFILE

> 2) Is it possible to have incomplete transctions in this file?
Yes. Run DBCC OPENTRAN

> If so, how do I get rid of them?
Well, you can identify them by SPID, and then KILL them manually. But make
sure it is a transaction you can afford to kill.
A

Log file won't truncate on backup (no open transaction)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running dbcc opentran in the problem database yields no open transactions
running
dbcc sqlperf(logspace)
shows log file over 95% full (1.5 gig )
Then doing a FULL database backup doesn't truncate the log (still 95%
full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
Problem just started in last couple of weeks. Running ok for over a year in
SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
backup.
I am fairly sure that dropping the database and recreating it from a backup
will probably fix this. I have done a restore to a new database and the log
is empty.
Any thoughts on how to fix without dropping and restoring the database?
Any thoughts on what may have caused this?
TIA,
MikeAre you doing transaction log backups? These should empty the log.
Christian Smith
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>|||As Christian indicate. Full (DB) backup does not empty the transaction log.
Only transaction log backup does. Perhaps the db was in simple recovery mode
and Tivoli (or someone else) did set it to full recovery mode). Also, before
you did your first db backup, the log is in "auto-truncate mode".
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>sql

Log file won't truncate on backup (no open transaction)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running dbcc opentran in the problem database yields no open transactions
running
dbcc sqlperf(logspace)
shows log file over 95% full (1.5 gig )
Then doing a FULL database backup doesn't truncate the log (still 95%
full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
Problem just started in last couple of weeks. Running ok for over a year in
SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
backup.
I am fairly sure that dropping the database and recreating it from a backup
will probably fix this. I have done a restore to a new database and the log
is empty.
Any thoughts on how to fix without dropping and restoring the database?
Any thoughts on what may have caused this?
TIA,
MikeAre you doing transaction log backups? These should empty the log.
Christian Smith
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>|||As Christian indicate. Full (DB) backup does not empty the transaction log.
Only transaction log backup does. Perhaps the db was in simple recovery mode
and Tivoli (or someone else) did set it to full recovery mode). Also, before
you did your first db backup, the log is in "auto-truncate mode".
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>

Log File Size Reducing

Hi Friends,
I am using SQL Server version 7 with Service Pack 4. We have a full database
backup job which is executed everyday at 12:00 noon. Now everyday manually
we goto to the Property Of that particular database to turn the Truncate Log
On Checkpoint Option TRUE.
Then, from the query analyzer we put CHECKPOINT. And then again we turn the
option FALSE. All this things are done before 12:00 noon, say around 11:55 a
m. Now suddenly we are noticing that the physical size of the Log File is ge
tting reduced. Say first d
ay it was 1024 MB, next day after the same operation it has become 512 mb...
The Auto Shrink option is not set, it is FALSE. Can anyone tell me why is th
is happening ? Actually I don't want the physical size of the log file to re
duce.
Any help in this regard is highly appreciated.
Dipankar.Dipankar Ganguly wrote:
> Hi Friends,
> I am using SQL Server version 7 with Service Pack 4. We have a full
> database backup job which is executed everyday at 12:00 noon. Now
> everyday manually we goto to the Property Of that particular database
> to turn the Truncate Log On Checkpoint Option TRUE. Then, from the
> query analyzer we put CHECKPOINT. And then again we turn the option
> FALSE. (...)
There are better ways to do this
Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the links. But please tell me why is this size reducing incident
happening ? Is there any other setting which is changed by someone and hence
it is behaving in that manner?|||I have had a lot of fun with this exact same problem. This happens when
someone does a DBCC SHRINKFILE(2, 512) on the log file. Once this has been
issued, it will always try to shrink to that size. There is no way of
turning this value off or back to the default of not shrinking. Even if you
manually grow the log file it will still shrink. A pain I know.
So the next question is how to get around it. If you want the log file to
be 1Gb, grow the log file to say 1.5GB, then shrink it to 1GB. Now it will
always shrink to 1Gb.
HTH
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dipankar Ganguly" <dipankarganguly@.hotmail.com> wrote in message
news:005FFB0F-7DA2-4C62-8D47-302601D7207A@.microsoft.com...
> Thanks for the links. But please tell me why is this size reducing
incident happening ? Is there any other setting which is changed by someone
and hence it is behaving in that manner?sql

log file size

Can anyone tell me in detail, what exactly log file stores. Even i shrik the
database log file does not srhink. Even after i backup,delete n restore db,
log file remain the same. How can i squueze the log file size?
And How large log file effetc the performance of db, quereis etc ?All modifications in a database are reflected in the transaction log file. S
ee
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for considerations and
how to shrink file
file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vikram" <aa@.aa> wrote in message news:uB9vy2afGHA.324@.TK2MSFTNGP02.phx.gbl...n">
> Can anyone tell me in detail, what exactly log file stores. Even i shrik t
he
> database log file does not srhink. Even after i backup,delete n restore db
,
> log file remain the same. How can i squueze the log file size?
> And How large log file effetc the performance of db, quereis etc ?
>

log file size

Can anyone tell me in detail, what exactly log file stores. Even i shrik the
database log file does not srhink. Even after i backup,delete n restore db,
log file remain the same. How can i squueze the log file size?
And How large log file effetc the performance of db, quereis etc ?All modifications in a database are reflected in the transaction log file. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for considerations and how to shrink file
file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vikram" <aa@.aa> wrote in message news:uB9vy2afGHA.324@.TK2MSFTNGP02.phx.gbl...
> Can anyone tell me in detail, what exactly log file stores. Even i shrik the
> database log file does not srhink. Even after i backup,delete n restore db,
> log file remain the same. How can i squueze the log file size?
> And How large log file effetc the performance of db, quereis etc ?
>

Log file size

I find that the following commands successfully decrease the size of the log
file:
- Backup Log MyDB With Truncate_Only
- DBCC SHRINKDATABASE (MyDB)
However the following commands (exec one after one) can't decrease the size
of the log file:
- DBCC SHRINKDATABASE (MyDb, TruncateOnly)
- DBCC SHRINKDATABASE (MyDB)
Can anybody explain why?
Thanks,
KMYou can use DBCC SHRINKFILE to shrink a log file. See the Books Online for
usage information.
Hope this helps.
Dan Guzman
SQL Server MVP
"krygim" <krygim@.hotmail.com> wrote in message
news:edS35FvIGHA.3056@.TK2MSFTNGP09.phx.gbl...
>I find that the following commands successfully decrease the size of the
>log
> file:
> - Backup Log MyDB With Truncate_Only
> - DBCC SHRINKDATABASE (MyDB)
> However the following commands (exec one after one) can't decrease the
> size
> of the log file:
> - DBCC SHRINKDATABASE (MyDb, TruncateOnly)
> - DBCC SHRINKDATABASE (MyDB)
> Can anybody explain why?
> Thanks,
> KM
>|||the TLog is made up internally of virtual log files. truncating the
log frees up the virtual files so shrinkdatabase/shrinkfile can take
back the space. if you run shrinkdatabase/shrinkfile without first
freeing up the virtual files, the space those virtual files take up
cannot be reallocated to the OS.
BOL has a GREAT explanation at
http://msdn.microsoft.com/library/d...r />
_1uzr.aspsql

LOG File Maintenance

Hello! Hope everybody is doing OK.
I have a quick question. I use to run the command below to keep the Log
File of my database small.
BACKUP LOG FlexSol WITH NO_LOG
DBCC SHRINKDATABASE (FlexSol, 10)
The question is, now that I setup replication in this database, would I be
able to run this command without affecting the way replication works, if so,
then what are the steps to perform maintenance in the log file.
Thank you in advance for any input.
Marlene A. Roman
if the log reader has not read and commited transactions in the tlog to the distribution database, you will not be able to truncate this portion of the log.
So, truncate away, as replication will not be affected.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||(...and if the replication type is merge or snapshot, this will not be affected either).
Regards,
Paul Ibison
|||Thank you! :P
|||Paul, you are starting to making me look bad, and I don't need your help for
that
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8EB60E9B-6EA6-48B3-A51E-8D60B858A60B@.microsoft.com...
> (...and if the replication type is merge or snapshot, this will not be
affected either).
> Regards,
> Paul Ibison

Log file is not truncated inspite of regular log backup

I am taking regular log backup (every 30 mins). But still
the log file size of one database is 1110MB even when no
users are connected.
While using dbcc shrinkfile, i am getting the following
msg:
Cannot shrink log file 2 (Gsap_Log) because all logical
log files are in use.
DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
Execution of the SP sp_repldone gives "The database is
not published."
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Can anyone help me in truncating my log file?
Regards,
MangaiCheck out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mangai" <mangaiyarkarasi_@.hotmail.com> wrote in message
news:007001c3af1e$a1c81570$a001280a@.phx.gbl...
> I am taking regular log backup (every 30 mins). But still
> the log file size of one database is 1110MB even when no
> users are connected.
> While using dbcc shrinkfile, i am getting the following
> msg:
> Cannot shrink log file 2 (Gsap_Log) because all logical
> log files are in use.
> DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> Execution of the SP sp_repldone gives "The database is
> not published."
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
>
> Can anyone help me in truncating my log file?
> Regards,
> Mangai
>|||Got the solution.
Just created a publication on the server for the database
and executed sp_repldone with null values. The log file
was truncated and I had shrinked the log file to 10 MB.
>--Original Message--
>I am taking regular log backup (every 30 mins). But
still
>the log file size of one database is 1110MB even when no
>users are connected.
>While using dbcc shrinkfile, i am getting the following
>msg:
>Cannot shrink log file 2 (Gsap_Log) because all logical
>log files are in use.
>DBCC OPENTRAN gives:
>Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
>Execution of the SP sp_repldone gives "The database is
>not published."
>EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
>@.numtrans = 0, @.time = 0, @.reset = 1
>
>Can anyone help me in truncating my log file?
>Regards,
>Mangai
>.
>

LOG file Growing, Similar Issue to a previous one

I have an 19 gig database that somehow has a 100gig log file. The DB MUST BE in full recovery mode, I backup the transaction logs EVERY hour and shrink nightly. but for some reason my logfile WILL NOT SHRINK.

HELP,

I've used both the DBCC Shrinkfile (xxxxxx) and DBCC ShrinkDatabase (xxxxx) and these don't seem to work. I Have No current backup, I have Not capacity for addtional 100 gig worth of backup drive or off-site tape.

I found a solution to my issue. Replication I have setup had a hung transaction.

log file for tempdb is full

I continue to get this message in event viewer for SQL Server 2000 "The log
file for database tempdb is full. Backup the transaction log for the
database to free up some log space."
How can I fix this problem?Check out this link
http://www.aspfaq.com/show.asp?id=2446
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthlink.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>|||This indicates that you need to increaes the log file for a tempdb. The size
required depends on your application's use of tempdb.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthlink.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>

log file for tempdb is full

I continue to get this message in event viewer for SQL Server 2000 "The log
file for database tempdb is full. Backup the transaction log for the
database to free up some log space."
How can I fix this problem?Check out this link
http://www.aspfaq.com/show.asp?id=2446
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthlink.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>|||This indicates that you need to increaes the log file for a tempdb. The size
required depends on your application's use of tempdb.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthlink.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>

log file for tempdb is full

I continue to get this message in event viewer for SQL Server 2000 "The log
file for database tempdb is full. Backup the transaction log for the
database to free up some log space."
How can I fix this problem?
Check out this link
http://www.aspfaq.com/show.asp?id=2446
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthli nk.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>
|||This indicates that you need to increaes the log file for a tempdb. The size
required depends on your application's use of tempdb.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:FDgue.7507$hK3.787@.newsread3.news.pas.earthli nk.net...
> I continue to get this message in event viewer for SQL Server 2000 "The
log
> file for database tempdb is full. Backup the transaction log for the
> database to free up some log space."
> How can I fix this problem?
>