Can I delete Transaction log on subscriber

Hi,
One of our subscriber server is full due to transaction
log too large. The replication was failed without no
space for the transaction log. My question is can I
delete the transation log? Or is there any way I can free
some space on that subscriber server? Please help me.
p.s. we do full backup on our publisher server every day.
Thanks,
While you can delete it I would urge you not to do this. By shutting down
SQL Server and deleting the actual log physical file is a bad idea, although
in some people have claimed to be able to do it, I have never been able to.
I hope you mean truncating it and perhaps shrinking the log file. This is a
better option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ping" <anonymous@.discussions.microsoft.com> wrote in message
news:228501c506fc$0ecd0690$a401280a@.phx.gbl...
> Hi,
> One of our subscriber server is full due to transaction
> log too large. The replication was failed without no
> space for the transaction log. My question is can I
> delete the transation log? Or is there any way I can free
> some space on that subscriber server? Please help me.
> p.s. we do full backup on our publisher server every day.
> Thanks,
|||Hi hilary,
Thanks for your reply.
Regarding truncating and shrinking, which one do you
recommend?
Tks
>--Original Message--
>While you can delete it I would urge you not to do this.
By shutting down
>SQL Server and deleting the actual log physical file is a
bad idea, although
>in some people have claimed to be able to do it, I have
never been able to.
>I hope you mean truncating it and perhaps shrinking the
log file. This is a
>better option.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>"Ping" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:228501c506fc$0ecd0690$a401280a@.phx.gbl...
free[vbcol=seagreen]
day.
>
>.
>

Can I delete BAK and TRN files?

I have an implementation of SQL Server 2000 with a job to
backup a customer database. The backup works file, but
none of the old backup and transaction files are removed
after each backup, so it is consuming a lot of disk
space. I have BAK and TRN files dating back 3 months.
Before I deleted any of the old files, I wanted to make
sure I am okay to do so. Does anyone know of a problem
with manually most of these files, leaving only the most
recent week or two of backups?
Thanks,
JasonAs far as whether deleting the old backup files will do
any damage to the backup setup (e.g. your maintanance
plans) is concerned, there is no problem. But you do need
to make sure that if you ever need them, you can get them
back (e.g. from your tape backup facility).
Linchi

quote:

---

>--Original Message--
>I have an implementation of SQL Server 2000 with a job to
>backup a customer database. The backup works file, but
>none of the old backup and transaction files are removed
>after each backup, so it is consuming a lot of disk
>space. I have BAK and TRN files dating back 3 months.
>Before I deleted any of the old files, I wanted to make
>sure I am okay to do so. Does anyone know of a problem
>with manually most of these files, leaving only the most
>recent week or two of backups?
>Thanks,
>Jason
>.
>

---

|||... and if the backups are created by a SQL Server maintenance plan, you ca
n
modify it to have the files older than a specified period removed. It's on
the Complete Backup and Transaction Log Backup tabs respectively, Remove
files older than item.
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:0b4a01c3d932$260faea0$a001280a@.phx.gbl...[QUOTE]
> As far as whether deleting the old backup files will do
> any damage to the backup setup (e.g. your maintanance
> plans) is concerned, there is no problem. But you do need
> to make sure that if you ever need them, you can get them
> back (e.g. from your tape backup facility).
> Linchi
>

Can errors occur while committing a tx?

Can an error occur /during/ the process of committing a transaction?
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
Abdullah"Abdullah Kauchali" <none@.none.com> wrote in message
news:eT1t1UAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction?
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
throw;
> }
>
Remember to rethrow the exception!
You know, that's a really good question.
Yes errors are possible, but pretty darn unlikely (at least in SQL Server).
When you go to commit the transaction all of the changes have allready been
made to the tables, and written to the memory cache of the log file. So
almost everything that could go wrong already would have. There could
possibly be some error flushing the log to disk, or you could loose your
connection to the database server, or the server could just fail. If the
commit fails on the server, the transaction will be rolled back (or at worst
it won't be there when the database recovers). But from a client there's
probably some possiblility that the commit succeeds, but a network problem
prevents you from learning about it.
> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
The transaction will be rolled back.
> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
>
Treat it like a server or network failure.
It's so unlikely to happen in the first place, and you are so unlikely to be
able to recover if it does, that I would just pretend like it's impossible.
Just pretend like it can't happen. Your responsibility is only to keep the
database in a logically consistent state. You've done that by coding your
transaction. You are not responsible for making sure the transaction
suceeds. That responsibility belongs to a higher context (ie a user or an
automated agent).
For most programs any kind of transaction retry is not worth the coding.
The code complexity and residual risk are just too great. Just propagate
the error out to the user or calling code and let them deal with it. You
just don't have the right context to deal with a server or network failures
in a meaningful way.
If you feel like you must deal with it, then propagate the exception out of
this method, and catch it at the level which knows how to retry the entire
transaction. Wait around for the instance to fail over to another server,
reconnect and issue the transaction again. If you know you are running
against a cluster with such high availability requirements that it the DBA's
must fail the instance over to perform routine maintenance then you can
expect this to happen, and you have no choice but to code around it. But in
any case the retry code does not belong in that method, but in an outer
controlling context.
David

Can anyone tell me.

Let's assume I have to go for setup of sql server and I have a 20 GB
database and daily transaction is around 200 KB size. Can any one tell me
what I have to do for setup sql server like what are the configuration and
what are the step I have to consider.
Thanks
John
For the most parts, SQL Server is smart enough to figure out the appropriate
configuration during installation. However, there are things to consider
before you install (hardware requirements, disk layout, etc...) and things
to do after you're done (maintenance).
Suggest you check out http://www.microsoft.com/sql/ and review some of the
installation and management papers then come back here with specific
questions.
joe.
"John" <naissani@.hotmail.com> wrote in message
news:Olt0u6cFFHA.1348@.TK2MSFTNGP14.phx.gbl...
> Let's assume I have to go for setup of sql server and I have a 20 GB
> database and daily transaction is around 200 KB size. Can any one tell me
> what I have to do for setup sql server like what are the configuration and
> what are the step I have to consider.
> Thanks
> John
>

Can a SSIS package join an external transaction?

Hi there,

I'm trying to come up with the best way to build some C# Unit tests for an SSIS package I've built.

My C# code does the following

1. Creates a Transaction Scope using System.Transactions

2. Puts some source data for my package into a table that the package will read

3. Kicks off the Package using System.Diagnostics.ProcessStartInfo

Im getting a Transaction TimeOut execption which I beleive is caused because the Package can't read the source data I've inserted becuase the package has not joined the transaction.

So the question is - can you call a run a package and make it participate in a transaction that you have created outside of the package?

Thanks.

I don't believe you can do this, as having a look through the documentation there does not seem to be anywhere to pass in that existing transaction context as you load or execute a package.

Can a DROP TABLE statement be rolled back?

I'm interested in finding out if its possible to rollback a DROP TABLE
statement if it is inside a transaction that fails.
For some reason, I dont think it is but would like have someone confirm.
Thanks!
JohnnyNevermind my question. The answer is YES, it can be rolled back provided the
transaction is not comitted. My bad.
"Johnny" wrote:

> I'm interested in finding out if its possible to rollback a DROP TABLE
> statement if it is inside a transaction that fails.
> For some reason, I dont think it is but would like have someone confirm.
> Thanks!
> Johnny|||HI,Johnny,
U cannot rollback the drop table or delete command except if u execute
it if u use transaction number or savepoint.
As u execute the drop table a checkpoint occurs and the transaction is
commited by default.
U can aslo recover it if u have backup.
for my information on rollback
read books on line
hope this helps u
from
killer|||What you are saying is not true with Explicit transactions, which is the typ
e
of transaction I am referring to.
You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
with a COMMIT or ROLLBACK statement) as long as the transaction is not
committed. That is the whole point of transactions. Deletes, inserts, etc.
can all be rolled back provided the transaction has not been comitted.
Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to learn
more.
"doller" wrote:

> HI,Johnny,
> U cannot rollback the drop table or delete command except if u execute
> it if u use transaction number or savepoint.
> As u execute the drop table a checkpoint occurs and the transaction is
> commited by default.
> U can aslo recover it if u have backup.
> for my information on rollback
> read books on line
> hope this helps u
> from
> killer
>|||You are right Johnny
The drop command is allowed inside a transaction only if the ddl in tran
option to sp_dboption is set to true
To set ddl in tran to true, enter:
sp_dboption database_name,"ddl in tran", true
you can found more information about that reviewing the next url:
*http://manuals.sybase.com/onlineboo...r />
iew/53001
regards
"Johnny" wrote:
[vbcol=seagreen]
> What you are saying is not true with Explicit transactions, which is the t
ype
> of transaction I am referring to.
> You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
> with a COMMIT or ROLLBACK statement) as long as the transaction is not
> committed. That is the whole point of transactions. Deletes, inserts, etc.
> can all be rolled back provided the transaction has not been comitted.
> Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to lear
n
> more.
> "doller" wrote:
>|||There is no 'ddl in tran' database option in Microsoft SQL Server. Since
Johnny posted his question to a Microsoft SQL Server forum, chances are that
he is using MSSQL instead of Sybase.
DDL is always allowed within a transaction in Microsoft SQL Server. An
explicit or implicit transaction must be started in order to issue a COMMIT
or ROLLBACK. DDL can't be explicitly rolled back in autocommit mode.
Autocommit, explicit and implicit transactions are described in the Bools
Online <tsqlref.chm::/ts_ta-tz_2x2y.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Hernandez" <DanHernandez@.discussions.microsoft.com> wrote in message
news:DAD308E2-D898-4C40-B811-AAE8C8CA74F1@.microsoft.com...[vbcol=seagreen]
> You are right Johnny
> The drop command is allowed inside a transaction only if the ddl in tran
> option to sp_dboption is set to true
>
> To set ddl in tran to true, enter:
> sp_dboption database_name,"ddl in tran", true
> you can found more information about that reviewing the next url:
> *http://manuals.sybase.com/onlineboo.../>
tView/53001
>
> regards
>
> "Johnny" wrote:
>

Can a DROP TABLE statement be rolled back?

I'm interested in finding out if its possible to rollback a DROP TABLE
statement if it is inside a transaction that fails.
For some reason, I dont think it is but would like have someone confirm.
Thanks!
Johnny
Nevermind my question. The answer is YES, it can be rolled back provided the
transaction is not comitted. My bad.
"Johnny" wrote:

> I'm interested in finding out if its possible to rollback a DROP TABLE
> statement if it is inside a transaction that fails.
> For some reason, I dont think it is but would like have someone confirm.
> Thanks!
> Johnny
|||HI,Johnny,
U cannot rollback the drop table or delete command except if u execute
it if u use transaction number or savepoint.
As u execute the drop table a checkpoint occurs and the transaction is
commited by default.
U can aslo recover it if u have backup.
for my information on rollback
read books on line
hope this helps u
from
killer
|||What you are saying is not true with Explicit transactions, which is the type
of transaction I am referring to.
You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
with a COMMIT or ROLLBACK statement) as long as the transaction is not
committed. That is the whole point of transactions. Deletes, inserts, etc.
can all be rolled back provided the transaction has not been comitted.
Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to learn
more.
"doller" wrote:

> HI,Johnny,
> U cannot rollback the drop table or delete command except if u execute
> it if u use transaction number or savepoint.
> As u execute the drop table a checkpoint occurs and the transaction is
> commited by default.
> U can aslo recover it if u have backup.
> for my information on rollback
> read books on line
> hope this helps u
> from
> killer
>
|||You are right Johnny
The drop command is allowed inside a transaction only if the ddl in tran
option to sp_dboption is set to true
To set ddl in tran to true, enter:
sp_dboption database_name,"ddl in tran", true
you can found more information about that reviewing the next url:
*http://manuals.sybase.com/onlinebook...TextView/53001
regards
"Johnny" wrote:
[vbcol=seagreen]
> What you are saying is not true with Explicit transactions, which is the type
> of transaction I am referring to.
> You can rollback a transaction (using BEGIN TRANSACTION & explicitly ended
> with a COMMIT or ROLLBACK statement) as long as the transaction is not
> committed. That is the whole point of transactions. Deletes, inserts, etc.
> can all be rolled back provided the transaction has not been comitted.
> Read the "BEGIN TRANSACTION (Transact-SQL) " topic in books online to learn
> more.
> "doller" wrote:
|||There is no 'ddl in tran' database option in Microsoft SQL Server. Since
Johnny posted his question to a Microsoft SQL Server forum, chances are that
he is using MSSQL instead of Sybase.
DDL is always allowed within a transaction in Microsoft SQL Server. An
explicit or implicit transaction must be started in order to issue a COMMIT
or ROLLBACK. DDL can't be explicitly rolled back in autocommit mode.
Autocommit, explicit and implicit transactions are described in the Bools
Online <tsqlref.chm::/ts_ta-tz_2x2y.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Hernandez" <DanHernandez@.discussions.microsoft.com> wrote in message
news:DAD308E2-D898-4C40-B811-AAE8C8CA74F1@.microsoft.com...[vbcol=seagreen]
> You are right Johnny
> The drop command is allowed inside a transaction only if the ddl in tran
> option to sp_dboption is set to true
>
> To set ddl in tran to true, enter:
> sp_dboption database_name,"ddl in tran", true
> you can found more information about that reviewing the next url:
> *http://manuals.sybase.com/onlinebook...TextView/53001
>
> regards
>
> "Johnny" wrote:

Can a commit fail??

Can an error occur /during/ the process of committing a transaction? So:
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
AbdullahYes. If the log disk fills up, the write of the commit sentinal can fail,
thus it's indeed possible for a commit to fail. I'm sure that there are
other scenarios that could cause a commit failure. Therefore, if the commit
fails, the transaction should be rolled back (if that hasn't already
happened as a result of the failure).
"Abdullah Kauchali" <none@.none.com> wrote in message
news:ukGjDWAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction? So:
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
> }
> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
> TIA,
> Abdullah|||Brian Selzer wrote:
> Yes. If the log disk fills up, the write of the commit sentinal can fail,
> thus it's indeed possible for a commit to fail. I'm sure that there are
> other scenarios that could cause a commit failure. Therefore, if the comm
it
> fails, the transaction should be rolled back (if that hasn't already
> happened as a result of the failure).
Thanks Brian.
Let's use your example. Suppose UpdateTable1() succeeds during the
COMMIT (SQL Server frees the resources and releases the locks for
Table1) and then attempts to commit statements in UpdateTable2() but
then realises "oops, transaction log is full!" Will UpdateTable1()
rollback on a rollback instruction? Won't the transaction logs still
be considered full for the rollback log entries to go through?
:)
(I am actually trying to understand the process of commit in a 2-phase
scenario (distributed transactions), but I'd like to understand the
concept from a local-transaction point of view first. So, I apologise
for my lack of knowledge there!
My question for the 2-phase (distributed) transaction is this: if the
DTC commits all preceding resources and then encounters a problem with
the very last resource in the chain of updates, can/does the DTC
actually "uncommit" the preceding resources it just instructed to
commit? No during the prepare phase, but during the commit phase.)|||The commit of UpdateTable1() succeeded, so a rollback isn't possible. The
commit of UpdateTable2() fails, the log file is full, and the database shuts
down. During recovery (assuming disk space has been freed or otherwise made
available), UpdateTable2() will be rolled back. Changes are written to the
transaction log before they're written to the database, and only after all
of the database changes have been flushed to the disk is the commit sentinal
written to the transaction log, so the recovery process can undo any changes
made by any uncommitted transactions.
I'm not sure exactly how the process works with a distributed transaction.
Maybe there's a different type of sentinal written to the transaction log
after the prepare phase has completed. During the prepare phase, all cached
changes in each participant are flushed to the disk and then a
ready-to-commit signal is sent back to the coordinator. Once the commit
signal has been sent, I don't think a rollback is possible, even if an error
occurs on one of the other participants. If communication is lost before
the commit signal is received, then the participant is required to roll back
the transaction. If it happens afterward, the transaction is supposed to be
committed. Again, I'm not sure exactly how the process works under the
covers. Maybe someone with more knowledge than I can give you a more
difinitive answer.
"Abdullah Kauchali" <none@.none.com> wrote in message
news:uUupV7EzFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Brian Selzer wrote:
> Thanks Brian.
> Let's use your example. Suppose UpdateTable1() succeeds during the
> COMMIT (SQL Server frees the resources and releases the locks for
> Table1) and then attempts to commit statements in UpdateTable2() but
> then realises "oops, transaction log is full!" Will UpdateTable1()
> rollback on a rollback instruction? Won't the transaction logs still
> be considered full for the rollback log entries to go through?
> :)
> (I am actually trying to understand the process of commit in a 2-phase
> scenario (distributed transactions), but I'd like to understand the
> concept from a local-transaction point of view first. So, I apologise
> for my lack of knowledge there!
> My question for the 2-phase (distributed) transaction is this: if the
> DTC commits all preceding resources and then encounters a problem with
> the very last resource in the chain of updates, can/does the DTC
> actually "uncommit" the preceding resources it just instructed to
> commit? No during the prepare phase, but during the commit phase.)
>