Locks and Blocks: error

Showing posts with label error. Show all posts

Friday, March 30, 2012

Log on server error

Hi all i am using crystal report with asp .net.

i am using report files already created an configured with a dsn.

when i try to view the report using crystal report viewer in a web form i am getting the error

"CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed."

i am using

<code>
ConnectionInfo cnnInfo = new ConnectionInfo ( ) ;

cnnInfo.ServerName = strConnInformation[0] ;
cnnInfo.DatabaseName = strConnInformation[1] ;
cnnInfo.UserID = strConnInformation[2] ;
cnnInfo.Password = strConnInformation[3] ;

ReportDocument rptDoc = new ReportDocument ( ) ;
rptDoc.Load ( Server.MapPath ( "..//Reports//" + reportFileName ),
OpenReportMethod.OpenReportByDefault ) ;

//CrystalDecisions.CrystalReports.Engine.Table table = rptDoc.Database.Tables[0] ;

CrystalDecisions.CrystalReports.Engine.Tables rptTables = rptDoc.Database.Tables ;
foreach ( CrystalDecisions.CrystalReports.Engine.Table tbl in rptTables )
{
tblLogOnInfo = tbl.LogOnInfo ;
tblLogOnInfo.ConnectionInfo = cnnInfo ;
tbl.ApplyLogOnInfo ( tblLogOnInfo ) ;

if ( tbl.Location.IndexOf ( "." ) > 0 )
{
tbl.Location = tbl.Location.Substring (
tbl.Location.LastIndexOf(".") + 1 ) ;
}
else
{
//tbl.Location = tbl.Location ;
}
}

</code>

its working fine when i try to execute a report file which configured direct to a database in another machine by specifying the server name, database name, user id and the password,

its showing the error only when i try to execute the report which configured using dsn. dsn is created in my machine and the actual database server is another one.

can any one help me in this matter as i have to use the reports with dsn only in my application.

i got the informations from the net that its same for both type of connectivity to use the ConnectionInfo object and the TableLogonInfo objects.

what my be the problem with me.

is its related to any permision issues.Hai Vinod,

Even i faced the same problem with my C# and Access .
But this prblem arised only when i have a subreport in my main report.

Do u have any subreports in ur appli...If so u have to provide login information to those reports also.

regards,
Kolluru.|||Thanks for your reply kolluru. i will try this and will get back :)|||Do u have any subreports in ur appli...If so u have to provide login information to those reports also.

Im having the same problem and I have subreports. How do you provide the login details for the subreports?sql

Log on problem

Why I can't make change account from Log on tab in properties MSSQLSERVER
service?
Regardless of account, I gett this error: "Could not start the MSSQLSERVER
service on Local Computer.
Error 1096: The service did not start to a logon failure."
SQL Server does work only with Local system account?!
Thanks
(MS SQLserver 2000 , WinXP SP2)You should never change the account directly thru the service. You need to
use Enterprise Manager instead as it sets other rights and properties that
the service panel does not.
Andrew J. Kelly SQL MVP
"ER" <vladoer@.email.t-com.hr> wrote in message
news:dufrvi$rlq$1@.ss405.t-com.hr...
> Why I can't make change account from Log on tab in properties MSSQLSERVER
> service?
> Regardless of account, I gett this error: "Could not start the MSSQLSERVER
> service on Local Computer.
> Error 1096: The service did not start to a logon failure."
> SQL Server does work only with Local system account?!
> Thanks
> (MS SQLserver 2000 , WinXP SP2)
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%235vQSLSQGHA.564@.TK2MSFTNGP12.phx.gbl...
> You should never change the account directly thru the service. You need to
> use Enterprise Manager instead as it sets other rights and properties that
> the service panel does not.
> --
> Andrew J. Kelly SQL MVP
I gett error all the same|||Are there any msgs in the event logs? Does it work with the local admin
account?
Andrew J. Kelly SQL MVP
"ER" <vladoer@.email.t-com.hr> wrote in message
news:duihmd$ru9$1@.ss405.t-com.hr...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%235vQSLSQGHA.564@.TK2MSFTNGP12.phx.gbl...
> I gett error all the same
>|||Andrew J. Kelly wrote:
> You should never change the account directly thru the service. You need to
> use Enterprise Manager instead as it sets other rights and properties that
> the service panel does not.
>
Just curious - which right is it that's being applied though EM and not
in the services panel?
I normally handle all changes to services from the services panel and I
don't recall that I've ever had problems with that.
Regards
Steen|||It depends upon the permissions configured for the new account. For instance
if you use the service panel to change a SQL Server service to run using
some administrator account, you probably won't have a problem. But if you
use the service panel to change a SQL Server service to to run under a low
priveledged user, then SQL Server might not be able to access sqlservr.exe
on the file system (and a bunch of other stuff). But if you use the SQL
Server tools, the tools will check to make sure the new account has adequate
access.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uHsEWEsQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
> Just curious - which right is it that's being applied though EM and not in
> the services panel?
> I normally handle all changes to services from the services panel and I
> don't recall that I've ever had problems with that.
> Regards
> Steen|||I have solved the problem. The difficulty was that I had a User Account
without password in my machine.
Thanks for your answer.|||Rick Byham [MS] wrote:
> It depends upon the permissions configured for the new account. For instan
ce
> if you use the service panel to change a SQL Server service to run using
> some administrator account, you probably won't have a problem. But if you
> use the service panel to change a SQL Server service to to run under a low
> priveledged user, then SQL Server might not be able to access sqlservr.exe
> on the file system (and a bunch of other stuff). But if you use the SQL
> Server tools, the tools will check to make sure the new account has adequa
te
> access.
Ok - that makes sense.
Regards
Steensql

Log on problem

Why I can't make change account from Log on tab in properties MSSQLSERVER
service?
Regardless of account, I gett this error: "Could not start the MSSQLSERVER
service on Local Computer.
Error 1096: The service did not start to a logon failure."
SQL Server does work only with Local system account?!
Thanks
(MS SQLserver 2000 , WinXP SP2)
You should never change the account directly thru the service. You need to
use Enterprise Manager instead as it sets other rights and properties that
the service panel does not.
Andrew J. Kelly SQL MVP
"ER" <vladoer@.email.t-com.hr> wrote in message
news:dufrvi$rlq$1@.ss405.t-com.hr...
> Why I can't make change account from Log on tab in properties MSSQLSERVER
> service?
> Regardless of account, I gett this error: "Could not start the MSSQLSERVER
> service on Local Computer.
> Error 1096: The service did not start to a logon failure."
> SQL Server does work only with Local system account?!
> Thanks
> (MS SQLserver 2000 , WinXP SP2)
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%235vQSLSQGHA.564@.TK2MSFTNGP12.phx.gbl...
> You should never change the account directly thru the service. You need to
> use Enterprise Manager instead as it sets other rights and properties that
> the service panel does not.
> --
> Andrew J. Kelly SQL MVP
I gett error all the same
|||Are there any msgs in the event logs? Does it work with the local admin
account?
Andrew J. Kelly SQL MVP
"ER" <vladoer@.email.t-com.hr> wrote in message
news:duihmd$ru9$1@.ss405.t-com.hr...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%235vQSLSQGHA.564@.TK2MSFTNGP12.phx.gbl...
> I gett error all the same
>
|||Andrew J. Kelly wrote:
> You should never change the account directly thru the service. You need to
> use Enterprise Manager instead as it sets other rights and properties that
> the service panel does not.
>
Just curious - which right is it that's being applied though EM and not
in the services panel?
I normally handle all changes to services from the services panel and I
don't recall that I've ever had problems with that.
Regards
Steen
|||It depends upon the permissions configured for the new account. For instance
if you use the service panel to change a SQL Server service to run using
some administrator account, you probably won't have a problem. But if you
use the service panel to change a SQL Server service to to run under a low
priveledged user, then SQL Server might not be able to access sqlservr.exe
on the file system (and a bunch of other stuff). But if you use the SQL
Server tools, the tools will check to make sure the new account has adequate
access.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uHsEWEsQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
> Just curious - which right is it that's being applied though EM and not in
> the services panel?
> I normally handle all changes to services from the services panel and I
> don't recall that I've ever had problems with that.
> Regards
> Steen
|||I have solved the problem. The difficulty was that I had a User Account
without password in my machine.
Thanks for your answer.
|||Rick Byham [MS] wrote:
> It depends upon the permissions configured for the new account. For instance
> if you use the service panel to change a SQL Server service to run using
> some administrator account, you probably won't have a problem. But if you
> use the service panel to change a SQL Server service to to run under a low
> priveledged user, then SQL Server might not be able to access sqlservr.exe
> on the file system (and a bunch of other stuff). But if you use the SQL
> Server tools, the tools will check to make sure the new account has adequate
> access.
Ok - that makes sense.
Regards
Steen

log in failed for user [servername]\ASPNET

can anyone help me with this error message, i follow the instruction of someone in other forum, but i cant find where the SQL Server Enterprise manager is, i am using MSDE.One way To see SQL Server Enterprise manager is to install SQL server :)|||If you don't have Enterprise Manage, you can use the command-line utility OSQL that comes with SQL Server.

Go to the command prompt and type in:

osql -S [servername]\instancename -E -q

You will have a 1> prompt.


use yourdatabasename
goEXEC sp_grantlogin '[servername]\ASPNET'
go
EXEC sp_grantdbaccess '[servername]\ASPNET''
go

You can also read this post by David Wier:Error Using Trusted Connection in DB Connection String. I don't recommend adding the ASPNET account as a db_owner, however, so I'd leave off that command at the end.

Terri

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

Log Import Error

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

Log full alert

Hi,
I want to create a job that will raise an error case Log is getting almost
full.
Is there a sp (or any other tool) that can assist?
Thanks, Hagay.Hi all,
I've found a way to extract log precentage usage:
master db has a table msysperinfo which holds (among other detail) log
precentage use of each database.
so the following query will give you what you need:
select cntr_value
from msysperfinfo
where counter_name = 'Percent Log Used'
and instance_name = @.databaseName
"Hagay Lupesko" <hagayl@.nice.com> wrote in message
news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to create a job that will raise an error case Log is getting almost
> full.
> Is there a sp (or any other tool) that can assist?
> Thanks, Hagay.
>
>|||Maybe you have ran a script to create that table as I don't think it is a
standard table, you could do
dbcc sqlperf('logspace')
to get log information and space used/free, from there you could throw that
to a table and check for percentage used to send an alert.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Hagay Lupesko" <hagayl@.nice.com> wrote in message
news:ezOTZOOkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I've found a way to extract log precentage usage:
> master db has a table msysperinfo which holds (among other detail) log
> precentage use of each database.
> so the following query will give you what you need:
> select cntr_value
> from msysperfinfo
> where counter_name = 'Percent Log Used'
> and instance_name = @.databaseName
> "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I want to create a job that will raise an error case Log is getting
almost
> > full.
> > Is there a sp (or any other tool) that can assist?
> >
> > Thanks, Hagay.
> >
> >
> >
>|||The counter in the sysperfinfo table and the value from DBCC
SQLPERF(LOGSPACE) are essentially the same. You can use these values to
alert you on a certain log usage threhold (e.g. 85%), if your log files are
not set to autogrow.
However, if one of your log files is set to autogrow, these values will
often give false alarms. For instance, your log might be 90% used, but if
you still have 10GB of free space of the drive on which the log file is set
to autogrow, you may not be too pleased to be paged at 3 AM.
In short, your script needs to consider the autogrow property, and it can
become rather complex if you have multiple log files on different drives.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:uQljKlOkDHA.2416@.TK2MSFTNGP10.phx.gbl...
> Maybe you have ran a script to create that table as I don't think it is a
> standard table, you could do
> dbcc sqlperf('logspace')
> to get log information and space used/free, from there you could throw
that
> to a table and check for percentage used to send an alert.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> news:ezOTZOOkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Hi all,
> >
> > I've found a way to extract log precentage usage:
> >
> > master db has a table msysperinfo which holds (among other detail) log
> > precentage use of each database.
> >
> > so the following query will give you what you need:
> >
> > select cntr_value
> > from msysperfinfo
> > where counter_name = 'Percent Log Used'
> > and instance_name = @.databaseName
> >
> > "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> > news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I want to create a job that will raise an error case Log is getting
> almost
> > > full.
> > > Is there a sp (or any other tool) that can assist?
> > >
> > > Thanks, Hagay.
> > >
> > >
> > >
> >
> >
>|||Just let Agent do this using a performance condition alert!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hagay Lupesko" <hagayl@.nice.com> wrote in message news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to create a job that will raise an error case Log is getting almost
> full.
> Is there a sp (or any other tool) that can assist?
> Thanks, Hagay.
>
>

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!
>

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!
>

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!
>

Monday, March 26, 2012

log files

when size of my lof file become full than it shows the error....so for this i manully delete the content of my log file...than it does not show the error....can anybody tell me any better way to avoid manual deletion...

thanx a lot

Right click the database go to Tasks->Shrink -> Files, select the log option and give a very small size.

after that run this command,

BACKUP LOG <db name> with TRUNCATE_ONLY

your transactional log size will be reduced in this manner.

|||hi thanx for reply....u mean i have to do what u said each time ...here also we are doing manual deletion....any automatic way to do this ?|||You can by developing a SSIS package with a Shrink Database task, call it using a SQL Server agent job and then schedule it as your need be.|||after right click it is showing shrink option disabled.why?|||The options I had said is for SSMS 2005. What are you using ?|||sqlserver 2000|||

Check if this helps....

http://support.microsoft.com/default.aspx/kb/272318

|||

Frequently backup you database and log.This is the way you can keep the log from growing.

Log files

I am getting the error message ,
"Could not allocate space for object '(SYSTEM table id: -1021390423)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full."
when i run a select stmt. I am thinking it's because my log file is full.
Can you pls tell me what i am supposed to do now?Read the error message closely, as it explains the problem very well. The pr
oblem is the tempdb
database. It is not the transaction log for tempdb, it is the database files
. They are not large
enough, and sometimes autogrow doesn't grow fast enough for the space needed
by the query
processing. Either pre.allocate storage for tempdb, or tweak the query (look
at query plan, add
indexes, modify the SQL etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>I am getting the error message ,
> "Could not allocate space for object '(SYSTEM table id: -1021390423)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full."
> when i run a select stmt. I am thinking it's because my log file is full.
> Can you pls tell me what i am supposed to do now?|||How do I make them grow?
"Tibor Karaszi" wrote:

> Read the error message closely, as it explains the problem very well. The
problem is the tempdb
> database. It is not the transaction log for tempdb, it is the database fil
es. They are not large
> enough, and sometimes autogrow doesn't grow fast enough for the space need
ed by the query
> processing. Either pre.allocate storage for tempdb, or tweak the query (lo
ok at query plan, add
> indexes, modify the SQL etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>
>|||Should i go to properties,data files and increase the size for space
allocated? Pls reply. Thanks in advance.
"PH" wrote:
> How do I make them grow?
> "Tibor Karaszi" wrote:
>|||Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:BA24839D-8D6E-4263-999A-2CF015CFAAA0@.microsoft.com...
> Should i go to properties,data files and increase the size for space
> allocated? Pls reply. Thanks in advance.
> "PH" wrote:
>

Wednesday, March 21, 2012

Log File path in Configuration file does not work.

Hi,

I cannot get the log file path read from the configuration.

If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?

Here is the portion of the config file. Everthing else is read from the config file correctly.

- <Configuration ConfiguredType="Property" Path="\Package.Connections[SalesforceConnectLog].Properties[ConnectionString]" ValueType="String"> ConfiguredValue>D:\APPS\BACIAR\SalesforceConnect\SalesforceConnect.log</ConfiguredValue>

</Configuration>

Appreciate a help.

Gulden

I'm not sure about this but it COULD be that the log fails validation before the config gets applied. Try setting it in the package to c:\temp\SalesforceConnect.log or something similar and see what happens.

-Jamie

|||

Jamie,

It worked..

I gather from here then, the path in the Connection Manager should be valid for the target environment. Only then, the real path is being read from the config file.

Thanks a lot....

Gulden

|||

Yeah, sort of. Its an unfortunate consequence of the order in which things occur when the package spins up. It seems the log file location needs to be a valid "place" even though its never actually used - that's why I suggested something as arbitrary as "c:\temp".

-Jamie

|||

What is interesting is, I have Excel file connections in the same package with paths invalid for the target environment. Those work fine... Valid paths are read from the config.

Thanks again..

Gulden

|||

Gulden wrote:

What is interesting is, I have Excel file connections in the same package with paths invalid for the target environment. Those work fine... Valid paths are read from the config.
Thanks again..
Gulden

Yeah but that's not really comparing apples with apples. A connection manager used by a logfile is used differently (i.e. alot more) than others. Also its a different type of connection manager.

I haven't given a very good answer to this thread because frankly, I don't know what the proper answer is. At least it works now anyway.

-Jamie

Monday, March 19, 2012

Log File is Full error: How to prevent this happening?

"The log file for database is full. Back up the transaction log for
the database to free up some log space."

Now I only know this way to deal with that manually,

Step1. in option , chance Recovery model from FULL to Simple.
Step2: go to task to manually shrink the log file
Step3: Change recovery model back from simple to FULL.

But by this way, I could get same problem again, the log file is fill,
and need free up.

Could you give an idea how to prevent this from happening? what and
how should I do?

Thanks a lot in advance for your help.(danceli@.gmail.com) writes:

Quote:

Originally Posted by

"The log file for database is full. Back up the transaction log for
the database to free up some log space."
>
Now I only know this way to deal with that manually,
>
Step1. in option , chance Recovery model from FULL to Simple.
Step2: go to task to manually shrink the log file
Step3: Change recovery model back from simple to FULL.
>
But by this way, I could get same problem again, the log file is fill,
and need free up.
>
Could you give an idea how to prevent this from happening? what and
how should I do?

First: decide what recovery mechanism do you need. Second: implement it.

Do you need up-to-the-point recovery? That is, if the database goes
belly-up, or somewhat makes a fatal mistake, are you content with
restoring the latest backup? Or do you want recovery as close to
the accident as possible. If you are content with the latest backup,
go for simple, and all you need to make sure is that you back up the
database regularly.

If you want up-to-the-point-recovery, the you need FULL recovery mode.
You also need to backup your transaction log regularly. How often
depends on the transaction rate in your database. If you never backup
the transaction log, it will outgrow the disk eventually. Note also that
by switching to simple recovery, you break the log chain, and the
ability to up-to-the-point recovery.

Also with full recovery, you need to backup your database regularly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||danc...
This can also be an application issue, I often encounter situations
where delevelopers do not batch their transactions, and thus do not
give the log a chance to truncate, if this is an inhouse app; updates
and deletes should be done in batches. The size of the batch should be
set by the ideal size of your transaction log, the size of the db
etc.. Also, be sure to dump the log periodically, typically I dump the
log every 15 minutes (when the db is in full recovery mode). This will
also assist in controlling the size of the log.
Cheers,
G|||GZ (gerald.zimmerman@.gmail.com) writes:

Quote:

Originally Posted by

This can also be an application issue, I often encounter situations
where delevelopers do not batch their transactions, and thus do not
give the log a chance to truncate, if this is an inhouse app; updates
and deletes should be done in batches. The size of the batch should be
set by the ideal size of your transaction log, the size of the db
etc.. Also, be sure to dump the log periodically, typically I dump the
log every 15 minutes (when the db is in full recovery mode). This will
also assist in controlling the size of the log.

I assume that you are thinking of is the case where you update, insert
or delete millions of rows in one go, and that in such case it may be
better to break up the operation in batches of say 500000 rows. Yes,
this can be a good idea. But it has to implemented in such a way that
if the operation fails half-way, the database is still consistent.

log file in USE

I am using the following command but it gave me an error that the log file
is in Use
alter database Aspstate set RESTRICTED_USER with ROLLBACK IMMEDIATE
Go
DBCC SHRINKFILE(Aspstate_Log, EMPTYFILE)
Go
any idea what I have to do?
Thanks
Noor
Hi,
Can you take the transaction log backup and try shrinking the transaction
log file.
Backup log <dbname > to disk='d:\backup\dbname.trn'
After that try shrinking the file using the comand:-
DBCC SHRINKFILE(Aspstate_Log, EMPTYFILE)
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:exrHGdWdEHA.1648@.TK2MSFTNGP11.phx.gbl...
> I am using the following command but it gave me an error that the log file
> is in Use
> alter database Aspstate set RESTRICTED_USER with ROLLBACK IMMEDIATE
> Go
> DBCC SHRINKFILE(Aspstate_Log, EMPTYFILE)
> Go
> any idea what I have to do?
> Thanks
> Noor
>
|||Thanks HARI
Noor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ert9hRYdEHA.3988@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> Can you take the transaction log backup and try shrinking the transaction
> log file.
> Backup log <dbname > to disk='d:\backup\dbname.trn'
> After that try shrinking the file using the comand:-
> DBCC SHRINKFILE(Aspstate_Log, EMPTYFILE)
> Thanks
> Hari
> MCDBA
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:exrHGdWdEHA.1648@.TK2MSFTNGP11.phx.gbl...
file
>

Log file grows (Error 9002).

Hello Group,
We have a project where we store the state of object instances in Sql Server
tables. Primarely these tables consist of an id column and an IMAGE type
column. We use .NET binary serialization to create byte arrays and use
(ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
The size of a binary array is approximately 300-350k. The database is set to
automatically grow the data en log files. The recovery model is set to
SIMPLE. Our problem is that the log file grows (huge!) until error 9002 shows
up. Backing up the log file helps, but... what is caution this error? Are we
using the wrong CRUD
statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
to see that the log file grows (why isn't Sql reclaiming the used (old)
space): am I missing the point of the SIMPLE recovery model?
(btw: I'm pretty sure there are no transactions 'hanging')
Many thanks in advance!
Kind regards,
Johan Bouwhuis.Try issuing Checkpoint through the application or whenever a heavy
transaction is applied...
"Johan Bouwhuis" wrote:
> Hello Group,
> We have a project where we store the state of object instances in Sql Server
> tables. Primarely these tables consist of an id column and an IMAGE type
> column. We use .NET binary serialization to create byte arrays and use
> (ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
> System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
> The size of a binary array is approximately 300-350k. The database is set to
> automatically grow the data en log files. The recovery model is set to
> SIMPLE. Our problem is that the log file grows (huge!) until error 9002 shows
> up. Backing up the log file helps, but... what is caution this error? Are we
> using the wrong CRUD
> statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
> to see that the log file grows (why isn't Sql reclaiming the used (old)
> space): am I missing the point of the SIMPLE recovery model?
> (btw: I'm pretty sure there are no transactions 'hanging')
> Many thanks in advance!
> Kind regards,
> Johan Bouwhuis.
>|||Hi,
Since the recover for your database is SIMPLE, the transction will be
cleared after each recovery interval. In your case looks like you
are doing a bulk DML operation. In this case the coomit will be done only
after completing the entire operation. To overcome this
instead of doing bulk DML operation do a batch by batch DML operation. THis
will ensure that your LDF will not grow to a higher
extend.
In SIMPLE recovery the log will be cleared automatically and you can not
perform a transaction log backup. If it is a production server then
it is recommened to go for FULL recovery model and schedule a Transaction
log backup. This will help you to recover the database fully/POINT IN TIME.
Thanks
Hari
SQL Server MVP
"Johan Bouwhuis" <JohanBouwhuis@.discussions.microsoft.com> wrote in message
news:E60F4B7A-430D-4E85-94AD-EC41A6187DE9@.microsoft.com...
> Hello Group,
> We have a project where we store the state of object instances in Sql
> Server
> tables. Primarely these tables consist of an id column and an IMAGE type
> column. We use .NET binary serialization to create byte arrays and use
> (ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
> System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
> The size of a binary array is approximately 300-350k. The database is set
> to
> automatically grow the data en log files. The recovery model is set to
> SIMPLE. Our problem is that the log file grows (huge!) until error 9002
> shows
> up. Backing up the log file helps, but... what is caution this error? Are
> we
> using the wrong CRUD
> statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
> to see that the log file grows (why isn't Sql reclaiming the used (old)
> space): am I missing the point of the SIMPLE recovery model?
> (btw: I'm pretty sure there are no transactions 'hanging')
> Many thanks in advance!
> Kind regards,
> Johan Bouwhuis.
>

Log File Full Stored Procedure Error

Does anyone know exactly what behaviour is exhibited when running a
stored procedure and the log file fills up while the procedure is still
running?
I am seeing, through a created history table, a stored procedure that
began to run (by the fact that there is a row in the history table) but
not finish correctly. It is being called by VB and no user is
reporting that VB is erroring out.
Checking some of the SQL log files, the times the SQL proc bombed is
around the same time the SQL transaction log had to be manually dumped
because it was full.

Thanks,(steven.cooper@.infocision.com) writes:
> Does anyone know exactly what behaviour is exhibited when running a
> stored procedure and the log file fills up while the procedure is still
> running?

Sounds like that stored procedure is making a lot of updates, and eventually
the log fills up. Is your database running in simple recovery mode, or
in bulk-logged/full mode?

> I am seeing, through a created history table, a stored procedure that
> began to run (by the fact that there is a row in the history table) but
> not finish correctly. It is being called by VB and no user is
> reporting that VB is erroring out.

Looks like you should check whethert the error handling in the VB is
appropriate.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Log File Full -> Back up Transaction Log

Hi.
I get the following error message occassionally,
"The log file for database 'TSBASE' is full. Back up the transaction log for
the database to free up some log space."
I'll backup the transaction log, and the database immediately. But after a
while (a few weeks), the error comes out again.
Why is there such an error? It seems like no matter how large I assign the
size of the transaction log to be, I'll still get this error one day. How
occassionally do I need to backup the transaction log? Is backing up the
transaction log the only solution? Will it come to a day whereby the log file
gets really so full that even backing up won't solve the problem?
Thank you in advance.This is a multi-part message in MIME format.
--000300010805090603020106
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
You need to do a bit of reading on basic database technology & concepts.
A transaction log is a record of all change to a database. So, every
time you change a database in any way (changing, adding, deleting data,
building indexes, dropping tables, adding new users, etc., etc.) the
transaction log will grow. The transaction log is a *very *important
part of a production database server.
Typically DBAs backup their production logs very regularly. If you
don't and you have some kind of failure on your database server
necessitating recovery from a backup, then you're going to lose vast
amounts of data. For example, I backup the transaction logs for all my
production databases every 15 minutes (with a full backup nightly) so,
in theory, I lose at most 15 minutes worth of data in the case of a disk
failure or database corruption of some sort. That's a very common thing
to do and 15 minutes, while it suits my situation but may not be right
for others, is also quite a common log backup interval. Some backup
their logs more often (every 5 minutes or less). Others backup their
logs less frequently (once an hour, once a day, etc.)
Timing all depends on your overall backup strategy. You must have one
of these - this is important.
If you're not interested in keeping incremental changes to a database
and you simply want to do full backups periodically then you can
automatically truncate the transaction log on every checkpoint
(typically every 30-120 seconds). This will keep the log from growing
very large (only a couple minutes transactions will be recorded before
getting truncated). You do this in SQL Server by changing the *recovery
model* of the database to SIMPLE with the ALTER DATABASE statement as
follows:
ALTER DATABASE TSBASE set RECOVERY SIMPLE
However, this will invalidate the transaction log and you will not be
able to backup the transaction log while in SIMPLE recovery mode.
(Actually you can backup the transaction log while in this mode but the
backups will be useless for recovery purposes because the log on those
backups will be invalid.)
For more information on transaction logs you could start with SQL Books
Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6xwz.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
wrytat wrote:
>Hi.
>I get the following error message occassionally,
>"The log file for database 'TSBASE' is full. Back up the transaction log for
>the database to free up some log space."
>I'll backup the transaction log, and the database immediately. But after a
>while (a few weeks), the error comes out again.
>Why is there such an error? It seems like no matter how large I assign the
>size of the transaction log to be, I'll still get this error one day. How
>occassionally do I need to backup the transaction log? Is backing up the
>transaction log the only solution? Will it come to a day whereby the log file
>gets really so full that even backing up won't solve the problem?
>Thank you in advance.
>
--000300010805090603020106
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You need to do a bit of reading on basic database technology &
concepts. 
 
A transaction log is a record of all change to a database.Â So, every
time you change a database in any way (changing, adding, deleting data,
building indexes, dropping tables, adding new users, etc., etc.) the
transaction log will grow.Â The transaction log is a very important
part of a production database server. 
 
Typically DBAs backup their production logs very regularly.Â If you
don't and you have some kind of failure on your database server
necessitating recovery from a backup, then you're going to lose vast
amounts of data.Â For example, I backup the transaction logs for all my
production databases every 15 minutes (with a full backup nightly) so,
in theory, I lose at most 15 minutes worth of data in the case of a
disk failure or database corruption of some sort.Â That's a very common
thing to do and 15 minutes, while it suits my situation but may not be
right for others, is also quite a common log backup interval.Â Some
backup their logs more often (every 5 minutes or less).Â Others backup
their logs less frequently (once an hour, once a day, etc.) 
 
Timing all depends on your overall backup strategy.Â You must have one
of these - this is important. 
 
If you're not interested in keeping incremental changes to a database
and you simply want to do full backups periodically then you can
automatically truncate the transaction log on every checkpoint
(typically every 30-120 seconds).Â This will keep the log from growing
very large (only a couple minutes transactions will be recorded before
getting truncated).Â You do this in SQL Server by changing the recovery
model of the database to SIMPLE with the ALTER DATABASE statement
as follows: 
</tt>
<blockquote><tt>ALTER DATABASE TSBASE set RECOVERY SIMPLE 
</tt></blockquote>
<tt>However, this will invalidate the transaction log and you will not
be able to backup the transaction log while in SIMPLE recovery mode.Â
(Actually you can backup the transaction log while in this mode but the
backups will be useless for recovery purposes because the log on those
backups will be invalid.) 
 
For more information on transaction logs you could start with SQL Books
Online: 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6xwz.asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6xwz.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6xwz.asp</a> 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
wrytat wrote:
<blockquote cite="mid2629587A-6C8D-4A63-AB5E-ECAC182CDEAC@.microsoft.com"
type="cite">
<pre wrap="">Hi.
I get the following error message occassionally,
"The log file for database 'TSBASE' is full. Back up the transaction log for
the database to free up some log space."
I'll backup the transaction log, and the database immediately. But after a
while (a few weeks), the error comes out again.
Why is there such an error? It seems like no matter how large I assign the
size of the transaction log to be, I'll still get this error one day. How
occassionally do I need to backup the transaction log? Is backing up the
transaction log the only solution? Will it come to a day whereby the log file
gets really so full that even backing up won't solve the problem?
Thank you in advance.
</pre>
</blockquote>
</body>
</html>
--000300010805090603020106--|||Thank you for the explanation. Does backing up the transaction log reduce the
size of the log file then?
"Mike Hodgson" wrote:
> You need to do a bit of reading on basic database technology & concepts.
> A transaction log is a record of all change to a database. So, every
> time you change a database in any way (changing, adding, deleting data,
> building indexes, dropping tables, adding new users, etc., etc.) the
> transaction log will grow. The transaction log is a *very *important
> part of a production database server.
> Typically DBAs backup their production logs very regularly. If you
> don't and you have some kind of failure on your database server
> necessitating recovery from a backup, then you're going to lose vast
> amounts of data. For example, I backup the transaction logs for all my
> production databases every 15 minutes (with a full backup nightly) so,
> in theory, I lose at most 15 minutes worth of data in the case of a disk
> failure or database corruption of some sort. That's a very common thing
> to do and 15 minutes, while it suits my situation but may not be right
> for others, is also quite a common log backup interval. Some backup
> their logs more often (every 5 minutes or less). Others backup their
> logs less frequently (once an hour, once a day, etc.)
> Timing all depends on your overall backup strategy. You must have one
> of these - this is important.
> If you're not interested in keeping incremental changes to a database
> and you simply want to do full backups periodically then you can
> automatically truncate the transaction log on every checkpoint
> (typically every 30-120 seconds). This will keep the log from growing
> very large (only a couple minutes transactions will be recorded before
> getting truncated). You do this in SQL Server by changing the *recovery
> model* of the database to SIMPLE with the ALTER DATABASE statement as
> follows:
> ALTER DATABASE TSBASE set RECOVERY SIMPLE
> However, this will invalidate the transaction log and you will not be
> able to backup the transaction log while in SIMPLE recovery mode.
> (Actually you can backup the transaction log while in this mode but the
> backups will be useless for recovery purposes because the log on those
> backups will be invalid.)
> For more information on transaction logs you could start with SQL Books
> Online:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6xwz.asp
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
>
> wrytat wrote:
> >Hi.
> >
> >I get the following error message occassionally,
> >"The log file for database 'TSBASE' is full. Back up the transaction log for
> >the database to free up some log space."
> >
> >I'll backup the transaction log, and the database immediately. But after a
> >while (a few weeks), the error comes out again.
> >
> >Why is there such an error? It seems like no matter how large I assign the
> >size of the transaction log to be, I'll still get this error one day. How
> >occassionally do I need to backup the transaction log? Is backing up the
> >transaction log the only solution? Will it come to a day whereby the log file
> >gets really so full that even backing up won't solve the problem?
> >
> >Thank you in advance.
> >
> >
>|||No. It just means the space can be re-used. It's not a good idea to
keep shrinking the transaction log because shrinking per se won't
reduce the storage requirement and auto-growing it again is a very
expensive operation. The way to control log file usage is to perform
regular transaction log backups and allocate a fixed, sustainable size
to the log.
--
David Portas
SQL Server MVP
--|||Yes Sir, Its not advisable to use
dbcc shrinkfile
or
backup log 'dbname' with truncate_only -- Will truncate Log File
but as if problem is running out of space then at this moment it need to
shrink the file ?!! M i right ?
"David Portas" wrote:
> No. It just means the space can be re-used. It's not a good idea to
> keep shrinking the transaction log because shrinking per se won't
> reduce the storage requirement and auto-growing it again is a very
> expensive operation. The way to control log file usage is to perform
> regular transaction log backups and allocate a fixed, sustainable size
> to the log.
> --
> David Portas
> SQL Server MVP
> --
>|||This is a multi-part message in MIME format.
--000303070709010108040507
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
It's only running out of disk space because the original poster never
backs up the transaction log. If he just sets up a backup scheduled (or
sets the recovery mode to simple) then he shouldn't have any worries.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hemant Goswami wrote:
>Yes Sir, Its not advisable to use
>dbcc shrinkfile
>or
>backup log 'dbname' with truncate_only -- Will truncate Log File
>but as if problem is running out of space then at this moment it need to
>shrink the file ?!! M i right ?
>"David Portas" wrote:
>
>>No. It just means the space can be re-used. It's not a good idea to
>>keep shrinking the transaction log because shrinking per se won't
>>reduce the storage requirement and auto-growing it again is a very
>>expensive operation. The way to control log file usage is to perform
>>regular transaction log backups and allocate a fixed, sustainable size
>>to the log.
>>--
>>David Portas
>>SQL Server MVP
>>--
>>
>>
--000303070709010108040507
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It's only running out of disk space because the original poster
never backs up the transaction log.Â If he just sets up a backup
scheduled (or sets the recovery mode to simple) then he shouldn't have
any worries.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Hemant Goswami wrote:
<blockquote cite="mid70310EFD-8DFC-4212-A588-EF601607BD2E@.microsoft.com"
type="cite">
<pre wrap="">Yes Sir, Its not advisable to use
dbcc shrinkfile
or
backup log 'dbname' with truncate_only -- Will truncate Log File
but as if problem is running out of space then at this moment it need to
shrink the file ?!! M i right ?
"David Portas" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">No. It just means the space can be re-used. It's not a good idea to
keep shrinking the transaction log because shrinking per se won't
reduce the storage requirement and auto-growing it again is a very
expensive operation. The way to control log file usage is to perform
regular transaction log backups and allocate a fixed, sustainable size
to the log.
--
David Portas
SQL Server MVP
--
</pre>
</blockquote>
</blockquote>
</body>
</html>
--000303070709010108040507--|||This is a multi-part message in MIME format.
--=_NextPart_000_0101_01C571B2.66267180
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
I agree with Mike, that if you have a proper backup strategy, the =problem should never occour. Ther are cases though where I feel it's ok =to truncate the log file, and that's if there has been some unususal =activity in the database. If you e.g. has merged 2 databases, that might =have made the logfile grow, but since this might be a "once in a =lifetime" thing that most likely won't happen again, it might be ok to =tshrink the logfile back to a decent size.
Regards
Steen
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> skrev i en =meddelelse news:eBnOiOZcFHA.3712@.TK2MSFTNGP12.phx.gbl...
It's only running out of disk space because the original poster never =backs up the transaction log. If he just sets up a backup scheduled (or =sets the recovery mode to simple) then he shouldn't have any worries.
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Hemant Goswami wrote: Yes Sir, Its not advisable to use
dbcc shrinkfile or
backup log 'dbname' with truncate_only -- Will truncate Log File
but as if problem is running out of space then at this moment it need to =
shrink the file ?!! M i right ?
"David Portas" wrote:
No. It just means the space can be re-used. It's not a good idea to
keep shrinking the transaction log because shrinking per se won't
reduce the storage requirement and auto-growing it again is a very
expensive operation. The way to control log file usage is to perform
regular transaction log backups and allocate a fixed, sustainable size
to the log.
-- David Portas SQL Server MVP --
--=_NextPart_000_0101_01C571B2.66267180
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I agree with Mike, that if you have a =proper backup strategy, the problem should never occour. Ther are cases though where I =feel it's ok to truncate the log file, and that's if there has been some =unususal activity in the database. If you e.g. has merged 2 databases, that might =have made the logfile grow, but since this might be a "once in a lifetime" =thing that most likely won't happen again, it might be ok to tshrink the logfile =back to a decent size.
Regards
Steen
"Mike Hodgson" skrev i en meddelelse news:eBnOiOZcFHA.3712=@.TK2MSFTNGP12.phx.gbl...It's only running out of disk space because the original poster never backs =up the transaction log. If he just sets up a backup scheduled (or sets =the recovery mode to simple) then he shouldn't have any worries.
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Hemant Goswami wrote: Yes Sir, Its not advisable to use
dbcc shrinkfile or
backup log 'dbname' with truncate_only -- Will truncate Log File
but as if problem is running out of space then at this moment it need to =shrink the file ?!! M i right ?
"David Portas" wrote:
No. It just means the space =can be re-used. It's not a good idea to
keep shrinking the transaction log because shrinking per se won't
reduce the storage requirement and auto-growing it again is a very
expensive operation. The way to control log file usage is to perform
regular transaction log backups and allocate a fixed, sustainable size
to the log.
-- David Portas SQL Server MVP --

--=_NextPart_000_0101_01C571B2.66267180--

Friday, March 30, 2012

Log on server error

Log on problem

Log on problem

log in failed for user [servername]\ASPNET

Wednesday, March 28, 2012

Log Import Error

Log Import Error

Log full alert

Log for tempdb is full

Log for tempdb is full

Log for tempdb is full

Monday, March 26, 2012

log files

Log files

Wednesday, March 21, 2012

Log File path in Configuration file does not work.

Monday, March 19, 2012

Log File is Full error: How to prevent this happening?

log file in USE

Log file grows (Error 9002).

Log File Full Stored Procedure Error

Log File Full -> Back up Transaction Log

Log File Full -> Back up Transaction Log

Log File Full -> Back up Transaction Log

Locks and Blocks

Blog Archive

About Me