are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980s
Hi,
Before i suggest u any thing just check the maintinance plan log file.
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killer

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980sHi,
Before i suggest u any thing just check the maintinance plan log file.
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killersql

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980sHi,
Before i suggest u any thing just check the maintinance plan log file.
--
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killer

Archiving and Pruning growing transaction tables

Hi,

I am using SQL Server 2005.

I would like to know best approaches for archiving and pruning couple of growing transaction tables in a database.

Possible approaches which I could think of
1) Take a backup of the database and delete records from tables based on date. (Issues - Deletion from existing tables takes a long time and the Indexes are disturbed requiring reindexing.)

2) Partition the table based on Date and possibly backup only the older partitions and remove them. (not sure if this can be done seemlessly).

Please let me know your thoughts.

Thanks,
Loonysan

I would suggest you look into partitioning, as you can drop a complete partition instead of doing a lot of deletes.

I worked on a system before where we had to delete a lot of records on date, and we had performance issues when we had to delete a lot of rows. We converted the system to partitioning, and could drop a partition at a time, which solved the performance problem.

Thanks,

Marcel van der Holst
[MSFT]

Appropriate Use of READ UNCOMMITTED?

I haven't used the READ UNCOMMITTED transaction isolation level
before, and I was wondering if this would be an appropriate use:

I have an ID table containing ID numbers that are randomly generated
and need to be unique. There is a stored procedure that potentially
generates thousands of these IDs in one execution and inserts them
into the ID table and various other tables. The basic idea is as
follows:

Begin Transaction
While not all IDs generated {
GenID:
@.NewID = GenerateID()
If @.NewID exists in ID table
GOTO GenID

Insert into ID table
Insert into various other tables
}
Commit Transaction

The problem occurs when the stored procedure is being run by more than
one process concurrently. The check to see whether @.NewID exists in
the ID table will block, waiting for the transaction in the other
process to commit.

Would this be an appropriate place to use the READ UNCOMMITTED
isolation level to allow different executions of the stored procedure
to see what the others are writing into the ID table before the
transactions finish? I only really care that the IDs generated are
unique; they're not in sequence or anything like that. Has anyone had
experience with anything similar?Hi

If you read uncommitted then you have to be sure that if the writing
transaction rolls back there are no consequences for the process that reads
the (phantom) data that was uncommitted. It is not clear from your
description if you can generate the same key twice if the process rolls
back.

As both your processes will also be writing simulaneously they may well be
blocking regardless of the reads, therefore you may want to look at
shortening the transactions.

John

"Pham Nguyen" <sherkaner77@.yahoo.com> wrote in message
news:f682e0f6.0411200043.65e5059c@.posting.google.c om...
>I haven't used the READ UNCOMMITTED transaction isolation level
> before, and I was wondering if this would be an appropriate use:
> I have an ID table containing ID numbers that are randomly generated
> and need to be unique. There is a stored procedure that potentially
> generates thousands of these IDs in one execution and inserts them
> into the ID table and various other tables. The basic idea is as
> follows:
> Begin Transaction
> While not all IDs generated {
> GenID:
> @.NewID = GenerateID()
> If @.NewID exists in ID table
> GOTO GenID
> Insert into ID table
> Insert into various other tables
> }
> Commit Transaction
> The problem occurs when the stored procedure is being run by more than
> one process concurrently. The check to see whether @.NewID exists in
> the ID table will block, waiting for the transaction in the other
> process to commit.
> Would this be an appropriate place to use the READ UNCOMMITTED
> isolation level to allow different executions of the stored procedure
> to see what the others are writing into the ID table before the
> transactions finish? I only really care that the IDs generated are
> unique; they're not in sequence or anything like that. Has anyone had
> experience with anything similar?|||Pham Nguyen (sherkaner77@.yahoo.com) writes:
> I have an ID table containing ID numbers that are randomly generated
> and need to be unique. There is a stored procedure that potentially
> generates thousands of these IDs in one execution and inserts them
> into the ID table and various other tables. The basic idea is as
> follows:
> Begin Transaction
> While not all IDs generated {
> GenID:
> @.NewID = GenerateID()
> If @.NewID exists in ID table
> GOTO GenID
> Insert into ID table
> Insert into various other tables
> }
> Commit Transaction
> The problem occurs when the stored procedure is being run by more than
> one process concurrently. The check to see whether @.NewID exists in
> the ID table will block, waiting for the transaction in the other
> process to commit.

It would only block if you generate a duplicate. Assuming that is that
the id colunm is indexed, so you don't have to scan the table each time.

A better approach may be to to push the key generation out of the
transaction. That presumes that your business requirements can accept
that a key does not have any rows with it.

In fact, I have a procedure which generates a key for a set of work tables,
and that procedure barfs if it's called from within a transaction to
avoid contention problems.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95A85B954D69Yazorman@.127.0.0.1>...
> It would only block if you generate a duplicate. Assuming that is that
> the id colunm is indexed, so you don't have to scan the table each time.

That's true.

> A better approach may be to to push the key generation out of the
> transaction. That presumes that your business requirements can accept
> that a key does not have any rows with it.

That was how the procedure was originally written; the new keys were
put into a temporary table as they were generated, and then got copied
over inside of a transaction. The problem we encountered was that the
stored procedure generates potentially thousands of keys in one
execution. With two processes running the stored procedure
concurrently, we saw a lot of duplicate keys, and had to rollback.

> In fact, I have a procedure which generates a key for a set of work tables,
> and that procedure barfs if it's called from within a transaction to
> avoid contention problems.|||Pham Nguyen (sherkaner77@.yahoo.com) writes:
> That was how the procedure was originally written; the new keys were
> put into a temporary table as they were generated, and then got copied
> over inside of a transaction. The problem we encountered was that the
> stored procedure generates potentially thousands of keys in one
> execution. With two processes running the stored procedure
> concurrently, we saw a lot of duplicate keys, and had to rollback.

OK, so the keys has to be written to a table to be persisted. And this
may require a transaction, but the transaction should be committed here.

Here is a procedure that we use:

CREATE PROCEDURE ak_get_aidkey_sp @.aidkey int OUTPUT AS

DECLARE @.err int

-- Check transaction.
IF @.@.trancount > 0
BEGIN
RAISERROR('Internal error: to avoid contention issues, this procedure
must not be called from a transaction in progress.', 16, 1)
RETURN 55555
END

-- Aidkeys is supposed to be emptied once a day, so the below is likely
-- to generate a unique key at the first shot.
WHILE 1 = 1
BEGIN
SELECT @.aidkey = -1 * abs(checksum(newid()))

BEGIN TRANSACTION

IF NOT EXISTS (SELECT * FROM aidkeys (SERIALIZABLE)
WHERE aidkey = @.aidkey)
BEGIN
INSERT aidkeys (aidkey) VALUES (@.aidkey)
SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err
BREAK
END

COMMIT TRANSACTION
END

COMMIT TRANSACTION

As you see, there is a transaction, but a very short one. Since you
generate many keys, you might need to modify the routine. Particularly,
if you generate 1000 keys in one go, the probability for at least one
collision increases.

A more brutal solution is to replace you current key column with a
uniqueidentifier and then use newid(). Then you can forget all about
collisions.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks - I have a few questions, though. What happens if the process
that is calling this procedure to generate keys errors out? Wouldn't
we want to be able to roll back the keys that have been generated?

Also, I'm not sure why the key table is emptied out every day, if the
keys have to be unique across days.

Is using READ UNCOMMITTED to scan the key table while generating keys
really bad? What sorts of problems can crop up?

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95A8B958CEC2AYazorman@.127.0.0.1>...

> OK, so the keys has to be written to a table to be persisted. And this
> may require a transaction, but the transaction should be committed here.
> Here is a procedure that we use:
>
> CREATE PROCEDURE ak_get_aidkey_sp @.aidkey int OUTPUT AS
> DECLARE @.err int
> -- Check transaction.
> IF @.@.trancount > 0
> BEGIN
> RAISERROR('Internal error: to avoid contention issues, this procedure
> must not be called from a transaction in progress.', 16, 1)
> RETURN 55555
> END
> -- Aidkeys is supposed to be emptied once a day, so the below is likely
> -- to generate a unique key at the first shot.
> WHILE 1 = 1
> BEGIN
> SELECT @.aidkey = -1 * abs(checksum(newid()))
> BEGIN TRANSACTION
> IF NOT EXISTS (SELECT * FROM aidkeys (SERIALIZABLE)
> WHERE aidkey = @.aidkey)
> BEGIN
> INSERT aidkeys (aidkey) VALUES (@.aidkey)
> SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err
> BREAK
> END
> COMMIT TRANSACTION
> END
> COMMIT TRANSACTION
>
> As you see, there is a transaction, but a very short one. Since you
> generate many keys, you might need to modify the routine. Particularly,
> if you generate 1000 keys in one go, the probability for at least one
> collision increases.
> A more brutal solution is to replace you current key column with a
> uniqueidentifier and then use newid(). Then you can forget all about
> collisions.|||Pham Nguyen (sherkaner77@.yahoo.com) writes:
> Thanks - I have a few questions, though. What happens if the process
> that is calling this procedure to generate keys errors out? Wouldn't
> we want to be able to roll back the keys that have been generated?

That depends on your application. For our usage, this is perfectly
acceptable. If you want to roll back keys beause the transaction bailed
out, you will have to face a contention problem, since you cannot
commit until the keys have been used.

> Also, I'm not sure why the key table is emptied out every day, if the
> keys have to be unique across days.

Sorry, I forgot that our purpose is a bit special. We have a coupld of
so called aid-tables. They are permanent temp tables so to speak. That
is, they do hold transient data during some sort of process. They are
not temp tables because of performance problems, or because it's un-
suitable for the process for some other reason.

Our system has a night job, which can assume that when it runs, nothing
else runs in the database. One section in this night job, empties all
aid tables (in case there are some data left behind), as well as the
aidkeys table.

Obviously, if your keys are generated for a permanent purpose, you need
to maintain the table with the keys.

> Is using READ UNCOMMITTED to scan the key table while generating keys
> really bad? What sorts of problems can crop up?

Well, one problem is that two processes can get the aame key value.
That is, they both attempt the same key value, both find that it's not
in use, both try to insert, and only one will survive.

Have you considered uniqueidentifier? That is probably the easy way out.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95A9ED3EB8277Yazorman@.127.0.0.1>...

> Well, one problem is that two processes can get the aame key value.
> That is, they both attempt the same key value, both find that it's not
> in use, both try to insert, and only one will survive.

Is this prevented from happening with a higher isolation level?

> Have you considered uniqueidentifier? That is probably the easy way out.

Unfortunately, this is an existing application that's being modified
and the keys are already being used in other systems out there that we
don't have control over.

We may have to just live with the contention problems. The process in
question isn't real-time (it's part of a file upload process that
dumps data into our database) so we may be able to get away with it.|||Pham Nguyen (sherkaner77@.yahoo.com) writes:
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns95A9ED3EB8277Yazorman@.127.0.0.1>...
>> Well, one problem is that two processes can get the aame key value.
>> That is, they both attempt the same key value, both find that it's not
>> in use, both try to insert, and only one will survive.
> Is this prevented from happening with a higher isolation level?

Yes, although for the point where you check whether a certain key value
is available, the default READ COMMITTED won't do. You need SERIALIZABLE
to hold a lock on the value which does not yet exist. Note that you
don't need SERIALIZABLE for the entire transaction, only for the query
where you check whether key is available.

(Depending on how these keys are assigned, it's possible that lower
levels will do, but as long as I don't know any details, I will have
to assume serializable.)

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

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

applying transaction logs from a crashed server

I would like to know the process to rebuilt a new server if the current
server crashes. I can install the server with the same service pack level,
then restore the system databases and then the User databases from the
backups on the new server.
How do we bring in and apply the Transaction logs from the crashed server to
the new server?You mean "the last" log backup? I.e., the log records produced since you produced your most recent
log backup?
It depends on how the server crashed. If the SQL Server is still available, then you just do
BACKUP LOG crashedDb TO .. WITH NO_TRUNCATE
If that SQL Server isn't accessible, then you do the following:
On a working server, create a new database.
Stop that SQL Server.
Delete the database files.
Copy the ldf file from the crashed server in place of the ldf file which you deleted in above step.
Start this SQL Server
BACKUP LOG dbname to ... WITH NO_TRUNCATE
You now have a chain of log backups up until the crash which you can use for your restore. Of
course, all this assumes that you do log backups in the first place and that you can access the ldf
file for your crashed database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F4187089-40A8-420B-B912-77F1B5A79834@.microsoft.com...
>I would like to know the process to rebuilt a new server if the current
> server crashes. I can install the server with the same service pack level,
> then restore the system databases and then the User databases from the
> backups on the new server.
> How do we bring in and apply the Transaction logs from the crashed server to
> the new server?

Applying of snapshot for transaction replication with DTS transfor

I created transaction replication with DTS transformation. Next I want to
move all data from publisher to subscriber - apply snapshot to subscriber.
Subscriber have different schema but no data. How it is possible to do? I
expected snapshot will use same DTS, but it not use it. Publisher DB is 24/7
system.
Have a look at transformable subscriptions. I would also advise you to have
a look at custom sync objects and encapsulating the data mapping in the
replication stored procedures as opposed to use DTS transforms due to
performance reasons.
Here is an article explaining how to do this.
http://www.dbazine.com/sql/sql-artic...rm=replicating
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:53CF05CA-FA93-4AAE-A4AA-37347B3060E7@.microsoft.com...
>I created transaction replication with DTS transformation. Next I want to
> move all data from publisher to subscriber - apply snapshot to subscriber.
> Subscriber have different schema but no data. How it is possible to do? I
> expected snapshot will use same DTS, but it not use it. Publisher DB is
> 24/7
> system.

Applying DBCC DBREINDEX on a database that is part of Transaction Replication

I am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.

Thanks in advance

Regards

R Suresh

Logreader will not replicate the index rebuild operation. Also if you use SQL 2005, I recommend you to use ALTER INDEX to rebuild index because DBCC DBREINDEX is a deprecated feature.

Of course, you need to do some experiment to see the performance impact if you do it frequently.

Peng

Applying DBCC DBREINDEX on a database that is part of Transaction Replication

I am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.

Thanks in advance

Regards

R Suresh

Logreader will not replicate the index rebuild operation. Also if you use SQL 2005, I recommend you to use ALTER INDEX to rebuild index because DBCC DBREINDEX is a deprecated feature.

Of course, you need to do some experiment to see the performance impact if you do it frequently.

Peng

Apply Old Transaction Logs

Hello Experts,
This may be a wacky question, but I'm gonna ask it anyway.
Can I apply some old tran logs in my db which now has a
different structure (changed after when the old tran logs
backed up) and ignore any additional/removed fields?
Thanks in advance?
No, if you are going to restore transaction logs the database has to be =
in standby mode. You place the database in standby mode when restoring =
a full database backup by specifying the WITH STANDBY option.
--=20
Keith
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in =
message news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
>=20
> This may be a wacky question, but I'm gonna ask it anyway.
>=20
> Can I apply some old tran logs in my db which now has a=20
> different structure (changed after when the old tran logs=20
> backed up) and ignore any additional/removed fields?
>=20
> Thanks in advance?
|||Hello Keith and you responding to my issue, why use
STANDBY, so users don't hurt my db while restore?
Thanks in advance.

>--Original Message--
>No, if you are going to restore transaction logs the
database has to be in standby mode. You place the
database in standby mode when restoring a full database
backup by specifying the WITH STANDBY option.
>--
>Keith
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...[vbcol=seagreen]
anyway.[vbcol=seagreen]
a[vbcol=seagreen]
logs
>.
>
|||You have to use STANDBY (or NORECOVERY) if you want to apply transaction =
logs. From Books Online:
Navigate to SQL Server Books Online (within the SQL Server program =
group) and search for RESTORE within the index tab. Read up on 'RESTORE =
(described).' Within that section will will find the following:
NORECOVERY
Instructs the restore operation to not roll back any uncommitted =
transactions. Either the NORECOVERY or STANDBY option must be specified =
if another transaction log has to be applied. If neither NORECOVERY, =
RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but =
the final RESTORE statement when restoring a database backup and =
multiple transaction logs, or when multiple RESTORE statements are =
needed (for example, a full database backup followed by a differential =
database backup).
--=20
Keith
"Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message =
news:ac3b01c4368e$d3106720$a101280a@.phx.gbl...[vbcol=seagreen]
>=20
> Hello Keith and you responding to my issue, why use=20
> STANDBY, so users don't hurt my db while restore?
>=20
> Thanks in advance.
>=20
> database has to be in standby mode. You place the=20
> database in standby mode when restoring a full database=20
> backup by specifying the WITH STANDBY option.
> <anonymous@.discussions.microsoft.com> wrote in message=20
> news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> anyway.
> a=20
> logs=20
|||"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
> This may be a wacky question, but I'm gonna ask it anyway.
>
No it is not a wacky question.

> Can I apply some old tran logs in my db which now has a
> different structure (changed after when the old tran logs
> backed up) and ignore any additional/removed fields?
What you want is a replay of the log in another
context/database.
The anwser to your question is : NO !
You can only apply the log files to the backups of
the database to which they belong and not to another
or a changed database.
http://www.lumigent.com/
Here you find some tools which can do more things
with a logfile. They have a tool 'Log Explorer' which
might be the tool you are looking for.
But if it works it is not as straitforward as 'replaying'
a log. And the tool is not a free tool.
Good luck and keep us informed.
ben brugman.

> Thanks in advance?

Apply Old Transaction Logs

Hello Experts,
This may be a wacky question, but I'm gonna ask it anyway.
Can I apply some old tran logs in my db which now has a
different structure (changed after when the old tran logs
backed up) and ignore any additional/removed fields?
Thanks in advance?No, if you are going to restore transaction logs the database has to be =in standby mode. You place the database in standby mode when restoring =a full database backup by specifying the WITH STANDBY option.
-- Keith
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in =message news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
> > This may be a wacky question, but I'm gonna ask it anyway.
> > Can I apply some old tran logs in my db which now has a > different structure (changed after when the old tran logs > backed up) and ignore any additional/removed fields?
> > Thanks in advance?|||Hello Keith and you responding to my issue, why use
STANDBY, so users don't hurt my db while restore?
Thanks in advance.
>--Original Message--
>No, if you are going to restore transaction logs the
database has to be in standby mode. You place the
database in standby mode when restoring a full database
backup by specifying the WITH STANDBY option.
>--
>Keith
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
>> Hello Experts,
>> This may be a wacky question, but I'm gonna ask it
anyway.
>> Can I apply some old tran logs in my db which now has
a
>> different structure (changed after when the old tran
logs
>> backed up) and ignore any additional/removed fields?
>> Thanks in advance?
>.
>|||You have to use STANDBY (or NORECOVERY) if you want to apply transaction =logs. From Books Online:
Navigate to SQL Server Books Online (within the SQL Server program =group) and search for RESTORE within the index tab. Read up on 'RESTORE =(described).' Within that section will will find the following:
NORECOVERY
Instructs the restore operation to not roll back any uncommitted =transactions. Either the NORECOVERY or STANDBY option must be specified =if another transaction log has to be applied. If neither NORECOVERY, =RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but =the final RESTORE statement when restoring a database backup and =multiple transaction logs, or when multiple RESTORE statements are =needed (for example, a full database backup followed by a differential =database backup).
-- Keith
"Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message =news:ac3b01c4368e$d3106720$a101280a@.phx.gbl...
> > Hello Keith and you responding to my issue, why use > STANDBY, so users don't hurt my db while restore?
> > Thanks in advance.
> > >--Original Message--
> >No, if you are going to restore transaction logs the > database has to be in standby mode. You place the > database in standby mode when restoring a full database > backup by specifying the WITH STANDBY option.
> >
> >-- > >Keith
> >
> >
> >"Konstantinos Michas" > <anonymous@.discussions.microsoft.com> wrote in message > news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> >> Hello Experts,
> >> > >> This may be a wacky question, but I'm gonna ask it > anyway.
> >> > >> Can I apply some old tran logs in my db which now has > a > >> different structure (changed after when the old tran > logs > >> backed up) and ignore any additional/removed fields?
> >> > >> Thanks in advance?
> >.
> >|||"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
> This may be a wacky question, but I'm gonna ask it anyway.
>
No it is not a wacky question.
> Can I apply some old tran logs in my db which now has a
> different structure (changed after when the old tran logs
> backed up) and ignore any additional/removed fields?
What you want is a replay of the log in another
context/database.
The anwser to your question is : NO !
You can only apply the log files to the backups of
the database to which they belong and not to another
or a changed database.
http://www.lumigent.com/
Here you find some tools which can do more things
with a logfile. They have a tool 'Log Explorer' which
might be the tool you are looking for.
But if it works it is not as straitforward as 'replaying'
a log. And the tool is not a free tool.
Good luck and keep us informed.
ben brugman.
> Thanks in advance?

Apply Old Transaction Logs

Hello Experts,
This may be a wacky question, but I'm gonna ask it anyway.
Can I apply some old tran logs in my db which now has a
different structure (changed after when the old tran logs
backed up) and ignore any additional/removed fields?
Thanks in advance?No, if you are going to restore transaction logs the database has to be =
in standby mode. You place the database in standby mode when restoring =
a full database backup by specifying the WITH STANDBY option.
--=20
Keith
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in =
message news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
>=20
> This may be a wacky question, but I'm gonna ask it anyway.
>=20
> Can I apply some old tran logs in my db which now has a=20
> different structure (changed after when the old tran logs=20
> backed up) and ignore any additional/removed fields?
>=20
> Thanks in advance?|||Hello Keith and you responding to my issue, why use
STANDBY, so users don't hurt my db while restore?
Thanks in advance.

>--Original Message--
>No, if you are going to restore transaction logs the
database has to be in standby mode. You place the
database in standby mode when restoring a full database
backup by specifying the WITH STANDBY option.
>--
>Keith
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
anyway.[vbcol=seagreen]
a[vbcol=seagreen]
logs[vbcol=seagreen]
>.
>|||You have to use STANDBY (or NORECOVERY) if you want to apply transaction =
logs. From Books Online:
Navigate to SQL Server Books Online (within the SQL Server program =
group) and search for RESTORE within the index tab. Read up on 'RESTORE =
(described).' Within that section will will find the following:
NORECOVERY
Instructs the restore operation to not roll back any uncommitted =
transactions. Either the NORECOVERY or STANDBY option must be specified =
if another transaction log has to be applied. If neither NORECOVERY, =
RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but =
the final RESTORE statement when restoring a database backup and =
multiple transaction logs, or when multiple RESTORE statements are =
needed (for example, a full database backup followed by a differential =
database backup).
--=20
Keith
"Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message =
news:ac3b01c4368e$d3106720$a101280a@.phx.gbl...[vbcol=seagreen]
>=20
> Hello Keith and you responding to my issue, why use=20
> STANDBY, so users don't hurt my db while restore?
>=20
> Thanks in advance.
>=20
> database has to be in standby mode. You place the=20
> database in standby mode when restoring a full database=20
> backup by specifying the WITH STANDBY option.
> <anonymous@.discussions.microsoft.com> wrote in message=20
> news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> anyway.
> a=20
> logs=20|||"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
> This may be a wacky question, but I'm gonna ask it anyway.
>
No it is not a wacky question.

> Can I apply some old tran logs in my db which now has a
> different structure (changed after when the old tran logs
> backed up) and ignore any additional/removed fields?
What you want is a replay of the log in another
context/database.
The anwser to your question is : NO !
You can only apply the log files to the backups of
the database to which they belong and not to another
or a changed database.
http://www.lumigent.com/
Here you find some tools which can do more things
with a logfile. They have a tool 'Log Explorer' which
might be the tool you are looking for.
But if it works it is not as straitforward as 'replaying'
a log. And the tool is not a free tool.
Good luck and keep us informed.
ben brugman.

> Thanks in advance?

Apply filter on dbcc log query

Hi,
I am reading online transaction log using dbcc log command.
But I don't want to read the whole online transaction log, because it will t
ake too long time. Is there any way through which I can filter the record an
d get lesser no. of records.
Is there any way through which I can use dbcc command directly in a query.
Or is there any undocumennted parameter that might passed to dcc log command
through which we can specify filter.
Thanks in advance
PushkarHi
dbcc log is undocumented, so all of it is unsupported.
AFAIK, the commands listed here are the only ones:
http://www.mssqlcity.com/Articles/U...CC.htm#part_2_8
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pushkar" wrote:

> Hi,
> I am reading online transaction log using dbcc log command.
> But I don't want to read the whole online transaction log, because it will
take too long time. Is there any way through which I can filter the record
and get lesser no. of records.
> Is there any way through which I can use dbcc command directly in a query.
> Or is there any undocumennted parameter that might passed to dcc log comma
nd through which we can specify filter.
> Thanks in advance
> Pushkar
>|||> But I don't want to read the whole online transaction log, because it will
> take too long time.
Once again, I suggest a 3rd party tool. This operation is meant for
disaster recovery and occasional usage, not for every day activities where
performance is a concern. http://www.aspfaq.com/2449
It seems you've already spent enough time on this that you would have paid
for the license, if your opportunity cost is more than minimum wage...

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application log and backuo operations

I'm using Log Shipping in order to update a backup database on SQL Server
2000. I scheduled the transaction log backup every 5 minutes. The problem is
that SQL Server logs an event into the Application Log every time backup is
completed so the Application Log becomes full very quickly.
Is there any way to disable this behaviour ?
Thank you for any suggestions.
Luigi
Hi,
In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshipping
task. Double click above that and
select "Notifications"
In that screen "UNCHECK" the Write to windows Application log and clieck
Apply and OK.
Thanks
Hari
MCDBA
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> I'm using Log Shipping in order to update a backup database on SQL Server
> 2000. I scheduled the transaction log backup every 5 minutes. The problem
is
> that SQL Server logs an event into the Application Log every time backup
is
> completed so the Application Log becomes full very quickly.
> Is there any way to disable this behaviour ?
> Thank you for any suggestions.
> Luigi
|||Thank you Hari for your suggestion.
I tried it but it doesn't solve my problem. The problem isn't about the
job's result : the job is set up in order to write to tha application log in
case of error only. It seems instead that it is the BACKUP command that logs
the event: when I launch my job, I find in the application log the same log
that I find when I backup database by Enterprise Maganer's facilities.
Bye
Luigi
"Hari Prasad" wrote:

> Hi,
> In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshipping
> task. Double click above that and
> select "Notifications"
> In that screen "UNCHECK" the Write to windows Application log and clieck
> Apply and OK.
> Thanks
> Hari
> MCDBA
>
> "Luigi" <Luigi@.discussions.microsoft.com> wrote in message
> news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> is
> is
>
>
|||SQL Server always write these messages to your logs. Even if you define in sysmessages that they shouldn't be
written. I have suggested to MS that SQL Server honors the setting in sysmessages, we'll see if any such
change appears in the future. If you want to communicate such a wish, you can use sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:07C296BE-FD47-46D1-94A6-669FD6FB9C67@.microsoft.com...[vbcol=seagreen]
> Thank you Hari for your suggestion.
> I tried it but it doesn't solve my problem. The problem isn't about the
> job's result : the job is set up in order to write to tha application log in
> case of error only. It seems instead that it is the BACKUP command that logs
> the event: when I launch my job, I find in the application log the same log
> that I find when I backup database by Enterprise Maganer's facilities.
> Bye
> Luigi
>
> "Hari Prasad" wrote:

Application log and backuo operations

I'm using Log Shipping in order to update a backup database on SQL Server
2000. I scheduled the transaction log backup every 5 minutes. The problem is
that SQL Server logs an event into the Application Log every time backup is
completed so the Application Log becomes full very quickly.
Is there any way to disable this behaviour '
Thank you for any suggestions.
LuigiHi,
In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshipping
task. Double click above that and
select "Notifications"
In that screen "UNCHECK" the Write to windows Application log and clieck
Apply and OK.
Thanks
Hari
MCDBA
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> I'm using Log Shipping in order to update a backup database on SQL Server
> 2000. I scheduled the transaction log backup every 5 minutes. The problem
is
> that SQL Server logs an event into the Application Log every time backup
is
> completed so the Application Log becomes full very quickly.
> Is there any way to disable this behaviour '
> Thank you for any suggestions.
> Luigi|||SQL Server always write these messages to your logs. Even if you define in sysmessages that they shouldn't be
written. I have suggested to MS that SQL Server honors the setting in sysmessages, we'll see if any such
change appears in the future. If you want to communicate such a wish, you can use sqlwish@.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:07C296BE-FD47-46D1-94A6-669FD6FB9C67@.microsoft.com...
> Thank you Hari for your suggestion.
> I tried it but it doesn't solve my problem. The problem isn't about the
> job's result : the job is set up in order to write to tha application log in
> case of error only. It seems instead that it is the BACKUP command that logs
> the event: when I launch my job, I find in the application log the same log
> that I find when I backup database by Enterprise Maganer's facilities.
> Bye
> Luigi
>
> "Hari Prasad" wrote:
> > Hi,
> >
> > In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshipping
> > task. Double click above that and
> > select "Notifications"
> >
> > In that screen "UNCHECK" the Write to windows Application log and clieck
> > Apply and OK.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Luigi" <Luigi@.discussions.microsoft.com> wrote in message
> > news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> > > I'm using Log Shipping in order to update a backup database on SQL Server
> > > 2000. I scheduled the transaction log backup every 5 minutes. The problem
> > is
> > > that SQL Server logs an event into the Application Log every time backup
> > is
> > > completed so the Application Log becomes full very quickly.
> > >
> > > Is there any way to disable this behaviour '
> > >
> > > Thank you for any suggestions.
> > >
> > > Luigi
> >
> >
> >

Application log and backuo operations

I'm using Log Shipping in order to update a backup database on SQL Server
2000. I scheduled the transaction log backup every 5 minutes. The problem is
that SQL Server logs an event into the Application Log every time backup is
completed so the Application Log becomes full very quickly.
Is there any way to disable this behaviour '
Thank you for any suggestions.
LuigiHi,
In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshipping
task. Double click above that and
select "Notifications"
In that screen "UNCHECK" the Write to windows Application log and clieck
Apply and OK.
Thanks
Hari
MCDBA
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> I'm using Log Shipping in order to update a backup database on SQL Server
> 2000. I scheduled the transaction log backup every 5 minutes. The problem
is
> that SQL Server logs an event into the Application Log every time backup
is
> completed so the Application Log becomes full very quickly.
> Is there any way to disable this behaviour '
> Thank you for any suggestions.
> Luigi|||Thank you Hari for your suggestion.
I tried it but it doesn't solve my problem. The problem isn't about the
job's result : the job is set up in order to write to tha application log in
case of error only. It seems instead that it is the BACKUP command that logs
the event: when I launch my job, I find in the application log the same log
that I find when I backup database by Enterprise Maganer's facilities.
Bye
Luigi
"Hari Prasad" wrote:

> Hi,
> In Enterprise manager -- SQL Server Agent -- Jobs -- Select the Logshippin
g
> task. Double click above that and
> select "Notifications"
> In that screen "UNCHECK" the Write to windows Application log and clieck
> Apply and OK.
> Thanks
> Hari
> MCDBA
>
> "Luigi" <Luigi@.discussions.microsoft.com> wrote in message
> news:3ACE4093-DD77-454C-B59C-A0369417D49D@.microsoft.com...
> is
> is
>
>|||SQL Server always write these messages to your logs. Even if you define in s
ysmessages that they shouldn't be
written. I have suggested to MS that SQL Server honors the setting in sysmes
sages, we'll see if any such
change appears in the future. If you want to communicate such a wish, you ca
n use sqlwish@.microsoft.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Luigi" <Luigi@.discussions.microsoft.com> wrote in message
news:07C296BE-FD47-46D1-94A6-669FD6FB9C67@.microsoft.com...[vbcol=seagreen]
> Thank you Hari for your suggestion.
> I tried it but it doesn't solve my problem. The problem isn't about the
> job's result : the job is set up in order to write to tha application log
in
> case of error only. It seems instead that it is the BACKUP command that lo
gs
> the event: when I launch my job, I find in the application log the same lo
g
> that I find when I backup database by Enterprise Maganer's facilities.
> Bye
> Luigi
>
> "Hari Prasad" wrote:
>

Application goes slow or not responding after few transaction

Dear all viewer ,

Thanks to everyone.

We are using -
MSSQL Server 2000 on windows 2000 advanced server.
PB 7.0 for client server front end tools.

Prior to few days our application works fine, rightnow it get slow or not responding after few (4/5) transaction. We don't know wht it is, If anyone has same experience so that is helpfull for us.

Thanks

R.MallMonitor your activity with SQL Profiler.|||Thanks for reply

How I can do it? Can you give me step by step methods?

R.Mall|||SELECT Profiler
FROM BooksOnline

or

SELECT Consultant
FROM YellowPages|||Thanks for right suggession, I will do it.

Thanks|||I wonder if he actually did that? :)

You can also find information about running Profiler from:

www.sql-server-performance.com
www.sqlteam.com
SQL Server 2000 Performance Tuning Technical Manual (Microsoft Press)