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 Import Error

I have a data warehouse that imports all of the required profile,
transactions, logs, etc. on a daily basis and has been running fine. The
other night, I started to receive the following errors during the web log
import.
My package logging shows:
Step 'DTSStep_CS_DTSLogImps.DTSLogImport_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:700
Step Execution Started: 9/19/2005 6:38:35 PM
Step Execution Completed: 9/19/2005 6:38:44 PM
Total Step Execution Time: 8.532 seconds
Progress count in Step: 0
The SQL job logging shows:
DTSRun OnError: DTSStep_CS_DTSLogImps.DTSLogImport_1, Error = -2147220421
(8004043B)
Error string: The task reported failure on execution.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
Error Detail Records:
Error: -2147220421 (8004043B); Provider Error: 0 (0)
Error string: The task reported failure on execution.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
The application event viewer has the following:
Event Type: Error
Event Source: CSDataWareHouse
Event Category: None
Event ID: 49176
Date: 9/19/2005
Time: 8:14:49 PM
User: N/A
Computer: INCDCPRDWSQL02
Description:
Log Import Task :Failed to import logs for site : StaplesCV
Event Type: Error
Event Source: Commerce Server 2002
Event Category: None
Event ID: 33123
Date: 9/19/2005
Time: 8:14:49 PM
User: N/A
Computer: INCDCPRDWSQL02
Description:
Log Import Task did not import any hits
Event Type: Error
Event Source: Commerce Server 2002
Event Category: None
Event ID: 33122
Date: 9/19/2005
Time: 8:14:49 PM
User: N/A
Computer: INCDCPRDWSQL02
Description:
Error launching parser. status code : -2147467259 (-2147467259)
I have searched but can not find anything related to an import process that
was functioning previously. Has anyone run into this problem before? Thank
s
- RichHello,
I replied you in microsoft.public.sqlserver.dts newsgroup. You may want to
go there for follow up.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Log Import Error
| thread-index: AcW+Bx7V6bTvIaN9TKyz1uqn7me4Ew==
| X-WBNR-Posting-Host: 66.181.92.2
| From: "examnotes" <rlanoue@.community.nospam>
| Subject: Log Import Error
| Date: Tue, 20 Sep 2005 10:17:04 -0700
| Lines: 70
| Message-ID: <6463656E-0E6C-4E5A-B329-985C948DB90B@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:2193
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| I have a data warehouse that imports all of the required profile,
| transactions, logs, etc. on a daily basis and has been running fine. The
| other night, I started to receive the following errors during the web log
| import.
|
| My package logging shows:
| Step 'DTSStep_CS_DTSLogImps.DTSLogImport_1' failed
| Step Error Source: Microsoft Data Transformation Services (DTS) Package
| Step Error Description:The task reported failure on execution.
| Step Error code: 8004043B
| Step Error Help File:sqldts80.hlp
| Step Error Help Context ID:700
| Step Execution Started: 9/19/2005 6:38:35 PM
| Step Execution Completed: 9/19/2005 6:38:44 PM
| Total Step Execution Time: 8.532 seconds
| Progress count in Step: 0
|
| The SQL job logging shows:
| DTSRun OnError: DTSStep_CS_DTSLogImps.DTSLogImport_1, Error =
-2147220421
| (8004043B)
| Error string: The task reported failure on execution.
| Error source: Microsoft Data Transformation Services (DTS) Package
| Help file: sqldts80.hlp
| Help context: 700
| Error Detail Records:
| Error: -2147220421 (8004043B); Provider Error: 0 (0)
| Error string: The task reported failure on execution.
| Error source: Microsoft Data Transformation Services (DTS) Package
| Help file: sqldts80.hlp
| Help context: 700
|
| The application event viewer has the following:
| Event Type: Error
| Event Source: CSDataWareHouse
| Event Category: None
| Event ID: 49176
| Date: 9/19/2005
| Time: 8:14:49 PM
| User: N/A
| Computer: INCDCPRDWSQL02
| Description:
| Log Import Task :Failed to import logs for site : StaplesCV
|
| Event Type: Error
| Event Source: Commerce Server 2002
| Event Category: None
| Event ID: 33123
| Date: 9/19/2005
| Time: 8:14:49 PM
| User: N/A
| Computer: INCDCPRDWSQL02
| Description:
| Log Import Task did not import any hits
|
| Event Type: Error
| Event Source: Commerce Server 2002
| Event Category: None
| Event ID: 33122
| Date: 9/19/2005
| Time: 8:14:49 PM
| User: N/A
| Computer: INCDCPRDWSQL02
| Description:
| Error launching parser. status code : -2147467259 (-2147467259)
|
| I have searched but can not find anything related to an import process
that
| was functioning previously. Has anyone run into this problem before?
Thanks
|
| - Rich
|
|

Log Import Error

I have a data warehouse that imports all of the required profile,
transactions, logs, etc. on a daily basis and has been running fine. The
other night, I started to receive the following errors during the web log
import.
My package logging shows:
Step 'DTSStep_CS_DTSLogImps.DTSLogImport_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:700
Step Execution Started: 9/19/2005 6:38:35 PM
Step Execution Completed: 9/19/2005 6:38:44 PM
Total Step Execution Time: 8.532 seconds
Progress count in Step: 0
The SQL job logging shows:
DTSRun OnError: DTSStep_CS_DTSLogImps.DTSLogImport_1, Error = -2147220421
(8004043B)
Error string: The task reported failure on execution.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
Error Detail Records:
Error: -2147220421 (8004043B); Provider Error: 0 (0)
Error string: The task reported failure on execution.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
The application event viewer has the following:
Event Type:Error
Event Source:CSDataWareHouse
Event Category:None
Event ID:49176
Date:9/19/2005
Time:8:14:49 PM
User:N/A
Computer:INCDCPRDWSQL02
Description:
Log Import Task :Failed to import logs for site : StaplesCV
Event Type:Error
Event Source:Commerce Server 2002
Event Category:None
Event ID:33123
Date:9/19/2005
Time:8:14:49 PM
User:N/A
Computer:INCDCPRDWSQL02
Description:
Log Import Task did not import any hits
Event Type:Error
Event Source:Commerce Server 2002
Event Category:None
Event ID:33122
Date:9/19/2005
Time:8:14:49 PM
User:N/A
Computer:INCDCPRDWSQL02
Description:
Error launching parser. status code : -2147467259 (-2147467259)
I have searched but can not find anything related to an import process that
was functioning previously. Has anyone run into this problem before? Thanks
- Rich
Hello,
I replied you in microsoft.public.sqlserver.dts newsgroup. You may want to
go there for follow up.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Log Import Error
| thread-index: AcW+Bx7V6bTvIaN9TKyz1uqn7me4Ew==
| X-WBNR-Posting-Host: 66.181.92.2
| From: "=?Utf-8?B?Ukxhbm91ZQ==?=" <rlanoue@.community.nospam>
| Subject: Log Import Error
| Date: Tue, 20 Sep 2005 10:17:04 -0700
| Lines: 70
| Message-ID: <6463656E-0E6C-4E5A-B329-985C948DB90B@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:2193
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| I have a data warehouse that imports all of the required profile,
| transactions, logs, etc. on a daily basis and has been running fine. The
| other night, I started to receive the following errors during the web log
| import.
|
| My package logging shows:
| Step 'DTSStep_CS_DTSLogImps.DTSLogImport_1' failed
| Step Error Source: Microsoft Data Transformation Services (DTS) Package
| Step Error Description:The task reported failure on execution.
| Step Error code: 8004043B
| Step Error Help File:sqldts80.hlp
| Step Error Help Context ID:700
| Step Execution Started: 9/19/2005 6:38:35 PM
| Step Execution Completed: 9/19/2005 6:38:44 PM
| Total Step Execution Time: 8.532 seconds
| Progress count in Step: 0
|
| The SQL job logging shows:
| DTSRun OnError: DTSStep_CS_DTSLogImps.DTSLogImport_1, Error =
-2147220421
| (8004043B)
| Error string: The task reported failure on execution.
| Error source: Microsoft Data Transformation Services (DTS) Package
| Help file: sqldts80.hlp
| Help context: 700
| Error Detail Records:
| Error: -2147220421 (8004043B); Provider Error: 0 (0)
| Error string: The task reported failure on execution.
| Error source: Microsoft Data Transformation Services (DTS) Package
| Help file: sqldts80.hlp
| Help context: 700
|
| The application event viewer has the following:
| Event Type:Error
| Event Source:CSDataWareHouse
| Event Category:None
| Event ID:49176
| Date:9/19/2005
| Time:8:14:49 PM
| User:N/A
| Computer:INCDCPRDWSQL02
| Description:
| Log Import Task :Failed to import logs for site : StaplesCV
|
| Event Type:Error
| Event Source:Commerce Server 2002
| Event Category:None
| Event ID:33123
| Date:9/19/2005
| Time:8:14:49 PM
| User:N/A
| Computer:INCDCPRDWSQL02
| Description:
| Log Import Task did not import any hits
|
| Event Type:Error
| Event Source:Commerce Server 2002
| Event Category:None
| Event ID:33122
| Date:9/19/2005
| Time:8:14:49 PM
| User:N/A
| Computer:INCDCPRDWSQL02
| Description:
| Error launching parser. status code : -2147467259 (-2147467259)
|
| I have searched but can not find anything related to an import process
that
| was functioning previously. Has anyone run into this problem before?
Thanks
|
| - Rich
|
|

Log Files!?!?

Hi,

I'm running a SQL Server used only for development and testing. Because of this, a lot of DELETE commands (and other "logable" operations) are issued.

Now, I DON'T WANT to use any logging at all on this server because I don't see the use for it and it's taking too much space on my hard drive.

How can I remove the log files and stop SQL Server from using them? Plus, maybe for some reason that I don't understand, this is not such a good idea. If so, can you please tell me why?

Thanks,

Skip.I'm not aware of a db that is operating normally without a log.

first of all, delete command is logged. If you look for non-logged operations, please check BOL for "bulk insert", "select into", "truncate table" etc. They will not cause the log to grow.

But a lot of times, you have to use "DELETE". you can turn the database into simple recovery mode 'cause it is testing server. The log will be truncated.

You can also run scheduled job to do "backup log xx with truncate_only" with proper frequency.

When none of the above works, you need to look into if you have open transactions by DBCC OPENTRAN. You should also modify your delete statement to do transactions at a smaller scale, say, commit transaction every 100 rows. It will allow the log to be checkpointed and truncated.|||Set the Recovery model of database to "Simple" :)|||Alright, now that's nice!

Still though, my transaction log is 602 Megs, I find it a little too big. I'd like to backup it because, as I understand, it's the only way to reduce it.

Now, when I go to the backup screen, I can't choose to backup my transaction log because the option is disabled. Why is that?

Thanks again,

Skip.|||In the database properties window navigate to Transaction Log tab and see how much is allocated. I suspect that 602 is the number you're going to see. You'll probably need to do shrinkfile against your trx log. The reason Backup Transaction Log option is disabled is because your database is in Simple Recovery mode.

LOG FILES

I have a problem with one of the SQL Logs where the LOG
file has reached 5GB in size the DB is only around 400MB,
I have also tried running the command:
dbcc shrinkfile (database, 256)
I have tried altering the number to reflect the size but
alway get the following:
Cannot shrink log file 2 (System21MD_Log) because all
logical log files are in use.
(1 row(s) affected)
also under grids I get:
DbId Filed Current Min Used estimated
size Size Size pages.
10 2 639912 128 639912 128
could anyone advise howto shrink this logfile or truncate
it, please. Any herlp is much appriciated.Adam
Did you try to backup the log file ?
For more details please refer to BOL
"Adam" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
> I have a problem with one of the SQL Logs where the LOG
> file has reached 5GB in size the DB is only around 400MB,
> I have also tried running the command:
> dbcc shrinkfile (database, 256)
> I have tried altering the number to reflect the size but
> alway get the following:
> Cannot shrink log file 2 (System21MD_Log) because all
> logical log files are in use.
> (1 row(s) affected)
>
> also under grids I get:
> DbId Filed Current Min Used estimated
> size Size Size pages.
> 10 2 639912 128 639912 128
>
> could anyone advise howto shrink this logfile or truncate
> it, please. Any herlp is much appriciated.|||the SQL is backed up every night using the veritas addon
for SQL and has previously kept the size down.
>--Original Message--
>Adam
>Did you try to backup the log file ?
>For more details please refer to BOL
>
>"Adam" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
>> I have a problem with one of the SQL Logs where the LOG
>> file has reached 5GB in size the DB is only around
400MB,
>> I have also tried running the command:
>> dbcc shrinkfile (database, 256)
>> I have tried altering the number to reflect the size but
>> alway get the following:
>> Cannot shrink log file 2 (System21MD_Log) because all
>> logical log files are in use.
>> (1 row(s) affected)
>>
>> also under grids I get:
>> DbId Filed Current Min Used estimated
>> size Size Size pages.
>> 10 2 639912 128 639912 128
>>
>> could anyone advise howto shrink this logfile or
truncate
>> it, please. Any herlp is much appriciated.
>
>.
>|||make usre you have backed up the log.
then retry the dbcc shrinkfile, until you get some love... It will not work
until all active transactions have moved off of the logical file... Maybe
try this every 15 minutes or so...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Adam" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2b01c3d52a$07340be0$a301280a@.phx.gbl...
> I have a problem with one of the SQL Logs where the LOG
> file has reached 5GB in size the DB is only around 400MB,
> I have also tried running the command:
> dbcc shrinkfile (database, 256)
> I have tried altering the number to reflect the size but
> alway get the following:
> Cannot shrink log file 2 (System21MD_Log) because all
> logical log files are in use.
> (1 row(s) affected)
>
> also under grids I get:
> DbId Filed Current Min Used estimated
> size Size Size pages.
> 10 2 639912 128 639912 128
>
> could anyone advise howto shrink this logfile or truncate
> it, please. Any herlp is much appriciated.|||This is a multi-part message in MIME format.
--=_NextPart_000_0048_01C3D534.884AB940
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Have you tried backing up the transaction log? This should truncate it.
Also, do you have any long running transactions? These inflate the transaction log, & if they are running at the time of =backup, they will not be truncated.
-- Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures
--=_NextPart_000_0048_01C3D534.884AB940
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Have you tried backing up the =transaction log? This should truncate it.
Also, do you have any long running =transactions? These inflate the transaction log, =& if they are running at the time of backup, they will not be =truncated.
-- Cheers,
James Goodman MCSE, MCDBAhttp://www.angelfire.com/sports/f1pictures">http://www.angelfire.=com/sports/f1pictures

---

--=_NextPart_000_0048_01C3D534.884AB940--

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 Maintenance

Hi,
I am using SQL2000 Standard.
The log file running up tremendously after the system life in production.
My ratio of size is 200mb for Data files and 650mb for Log file, in a month.
Apparently I wish to know:
1) What is cause of the Log file turn big? I am worry it was cause by error
(any error eg, ADO or SQL Transaction Log).
2) It is possible to read / study the log file content?
3) How to Backup or Truncate the log file?
4) What is the Standard or Typical settting in SQL Server Properties and
Configuration in regards to Log File maintenance? I am using all default
setting now.
Thanks.> 1) What is cause of the Log file turn big? I am worry it was cause by
error
> (any error eg, ADO or SQL Transaction Log).
Full or Bulk Logged recovery model without a backup plan, I guess in your
case.
> 2) It is possible to read / study the log file content?
Log Explorer - www.lumigent.com.
> 3) How to Backup or Truncate the log file?
With Backup Log T-SQL statement. Check the syntax in Books OnLine.
> 4) What is the Standard or Typical settting in SQL Server Properties and
> Configuration in regards to Log File maintenance? I am using all default
> setting now.
Default settings differ for server and desktop editions for SQL Server, so
you didn't give us much info. Anyway, for a server in production, you should
use Full recovery model with appropriate backup plan.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Log file size growing rapidly.. increased by 1 GB every week

Hi,
My sql server database log file size growing rapidly, 1 GB by every week. I
have a service running every 2 hours which deletes and inserts some data,
say around 5000 records for every two hours.
I know the source of the problem is deleting and inserting records for every
2 hours. I want to go for a densive mechansim for this, that is: i don't
want to change the service code (for example using TRUNCATE statement
instead of DELETE statement) for any reason.
So I am thinking to write sql job whose job is:
Backup transaction log and shrink log file.
This job runs every day at 4:00 AM.
I alreday have a full backup job which runs at 3:00 AM daily. Probably I
will add the above commnads (backup transaction log and then shrink log
file) to the full backup job schedule as another step.
Does any one experainced this? is it write thing to shrink file every day?
Thanks,
VenkatVenkat
What is recovery model of your db? Its possible it has FULL recovery mode.
If so, please start performing BACKUP LOG to move an inactive transactions
to be moved on the disk. I'd suggest you read about recovery models in the
BOL to understand their impact on the database...
"Venkat" <venkatarao.v@.gmail.com> wrote in message
news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> Hi,
> My sql server database log file size growing rapidly, 1 GB by every week.
> I have a service running every 2 hours which deletes and inserts some
> data, say around 5000 records for every two hours.
> I know the source of the problem is deleting and inserting records for
> every 2 hours. I want to go for a densive mechansim for this, that is: i
> don't want to change the service code (for example using TRUNCATE
> statement instead of DELETE statement) for any reason.
> So I am thinking to write sql job whose job is:
> Backup transaction log and shrink log file.
> This job runs every day at 4:00 AM.
> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> will add the above commnads (backup transaction log and then shrink log
> file) to the full backup job schedule as another step.
> Does any one experainced this? is it write thing to shrink file every day?
> Thanks,
> Venkat
>|||... and to understand the implications of doing a regular shrink, check out:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Venkat
> What is recovery model of your db? Its possible it has FULL recovery mode.
> If so, please start performing BACKUP LOG to move an inactive transactions
> to be moved on the disk. I'd suggest you read about recovery models in the
> BOL to understand their impact on the database...
>
> "Venkat" <venkatarao.v@.gmail.com> wrote in message
> news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> My sql server database log file size growing rapidly, 1 GB by every week.
>> I have a service running every 2 hours which deletes and inserts some
>> data, say around 5000 records for every two hours.
>> I know the source of the problem is deleting and inserting records for
>> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> don't want to change the service code (for example using TRUNCATE
>> statement instead of DELETE statement) for any reason.
>> So I am thinking to write sql job whose job is:
>> Backup transaction log and shrink log file.
>> This job runs every day at 4:00 AM.
>> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> will add the above commnads (backup transaction log and then shrink log
>> file) to the full backup job schedule as another step.
>> Does any one experainced this? is it write thing to shrink file every day?
>> Thanks,
>> Venkat
>|||Example with "Leaking roof and bucket" is great, easy to understand :-)
--
Regards,
anxcomp|||Thanks :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
> Example with "Leaking roof and bucket" is great, easy to understand :-)
> --
> Regards,
> anxcomp|||Hi Uri Dimant, Thsnaks for the reply.
Yes the recovery model is FULL
As I mentioned in my post, i have maintennace plan, which will backup the
database every day at 3:00 AM
So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
log file) then this will address the log file size issue?
Or
I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
LOG?
Thanks,
Venkat
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Venkat
> What is recovery model of your db? Its possible it has FULL recovery mode.
> If so, please start performing BACKUP LOG to move an inactive
> transactions to be moved on the disk. I'd suggest you read about recovery
> models in the BOL to understand their impact on the database...
>
> "Venkat" <venkatarao.v@.gmail.com> wrote in message
> news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> My sql server database log file size growing rapidly, 1 GB by every week.
>> I have a service running every 2 hours which deletes and inserts some
>> data, say around 5000 records for every two hours.
>> I know the source of the problem is deleting and inserting records for
>> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> don't want to change the service code (for example using TRUNCATE
>> statement instead of DELETE statement) for any reason.
>> So I am thinking to write sql job whose job is:
>> Backup transaction log and shrink log file.
>> This job runs every day at 4:00 AM.
>> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> will add the above commnads (backup transaction log and then shrink log
>> file) to the full backup job schedule as another step.
>> Does any one experainced this? is it write thing to shrink file every
>> day?
>> Thanks,
>> Venkat
>|||Venkat,
Just schedule the transaction log backup job. Do not schedule any shrinkfile
operation.
Once you have performed a transaction log backup you can do a shrink file
operation manually, but only once.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Uri Dimant, Thsnaks for the reply.
> Yes the recovery model is FULL
> As I mentioned in my post, i have maintennace plan, which will backup the
> database every day at 3:00 AM
> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> log file) then this will address the log file size issue?
> Or
> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
> LOG?
> Thanks,
> Venkat
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> > Venkat
> > What is recovery model of your db? Its possible it has FULL recovery mode.
> > If so, please start performing BACKUP LOG to move an inactive
> > transactions to be moved on the disk. I'd suggest you read about recovery
> > models in the BOL to understand their impact on the database...
> >
> >
> >
> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >> My sql server database log file size growing rapidly, 1 GB by every week.
> >> I have a service running every 2 hours which deletes and inserts some
> >> data, say around 5000 records for every two hours.
> >>
> >> I know the source of the problem is deleting and inserting records for
> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
> >> don't want to change the service code (for example using TRUNCATE
> >> statement instead of DELETE statement) for any reason.
> >>
> >> So I am thinking to write sql job whose job is:
> >> Backup transaction log and shrink log file.
> >>
> >> This job runs every day at 4:00 AM.
> >>
> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> >> will add the above commnads (backup transaction log and then shrink log
> >> file) to the full backup job schedule as another step.
> >>
> >> Does any one experainced this? is it write thing to shrink file every
> >> day?
> >>
> >> Thanks,
> >> Venkat
> >>
> >
> >
>
>|||Hi Tibor,
Thanks for the reply. Yes I read your post before posting the request here.
Your explanation is good on this topic. But I have one question:
Even if you allocate enough space to log file, say 20 GB to log file, after
some time some one has to go and clean it up before it overshoots the
allocated size.
So is the reason for the automated job to freeup the log file space.
From customers angle: Instead of realying upon DBA or some one to moitor
this log file and clean it up, they would like the system to take care this
automatically. So this is the reason why people want to go for regular
shrink of the log file to freeup the space
Please reply with your comments.
Thanks,
Venkat
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:5281B2E6-A3DC-43E6-BF11-EAF3F24BD2DC@.microsoft.com...
> Thanks :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
> news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
>> Example with "Leaking roof and bucket" is great, easy to understand :-)
>> --
>> Regards,
>> anxcomp
>|||Venkat,
Just schedule the transaction log backup job. Do not schedule any shrinkfile
operation.
Once you have performed a transaction log backup you can do a shrink file
operation manually, but only once.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Uri Dimant, Thsnaks for the reply.
> Yes the recovery model is FULL
> As I mentioned in my post, i have maintennace plan, which will backup the
> database every day at 3:00 AM
> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> log file) then this will address the log file size issue?
> Or
> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
> LOG?
> Thanks,
> Venkat
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> > Venkat
> > What is recovery model of your db? Its possible it has FULL recovery mode.
> > If so, please start performing BACKUP LOG to move an inactive
> > transactions to be moved on the disk. I'd suggest you read about recovery
> > models in the BOL to understand their impact on the database...
> >
> >
> >
> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >> My sql server database log file size growing rapidly, 1 GB by every week.
> >> I have a service running every 2 hours which deletes and inserts some
> >> data, say around 5000 records for every two hours.
> >>
> >> I know the source of the problem is deleting and inserting records for
> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
> >> don't want to change the service code (for example using TRUNCATE
> >> statement instead of DELETE statement) for any reason.
> >>
> >> So I am thinking to write sql job whose job is:
> >> Backup transaction log and shrink log file.
> >>
> >> This job runs every day at 4:00 AM.
> >>
> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> >> will add the above commnads (backup transaction log and then shrink log
> >> file) to the full backup job schedule as another step.
> >>
> >> Does any one experainced this? is it write thing to shrink file every
> >> day?
> >>
> >> Thanks,
> >> Venkat
> >>
> >
> >
>
>|||Hi Ben Nevarez,
So backup database command does not automatically backup log file?
What is the difference between shrinking log file manually and automatically
(when I say automatically, I might shrink log file once in a Week by
scheduling a monthly job)
Thanks,
venkat
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
> Venkat,
> Just schedule the transaction log backup job. Do not schedule any
> shrinkfile
> operation.
> Once you have performed a transaction log backup you can do a shrink file
> operation manually, but only once.
> Hope this helps,
> Ben Nevarez
>
>
> "Venkat" wrote:
>> Hi Uri Dimant, Thsnaks for the reply.
>> Yes the recovery model is FULL
>> As I mentioned in my post, i have maintennace plan, which will backup the
>> database every day at 3:00 AM
>> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
>> log file) then this will address the log file size issue?
>> Or
>> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the
>> BACKUP
>> LOG?
>> Thanks,
>> Venkat
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
>> > Venkat
>> > What is recovery model of your db? Its possible it has FULL recovery
>> > mode.
>> > If so, please start performing BACKUP LOG to move an inactive
>> > transactions to be moved on the disk. I'd suggest you read about
>> > recovery
>> > models in the BOL to understand their impact on the database...
>> >
>> >
>> >
>> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
>> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> >> Hi,
>> >> My sql server database log file size growing rapidly, 1 GB by every
>> >> week.
>> >> I have a service running every 2 hours which deletes and inserts some
>> >> data, say around 5000 records for every two hours.
>> >>
>> >> I know the source of the problem is deleting and inserting records for
>> >> every 2 hours. I want to go for a densive mechansim for this, that is:
>> >> i
>> >> don't want to change the service code (for example using TRUNCATE
>> >> statement instead of DELETE statement) for any reason.
>> >>
>> >> So I am thinking to write sql job whose job is:
>> >> Backup transaction log and shrink log file.
>> >>
>> >> This job runs every day at 4:00 AM.
>> >>
>> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably
>> >> I
>> >> will add the above commnads (backup transaction log and then shrink
>> >> log
>> >> file) to the full backup job schedule as another step.
>> >>
>> >> Does any one experainced this? is it write thing to shrink file every
>> >> day?
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >
>> >
>>|||If you have a Full Backup that does not backup the transaction log. You need
to specify a Transaction Log Backup.
What I meant by manually is that you will need to shrink the file only once.
Do not schedule any job.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Ben Nevarez,
> So backup database command does not automatically backup log file?
> What is the difference between shrinking log file manually and automatically
> (when I say automatically, I might shrink log file once in a Week by
> scheduling a monthly job)
> Thanks,
> venkat
> "Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
> news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
> >
> > Venkat,
> >
> > Just schedule the transaction log backup job. Do not schedule any
> > shrinkfile
> > operation.
> >
> > Once you have performed a transaction log backup you can do a shrink file
> > operation manually, but only once.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Venkat" wrote:
> >
> >> Hi Uri Dimant, Thsnaks for the reply.
> >>
> >> Yes the recovery model is FULL
> >>
> >> As I mentioned in my post, i have maintennace plan, which will backup the
> >> database every day at 3:00 AM
> >> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> >> log file) then this will address the log file size issue?
> >>
> >> Or
> >> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the
> >> BACKUP
> >> LOG?
> >>
> >> Thanks,
> >> Venkat
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> >> > Venkat
> >> > What is recovery model of your db? Its possible it has FULL recovery
> >> > mode.
> >> > If so, please start performing BACKUP LOG to move an inactive
> >> > transactions to be moved on the disk. I'd suggest you read about
> >> > recovery
> >> > models in the BOL to understand their impact on the database...
> >> >
> >> >
> >> >
> >> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> >> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> >> Hi,
> >> >> My sql server database log file size growing rapidly, 1 GB by every
> >> >> week.
> >> >> I have a service running every 2 hours which deletes and inserts some
> >> >> data, say around 5000 records for every two hours.
> >> >>
> >> >> I know the source of the problem is deleting and inserting records for
> >> >> every 2 hours. I want to go for a densive mechansim for this, that is:
> >> >> i
> >> >> don't want to change the service code (for example using TRUNCATE
> >> >> statement instead of DELETE statement) for any reason.
> >> >>
> >> >> So I am thinking to write sql job whose job is:
> >> >> Backup transaction log and shrink log file.
> >> >>
> >> >> This job runs every day at 4:00 AM.
> >> >>
> >> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably
> >> >> I
> >> >> will add the above commnads (backup transaction log and then shrink
> >> >> log
> >> >> file) to the full backup job schedule as another step.
> >> >>
> >> >> Does any one experainced this? is it write thing to shrink file every
> >> >> day?
> >> >>
> >> >> Thanks,
> >> >> Venkat
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||> Even if you allocate enough space to log file, say 20 GB to log file, after some time some one has
> to go and clean it up before it overshoots the allocated size.
Yes, this is what the BACKUP LOG command does, it empties the log. And, of course, this is something
we schedule.
If you don't want to do backup log, then set the database in simple recovery model. Then SQL Server
will truncate/empty the log every time a checkpoint occurs (something that happens pretty regularly
in the database).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Venkat" <venkatarao.v@.gmail.com> wrote in message news:uIutBC8ZIHA.4160@.TK2MSFTNGP03.phx.gbl...
> Hi Tibor,
> Thanks for the reply. Yes I read your post before posting the request here. Your explanation is
> good on this topic. But I have one question:
> Even if you allocate enough space to log file, say 20 GB to log file, after some time some one has
> to go and clean it up before it overshoots the allocated size.
> So is the reason for the automated job to freeup the log file space.
> From customers angle: Instead of realying upon DBA or some one to moitor this log file and clean
> it up, they would like the system to take care this automatically. So this is the reason why
> people want to go for regular shrink of the log file to freeup the space
> Please reply with your comments.
> Thanks,
> Venkat
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:5281B2E6-A3DC-43E6-BF11-EAF3F24BD2DC@.microsoft.com...
>> Thanks :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
>> Example with "Leaking roof and bucket" is great, easy to understand :-)
>> --
>> Regards,
>> anxcomp
>|||> So backup database command does not automatically backup log file?
Backup database do include some log records. That that isn't the important question. The important
question is whether backup database empties the log. And the answer is: "It doesn't".
> What is the difference between shrinking log file manually and automatically (when I say
> automatically, I might shrink log file once in a Week by scheduling a monthly job)
That is what I tried to explain in my "leaking roof" analogy. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Venkat" <venkatarao.v@.gmail.com> wrote in message news:e4g41F8ZIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi Ben Nevarez,
> So backup database command does not automatically backup log file?
> What is the difference between shrinking log file manually and automatically (when I say
> automatically, I might shrink log file once in a Week by scheduling a monthly job)
> Thanks,
> venkat
> "Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
> news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
>> Venkat,
>> Just schedule the transaction log backup job. Do not schedule any shrinkfile
>> operation.
>> Once you have performed a transaction log backup you can do a shrink file
>> operation manually, but only once.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "Venkat" wrote:
>> Hi Uri Dimant, Thsnaks for the reply.
>> Yes the recovery model is FULL
>> As I mentioned in my post, i have maintennace plan, which will backup the
>> database every day at 3:00 AM
>> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
>> log file) then this will address the log file size issue?
>> Or
>> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
>> LOG?
>> Thanks,
>> Venkat
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
>> > Venkat
>> > What is recovery model of your db? Its possible it has FULL recovery mode.
>> > If so, please start performing BACKUP LOG to move an inactive
>> > transactions to be moved on the disk. I'd suggest you read about recovery
>> > models in the BOL to understand their impact on the database...
>> >
>> >
>> >
>> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
>> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> >> Hi,
>> >> My sql server database log file size growing rapidly, 1 GB by every week.
>> >> I have a service running every 2 hours which deletes and inserts some
>> >> data, say around 5000 records for every two hours.
>> >>
>> >> I know the source of the problem is deleting and inserting records for
>> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> >> don't want to change the service code (for example using TRUNCATE
>> >> statement instead of DELETE statement) for any reason.
>> >>
>> >> So I am thinking to write sql job whose job is:
>> >> Backup transaction log and shrink log file.
>> >>
>> >> This job runs every day at 4:00 AM.
>> >>
>> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> >> will add the above commnads (backup transaction log and then shrink log
>> >> file) to the full backup job schedule as another step.
>> >>
>> >> Does any one experainced this? is it write thing to shrink file every
>> >> day?
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >
>> >
>>
>

Log file size

Hi there,
I have a SQL Server 2000 database running on a Windows 2000 Server. The data
file is about 1GB large. The size of the log file is allways about 2GB. When
it reaches this size, I run 'BACKUP LOG <database> WITH TRUNCATE_ONLY' to
empty the file. Then, the file keeps growing again until 2GB.
My question is, it is normal that the log file grows like this way?
I can also tell that there is an application that runs over the database,
and it's installed in 10/15 workstations. There are allways 8/9+
workstations working.
I would appreciate any commentary...
Regards..
Marco Pais> My question is, it is normal that the log file grows like this way?
Well, what is your recovery model? If you don't backup your database then
the log will continue to grow until you either backup the database or backup
the log. If you are not bothering to backup your database then switch to
simple recovery model and the effect on the log won't be so great (but of
course your recovery options become more limited).
In our environment, we leave enough room for the log files to grow as they
need to. If you are fretting over 1 GB now what's going to happen when you
have a real data store.
A|||Hello Aaron,
First of all, thanks for the answer.
The database is backuped once a day, every days of week.
Can the log file growing be caused by something else? Can it affect
performance?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Well, what is your recovery model? If you don't backup your database then
> the log will continue to grow until you either backup the database or
> backup the log. If you are not bothering to backup your database then
> switch to simple recovery model and the effect on the log won't be so
> great (but of course your recovery options become more limited).
> In our environment, we leave enough room for the log files to grow as they
> need to. If you are fretting over 1 GB now what's going to happen when
> you have a real data store.
> A
>|||Apart from long running transactions in the application or badly configured
replication the most common cause of T-log becoming excessive is the
optimization job. This will make copies of existing data while rebuilding
and if you have particularly large tables this will cause a long running
transaction.
Providing the used part of the log stays reasonable small (say <100MB)
during your busy times you should not suffer performance degredation.
Regards,
Tim G-J
Professional Database Solutions Ltd.
www.pdbsolutions.co.uk
"Marco Pais" wrote:

> Hello Aaron,
> First of all, thanks for the answer.
> The database is backuped once a day, every days of week.
> Can the log file growing be caused by something else? Can it affect
> performance?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
>
>

Log file size

Hi there,
I have a SQL Server 2000 database running on a Windows 2000 Server. The data
file is about 1GB large. The size of the log file is allways about 2GB. When
it reaches this size, I run 'BACKUP LOG <database> WITH TRUNCATE_ONLY' to
empty the file. Then, the file keeps growing again until 2GB.
My question is, it is normal that the log file grows like this way?
I can also tell that there is an application that runs over the database,
and it's installed in 10/15 workstations. There are allways 8/9+
workstations working.
I would appreciate any commentary...
Regards..
Marco Pais> My question is, it is normal that the log file grows like this way?
Well, what is your recovery model? If you don't backup your database then
the log will continue to grow until you either backup the database or backup
the log. If you are not bothering to backup your database then switch to
simple recovery model and the effect on the log won't be so great (but of
course your recovery options become more limited).
In our environment, we leave enough room for the log files to grow as they
need to. If you are fretting over 1 GB now what's going to happen when you
have a real data store.
A|||Hello Aaron,
First of all, thanks for the answer.
The database is backuped once a day, every days of week.
Can the log file growing be caused by something else? Can it affect
performance?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
>> My question is, it is normal that the log file grows like this way?
> Well, what is your recovery model? If you don't backup your database then
> the log will continue to grow until you either backup the database or
> backup the log. If you are not bothering to backup your database then
> switch to simple recovery model and the effect on the log won't be so
> great (but of course your recovery options become more limited).
> In our environment, we leave enough room for the log files to grow as they
> need to. If you are fretting over 1 GB now what's going to happen when
> you have a real data store.
> A
>

Log file size

Hi there,
I have a SQL Server 2000 database running on a Windows 2000 Server. The data
file is about 1GB large. The size of the log file is allways about 2GB. When
it reaches this size, I run 'BACKUP LOG <database> WITH TRUNCATE_ONLY' to
empty the file. Then, the file keeps growing again until 2GB.
My question is, it is normal that the log file grows like this way?
I can also tell that there is an application that runs over the database,
and it's installed in 10/15 workstations. There are allways 8/9+
workstations working.
I would appreciate any commentary...
Regards..
Marco Pais
> My question is, it is normal that the log file grows like this way?
Well, what is your recovery model? If you don't backup your database then
the log will continue to grow until you either backup the database or backup
the log. If you are not bothering to backup your database then switch to
simple recovery model and the effect on the log won't be so great (but of
course your recovery options become more limited).
In our environment, we leave enough room for the log files to grow as they
need to. If you are fretting over 1 GB now what's going to happen when you
have a real data store.
A
|||Hello Aaron,
First of all, thanks for the answer.
The database is backuped once a day, every days of week.
Can the log file growing be caused by something else? Can it affect
performance?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Well, what is your recovery model? If you don't backup your database then
> the log will continue to grow until you either backup the database or
> backup the log. If you are not bothering to backup your database then
> switch to simple recovery model and the effect on the log won't be so
> great (but of course your recovery options become more limited).
> In our environment, we leave enough room for the log files to grow as they
> need to. If you are fretting over 1 GB now what's going to happen when
> you have a real data store.
> A
>
|||Apart from long running transactions in the application or badly configured
replication the most common cause of T-log becoming excessive is the
optimization job. This will make copies of existing data while rebuilding
and if you have particularly large tables this will cause a long running
transaction.
Providing the used part of the log stays reasonable small (say <100MB)
during your busy times you should not suffer performance degredation.
Regards,
Tim G-J
Professional Database Solutions Ltd.
www.pdbsolutions.co.uk
"Marco Pais" wrote:

> Hello Aaron,
> First of all, thanks for the answer.
> The database is backuped once a day, every days of week.
> Can the log file growing be caused by something else? Can it affect
> performance?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
>
>

Log file not freeing space (FULL recovery model) - even after transaction log backups

Hey all,
I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
David
Hi David
Check to see if there are open transactions with DBCC OPENTRAN
John
"David Conte" wrote:

> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>
|||How have you determined that there is 'no free space in it'? Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegr oups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>
|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> How have you determined that there is 'no free space in it'? Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even if
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegr oups.com...
>
>
>
|||Hi
What does the DBCC command give after you backup the log?
Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
should not shrink it to a size where under normal operations it will expand.
John
"David Conte" wrote:

> Thanks for the info John and Kevin.
> I didn't think that there could be an open transaction.. so tried DBCC
> OPENTRAN.
> Running DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Sounds like this has to do with replication? (excuse my ignorance
> here!)
> We don't have replication setup.
> Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
> log file via Management Studio it gives the free space percentage
> there before attempting the shrink. Just to confirm, I ran the SQL and
> it gives:
> 41297.24 99.79205% full
> Thanks
> David
>
> On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
|||Log still shows minimal free space even after transaction logs.
Hence, running DBCC SHRINKFILE does nothing as there is no free space
to be recovered.
This whole thing smells like a bug.
If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
is "REPLICATION".
Again, this database (or any other databases on the server) has never
had replication setup!
I tried running exec sp_removedbreplication - it completes, but the
log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
gives:
Transaction information for database 'xxxxx'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I think we are going to resort to setting up replication on it, run
sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
David
On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> What does the DBCC command give after you backup the log?
> Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> should not shrink it to a size where under normal operations it will expand.
> John
|||Hi David
I am not sure how that has occurred, but you could try rebuilding the log
file.
John
"David Conte" wrote:

> Log still shows minimal free space even after transaction logs.
> Hence, running DBCC SHRINKFILE does nothing as there is no free space
> to be recovered.
> This whole thing smells like a bug.
> If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
> is "REPLICATION".
> Again, this database (or any other databases on the server) has never
> had replication setup!
> I tried running exec sp_removedbreplication - it completes, but the
> log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
> gives:
> Transaction information for database 'xxxxx'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I think we are going to resort to setting up replication on it, run
> sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
> David
> On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
|||Ok well I found a forum post here on an identical issue.
Sounds like it is indeed an SQL Server bug.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
The workaround (which I've managed to do on a test restore of the live
database which experiences the same issue) is to setup replication on
the database, then remove the publication. After this there was now
99% free space in the logfile and I was able to shrink it from 44 GB
to 1 GB.
DBCC OPENTRAN now says there are no active transactions, and the
log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
So looking good again.
Now to do the process on the live database
Thanks everyone for their time!
Dave
|||Hi David
According to the post the bug may have been fixed in SP2, if you are not
running this version you may want to upgrade to SP2 plus hotfixes.
John
"David Conte" wrote:

> Ok well I found a forum post here on an identical issue.
> Sounds like it is indeed an SQL Server bug.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
> The workaround (which I've managed to do on a test restore of the live
> database which experiences the same issue) is to setup replication on
> the database, then remove the publication. After this there was now
> 99% free space in the logfile and I was able to shrink it from 44 GB
> to 1 GB.
> DBCC OPENTRAN now says there are no active transactions, and the
> log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
> So looking good again.
> Now to do the process on the live database
> Thanks everyone for their time!
> Dave
>

Log file not freeing space (FULL recovery model) - even after transaction log backups

Hey all,
I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
DavidHi David
Check to see if there are open transactions with DBCC OPENTRAN
John
"David Conte" wrote:
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>|||How have you determined that there is 'no free space in it'' Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> How have you determined that there is 'no free space in it'' Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even if
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> > Hey all,
> > I have a site that has their log file at 40 gig (running SQL Server
> > 2005).
> > The database is set to FULL recovery model, and transaction log
> > backups are performed every 3 hours.
> > The log file is HUGE compared to usual and I need to shrink it down.
> > However, it has no free space in it which is very strange considering
> > we are backing up the logs every 3 hours.
> > I have full backups occuring nightly.
> > I also tried switching to SIMPLE recovery model and shrinking the log
> > file.
> > Obviously this doesn't work as there isn't any free space in the log.
> > The site did have some db corruption issues a few weeks ago due to a
> > SAN issue.
> > The hardware has been repaired as well as the corruptions (they were
> > index corruptions so we rebuilt the indexes).
> > Could this have caused the transaction log to have issues to and not
> > free comitted transactions?
> > We have many other sites who have small transaction logs and I am at a
> > loss as to why it is so large.
> > The database is also 40 gig (and thus backups are 80+ gig in size!)
> > Anything I can try?
> > I am concerned about trashing the log by detaching / reattaching the
> > db without the log file as it seems brutal, but is that the only
> > choice?
> > Thanks,
> > David|||Hi
What does the DBCC command give after you backup the log?
Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
should not shrink it to a size where under normal operations it will expand.
John
"David Conte" wrote:
> Thanks for the info John and Kevin.
> I didn't think that there could be an open transaction.. so tried DBCC
> OPENTRAN.
> Running DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Sounds like this has to do with replication? (excuse my ignorance
> here!)
> We don't have replication setup.
> Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
> log file via Management Studio it gives the free space percentage
> there before attempting the shrink. Just to confirm, I ran the SQL and
> it gives:
> 41297.24 99.79205% full
> Thanks
> David
>
> On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> > How have you determined that there is 'no free space in it'' Did you
> > execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> > reduce in physical size? There is some very complicated stuff about the
> > internals of the log file that can prevent it from shrinking (much) even if
> > it has virtually no information in it. Search the web for sql server log
> > file shrink and you will find a number of helpful scripts to get over this
> > situation.
> >
> > --
> > Kevin G. Boles
> > TheSQLGuru
> > Indicium Resources, Inc.
> >
> > "David Conte" <davco...@.gmail.com> wrote in message
> >
> > news:1194500150.103297.201900@.y27g2000pre.googlegroups.com...
> >
> > > Hey all,
> >
> > > I have a site that has their log file at 40 gig (running SQL Server
> > > 2005).
> > > The database is set to FULL recovery model, and transaction log
> > > backups are performed every 3 hours.
> >
> > > The log file is HUGE compared to usual and I need to shrink it down.
> > > However, it has no free space in it which is very strange considering
> > > we are backing up the logs every 3 hours.
> > > I have full backups occuring nightly.
> > > I also tried switching to SIMPLE recovery model and shrinking the log
> > > file.
> > > Obviously this doesn't work as there isn't any free space in the log.
> >
> > > The site did have some db corruption issues a few weeks ago due to a
> > > SAN issue.
> > > The hardware has been repaired as well as the corruptions (they were
> > > index corruptions so we rebuilt the indexes).
> > > Could this have caused the transaction log to have issues to and not
> > > free comitted transactions?
> >
> > > We have many other sites who have small transaction logs and I am at a
> > > loss as to why it is so large.
> > > The database is also 40 gig (and thus backups are 80+ gig in size!)
> >
> > > Anything I can try?
> > > I am concerned about trashing the log by detaching / reattaching the
> > > db without the log file as it seems brutal, but is that the only
> > > choice?
> >
> > > Thanks,
> > > David
>
>|||Log still shows minimal free space even after transaction logs.
Hence, running DBCC SHRINKFILE does nothing as there is no free space
to be recovered.
This whole thing smells like a bug.
If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
is "REPLICATION".
Again, this database (or any other databases on the server) has never
had replication setup!
I tried running exec sp_removedbreplication - it completes, but the
log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
gives:
Transaction information for database 'xxxxx'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I think we are going to resort to setting up replication on it, run
sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
David
On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> What does the DBCC command give after you backup the log?
> Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> should not shrink it to a size where under normal operations it will expand.
> John|||Hi David
I am not sure how that has occurred, but you could try rebuilding the log
file.
John
"David Conte" wrote:
> Log still shows minimal free space even after transaction logs.
> Hence, running DBCC SHRINKFILE does nothing as there is no free space
> to be recovered.
> This whole thing smells like a bug.
> If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
> is "REPLICATION".
> Again, this database (or any other databases on the server) has never
> had replication setup!
> I tried running exec sp_removedbreplication - it completes, but the
> log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
> gives:
> Transaction information for database 'xxxxx'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I think we are going to resort to setting up replication on it, run
> sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
> David
> On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > What does the DBCC command give after you backup the log?
> >
> > Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> > should not shrink it to a size where under normal operations it will expand.
> >
> > John
>|||Ok well I found a forum post here on an identical issue.
Sounds like it is indeed an SQL Server bug.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
The workaround (which I've managed to do on a test restore of the live
database which experiences the same issue) is to setup replication on
the database, then remove the publication. After this there was now
99% free space in the logfile and I was able to shrink it from 44 GB
to 1 GB.
DBCC OPENTRAN now says there are no active transactions, and the
log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
So looking good again.
Now to do the process on the live database :)
Thanks everyone for their time!
Dave|||Hi David
According to the post the bug may have been fixed in SP2, if you are not
running this version you may want to upgrade to SP2 plus hotfixes.
John
"David Conte" wrote:
> Ok well I found a forum post here on an identical issue.
> Sounds like it is indeed an SQL Server bug.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
> The workaround (which I've managed to do on a test restore of the live
> database which experiences the same issue) is to setup replication on
> the database, then remove the publication. After this there was now
> 99% free space in the logfile and I was able to shrink it from 44 GB
> to 1 GB.
> DBCC OPENTRAN now says there are no active transactions, and the
> log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
> So looking good again.
> Now to do the process on the live database :)
> Thanks everyone for their time!
> Dave
>