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
|
|
Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts
Wednesday, March 28, 2012
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
|
|
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
|
|
Monday, March 26, 2012
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
>
>
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
>
>
Monday, March 19, 2012
Log File Full
My production database is very big in size and due to contious transactions, my log file becomes full frequently. I manually shrink log file when it becomes large. But i believe shrinking many times is not a good practise in terms of performance. So what should i do? Can anyone suggest a better approach.
How much frequency are you taking backup of transaction log ?
For example, if you are taking transaction log backup every 30 minutes, change it to every 15 minutes.
Thanks
Naras.
|||Yes, you rightly said that shrinking is not a good practice. You should control the growth of Transaction log and if space is not there in the current drive, you should shift TL to a better place. You can control the growth by taking backup of transaction log more frequently. When you shrink the transaciton log the backup chain breaks. so the first thing you do should be full backup. If you dont want point in time restore you can change the recovery model to simple which will restirct the gwoth of TL to a certain limit.
How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235.
Madhu
|||I take trn log backup every 2 hours. As i want point in time restore, i need to take tl back up atleast 2 hr.|||But if i don't shrink it grows beyond 2 GB in week. I need a poin in time restore so i had to keep full recovery model.|||what is the frequency of TL backup of this database.
Madhu
|||Every 2 hr TL Backup, daily Diff backup and weekly Full backup.|||You can still achieve a point in time restore, even if you take transaction log backup every 30/60/90/120 minutes.
Thanks
Naras.
|||schedule TL backup more frequently... say every 15 min... this is the better approach
Madhu
|||http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=263
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Addition to Madhu's references.
Monday, March 12, 2012
log file backup frequency
Helllo,
How can I know how many transactions are saved in a transaction log backup? Is there a way to compute transaction log backup frequency?Transaction log backups are scheduled events, and how often you schedule them depends upon the recoverability and restorability you require. How many transactions are stored depends upon how many occured since the last log backup. Could be 1, could 1,000,000. You will need to read up in Books Online to decide what is best for you, but in the mean time use the maintenance plan wizard to set something up.
blindman
How can I know how many transactions are saved in a transaction log backup? Is there a way to compute transaction log backup frequency?Transaction log backups are scheduled events, and how often you schedule them depends upon the recoverability and restorability you require. How many transactions are stored depends upon how many occured since the last log backup. Could be 1, could 1,000,000. You will need to read up in Books Online to decide what is best for you, but in the mean time use the maintenance plan wizard to set something up.
blindman
Subscribe to:
Posts (Atom)