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 logs. Show all posts
Showing posts with label logs. 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
|
|
log full message
I have sql server database logs set to expand with no
limit. So why do I get a log full message in the server
log. Thanks, Steve B.Perhaps the log file grew and grew until it filled up the disk.
You will probably want to truncate the log and then shrink it in order =to reclaim the disk space.
-- Keith, SQL Server MVP
"Steve B" <Steve.Baiotto@.ercgroup.com> wrote in message =news:02c301c34a4d$64ae7d40$a501280a@.phx.gbl...
> I have sql server database logs set to expand with no > limit. So why do I get a log full message in the server > log. Thanks, Steve B. >sql
limit. So why do I get a log full message in the server
log. Thanks, Steve B.Perhaps the log file grew and grew until it filled up the disk.
You will probably want to truncate the log and then shrink it in order =to reclaim the disk space.
-- Keith, SQL Server MVP
"Steve B" <Steve.Baiotto@.ercgroup.com> wrote in message =news:02c301c34a4d$64ae7d40$a501280a@.phx.gbl...
> I have sql server database logs set to expand with no > limit. So why do I get a log full message in the server > log. Thanks, Steve B. >sql
Log for tempdb is full
I keep getting the following error in my SQL 2K SP3a server logs:
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space..
But using the EM I cannot backup the transaction logs - the options are
grayed out. If I try to backup the tempdb through EM, I get an error that
Backup and Restore are not allowed on tempdb. I have the options set to
allow unlimited growth. There are many GB's of space on the drive. What is
the problem/solution?
TIA!http://www.aspfaq.com/2446
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>|||Perhaps there's code in some application that has an open transaction that
has run amok?
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space..
But using the EM I cannot backup the transaction logs - the options are
grayed out. If I try to backup the tempdb through EM, I get an error that
Backup and Restore are not allowed on tempdb. I have the options set to
allow unlimited growth. There are many GB's of space on the drive. What is
the problem/solution?
TIA!http://www.aspfaq.com/2446
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>|||Perhaps there's code in some application that has an open transaction that
has run amok?
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:u95Znp3wEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I keep getting the following error in my SQL 2K SP3a server logs:
> The log file for database 'tempdb' is full. Back up the transaction log
for
> the database to free up some log space..
> But using the EM I cannot backup the transaction logs - the options are
> grayed out. If I try to backup the tempdb through EM, I get an error that
> Backup and Restore are not allowed on tempdb. I have the options set to
> allow unlimited growth. There are many GB's of space on the drive. What is
> the problem/solution?
> TIA!
>
Log files WAY too big
I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default...b;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIA
Open Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
sql
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default...b;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIA
Open Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
sql
Log files WAY too big
I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
Log files WAY too big
I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/defaul...kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/defaul...kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
Log files taking up a lot of disk space
Hello,
I ran into an issue with all these logs made by default.
Disk space crunch leading to performance degradation.
OK, I moved the log dir outside the default app volume (Script/restart) onto a 100GB drive I use for the SQL transactions logs, but.
Analysis Services accumulated 20GB of logs in 2-3 days, did not clean-up the old stuff and led to disk space alerts and applications performance issues. All this with out of the box settings.
The server has in the 30 to 60 connections alive around the clock with each of these connections performing queries every 2-3 seconds or so.
I also use the query log function for optimisation purposes but for that one I log to table on a specific drive.
I run Analysis services 64 bits build 9.00.2047
Log files eating the drive have name like msmdsrv.log, FlightRecorderCurrent.trc, FlightRecorderBack.trc, SQLDmpr4495.mdmp, SQLDmpr4495.log and SQLDUMPER_ERRORLOG.log
Is the high frequency of log files creation (4MB every 5 minutes) the indication of a bigger issue?
Is there any way to set up these logs to be less intrusive, less verbose and more self-cleaning while still reaping the benefits of unattended problems loging?
Thanks,
Philippe
Yes.
You might be having different and potentially more seriouse issues. Creation of SQLDmpr***.mdmp SQLDmpr***.log files is indication of something going wrong in your server.
You should try and contact customer support and report your problems.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thank you,
The problem was very simple, the login in the QueryLog connection string did not have write access to the logDB.
This was generating all these Dmpr log files. Not a single log was generated since I fixed it last week.
Philippe
|||I split and edited this thread from an unrelated thread on the Flight Recorder to make it easier for people searching the forum to find this information.Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Why shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>
|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'************************************************* ******************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '************************************************* ******************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe
|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/Tr...leGrows_1.html
|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Why shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>
|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'************************************************* ******************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '************************************************* ******************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe
|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/Tr...leGrows_1.html
|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size
.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run
a
DBCC shrinkfile how small can you get it? This may well be the original siz
e.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do
a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same si
ze.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files bac
k
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'***************************************
****************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '***************************************
****************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about th
e
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/T...ileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size
.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run
a
DBCC shrinkfile how small can you get it? This may well be the original siz
e.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do
a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same si
ze.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files bac
k
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'***************************************
****************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '***************************************
****************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about th
e
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/T...ileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'*******************************************************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '*******************************************************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'*******************************************************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '*******************************************************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Monday, March 26, 2012
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--
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 Too Big.
Hi !
I have a small Sql server database (about 50 Mb) for a pubblic web
application.
But the log's file is is about 4 Gb and it increase very fast.......
It's normal ?
I have to set something ?
The web site has a very little amount of traffic......so there are a
little number of Sql server query.....
Thanks for your help !
Matteo Mazzoni
Matteo,
Seems like you are using Full recovery model without backing up the log. You
should backup log regularly as well. f you do not eed log backups, then
consider switching to SImple recovery model. Do please read more about
recovery model in Books OnLine. Meanwhile, this article shows ow can you
shrink the log file:
http://support.microsoft.com/default...;EN-US;272318.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Matteo Mazzoni" <Matteo Mazzoni@.discussions.microsoft.com> wrote in message
news:33E6A5A0-D5C9-4CCB-9A6F-39383E7C1C73@.microsoft.com...
> Hi !
> I have a small Sql server database (about 50 Mb) for a pubblic web
> application.
> But the log's file is is about 4 Gb and it increase very fast.......
> It's normal ?
> I have to set something ?
> The web site has a very little amount of traffic......so there are a
> little number of Sql server query.....
> Thanks for your help !
> Matteo Mazzoni
|||Another Idea may be to temporarly changing the recovery
model to simple then performing a shrink database, that
will get your disk space back.
After that you can back up your new streamline DB with the
Full Recovery model, and not use 4gb per backup.
Peter
>--Original Message--
>Matteo,
>Seems like you are using Full recovery model without
backing up the log. You
>should backup log regularly as well. f you do not eed log
backups, then
>consider switching to SImple recovery model. Do please
read more about
>recovery model in Books OnLine. Meanwhile, this article
shows ow can you
>shrink the log file:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Matteo Mazzoni" <Matteo
Mazzoni@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:33E6A5A0-D5C9-4CCB-9A6F-39383E7C1C73@.microsoft.com...
pubblic web[vbcol=seagreen]
very fast.......[vbcol=seagreen]
traffic......so there are a
>
>.
>
|||How i can changing the recovery
model to simple ?
Thanks again for your help.
"Peter The Spate" wrote:
> Another Idea may be to temporarly changing the recovery
> model to simple then performing a shrink database, that
> will get your disk space back.
> After that you can back up your new streamline DB with the
> Full Recovery model, and not use 4gb per backup.
> Peter
>
> backing up the log. You
> backups, then
> read more about
> shows ow can you
> US;272318.
> Mazzoni@.discussions.microsoft.com> wrote in message
> pubblic web
> very fast.......
> traffic......so there are a
>
|||morello
ALTER DATABASE ... SET RECOVERY SIMPLE
"morello" <morello@.discussions.microsoft.com> wrote in message
news:2E0701F1-DC46-41CE-87C7-93D1FB3BB3DA@.microsoft.com...[vbcol=seagreen]
> How i can changing the recovery
> model to simple ?
> Thanks again for your help.
> "Peter The Spate" wrote:
I have a small Sql server database (about 50 Mb) for a pubblic web
application.
But the log's file is is about 4 Gb and it increase very fast.......
It's normal ?
I have to set something ?
The web site has a very little amount of traffic......so there are a
little number of Sql server query.....
Thanks for your help !
Matteo Mazzoni
Matteo,
Seems like you are using Full recovery model without backing up the log. You
should backup log regularly as well. f you do not eed log backups, then
consider switching to SImple recovery model. Do please read more about
recovery model in Books OnLine. Meanwhile, this article shows ow can you
shrink the log file:
http://support.microsoft.com/default...;EN-US;272318.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Matteo Mazzoni" <Matteo Mazzoni@.discussions.microsoft.com> wrote in message
news:33E6A5A0-D5C9-4CCB-9A6F-39383E7C1C73@.microsoft.com...
> Hi !
> I have a small Sql server database (about 50 Mb) for a pubblic web
> application.
> But the log's file is is about 4 Gb and it increase very fast.......
> It's normal ?
> I have to set something ?
> The web site has a very little amount of traffic......so there are a
> little number of Sql server query.....
> Thanks for your help !
> Matteo Mazzoni
|||Another Idea may be to temporarly changing the recovery
model to simple then performing a shrink database, that
will get your disk space back.
After that you can back up your new streamline DB with the
Full Recovery model, and not use 4gb per backup.
Peter
>--Original Message--
>Matteo,
>Seems like you are using Full recovery model without
backing up the log. You
>should backup log regularly as well. f you do not eed log
backups, then
>consider switching to SImple recovery model. Do please
read more about
>recovery model in Books OnLine. Meanwhile, this article
shows ow can you
>shrink the log file:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Matteo Mazzoni" <Matteo
Mazzoni@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:33E6A5A0-D5C9-4CCB-9A6F-39383E7C1C73@.microsoft.com...
pubblic web[vbcol=seagreen]
very fast.......[vbcol=seagreen]
traffic......so there are a
>
>.
>
|||How i can changing the recovery
model to simple ?
Thanks again for your help.
"Peter The Spate" wrote:
> Another Idea may be to temporarly changing the recovery
> model to simple then performing a shrink database, that
> will get your disk space back.
> After that you can back up your new streamline DB with the
> Full Recovery model, and not use 4gb per backup.
> Peter
>
> backing up the log. You
> backups, then
> read more about
> shows ow can you
> US;272318.
> Mazzoni@.discussions.microsoft.com> wrote in message
> pubblic web
> very fast.......
> traffic......so there are a
>
|||morello
ALTER DATABASE ... SET RECOVERY SIMPLE
"morello" <morello@.discussions.microsoft.com> wrote in message
news:2E0701F1-DC46-41CE-87C7-93D1FB3BB3DA@.microsoft.com...[vbcol=seagreen]
> How i can changing the recovery
> model to simple ?
> Thanks again for your help.
> "Peter The Spate" wrote:
Monday, March 19, 2012
LOG file Growing, Similar Issue to a previous one
I have an 19 gig database that somehow has a 100gig log file. The DB MUST BE in full recovery mode, I backup the transaction logs EVERY hour and shrink nightly. but for some reason my logfile WILL NOT SHRINK.
HELP,
I've used both the DBCC Shrinkfile (xxxxxx) and DBCC ShrinkDatabase (xxxxx) and these don't seem to work. I Have No current backup, I have Not capacity for addtional 100 gig worth of backup drive or off-site tape.
I found a solution to my issue. Replication I have setup had a hung transaction.
Friday, March 9, 2012
log file
How can I delete the transaction logs? Every time I try it errors saying the
file isn't empty and I don't know how to empty it! I've tried everything and
the log file is over 200 mb so far!
Thanks,
ToddHave you tried BACKUP LOG your_db_name WITH TRUNCATE_ONLY?
How did you try to 'delete' the xaction log?
The other thing you might think about is put the DB
recovery option to SIMPLE (equiv to trunc. log on chkpt).
That way, you won't have to manual delete the xaction log.
hth
-a
>--Original Message--
>How can I delete the transaction logs? Every time I try
it errors saying the
>file isn't empty and I don't know how to empty it! I've
tried everything and
>the log file is over 200 mb so far!
>Thanks,
>Todd
>
>.
>|||If you have multiple log files for this database and you want to 'delete'
(i.e. remove one of them), you can try DBCC SHRINKFILE with the EMPTYFILE
option. This do NOT work with the primary transaction log file, though.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Todd Ellington" <todd@.vtserve.com> wrote in message
news:u7zuupjiDHA.2824@.tk2msftngp13.phx.gbl...
> How can I delete the transaction logs? Every time I try it errors saying
the
> file isn't empty and I don't know how to empty it! I've tried everything
and
> the log file is over 200 mb so far!
> Thanks,
> Todd
>
file isn't empty and I don't know how to empty it! I've tried everything and
the log file is over 200 mb so far!
Thanks,
ToddHave you tried BACKUP LOG your_db_name WITH TRUNCATE_ONLY?
How did you try to 'delete' the xaction log?
The other thing you might think about is put the DB
recovery option to SIMPLE (equiv to trunc. log on chkpt).
That way, you won't have to manual delete the xaction log.
hth
-a
>--Original Message--
>How can I delete the transaction logs? Every time I try
it errors saying the
>file isn't empty and I don't know how to empty it! I've
tried everything and
>the log file is over 200 mb so far!
>Thanks,
>Todd
>
>.
>|||If you have multiple log files for this database and you want to 'delete'
(i.e. remove one of them), you can try DBCC SHRINKFILE with the EMPTYFILE
option. This do NOT work with the primary transaction log file, though.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Todd Ellington" <todd@.vtserve.com> wrote in message
news:u7zuupjiDHA.2824@.tk2msftngp13.phx.gbl...
> How can I delete the transaction logs? Every time I try it errors saying
the
> file isn't empty and I don't know how to empty it! I've tried everything
and
> the log file is over 200 mb so far!
> Thanks,
> Todd
>
Wednesday, March 7, 2012
Log backups
Hello,
Im looking for an easy, efficient, inexpensive way to backup the SQL transaction logs. I was thinking about purchasing a DVD burner to back them up to, but I don't know a whole lot about DVD burners or SQL backup for that matter. Will I be able to do this? I would like to have the logs backed up to the DVD every half hour, will a DVD support this type of write/re-write access? Of course in addition to that I plan to have full db backups done once a day to tape, but I can take care of that part. My main concern is with the log files, does anyone know if backing up to a DVD drive like that will work?
Thanks.
NATHAN WALTER
Santa Barbara, CA
Please don't post in HTML.
I think a hard drive would be far easier and cheaper.
How many days/weeks of backups do you expect to need to save?
"Nathan Walter" <nwalter@.fielding.edu> wrote in message
news:eECt22j4EHA.1452@.TK2MSFTNGP11.phx.gbl...
Hello,
Im looking for an easy, efficient, inexpensive way to backup the SQL
transaction logs. I was thinking about purchasing a DVD burner to back them
up to, but I don't know a whole lot about DVD burners or SQL backup for that
matter. Will I be able to do this? I would like to have the logs backed up
to the DVD every half hour, will a DVD support this type of write/re-write
access? Of course in addition to that I plan to have full db backups done
once a day to tape, but I can take care of that part. My main concern is
with the log files, does anyone know if backing up to a DVD drive like that
will work?
Thanks.
NATHAN WALTER
Santa Barbara, CA
|||Well I was hoping to be able to take the logs off site for storage and a
DVD burner would allow me to keep as many as I like for a relatively low
cost. Getting a hard drive is a possibility but its a raid/scsi server
system and a 100 or so gig hard drive would cost about $600.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||"Nathan Walter" <nwalter@.fielding.edu> wrote in message
news:uf%2371el4EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Well I was hoping to be able to take the logs off site for storage and a
> DVD burner would allow me to keep as many as I like for a relatively low
> cost. Getting a hard drive is a possibility but its a raid/scsi server
> system and a 100 or so gig hard drive would cost about $600.
You probably don't want the hard drive directly on the server, if say the
RAID controller goes bad and starts writing bad data, your backup could be
hosed, etc.
In any case, unless you need archival, (i.e. years worth) for some reason,
one option is a pair of portable USB drives. Not super fast, but workable.
Swap one in and one out at a time.
I mean it may be possible to use the DVD drive, but I'd be hesitant to try.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I have never tried to backup directly to a DVD, but it wouldn't take but a second to find out...
If you are going to backup to a disk drive, just buy a cheap IDE drive, and put it on another server somewhere. Backup the logs to the other drive. You could uy 2 drives and swap them out weekly ( depending on how much data you will be backing up).
You could also backup to the IDE drive, THEN copy the backup files to the DVD using normal file copy and backup procedures, then remove the files from the disk drive...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Nathan Walter" <nwalter@.fielding.edu> wrote in message news:eECt22j4EHA.1452@.TK2MSFTNGP11.phx.gbl...
Hello,
Im looking for an easy, efficient, inexpensive way to backup the SQL transaction logs. I was thinking about purchasing a DVD burner to back them up to, but I don't know a whole lot about DVD burners or SQL backup for that matter. Will I be able to do this? I would like to have the logs backed up to the DVD every half hour, will a DVD support this type of write/re-write access? Of course in addition to that I plan to have full db backups done once a day to tape, but I can take care of that part. My main concern is with the log files, does anyone know if backing up to a DVD drive like that will work?
Thanks.
NATHAN WALTER
Santa Barbara, CA
|||You can use ntbackup with http://www.firestreamer.com/fsdvd/. This will
allow you to backup with Volume Shadow Copy directly to DVD.
Im looking for an easy, efficient, inexpensive way to backup the SQL transaction logs. I was thinking about purchasing a DVD burner to back them up to, but I don't know a whole lot about DVD burners or SQL backup for that matter. Will I be able to do this? I would like to have the logs backed up to the DVD every half hour, will a DVD support this type of write/re-write access? Of course in addition to that I plan to have full db backups done once a day to tape, but I can take care of that part. My main concern is with the log files, does anyone know if backing up to a DVD drive like that will work?
Thanks.
NATHAN WALTER
Santa Barbara, CA
Please don't post in HTML.
I think a hard drive would be far easier and cheaper.
How many days/weeks of backups do you expect to need to save?
"Nathan Walter" <nwalter@.fielding.edu> wrote in message
news:eECt22j4EHA.1452@.TK2MSFTNGP11.phx.gbl...
Hello,
Im looking for an easy, efficient, inexpensive way to backup the SQL
transaction logs. I was thinking about purchasing a DVD burner to back them
up to, but I don't know a whole lot about DVD burners or SQL backup for that
matter. Will I be able to do this? I would like to have the logs backed up
to the DVD every half hour, will a DVD support this type of write/re-write
access? Of course in addition to that I plan to have full db backups done
once a day to tape, but I can take care of that part. My main concern is
with the log files, does anyone know if backing up to a DVD drive like that
will work?
Thanks.
NATHAN WALTER
Santa Barbara, CA
|||Well I was hoping to be able to take the logs off site for storage and a
DVD burner would allow me to keep as many as I like for a relatively low
cost. Getting a hard drive is a possibility but its a raid/scsi server
system and a 100 or so gig hard drive would cost about $600.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||"Nathan Walter" <nwalter@.fielding.edu> wrote in message
news:uf%2371el4EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Well I was hoping to be able to take the logs off site for storage and a
> DVD burner would allow me to keep as many as I like for a relatively low
> cost. Getting a hard drive is a possibility but its a raid/scsi server
> system and a 100 or so gig hard drive would cost about $600.
You probably don't want the hard drive directly on the server, if say the
RAID controller goes bad and starts writing bad data, your backup could be
hosed, etc.
In any case, unless you need archival, (i.e. years worth) for some reason,
one option is a pair of portable USB drives. Not super fast, but workable.
Swap one in and one out at a time.
I mean it may be possible to use the DVD drive, but I'd be hesitant to try.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I have never tried to backup directly to a DVD, but it wouldn't take but a second to find out...
If you are going to backup to a disk drive, just buy a cheap IDE drive, and put it on another server somewhere. Backup the logs to the other drive. You could uy 2 drives and swap them out weekly ( depending on how much data you will be backing up).
You could also backup to the IDE drive, THEN copy the backup files to the DVD using normal file copy and backup procedures, then remove the files from the disk drive...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Nathan Walter" <nwalter@.fielding.edu> wrote in message news:eECt22j4EHA.1452@.TK2MSFTNGP11.phx.gbl...
Hello,
Im looking for an easy, efficient, inexpensive way to backup the SQL transaction logs. I was thinking about purchasing a DVD burner to back them up to, but I don't know a whole lot about DVD burners or SQL backup for that matter. Will I be able to do this? I would like to have the logs backed up to the DVD every half hour, will a DVD support this type of write/re-write access? Of course in addition to that I plan to have full db backups done once a day to tape, but I can take care of that part. My main concern is with the log files, does anyone know if backing up to a DVD drive like that will work?
Thanks.
NATHAN WALTER
Santa Barbara, CA
|||You can use ntbackup with http://www.firestreamer.com/fsdvd/. This will
allow you to backup with Volume Shadow Copy directly to DVD.
Friday, February 24, 2012
Log Backup Job works, but reports failure.
All the transaction logs are created, but the Application log entry below
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
>
|||Here's the explanation!
http://support.microsoft.com/default...&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
>
|||Here's the explanation!
http://support.microsoft.com/default...&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
Log Backup Job works, but reports failure.
All the transaction logs are created, but the Application log entry below
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
--
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
----
--Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
--
>|||Here's the explanation!
http://support.microsoft.com/defaul...2&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
--
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
----
--Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
--
>|||Here's the explanation!
http://support.microsoft.com/defaul...2&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
Subscribe to:
Posts (Atom)