Howdy,
We have very a tight limit on disk space for backups.
We have a 3 GB db that we do full a weekly full backup on, followed by hourly tran log backups for the rest of the week - this adds up to a LOT of disk space usage ( we cant do differential backups due to space limitations, as diffs are almost 3 GB in size... ).
As disk space is limited, we can only keep a weeks worth of backups on the disk, and previous weeks get backed up onto tape. No big deal there....
So, when weekly full backup occurs, the only way to recover the previous weeks data is to restore previous full backup & allpy the required number of tran logs.
Is there another way to do backups perhaps using file backups , so we dont have to go all the way back to the previous weeks backups?
Thanks in advance,
SG.Do you have a tape device on the machine? If so you could copy the backup off every night and run a full backup every night...if this is an important server, buy more disks!! I bought an external 80gb drive for $90, I would push back and have the company buy more space.
HTH|||Hi,
Thanks - I have suggested to the clients the disk space option is the best way of going, I just wanted someone else to say it too. I figure its not worth the grief for skimping on disk space for the sake of operational integrity.
Cheers
SG|||Hi,
If you haven't yet bought hard disk, you could do this. You could schedule the full backups daily and 'overwrite the existing media' instead of appending. Also you can delete the transactional log after the successful backup at the end of the day.
You could continue with the weekly tape backups too. If you need to restore, you need to the full with no recovery and the tran log for just the previous day. Ofcourse if the restoration is beyond a days time, you will need to use the tape, previous days full backup and transaction logs.
Hope this help you.
Regards,
Showing posts with label tran. Show all posts
Showing posts with label tran. Show all posts
Thursday, March 8, 2012
Monday, February 13, 2012
backup job never completes
Hello,
I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring problem
. A maintenance plan does full back and tran log backup on the one database
with no other steps. It works fine for weeks or months at a time. Then the j
obs start executing, but ne
ver complete. No errors are written to the NT log, no errors for the job. I
just had it write to a text file and it created the file when the scheduled
job started, but it is empty. The job says "executing" and has to be manuall
y canceled. Trying to run t
he job manually does the same thing - never completes. I can do manual backu
ps.
The weird thing is, if I re-start the SQL service, both full and trans log b
ackups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6, whic
h is currently installed.
There are many other databases on this server and all other maintenance plan
s work with no problems. I have deleted jobs and the plan and recreated it t
o no avail.
Any Ideas'
Please help.
PeteCould be a problem with your SQL Agent service. When this happened, have you
checked the SQL Server error log and the Agent error log?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"PeteN" <anonymous@.discussions.microsoft.com> wrote in message
news:A9F1C8E4-7EC1-4395-A41C-91F210DBC763@.microsoft.com...
Hello,
I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring
problem. A maintenance plan does full back and tran log backup on the one
database with no other steps. It works fine for weeks or months at a time.
Then the jobs start executing, but never complete. No errors are written to
the NT log, no errors for the job. I just had it write to a text file and it
created the file when the scheduled job started, but it is empty. The job
says "executing" and has to be manually canceled. Trying to run the job
manually does the same thing - never completes. I can do manual backups.
The weird thing is, if I re-start the SQL service, both full and trans log
backups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6,
which is currently installed.
There are many other databases on this server and all other maintenance
plans work with no problems. I have deleted jobs and the plan and recreated
it to no avail.
Any Ideas'
Please help.
Pete|||Is the last step an email notification of success or failure? It could be a
stuck MAPI service. That happens fairly often in SQL.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"PeteN" <anonymous@.discussions.microsoft.com> wrote in message
news:A9F1C8E4-7EC1-4395-A41C-91F210DBC763@.microsoft.com...
> Hello,
> I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring
problem. A maintenance plan does full back and tran log backup on the one
database with no other steps. It works fine for weeks or months at a time.
Then the jobs start executing, but never complete. No errors are written to
the NT log, no errors for the job. I just had it write to a text file and it
created the file when the scheduled job started, but it is empty. The job
says "executing" and has to be manually canceled. Trying to run the job
manually does the same thing - never completes. I can do manual backups.
> The weird thing is, if I re-start the SQL service, both full and trans log
backups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6,
which is currently installed.
> There are many other databases on this server and all other maintenance
plans work with no problems. I have deleted jobs and the plan and recreated
it to no avail.
> Any Ideas'
> Please help.
> Pete
>|||Neither of these logs have error messages from this. I have also tried to st
op and re-start the server agent, but that did not help. If I manually start
the job, It has the same problem - never completes.|||There are no steps other than the full and tran backup - and now the write e
rrors to text file , which came up as an empty file. There is no email servi
ce running.|||It seems that there is no answer to this, SO... I went with Andrew's advice
posted elswhere on this board and got rid of the maintenance plan and create
d and scheduled my own backup jobs. So far, all is working good.
Thanks for your help.
Pete
I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring problem
. A maintenance plan does full back and tran log backup on the one database
with no other steps. It works fine for weeks or months at a time. Then the j
obs start executing, but ne
ver complete. No errors are written to the NT log, no errors for the job. I
just had it write to a text file and it created the file when the scheduled
job started, but it is empty. The job says "executing" and has to be manuall
y canceled. Trying to run t
he job manually does the same thing - never completes. I can do manual backu
ps.
The weird thing is, if I re-start the SQL service, both full and trans log b
ackups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6, whic
h is currently installed.
There are many other databases on this server and all other maintenance plan
s work with no problems. I have deleted jobs and the plan and recreated it t
o no avail.
Any Ideas'
Please help.
PeteCould be a problem with your SQL Agent service. When this happened, have you
checked the SQL Server error log and the Agent error log?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"PeteN" <anonymous@.discussions.microsoft.com> wrote in message
news:A9F1C8E4-7EC1-4395-A41C-91F210DBC763@.microsoft.com...
Hello,
I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring
problem. A maintenance plan does full back and tran log backup on the one
database with no other steps. It works fine for weeks or months at a time.
Then the jobs start executing, but never complete. No errors are written to
the NT log, no errors for the job. I just had it write to a text file and it
created the file when the scheduled job started, but it is empty. The job
says "executing" and has to be manually canceled. Trying to run the job
manually does the same thing - never completes. I can do manual backups.
The weird thing is, if I re-start the SQL service, both full and trans log
backups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6,
which is currently installed.
There are many other databases on this server and all other maintenance
plans work with no problems. I have deleted jobs and the plan and recreated
it to no avail.
Any Ideas'
Please help.
Pete|||Is the last step an email notification of success or failure? It could be a
stuck MAPI service. That happens fairly often in SQL.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"PeteN" <anonymous@.discussions.microsoft.com> wrote in message
news:A9F1C8E4-7EC1-4395-A41C-91F210DBC763@.microsoft.com...
> Hello,
> I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring
problem. A maintenance plan does full back and tran log backup on the one
database with no other steps. It works fine for weeks or months at a time.
Then the jobs start executing, but never complete. No errors are written to
the NT log, no errors for the job. I just had it write to a text file and it
created the file when the scheduled job started, but it is empty. The job
says "executing" and has to be manually canceled. Trying to run the job
manually does the same thing - never completes. I can do manual backups.
> The weird thing is, if I re-start the SQL service, both full and trans log
backups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6,
which is currently installed.
> There are many other databases on this server and all other maintenance
plans work with no problems. I have deleted jobs and the plan and recreated
it to no avail.
> Any Ideas'
> Please help.
> Pete
>|||Neither of these logs have error messages from this. I have also tried to st
op and re-start the server agent, but that did not help. If I manually start
the job, It has the same problem - never completes.|||There are no steps other than the full and tran backup - and now the write e
rrors to text file , which came up as an empty file. There is no email servi
ce running.|||It seems that there is no answer to this, SO... I went with Andrew's advice
posted elswhere on this board and got rid of the maintenance plan and create
d and scheduled my own backup jobs. So far, all is working good.
Thanks for your help.
Pete
backup job never completes
Hello,
I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring problem. A maintenance plan does full back and tran log backup on the one database with no other steps. It works fine for weeks or months at a time. Then the jobs start executing, but ne
ver complete. No errors are written to the NT log, no errors for the job. I just had it write to a text file and it created the file when the scheduled job started, but it is empty. The job says "executing" and has to be manually canceled. Trying to run t
he job manually does the same thing - never completes. I can do manual backups.
The weird thing is, if I re-start the SQL service, both full and trans log backups will work for some arbitrary period of time before the problem comes up again. This has been happening with different service packs up to 6, which is currently installed.
There are many other databases on this server and all other maintenance plans work with no problems. I have deleted jobs and the plan and recreated it to no avail.
Any Ideas?
Please help.
Pete
Could be a problem with your SQL Agent service. When this happened, have you
checked the SQL Server error log and the Agent error log?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"PeteN" <anonymous@.discussions.microsoft.com> wrote in message
news:A9F1C8E4-7EC1-4395-A41C-91F210DBC763@.microsoft.com...
Hello,
I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring
problem. A maintenance plan does full back and tran log backup on the one
database with no other steps. It works fine for weeks or months at a time.
Then the jobs start executing, but never complete. No errors are written to
the NT log, no errors for the job. I just had it write to a text file and it
created the file when the scheduled job started, but it is empty. The job
says "executing" and has to be manually canceled. Trying to run the job
manually does the same thing - never completes. I can do manual backups.
The weird thing is, if I re-start the SQL service, both full and trans log
backups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6,
which is currently installed.
There are many other databases on this server and all other maintenance
plans work with no problems. I have deleted jobs and the plan and recreated
it to no avail.
Any Ideas?
Please help.
Pete
|||Is the last step an email notification of success or failure? It could be a
stuck MAPI service. That happens fairly often in SQL.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"PeteN" <anonymous@.discussions.microsoft.com> wrote in message
news:A9F1C8E4-7EC1-4395-A41C-91F210DBC763@.microsoft.com...
> Hello,
> I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring
problem. A maintenance plan does full back and tran log backup on the one
database with no other steps. It works fine for weeks or months at a time.
Then the jobs start executing, but never complete. No errors are written to
the NT log, no errors for the job. I just had it write to a text file and it
created the file when the scheduled job started, but it is empty. The job
says "executing" and has to be manually canceled. Trying to run the job
manually does the same thing - never completes. I can do manual backups.
> The weird thing is, if I re-start the SQL service, both full and trans log
backups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6,
which is currently installed.
> There are many other databases on this server and all other maintenance
plans work with no problems. I have deleted jobs and the plan and recreated
it to no avail.
> Any Ideas?
> Please help.
> Pete
>
|||Neither of these logs have error messages from this. I have also tried to stop and re-start the server agent, but that did not help. If I manually start the job, It has the same problem - never completes.
|||There are no steps other than the full and tran backup - and now the write errors to text file , which came up as an empty file. There is no email service running.
|||It seems that there is no answer to this, SO... I went with Andrew's advice posted elswhere on this board and got rid of the maintenance plan and created and scheduled my own backup jobs. So far, all is working good.
Thanks for your help.
Pete
I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring problem. A maintenance plan does full back and tran log backup on the one database with no other steps. It works fine for weeks or months at a time. Then the jobs start executing, but ne
ver complete. No errors are written to the NT log, no errors for the job. I just had it write to a text file and it created the file when the scheduled job started, but it is empty. The job says "executing" and has to be manually canceled. Trying to run t
he job manually does the same thing - never completes. I can do manual backups.
The weird thing is, if I re-start the SQL service, both full and trans log backups will work for some arbitrary period of time before the problem comes up again. This has been happening with different service packs up to 6, which is currently installed.
There are many other databases on this server and all other maintenance plans work with no problems. I have deleted jobs and the plan and recreated it to no avail.
Any Ideas?
Please help.
Pete
Could be a problem with your SQL Agent service. When this happened, have you
checked the SQL Server error log and the Agent error log?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"PeteN" <anonymous@.discussions.microsoft.com> wrote in message
news:A9F1C8E4-7EC1-4395-A41C-91F210DBC763@.microsoft.com...
Hello,
I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring
problem. A maintenance plan does full back and tran log backup on the one
database with no other steps. It works fine for weeks or months at a time.
Then the jobs start executing, but never complete. No errors are written to
the NT log, no errors for the job. I just had it write to a text file and it
created the file when the scheduled job started, but it is empty. The job
says "executing" and has to be manually canceled. Trying to run the job
manually does the same thing - never completes. I can do manual backups.
The weird thing is, if I re-start the SQL service, both full and trans log
backups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6,
which is currently installed.
There are many other databases on this server and all other maintenance
plans work with no problems. I have deleted jobs and the plan and recreated
it to no avail.
Any Ideas?
Please help.
Pete
|||Is the last step an email notification of success or failure? It could be a
stuck MAPI service. That happens fairly often in SQL.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"PeteN" <anonymous@.discussions.microsoft.com> wrote in message
news:A9F1C8E4-7EC1-4395-A41C-91F210DBC763@.microsoft.com...
> Hello,
> I have a small (300mb) db on SQL 7 on NT. We have had a re-occurring
problem. A maintenance plan does full back and tran log backup on the one
database with no other steps. It works fine for weeks or months at a time.
Then the jobs start executing, but never complete. No errors are written to
the NT log, no errors for the job. I just had it write to a text file and it
created the file when the scheduled job started, but it is empty. The job
says "executing" and has to be manually canceled. Trying to run the job
manually does the same thing - never completes. I can do manual backups.
> The weird thing is, if I re-start the SQL service, both full and trans log
backups will work for some arbitrary period of time before the problem comes
up again. This has been happening with different service packs up to 6,
which is currently installed.
> There are many other databases on this server and all other maintenance
plans work with no problems. I have deleted jobs and the plan and recreated
it to no avail.
> Any Ideas?
> Please help.
> Pete
>
|||Neither of these logs have error messages from this. I have also tried to stop and re-start the server agent, but that did not help. If I manually start the job, It has the same problem - never completes.
|||There are no steps other than the full and tran backup - and now the write errors to text file , which came up as an empty file. There is no email service running.
|||It seems that there is no answer to this, SO... I went with Andrew's advice posted elswhere on this board and got rid of the maintenance plan and created and scheduled my own backup jobs. So far, all is working good.
Thanks for your help.
Pete
Subscribe to:
Posts (Atom)