With SQL 2000 I am aware that a transaction log backup cannot be done while
a
transaction log backup of the same database is already in progress. Instead
of starting a transaction log backup and having it fail, is there some statu
s
to check somewhere (say in the SQL catalog) that indicates that a backup is
in progress?
Thank-you!"Howard" wrote:
> With SQL 2000 I am aware that a transaction log backup cannot be done whil
e a
> transaction log backup of the same database is already in progress. Instea
d
> of starting a transaction log backup and having it fail, is there some sta
tus
> to check somewhere (say in the SQL catalog) that indicates that a backup i
s
> in progress?
> Thank-you!
Oops, I didn't state this quite right. I meant to say that the transaction
log backup would fail if their were a FULL backup in progress. So again, is
there anyway to detect that the full backup is currently in progress. Thanks
again.|||Howard,
I use this script to check if a job is executing, and if it is to stop it.
You could adapt it for your own purposes:
http://www.replicationanswers.com/D...RunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I already know how to stop a job. What I need to know is whether a backup fo
r
database X is currently in progress. E.g., I know if a restore is in progres
s
because loading state would be set in the 'status' field of sysdatabases. I
s
there any way to know that a backup is in progress? (Again, I want to know
this because I will not start a transaction log backup if there is a full in
progress).
Thanks again.
"Paul Ibison" wrote:
> Howard,
> I use this script to check if a job is executing, and if it is to stop it.
> You could adapt it for your own purposes:
> http://www.replicationanswers.com/D...RunningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Howard,
this script doesn't just stop a job - the stored procedure call checks to
see if the job is currently executing. As long as your database backup is
done in a job and you know the job name it should be ok.
Paul Ibison|||Paul,
Unfortunately, I wouldn't know the job name of the executing backup or the
jobid. I just need to know that particular database is currently being backe
d
up.
Thanks.
"Paul Ibison" wrote:
> Howard,
> this script doesn't just stop a job - the stored procedure call checks to
> see if the job is currently executing. As long as your database backup is
> done in a job and you know the job name it should be ok.
> Paul Ibison
>
>|||Howard,
please try this:
select cmd, db_name(dbid) as DatabaseName from sysprocesses
where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
Cheers,
Paul Ibison, SQL Server MVP|||Beautiful!
Thank-you, Howard
"Paul Ibison" wrote:
> Howard,
> please try this:
> select cmd, db_name(dbid) as DatabaseName from sysprocesses
> where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
> Cheers,
> Paul Ibison, SQL Server MVP
>
>
Showing posts with label atransaction. Show all posts
Showing posts with label atransaction. Show all posts
Thursday, February 16, 2012
Monday, February 13, 2012
Backup with SQL2000
With SQL 2000 I am aware that a transaction log backup cannot be done while a
transaction log backup of the same database is already in progress. Instead
of starting a transaction log backup and having it fail, is there some status
to check somewhere (say in the SQL catalog) that indicates that a backup is
in progress?
Thank-you!
"Howard" wrote:
> With SQL 2000 I am aware that a transaction log backup cannot be done while a
> transaction log backup of the same database is already in progress. Instead
> of starting a transaction log backup and having it fail, is there some status
> to check somewhere (say in the SQL catalog) that indicates that a backup is
> in progress?
> Thank-you!
Oops, I didn't state this quite right. I meant to say that the transaction
log backup would fail if their were a FULL backup in progress. So again, is
there anyway to detect that the full backup is currently in progress. Thanks
again.
|||Howard,
I use this script to check if a job is executing, and if it is to stop it.
You could adapt it for your own purposes:
http://www.replicationanswers.com/Do...unningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I already know how to stop a job. What I need to know is whether a backup for
database X is currently in progress. E.g., I know if a restore is in progress
because loading state would be set in the 'status' field of sysdatabases. Is
there any way to know that a backup is in progress? (Again, I want to know
this because I will not start a transaction log backup if there is a full in
progress).
Thanks again.
"Paul Ibison" wrote:
> Howard,
> I use this script to check if a job is executing, and if it is to stop it.
> You could adapt it for your own purposes:
> http://www.replicationanswers.com/Do...unningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Howard,
this script doesn't just stop a job - the stored procedure call checks to
see if the job is currently executing. As long as your database backup is
done in a job and you know the job name it should be ok.
Paul Ibison
|||Paul,
Unfortunately, I wouldn't know the job name of the executing backup or the
jobid. I just need to know that particular database is currently being backed
up.
Thanks.
"Paul Ibison" wrote:
> Howard,
> this script doesn't just stop a job - the stored procedure call checks to
> see if the job is currently executing. As long as your database backup is
> done in a job and you know the job name it should be ok.
> Paul Ibison
>
>
|||Howard,
please try this:
select cmd, db_name(dbid) as DatabaseName from sysprocesses
where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
Cheers,
Paul Ibison, SQL Server MVP
|||Beautiful!
Thank-you, Howard
"Paul Ibison" wrote:
> Howard,
> please try this:
> select cmd, db_name(dbid) as DatabaseName from sysprocesses
> where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
> Cheers,
> Paul Ibison, SQL Server MVP
>
>
transaction log backup of the same database is already in progress. Instead
of starting a transaction log backup and having it fail, is there some status
to check somewhere (say in the SQL catalog) that indicates that a backup is
in progress?
Thank-you!
"Howard" wrote:
> With SQL 2000 I am aware that a transaction log backup cannot be done while a
> transaction log backup of the same database is already in progress. Instead
> of starting a transaction log backup and having it fail, is there some status
> to check somewhere (say in the SQL catalog) that indicates that a backup is
> in progress?
> Thank-you!
Oops, I didn't state this quite right. I meant to say that the transaction
log backup would fail if their were a FULL backup in progress. So again, is
there anyway to detect that the full backup is currently in progress. Thanks
again.
|||Howard,
I use this script to check if a job is executing, and if it is to stop it.
You could adapt it for your own purposes:
http://www.replicationanswers.com/Do...unningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I already know how to stop a job. What I need to know is whether a backup for
database X is currently in progress. E.g., I know if a restore is in progress
because loading state would be set in the 'status' field of sysdatabases. Is
there any way to know that a backup is in progress? (Again, I want to know
this because I will not start a transaction log backup if there is a full in
progress).
Thanks again.
"Paul Ibison" wrote:
> Howard,
> I use this script to check if a job is executing, and if it is to stop it.
> You could adapt it for your own purposes:
> http://www.replicationanswers.com/Do...unningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Howard,
this script doesn't just stop a job - the stored procedure call checks to
see if the job is currently executing. As long as your database backup is
done in a job and you know the job name it should be ok.
Paul Ibison
|||Paul,
Unfortunately, I wouldn't know the job name of the executing backup or the
jobid. I just need to know that particular database is currently being backed
up.
Thanks.
"Paul Ibison" wrote:
> Howard,
> this script doesn't just stop a job - the stored procedure call checks to
> see if the job is currently executing. As long as your database backup is
> done in a job and you know the job name it should be ok.
> Paul Ibison
>
>
|||Howard,
please try this:
select cmd, db_name(dbid) as DatabaseName from sysprocesses
where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
Cheers,
Paul Ibison, SQL Server MVP
|||Beautiful!
Thank-you, Howard
"Paul Ibison" wrote:
> Howard,
> please try this:
> select cmd, db_name(dbid) as DatabaseName from sysprocesses
> where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
> Cheers,
> Paul Ibison, SQL Server MVP
>
>
Sunday, February 12, 2012
backup Transaction Log
Hi All,
Yesterday i posted my queston and i got couple of replies.
However, I was not clear with how i could back up a
transaction log file that is 25 GB when i have only 1GB
free space available.
Does it mean that i should not worry about the size of
the disk space available when i run the following code:
BACKUP LOG pubs WITH TRUNCATE_ONLY
Thank you,
Mitra
Correct. Incidentally, you might want to read the documentation on the
commands before you do anything recommended in the newsgroups to make sure
you understand what the commands do. After all, we are not affected if your
database is destroyed.
"Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:138f701c44421$7e747f30$a601280a@.phx.gbl...
> Hi All,
> Yesterday i posted my queston and i got couple of replies.
> However, I was not clear with how i could back up a
> transaction log file that is 25 GB when i have only 1GB
> free space available.
> Does it mean that i should not worry about the size of
> the disk space available when i run the following code:
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> Thank you,
> Mitra
|||Hi,
Look into the SQL Server Recovery models documentation in Books online.
I recommend you to set the recovery model to "SIMPLE" incase if your
database is not a production data.
In this model the transaction file will be cleared on each commit. This will
ensure that your transaction file wont grow that big.
Thanks
Hari
MCDBA
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OHrx7cCREHA.2572@.TK2MSFTNGP12.phx.gbl...
> Correct. Incidentally, you might want to read the documentation on the
> commands before you do anything recommended in the newsgroups to make sure
> you understand what the commands do. After all, we are not affected if
your
> database is destroyed.
> "Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:138f701c44421$7e747f30$a601280a@.phx.gbl...
>
Yesterday i posted my queston and i got couple of replies.
However, I was not clear with how i could back up a
transaction log file that is 25 GB when i have only 1GB
free space available.
Does it mean that i should not worry about the size of
the disk space available when i run the following code:
BACKUP LOG pubs WITH TRUNCATE_ONLY
Thank you,
Mitra
Correct. Incidentally, you might want to read the documentation on the
commands before you do anything recommended in the newsgroups to make sure
you understand what the commands do. After all, we are not affected if your
database is destroyed.
"Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:138f701c44421$7e747f30$a601280a@.phx.gbl...
> Hi All,
> Yesterday i posted my queston and i got couple of replies.
> However, I was not clear with how i could back up a
> transaction log file that is 25 GB when i have only 1GB
> free space available.
> Does it mean that i should not worry about the size of
> the disk space available when i run the following code:
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> Thank you,
> Mitra
|||Hi,
Look into the SQL Server Recovery models documentation in Books online.
I recommend you to set the recovery model to "SIMPLE" incase if your
database is not a production data.
In this model the transaction file will be cleared on each commit. This will
ensure that your transaction file wont grow that big.
Thanks
Hari
MCDBA
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OHrx7cCREHA.2572@.TK2MSFTNGP12.phx.gbl...
> Correct. Incidentally, you might want to read the documentation on the
> commands before you do anything recommended in the newsgroups to make sure
> you understand what the commands do. After all, we are not affected if
your
> database is destroyed.
> "Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:138f701c44421$7e747f30$a601280a@.phx.gbl...
>
backup Transaction Log
Hi All,
Yesterday i posted my queston and i got couple of replies.
However, I was not clear with how i could back up a
transaction log file that is 25 GB when i have only 1GB
free space available.
Does it mean that i should not worry about the size of
the disk space available when i run the following code:
BACKUP LOG pubs WITH TRUNCATE_ONLY
Thank you,
MitraCorrect. Incidentally, you might want to read the documentation on the
commands before you do anything recommended in the newsgroups to make sure
you understand what the commands do. After all, we are not affected if your
database is destroyed.
"Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:138f701c44421$7e747f30$a601280a@.phx
.gbl...
> Hi All,
> Yesterday i posted my queston and i got couple of replies.
> However, I was not clear with how i could back up a
> transaction log file that is 25 GB when i have only 1GB
> free space available.
> Does it mean that i should not worry about the size of
> the disk space available when i run the following code:
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> Thank you,
> Mitra|||Hi,
Look into the SQL Server Recovery models documentation in Books online.
I recommend you to set the recovery model to "SIMPLE" incase if your
database is not a production data.
In this model the transaction file will be cleared on each commit. This will
ensure that your transaction file wont grow that big.
Thanks
Hari
MCDBA
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OHrx7cCREHA.2572@.TK2MSFTNGP12.phx.gbl...
> Correct. Incidentally, you might want to read the documentation on the
> commands before you do anything recommended in the newsgroups to make sure
> you understand what the commands do. After all, we are not affected if
your
> database is destroyed.
> "Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:138f701c44421$7e747f30$a601280a@.phx
.gbl...
>
Yesterday i posted my queston and i got couple of replies.
However, I was not clear with how i could back up a
transaction log file that is 25 GB when i have only 1GB
free space available.
Does it mean that i should not worry about the size of
the disk space available when i run the following code:
BACKUP LOG pubs WITH TRUNCATE_ONLY
Thank you,
MitraCorrect. Incidentally, you might want to read the documentation on the
commands before you do anything recommended in the newsgroups to make sure
you understand what the commands do. After all, we are not affected if your
database is destroyed.
"Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:138f701c44421$7e747f30$a601280a@.phx
.gbl...
> Hi All,
> Yesterday i posted my queston and i got couple of replies.
> However, I was not clear with how i could back up a
> transaction log file that is 25 GB when i have only 1GB
> free space available.
> Does it mean that i should not worry about the size of
> the disk space available when i run the following code:
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> Thank you,
> Mitra|||Hi,
Look into the SQL Server Recovery models documentation in Books online.
I recommend you to set the recovery model to "SIMPLE" incase if your
database is not a production data.
In this model the transaction file will be cleared on each commit. This will
ensure that your transaction file wont grow that big.
Thanks
Hari
MCDBA
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OHrx7cCREHA.2572@.TK2MSFTNGP12.phx.gbl...
> Correct. Incidentally, you might want to read the documentation on the
> commands before you do anything recommended in the newsgroups to make sure
> you understand what the commands do. After all, we are not affected if
your
> database is destroyed.
> "Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:138f701c44421$7e747f30$a601280a@.phx
.gbl...
>
Subscribe to:
Posts (Atom)