sql report manager: transaction

Showing posts with label transaction. Show all posts

Sunday, March 25, 2012

Can't start MSDTC

I can't start Distributed Transaction Coordinator (MSDTC)
I have following entries in event log
Event Type: Information
Event Source: MSDTC
Event Category: TM
Event ID: 53283
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: Some
Description:
DTC Security Configuration values. Network Administration of Transactions =
1, Network Clients = 0, Distributed Transactions using Native MSDTC
Protocol = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions = 1.
(OFF = 0 and ON = 1).
Event Type: Error
Event Source: MSDTC
Event Category: LOG
Event ID: 4163
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: some
Description:
MS DTC log file not found. After ensuring that all Resource Managers
coordinated by MS DTC have no indoubt transactions, please run
msdtc -resetlog to create the log file.
[I have tried it and with no success]
Event Type: Error
Event Source: MSDTC
Event Category: SVC
Event ID: 4437
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: Some
Description:
The account that the MS DTC service is running under is invalid. This can
happen if the service account information has been changed using the
Services snap-in in Microsoft Management Console (MMC). MS DTC service will
continue to start. Please make sure that the MS DTC service account
information is updated using the Component Services Explorer.
How can I fix it?
It seems that because of it I can't install SQL Server 2005 Express.
If it is wrong newsgroup maybe someone can redirect me to the right one.
Thanks,
Shimon.
Go to Control panel->services or Administrative Tools-> services and find
the MS DTC service. There will be a properties box where you can change the
login/password under which dtc run... The first error message states that
the login/password is bad... (Maybe someone changed the password for the DTC
login...)
Hope this helps.
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
"Shimon Sim" <estshim@.att.net> wrote in message
news:ufa0SmIZEHA.728@.TK2MSFTNGP09.phx.gbl...
> I can't start Distributed Transaction Coordinator (MSDTC)
> I have following entries in event log
>
> Event Type: Information
> Event Source: MSDTC
> Event Category: TM
> Event ID: 53283
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: Some
> Description:
> DTC Security Configuration values. Network Administration of Transactions
=
> 1, Network Clients = 0, Distributed Transactions using Native MSDTC
> Protocol = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions =
1.
> (OFF = 0 and ON = 1).
> Event Type: Error
> Event Source: MSDTC
> Event Category: LOG
> Event ID: 4163
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: some
> Description:
> MS DTC log file not found. After ensuring that all Resource Managers
> coordinated by MS DTC have no indoubt transactions, please run
> msdtc -resetlog to create the log file.
> [I have tried it and with no success]
> Event Type: Error
> Event Source: MSDTC
> Event Category: SVC
> Event ID: 4437
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: Some
> Description:
> The account that the MS DTC service is running under is invalid. This can
> happen if the service account information has been changed using the
> Services snap-in in Microsoft Management Console (MMC). MS DTC service
will
> continue to start. Please make sure that the MS DTC service account
> information is updated using the Component Services Explorer.
> How can I fix it?
> It seems that because of it I can't install SQL Server 2005 Express.
> If it is wrong newsgroup maybe someone can redirect me to the right one.
> Thanks,
> Shimon.
>
>
|||Is a local account option is good for this?
Shimon
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:O3zSJSOZEHA.3476@.tk2msftngp13.phx.gbl...
> Go to Control panel->services or Administrative Tools-> services and find
> the MS DTC service. There will be a properties box where you can change
the
> login/password under which dtc run... The first error message states that
> the login/password is bad... (Maybe someone changed the password for the
DTC[vbcol=seagreen]
> login...)
> Hope this helps.
> --
> 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
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:ufa0SmIZEHA.728@.TK2MSFTNGP09.phx.gbl...
Transactions[vbcol=seagreen]
> =
=[vbcol=seagreen]
> 1.
can
> will
>

Can't start MSDTC

I can't start Distributed Transaction Coordinator (MSDTC)
I have following entries in event log
Event Type: Information
Event Source: MSDTC
Event Category: TM
Event ID: 53283
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: Some
Description:
DTC Security Configuration values. Network Administration of Transactions =
1, Network Clients = 0, Distributed Transactions using Native MSDTC
Protocol = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions = 1.
(OFF = 0 and ON = 1).
Event Type: Error
Event Source: MSDTC
Event Category: LOG
Event ID: 4163
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: some
Description:
MS DTC log file not found. After ensuring that all Resource Managers
coordinated by MS DTC have no indoubt transactions, please run
msdtc -resetlog to create the log file.
[I have tried it and with no success]
Event Type: Error
Event Source: MSDTC
Event Category: SVC
Event ID: 4437
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: Some
Description:
The account that the MS DTC service is running under is invalid. This can
happen if the service account information has been changed using the
Services snap-in in Microsoft Management Console (MMC). MS DTC service will
continue to start. Please make sure that the MS DTC service account
information is updated using the Component Services Explorer.
How can I fix it?
It seems that because of it I can't install SQL Server 2005 Express.
If it is wrong newsgroup maybe someone can redirect me to the right one.
Thanks,
Shimon.Go to Control panel->services or Administrative Tools-> services and find
the MS DTC service. There will be a properties box where you can change the
login/password under which dtc run... The first error message states that
the login/password is bad... (Maybe someone changed the password for the DTC
login...)
Hope this helps.
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
"Shimon Sim" <estshim@.att.net> wrote in message
news:ufa0SmIZEHA.728@.TK2MSFTNGP09.phx.gbl...
> I can't start Distributed Transaction Coordinator (MSDTC)
> I have following entries in event log
>
> Event Type: Information
> Event Source: MSDTC
> Event Category: TM
> Event ID: 53283
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: Some
> Description:
> DTC Security Configuration values. Network Administration of Transactions
=
> 1, Network Clients = 0, Distributed Transactions using Native MSDTC
> Protocol = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions =
1.
> (OFF = 0 and ON = 1).
> Event Type: Error
> Event Source: MSDTC
> Event Category: LOG
> Event ID: 4163
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: some
> Description:
> MS DTC log file not found. After ensuring that all Resource Managers
> coordinated by MS DTC have no indoubt transactions, please run
> msdtc -resetlog to create the log file.
> [I have tried it and with no success]
> Event Type: Error
> Event Source: MSDTC
> Event Category: SVC
> Event ID: 4437
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: Some
> Description:
> The account that the MS DTC service is running under is invalid. This can
> happen if the service account information has been changed using the
> Services snap-in in Microsoft Management Console (MMC). MS DTC service
will
> continue to start. Please make sure that the MS DTC service account
> information is updated using the Component Services Explorer.
> How can I fix it?
> It seems that because of it I can't install SQL Server 2005 Express.
> If it is wrong newsgroup maybe someone can redirect me to the right one.
> Thanks,
> Shimon.
>
>|||Is a local account option is good for this?
Shimon
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:O3zSJSOZEHA.3476@.tk2msftngp13.phx.gbl...
> Go to Control panel->services or Administrative Tools-> services and find
> the MS DTC service. There will be a properties box where you can change
the
> login/password under which dtc run... The first error message states that
> the login/password is bad... (Maybe someone changed the password for the
DTC
> login...)
> Hope this helps.
> --
> 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
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:ufa0SmIZEHA.728@.TK2MSFTNGP09.phx.gbl...
Transactions[vbcol=seagreen]
> =
=[vbcol=seagreen]
> 1.
can[vbcol=seagreen]
> will
>

Can't start MSDTC

I can't start Distributed Transaction Coordinator (MSDTC)
I have following entries in event log
Event Type: Information
Event Source: MSDTC
Event Category: TM
Event ID: 53283
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: Some
Description:
DTC Security Configuration values. Network Administration of Transactions = 1, Network Clients = 0, Distributed Transactions using Native MSDTC
Protocol = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions = 1.
(OFF = 0 and ON = 1).
Event Type: Error
Event Source: MSDTC
Event Category: LOG
Event ID: 4163
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: some
Description:
MS DTC log file not found. After ensuring that all Resource Managers
coordinated by MS DTC have no indoubt transactions, please run
msdtc -resetlog to create the log file.
[I have tried it and with no success]
Event Type: Error
Event Source: MSDTC
Event Category: SVC
Event ID: 4437
Date: 7/7/2004
Time: 9:19:01 AM
User: N/A
Computer: Some
Description:
The account that the MS DTC service is running under is invalid. This can
happen if the service account information has been changed using the
Services snap-in in Microsoft Management Console (MMC). MS DTC service will
continue to start. Please make sure that the MS DTC service account
information is updated using the Component Services Explorer.
How can I fix it?
It seems that because of it I can't install SQL Server 2005 Express.
If it is wrong newsgroup maybe someone can redirect me to the right one.
Thanks,
Shimon.Go to Control panel->services or Administrative Tools-> services and find
the MS DTC service. There will be a properties box where you can change the
login/password under which dtc run... The first error message states that
the login/password is bad... (Maybe someone changed the password for the DTC
login...)
Hope this helps.
--
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
"Shimon Sim" <estshim@.att.net> wrote in message
news:ufa0SmIZEHA.728@.TK2MSFTNGP09.phx.gbl...
> I can't start Distributed Transaction Coordinator (MSDTC)
> I have following entries in event log
>
> Event Type: Information
> Event Source: MSDTC
> Event Category: TM
> Event ID: 53283
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: Some
> Description:
> DTC Security Configuration values. Network Administration of Transactions
=> 1, Network Clients = 0, Distributed Transactions using Native MSDTC
> Protocol = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions =1.
> (OFF = 0 and ON = 1).
> Event Type: Error
> Event Source: MSDTC
> Event Category: LOG
> Event ID: 4163
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: some
> Description:
> MS DTC log file not found. After ensuring that all Resource Managers
> coordinated by MS DTC have no indoubt transactions, please run
> msdtc -resetlog to create the log file.
> [I have tried it and with no success]
> Event Type: Error
> Event Source: MSDTC
> Event Category: SVC
> Event ID: 4437
> Date: 7/7/2004
> Time: 9:19:01 AM
> User: N/A
> Computer: Some
> Description:
> The account that the MS DTC service is running under is invalid. This can
> happen if the service account information has been changed using the
> Services snap-in in Microsoft Management Console (MMC). MS DTC service
will
> continue to start. Please make sure that the MS DTC service account
> information is updated using the Component Services Explorer.
> How can I fix it?
> It seems that because of it I can't install SQL Server 2005 Express.
> If it is wrong newsgroup maybe someone can redirect me to the right one.
> Thanks,
> Shimon.
>
>|||Is a local account option is good for this?
Shimon
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:O3zSJSOZEHA.3476@.tk2msftngp13.phx.gbl...
> Go to Control panel->services or Administrative Tools-> services and find
> the MS DTC service. There will be a properties box where you can change
the
> login/password under which dtc run... The first error message states that
> the login/password is bad... (Maybe someone changed the password for the
DTC
> login...)
> Hope this helps.
> --
> 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
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:ufa0SmIZEHA.728@.TK2MSFTNGP09.phx.gbl...
> > I can't start Distributed Transaction Coordinator (MSDTC)
> > I have following entries in event log
> >
> >
> > Event Type: Information
> > Event Source: MSDTC
> > Event Category: TM
> > Event ID: 53283
> > Date: 7/7/2004
> > Time: 9:19:01 AM
> > User: N/A
> > Computer: Some
> > Description:
> > DTC Security Configuration values. Network Administration of
Transactions
> => > 1, Network Clients = 0, Distributed Transactions using Native MSDTC
> > Protocol = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions
=> 1.
> > (OFF = 0 and ON = 1).
> >
> > Event Type: Error
> > Event Source: MSDTC
> > Event Category: LOG
> > Event ID: 4163
> > Date: 7/7/2004
> > Time: 9:19:01 AM
> > User: N/A
> > Computer: some
> > Description:
> > MS DTC log file not found. After ensuring that all Resource Managers
> > coordinated by MS DTC have no indoubt transactions, please run
> > msdtc -resetlog to create the log file.
> > [I have tried it and with no success]
> >
> > Event Type: Error
> > Event Source: MSDTC
> > Event Category: SVC
> > Event ID: 4437
> > Date: 7/7/2004
> > Time: 9:19:01 AM
> > User: N/A
> > Computer: Some
> > Description:
> > The account that the MS DTC service is running under is invalid. This
can
> > happen if the service account information has been changed using the
> > Services snap-in in Microsoft Management Console (MMC). MS DTC service
> will
> > continue to start. Please make sure that the MS DTC service account
> > information is updated using the Component Services Explorer.
> >
> > How can I fix it?
> > It seems that because of it I can't install SQL Server 2005 Express.
> > If it is wrong newsgroup maybe someone can redirect me to the right one.
> > Thanks,
> > Shimon.
> >
> >
> >
> >
>sql

Can't start DTC

Can't start a cloned connection while in manual transaction mode.

I got this error recently using SQL Server, and I found a microsoft site
which gives a solution by setting the SelectMethod property to Cursor but I
don't understand why. We've run this app before with no problems, yet now all
of a sudden we need to customize the URL string of the driver connection.
Can anyone explain what this means exactly and why this might have become a
problem now but was not before?
Most appreciated,
ol,
add "SelectMethod=cursor" your URL connection
ex:
Connection conn =
DriverManager.getConnection("jdbc:microsoft:sqlser ver://localhost:1433;SelectMethod=cursor");
newton
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1022266.html
|||| Thread-Topic: Can't start a cloned connection while in manual transaction
mode.
| thread-index: AcVBMGMZaFbT7Q3cQUeyebz5WmA8JQ==
| X-WBNR-Posting-Host: 66.114.64.114
| From: "=?Utf-8?B?TXJOb2JvZHk=?=" <MrNobody@.discussions.microsoft.com>
| Subject: Can't start a cloned connection while in manual transaction mode.
| Date: Thu, 14 Apr 2005 13:27:32 -0700
| Lines: 11
| Message-ID: <120F0C3C-9AAD-4B6A-B7F4-3F966E95E4DD@.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.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6826
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I got this error recently using SQL Server, and I found a microsoft site
| which gives a solution by setting the SelectMethod property to Cursor but
I
| don't understand why. We've run this app before with no problems, yet now
all
| of a sudden we need to customize the URL string of the driver connection.
|
| Can anyone explain what this means exactly and why this might have become
a
| problem now but was not before?
|
| Most appreciated,
|
|
|
Hello,
You will definitely need to use cursor mode to allow cloned connections to
be spawned on your behalf in a transaction. The behavior of SelectMethod
has been the same for the last several years.
If your code always used SelectMethod=direct, then the only thing I can
think of is that your app never entered the codepath that required a new
connection to be spawned.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Can't shrink transaction log

Instead of shrinking it, back it up first, this should get
rid of the active parts of the log file, then shrink it.
Peter

>--Original Message--
>Hi,
>A SQL Server2000 database we have has ended up with a
>massive Transaction Log (basically because the
maintenance
>schedule needs looking at).
>Anyway to cut a long story short, I backed up the
>transaction log which truncated it, but I can't seem to
>shrink the logfile to free up the space.
>I tried:
>DBCC SHRINKFILE (2, 200, TRUNCATEONLY)
>[where 2 is the filenumber of the logfile]
>This came back with:
>'Cannot shrink log file 2 (Main_Log) because all logical
>log files are in use.'
>Can anyone tell me how I can shrink this log file please?
>Regards,
>Vince
>.
>Check this article out
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Regards,
Gayathri Rau [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

Can't shrink transaction log

Hi,
A SQL Server2000 database we have has ended up with a
massive Transaction Log (basically because the maintenance
schedule needs looking at).
Anyway to cut a long story short, I backed up the
transaction log which truncated it, but I can't seem to
shrink the logfile to free up the space.
I tried:
DBCC SHRINKFILE (2, 200, TRUNCATEONLY)
[where 2 is the filenumber of the logfile]
This came back with:
'Cannot shrink log file 2 (Main_Log) because all logical
log files are in use.'
Can anyone tell me how I can shrink this log file please?
Regards,
VinceVince Beaney wrote:

> Can anyone tell me how I can shrink this log file please?
Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have had a similar problem. The only thing that worked was running these
commands:
First, backup the log with truncate_only:
BACKUP Log DBNAME with TRUNCATE_ONLY
Then you can run the SHRINKFILE DBCC Command:
DBCC SHRINKFILE (LOGFILE, 10)|||Excellent!
Thankyou, the "INF: Shrinking the Transaction Log in SQL
Server 2000 with DBCC SHRINKFILE" is what I was looking
for.
Thanks,
Vince

>--Original Message--
>Vince Beaney wrote:
>
please?
>Check out below KB articles:
>INF: How to Shrink the SQL Server 7.0 Transaction Log
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;256650
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>Log File Grows too big
>http://www.support.microsoft.com/?id=317375
>Log file filling up
>http://www.support.microsoft.com/?id=110139
>Considerations for Autogrow and AutoShrink
>http://www.support.microsoft.com/?id=315512
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
>sincerely,
>--
>Sebastian K. Zaklada
>Skilled Software
>http://www.skilledsoftware.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>sql

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John L
Hi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>
|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
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
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John LHi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:
> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
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
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John LHi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to th
e
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
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
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.
4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John Lsql

Thursday, March 22, 2012

Can't seem to get the newly created entry's identity field :(

Hi there,

I have a stored procedure that executes a transaction which updates/inserts into a table on a remote server and should then use that new entry's autogenerated primary key to update another table.
I try to store that key into a variable called @.NewlyCreatedPastelDClinkNumber but to no avail

I have used the @.@.Identity approach and now try to manually get that key to from other information but still no go..My thinking is: since i am using transactions, maybe the entry cant be found until the transaction has been committed...but surely it should find it after the commit statement? Here is my code: I have marked the interesting bit with blue for simplicity.

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

DECLARE @.PastelDCLinkNumber int
DECLARE @.ClientID int
DECLARE @.Name varchar(25)
DECLARE @.Surname nvarchar(25)
DECLARE @.POBoxAddr1 varchar(40)
DECLARE @.POBoxAddr2 varchar(40)
DECLARE @.PostalCode varchar(5)
DECLARE @.ClientCreditCardNumber nvarchar(50)
DECLARE @.NewlyCreatedPastelDClinkNumber int
DECLARE PastelClientPortCursor CURSOR FOR

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

OPEN PastelClientPortCursor;
FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber,@.ClientCreditCardNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN

--Step2 insert/update the client information into the Synergy invoice and client table
BEGIN TRANSACTION T1
IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET
Name = (@.Name + ' ' + @.Surname),
Account = (@.Name + ' ' + @.Surname),
Post1 = @.POBoxAddr1,
Post2 = @.POBoxAddr2,
PostPC = @.PostalCode,
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber
WHERE DCLink = @.PastelDCLinkNumber
End

ELSE
Begin
--Insert into the Pastel Client table
INSERT INTO [server].[Satra Corporate (Pty) Ltd].dbo.Client
(Account, Name, Post1, Post2, PostPC, AccountTerms, CT, Credit_Limit,
RepID, Interest_Rate, Discount, On_Hold, BFOpenType, BankLink,
AutoDisc,
DiscMtrxRow, CashDebtor, DCBalance, CheckTerms, UseEmail,
iCountryID, cAccDescription, iCurrencyID, bStatPrint,
bStatEmail, bForCurAcc,
fForeignBalance, bTaxPrompt, iARPriceListNameID)
VALUES
(
SUBSTRING(@.Name, 1,19), --Account
(@.Name + ' ' + @.Surname), --Name
@.POBoxAddr1, --Post1
@.POBoxAddr2, --Post2
@.PostalCode, --PostPC
0, --Account_Terms
1, --CT
0,--Credit_Limit
0, --Rep_ID
0, --interest_Rate
0, --Discount
0, --On_Hold
0, --BFOpenType
0, --BankLink
0, --AutoDisc
0, --DiscMatrix
0, --CashDebtor
0, --DCBalance
1, --CheckTerms
0, --UseEmail
0, --iCountryID
'Credit Card Number: ' + @.ClientCreditCardNumber,
0, --iCurrencyID
1, --StatPrint
0, --StatEmail
0, --bForCurAcc
0, --ForeignBal
1, --bTaxPrompt
1 --iARPriceListNameID
)
End

--If the rowcount is 0, an error has occured
IF @.@.ROWCOUNT <> 0
Begin
COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Begin
SET @.NewlyCreatedPastelDClinkNumber = (SELECT DCLink From [server].[Satra Corporate (Pty) Ltd].dbo.Client
WHERE Name = (@.Name + ' ' + @.Surname)AND
Account = (@.Name + ' ' + @.Surname)AND
Post1 = @.POBoxAddr1 AND
Post2 = @.POBoxAddr2 AND
PostPC = @.PostalCode AND
cAccDescription = 'Credit Card Number: ' + @.ClientCreditCardNumber)
End

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

FETCH NEXT FROM PastelClientPortCursor
INTO @.ClientID,@.Name,@.Surname,@.POBoxAddr1,@.POBoxAddr2,@.PostalCode,@.PastelDCLinkNumber, @.ClientCreditCardNumber
END
CLOSE PastelClientPortCursor;
DEALLOCATE PastelClientPortCursor;

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

You should use a remote sproc to do the update/insert stuff on the remote server. MUCH more efficient. Pass parameters in and then local stuff does work with a cached query plan and only one network trip.

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mike

Can't seem to get the newly created entry's identity field :(

Hi there,

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

--Update the Pastel Client table

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mikesql

Can''t seem to get the newly created entry''s identity field :(

Hi there,

ALTER procedure [dbo].[inv_SynchronizeClientsWithPastel]
as
BEGIN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET XACT_ABORT ON

SELECT id, [Name], Surname, POBoxAddr1, POBoxAddr2, PostalCode, PastelDCLinkNumber ,CreditCardNumber
FROM inc_Client
WHERE HasSynchronizedWithPastel = 0

--Update the Pastel Client table

Begin
Update inc_Client SET
HasSynchronizedWithPastel = 1 ,
PastelDCLinkNumber = @.NewlyCreatedPastelDClinkNumber
WHERE id = @.ClientID
End
End

--Rollback transaction
IF @.@.TRANCOUNT > 0
Begin
ROLLBACK TRANSACTION T1
execute sec_LogSQLTransactionError 'inv_SynchronizeClientsWithPastel','inc_Client_id',@.ClientID,'System'
End

END

SET XACT_ABORT OFF
Go

Instead of:

COMMIT TRANSACTION T1
execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

Try:

COMMIT TRANSACTION T1

SET @.NewlyCreatedPastelDClinkNumber = scope_identity()

execute sec_LogSQLTransaction 'inv_SynchronizeClientsWithPastel' ,'inc_Client_id',@.ClientID,'System'
-- Step 3Update the HasSynchronizedWithPastel Flag

|||

Problem 1)

IF Exists(SELECT DCLink from [server].[Satra Corporate (Pty) Ltd].dbo.Client WHERE DCLink = @.PastelDCLinkNumber)

--Update the Pastel Client table

Begin
Update [server].[Satra Corporate (Pty) Ltd].dbo.Client SET

Don't check for the data and then do the update. Simply DO the update first. If you update 0 rows, then you do the insert. This cuts exection in half when there is an update to be performed.

Problem 2)

IMMEDIATELY after the INSERT, put the @.@.ERROR and @.@.ROWCOUNT AND scope_identity() values into a variable. Then act on those three in appropriate order.

Problem 3)

|||Thanks for all your support. I'm going to give this a try|||Thank you for all your help. It worked.

I looked at your suggestions in your steps(problems) and immediately saw the advantages of your suggestions.

I will now adapt future stored procedure's templates to include those good practices. Thanks once more.

Regards
Mike

Thursday, March 8, 2012

Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -
Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
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
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential[vbcol=seagreen]
> backup however.
> Hope this helps..
> --
> 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
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't
>

Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
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
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> 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
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > My database is set to Full Recovery model. I can shrink my transaction
log
> > by executing a logfile backup, but when I execute a Full or Incremental
> > backup, this option is disabled. I don't understand why. With the
> execution
> > of a successful backup, entries in the log prior to the point of backup
> > become irrelevant! I might need subsequent logfile transactions in the
> event
> > of a failure, but I won't need the logfile prior to the point of backup.
> So
> > why can't I shrink the logfile as part and parcel of the backup
operation?
> > Must I always execute a separate logfile backup prior to my Full /
> > Incremental backup for the sole purpose of shrinking the logfile? I
don't
> > get it.
> >
> > Thanks for your advice!
> >
> > - Joe Geretz -
> >
> >
>

Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
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
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> 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
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't[vbcol=seagreen]
>

Sunday, February 12, 2012

Cant Handle Dts Error - Transaction was deadlocked...

Hi, i get this error while imanuallyexecute dts. But when i execute dts on my .aspx page, i can't handle this error on "... catch(Exception ex) {... }" part.

catch (Exception ex)
 {return ex.Message ;
//DtsPackage.OnError += new PackageEvents_OnErrorEventHandler(DtsPackage_OnError); }

Here is dts message in a text file.

Step 'DTSStep_DTSDataPumpTask_3' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Transaction (Process ID 124) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Any idea?

A deadlock happens when two users are trying to get each other's resources.

Here's an example:

Bob is running a process that is updating the Employee table and, in the middle of it, his code tries to update the Customer table.

Sally is running a process that is updating the Customer table on the same Customer that Bob is ABOUT TO deal with and, in the middle of it, her code tries to update the Employee that Bob has already got locked.

Neither can get the other's resource until the other one lets go.

|||

Of course it is. I know what a deadlock is. My question is i can't handle error from my web page. Dts does not return error code. It says "Dts succeeded...".

And i handle it with getting output and get the rows which start with "Error....".

Thanks for your reply..

Sunday, March 25, 2012

Can't start MSDTC

Can't start MSDTC

Can't start MSDTC

Can't start DTC

Can't start a cloned connection while in manual transaction mode.

Can't shrink transaction log

Can't shrink transaction log

Can't shrink a Transaction Log File

Can't shrink a Transaction Log File

Can't shrink a Transaction Log File

Thursday, March 22, 2012

Can't seem to get the newly created entry's identity field :(

Can't seem to get the newly created entry's identity field :(

Can''t seem to get the newly created entry''s identity field :(

Thursday, March 8, 2012

Can't remove inactive entries from transaction log

Can't remove inactive entries from transaction log

Can't remove inactive entries from transaction log

Sunday, February 12, 2012

Cant Handle Dts Error - Transaction was deadlocked...

sql report manager

Blog Archive

About Me