Problem! Transaction log backup job

Hi there,
I have a problem which began yesterday (monday). Till that day we have been
running a full backup job at 02:00 am - and then in working hours (09:00
am - 7:00 pm) we have been running a transaction log backup every hour -
this has been working fine for about a year.
Sunday we imported some data into our Navision (backend is the SQL 2000 Srv
SP3a).
Now the translog backup job just stands "Executing job step '(step 1)'" for
a very long time (i manually stop it every time) - this job takes 0.390
seconds normally.
In Enterprise Manager the database states that it is 16.266 MB large, and
1435 MB free. This seems as alot to me...
Please help - is there some maintenance I can run to check something...? And
can it be done while users are working? I'm pretty lost.
Best regards
Jakob H. HeidelbergSocrates
What does the job do? Full database backup or transaction log backup
How may data you are inserted?
Did you do it with one statement?
Also use DBCC LOGINFO(dtabase_id) to see whether or not there are opened
transaction.
Status 2 in the bottom lime indicates that there are active transaction/s
""Sokrates"" <remove@.noshit.dk> wrote in message
news:c0smip$7pe$1@.news.cybercity.dk...
> Hi there,
> I have a problem which began yesterday (monday). Till that day we have
been
> running a full backup job at 02:00 am - and then in working hours (09:00
> am - 7:00 pm) we have been running a transaction log backup every hour -
> this has been working fine for about a year.
> Sunday we imported some data into our Navision (backend is the SQL 2000
Srv
> SP3a).
> Now the translog backup job just stands "Executing job step '(step 1)'"
for
> a very long time (i manually stop it every time) - this job takes 0.390
> seconds normally.
> In Enterprise Manager the database states that it is 16.266 MB large, and
> 1435 MB free. This seems as alot to me...
> Please help - is there some maintenance I can run to check something...?
And
> can it be done while users are working? I'm pretty lost.
>
> Best regards
> Jakob H. Heidelberg
>|||Hi,
Execute sp_who from Query analyzer and check the BACKUP TRANSACTION process
id is blocked. If blocked possibly kill the
process id locking this process.
If there is no blocks then execute DBCC SQLPERF(LOGSPACE) and check the log
size. If the size Log is huge then the backup log
will take some time.
How to identify the log backup is going on,
From Query analyzer execute XP_FIXEDDRIVES and check whether the hard disk
free space is coming down.
Thanks
Hari
MCDBA
""Sokrates"" <remove@.noshit.dk> wrote in message
news:c0smip$7pe$1@.news.cybercity.dk...
> Hi there,
> I have a problem which began yesterday (monday). Till that day we have
been
> running a full backup job at 02:00 am - and then in working hours (09:00
> am - 7:00 pm) we have been running a transaction log backup every hour -
> this has been working fine for about a year.
> Sunday we imported some data into our Navision (backend is the SQL 2000
Srv
> SP3a).
> Now the translog backup job just stands "Executing job step '(step 1)'"
for
> a very long time (i manually stop it every time) - this job takes 0.390
> seconds normally.
> In Enterprise Manager the database states that it is 16.266 MB large, and
> 1435 MB free. This seems as alot to me...
> Please help - is there some maintenance I can run to check something...?
And
> can it be done while users are working? I'm pretty lost.
>
> Best regards
> Jakob H. Heidelberg
>|||It seems your import produces a very large amount of log records which of
course will be backed up when you backup the log. Use DBCC SQLPERF(logspace)
to see size of log and how full it is. Of you want to do a once off empty of
the log, use
BACKUP LOG dbname WITH NO_LOG
However, subsequent log backups will now be useless until you do a db
backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
""Sokrates"" <remove@.noshit.dk> wrote in message
news:c0smip$7pe$1@.news.cybercity.dk...
> Hi there,
> I have a problem which began yesterday (monday). Till that day we have
been
> running a full backup job at 02:00 am - and then in working hours (09:00
> am - 7:00 pm) we have been running a transaction log backup every hour -
> this has been working fine for about a year.
> Sunday we imported some data into our Navision (backend is the SQL 2000
Srv
> SP3a).
> Now the translog backup job just stands "Executing job step '(step 1)'"
for
> a very long time (i manually stop it every time) - this job takes 0.390
> seconds normally.
> In Enterprise Manager the database states that it is 16.266 MB large, and
> 1435 MB free. This seems as alot to me...
> Please help - is there some maintenance I can run to check something...?
And
> can it be done while users are working? I'm pretty lost.
>
> Best regards
> Jakob H. Heidelberg
>|||Thank you all for the responses - it's wonderfull to feel the team such a
teamspirit ;)
I did the "dbcc sqlperf(logspace)" wich showed more that 9 GB of the
database I'm having troubles with. I then did the "backup log database with
no_log" and everything seems to work nicely now. The job can run again, so
it must have been to many transactions for it to handle...?
As I understand it, my backup tonight at 02:00 (a FULL backup) will be the
basis for the translog backup I will perform through the day (every hour)
tomorrow - right?
Thanx all!
Jakob
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:ul10AVU9DHA.1548@.tk2msftngp13.phx.gbl...
> It seems your import produces a very large amount of log records which of
> course will be backed up when you backup the log. Use DBCC
SQLPERF(logspace)
> to see size of log and how full it is. Of you want to do a once off empty
of
> the log, use
> BACKUP LOG dbname WITH NO_LOG
> However, subsequent log backups will now be useless until you do a db
> backup.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> ""Sokrates"" <remove@.noshit.dk> wrote in message
> news:c0smip$7pe$1@.news.cybercity.dk...
> > Hi there,
> >
> > I have a problem which began yesterday (monday). Till that day we have
> been
> > running a full backup job at 02:00 am - and then in working hours (09:00
> > am - 7:00 pm) we have been running a transaction log backup every hour -
> > this has been working fine for about a year.
> >
> > Sunday we imported some data into our Navision (backend is the SQL 2000
> Srv
> > SP3a).
> >
> > Now the translog backup job just stands "Executing job step '(step 1)'"
> for
> > a very long time (i manually stop it every time) - this job takes 0.390
> > seconds normally.
> >
> > In Enterprise Manager the database states that it is 16.266 MB large,
and
> > 1435 MB free. This seems as alot to me...
> >
> > Please help - is there some maintenance I can run to check something...?
> And
> > can it be done while users are working? I'm pretty lost.
> >
> >
> > Best regards
> > Jakob H. Heidelberg
> >
> >
>|||Jakob,
> As I understand it, my backup tonight at 02:00 (a FULL backup) will be the
> basis for the translog backup I will perform through the day (every hour)
> tomorrow - right?
Yes, you have now broken the log sequence, so the log backups you take from
now up to 02:00 will be useless. When you do a db backup the following log
backups will be usable based on that db backup. (Think of what you did as
doing a log backup and throwing away the backup file.)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
""Sokrates"" <remove@.noshit.dk> wrote in message
news:c0t5ge$lgj$1@.news.cybercity.dk...
> Thank you all for the responses - it's wonderfull to feel the team such a
> teamspirit ;)
> I did the "dbcc sqlperf(logspace)" wich showed more that 9 GB of the
> database I'm having troubles with. I then did the "backup log database
with
> no_log" and everything seems to work nicely now. The job can run again, so
> it must have been to many transactions for it to handle...?
> As I understand it, my backup tonight at 02:00 (a FULL backup) will be the
> basis for the translog backup I will perform through the day (every hour)
> tomorrow - right?
>
> Thanx all!
> Jakob
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:ul10AVU9DHA.1548@.tk2msftngp13.phx.gbl...
> > It seems your import produces a very large amount of log records which
of
> > course will be backed up when you backup the log. Use DBCC
> SQLPERF(logspace)
> > to see size of log and how full it is. Of you want to do a once off
empty
> of
> > the log, use
> > BACKUP LOG dbname WITH NO_LOG
> >
> > However, subsequent log backups will now be useless until you do a db
> > backup.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > ""Sokrates"" <remove@.noshit.dk> wrote in message
> > news:c0smip$7pe$1@.news.cybercity.dk...
> > > Hi there,
> > >
> > > I have a problem which began yesterday (monday). Till that day we have
> > been
> > > running a full backup job at 02:00 am - and then in working hours
(09:00
> > > am - 7:00 pm) we have been running a transaction log backup every
hour -
> > > this has been working fine for about a year.
> > >
> > > Sunday we imported some data into our Navision (backend is the SQL
2000
> > Srv
> > > SP3a).
> > >
> > > Now the translog backup job just stands "Executing job step '(step
1)'"
> > for
> > > a very long time (i manually stop it every time) - this job takes
0.390
> > > seconds normally.
> > >
> > > In Enterprise Manager the database states that it is 16.266 MB large,
> and
> > > 1435 MB free. This seems as alot to me...
> > >
> > > Please help - is there some maintenance I can run to check
something...?
> > And
> > > can it be done while users are working? I'm pretty lost.
> > >
> > >
> > > Best regards
> > > Jakob H. Heidelberg
> > >
> > >
> >
> >
>|||Thanx, my backup ran nicelys today - 02:00 the FULL and then in the working
hours the TRANS LOG backup job did the job - so thanx, we are back "on
line".
Jakob
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:%23GmlKKW9DHA.3444@.TK2MSFTNGP09.phx.gbl...
> Jakob,
> > As I understand it, my backup tonight at 02:00 (a FULL backup) will be
the
> > basis for the translog backup I will perform through the day (every
hour)
> > tomorrow - right?
> Yes, you have now broken the log sequence, so the log backups you take
from
> now up to 02:00 will be useless. When you do a db backup the following log
> backups will be usable based on that db backup. (Think of what you did as
> doing a log backup and throwing away the backup file.)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> ""Sokrates"" <remove@.noshit.dk> wrote in message
> news:c0t5ge$lgj$1@.news.cybercity.dk...
> > Thank you all for the responses - it's wonderfull to feel the team such
a
> > teamspirit ;)
> >
> > I did the "dbcc sqlperf(logspace)" wich showed more that 9 GB of the
> > database I'm having troubles with. I then did the "backup log database
> with
> > no_log" and everything seems to work nicely now. The job can run again,
so
> > it must have been to many transactions for it to handle...?
> >
> > As I understand it, my backup tonight at 02:00 (a FULL backup) will be
the
> > basis for the translog backup I will perform through the day (every
hour)
> > tomorrow - right?
> >
> >
> > Thanx all!
> > Jakob
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev
i
> > en meddelelse news:ul10AVU9DHA.1548@.tk2msftngp13.phx.gbl...
> > > It seems your import produces a very large amount of log records which
> of
> > > course will be backed up when you backup the log. Use DBCC
> > SQLPERF(logspace)
> > > to see size of log and how full it is. Of you want to do a once off
> empty
> > of
> > > the log, use
> > > BACKUP LOG dbname WITH NO_LOG
> > >
> > > However, subsequent log backups will now be useless until you do a db
> > > backup.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > ""Sokrates"" <remove@.noshit.dk> wrote in message
> > > news:c0smip$7pe$1@.news.cybercity.dk...
> > > > Hi there,
> > > >
> > > > I have a problem which began yesterday (monday). Till that day we
have
> > > been
> > > > running a full backup job at 02:00 am - and then in working hours
> (09:00
> > > > am - 7:00 pm) we have been running a transaction log backup every
> hour -
> > > > this has been working fine for about a year.
> > > >
> > > > Sunday we imported some data into our Navision (backend is the SQL
> 2000
> > > Srv
> > > > SP3a).
> > > >
> > > > Now the translog backup job just stands "Executing job step '(step
> 1)'"
> > > for
> > > > a very long time (i manually stop it every time) - this job takes
> 0.390
> > > > seconds normally.
> > > >
> > > > In Enterprise Manager the database states that it is 16.266 MB
large,
> > and
> > > > 1435 MB free. This seems as alot to me...
> > > >
> > > > Please help - is there some maintenance I can run to check
> something...?
> > > And
> > > > can it be done while users are working? I'm pretty lost.
> > > >
> > > >
> > > > Best regards
> > > > Jakob H. Heidelberg
> > > >
> > > >
> > >
> > >
> >
> >
>