Hi I'm just looking for a little bit of advice to point me in the right direction before I start to put this together.
The task I have is too automate the clear down of the transaction logs. Presently I am manually going in and using this method:
BACKUP LOG {database name} WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( {log file name},2 )
However now we have decided this would be best to be automated and I'm faily new to SQL server so I'm not sure the best way to go about this. So I was hoping someone could point me in the right direction that I go about doing it so I don't go off on a complete tangent. Ow and Can anyone think of any problem automating this job could cause.
My eternal graitude, EdWhy are you backing up with TRUNCATE ONLY? This does not give you any recoverability for transactions occuring after the last backup. If you really don't need point-in-time recoverability then you could just put the database in simple recovery mode.
blindman|||Ahh I see
I was trying to come up with a way of reducing the size transaction Log without, stopping the database and restarting and I came across this advice, I used the first statment because otherwise the the transactionlog won't shrink much. I didn't really consider the point that (I said I was new too this) this basically mean that the it is no longer actuly producing a functional backup.
Ouch
Well in that case I need to come up with a solution, Do you have any Idea what the best time to clear down the transaction logs would be? erm could I run this immediately before the back up. but then if the backup is faulty we would lose a whole days work and if I run it just after the backup then the next days would be useless, unless I run it immediately and make sure their is no transaction inbetween the backup and the transaction shrinking!
Do you know how to restore the transaction log to it orginal functioning glory!
Thanks Again Ed
Showing posts with label task. Show all posts
Showing posts with label task. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Advice Needed
I need to set up a table to define times when tasks will run. It needs to
allow for multiple times per day and if the task should run on the w
ends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs t
o allow for multiple times per day and if the task should run on the wend
s. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
.);
Just fill in the schedule for each task.
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip wends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>|||Yes you can run the job except for the wends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule an
d
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need t
he
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip wends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>sql
allow for multiple times per day and if the task should run on the w
ends.Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs t
o allow for multiple times per day and if the task should run on the w
ends. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
.);
Just fill in the schedule for each task.
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip w
ends. Can I do allthis using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>|||Yes you can run the job except for the w
ends. In Enterprise Manager, goto the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule an
d
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need t
he
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip w
ends. Can I do all> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>sql
Advice Needed
I need to set up a table to define times when tasks will run. It needs to
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.
>> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
..);
Just fill in the schedule for each task.
[vbcol=seagreen]
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.
|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>
|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...[vbcol=seagreen]
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule and
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need the
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.
>> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
..);
Just fill in the schedule for each task.
[vbcol=seagreen]
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.
|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>
|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...[vbcol=seagreen]
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule and
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need the
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>
Advice Needed
I need to set up a table to define times when tasks will run. It needs to
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs t
o allow for multiple times per day and if the task should run on the weekend
s. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
.);
Just fill in the schedule for each task.
[vbcol=seagreen]
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...[vbcol=seagreen]
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule an
d
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need t
he
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs t
o allow for multiple times per day and if the task should run on the weekend
s. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
.);
Just fill in the schedule for each task.
[vbcol=seagreen]
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...[vbcol=seagreen]
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule an
d
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need t
he
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>
Advice Needed
I need to set up a table to define times when tasks will run. It needs to
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
..);
Just fill in the schedule for each task.
>> Once the task has run once for the specified service window for that time of day I need to prevent the task from running again. <<
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> >> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
> >> Once the task has run once for the specified service window for that time of day I need to prevent the task from running again. <<
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>> >> I need to set up a table to define times when tasks will run. It
>> >> needs to allow for multiple times per day and if the task should run
>> >> on the weekends. <<
>> CREATE TABLE TaskSchedule
>> (task_name CHAR(20) NOT NULL,
>> task_time DATETIME NOT NULL,
>> PRIMARY KEY (task_name, task_time),
>> ..);
>> Just fill in the schedule for each task.
>> >> Once the task has run once for the specified service window for that
>> >> time of day I need to prevent the task from running again. <<
>> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
>> events. What were you thinking about doing? We do not compute things
>> in SQL; we put them in tables and do joins.
>>|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule and
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need the
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> > I'm wondering why you need a table. If the tasks are SQL Scripts, then
> > just
> > run a scheduled DTS job or Store Procedure. You can create jobs to run at
> > the specific times and they would only run once. Plus you can have
> > failure
> > emails if the job fails and will contact you.
> >
> > "--CELKO--" wrote:
> >
> >> >> I need to set up a table to define times when tasks will run. It
> >> >> needs to allow for multiple times per day and if the task should run
> >> >> on the weekends. <<
> >>
> >> CREATE TABLE TaskSchedule
> >> (task_name CHAR(20) NOT NULL,
> >> task_time DATETIME NOT NULL,
> >> PRIMARY KEY (task_name, task_time),
> >> ..);
> >>
> >> Just fill in the schedule for each task.
> >>
> >> >> Once the task has run once for the specified service window for that
> >> >> time of day I need to prevent the task from running again. <<
> >>
> >> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> >> events. What were you thinking about doing? We do not compute things
> >> in SQL; we put them in tables and do joins.
> >>
> >>
>
>sql
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
..);
Just fill in the schedule for each task.
>> Once the task has run once for the specified service window for that time of day I need to prevent the task from running again. <<
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> >> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
> >> Once the task has run once for the specified service window for that time of day I need to prevent the task from running again. <<
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>> >> I need to set up a table to define times when tasks will run. It
>> >> needs to allow for multiple times per day and if the task should run
>> >> on the weekends. <<
>> CREATE TABLE TaskSchedule
>> (task_name CHAR(20) NOT NULL,
>> task_time DATETIME NOT NULL,
>> PRIMARY KEY (task_name, task_time),
>> ..);
>> Just fill in the schedule for each task.
>> >> Once the task has run once for the specified service window for that
>> >> time of day I need to prevent the task from running again. <<
>> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
>> events. What were you thinking about doing? We do not compute things
>> in SQL; we put them in tables and do joins.
>>|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule and
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need the
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> > I'm wondering why you need a table. If the tasks are SQL Scripts, then
> > just
> > run a scheduled DTS job or Store Procedure. You can create jobs to run at
> > the specific times and they would only run once. Plus you can have
> > failure
> > emails if the job fails and will contact you.
> >
> > "--CELKO--" wrote:
> >
> >> >> I need to set up a table to define times when tasks will run. It
> >> >> needs to allow for multiple times per day and if the task should run
> >> >> on the weekends. <<
> >>
> >> CREATE TABLE TaskSchedule
> >> (task_name CHAR(20) NOT NULL,
> >> task_time DATETIME NOT NULL,
> >> PRIMARY KEY (task_name, task_time),
> >> ..);
> >>
> >> Just fill in the schedule for each task.
> >>
> >> >> Once the task has run once for the specified service window for that
> >> >> time of day I need to prevent the task from running again. <<
> >>
> >> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> >> events. What were you thinking about doing? We do not compute things
> >> in SQL; we put them in tables and do joins.
> >>
> >>
>
>sql
Advice badly needed
I have just been given a ridiculous task... There is an existing SQL database that has one table with two fields. The first is an ID column, the second has information that I somehow need to retrieve. This is my dilemma. Here's something like how the ROWS look in this table:
__________________________________________________ ______________
PRACTICE: Practice 1 COMPLETE HISTORY REPORT PAGE:
DATE: AUG 18, 1973 TIME: 9:04:36
NULL
NULL
12-53-48 DOE JOHN BALANCE: .00
123 Street DR
Somewhere ST 12345
DATE DR PROC.. MD DIAG.. DESCRIPTION........... TRN.AMOUNT TR.BALAN
NULL
JOHN DOE
08/12/94 13 73630 1 959.7 FOOT-COMPLETE 3+ VIEWS 23.35 .00
08/23/94 13 *INS. FILED WITH TRAVELERS
08/29/94 13 39 PAYMENT - WORKER'S COMP. -23.35 .00
TOTAL DUE: .00
__________________________________________________ ______________
Each line is a seperate row in the table!?! Also, the lines with the dates, near the bottom, can have different numbers of rows (Sometimes several, sometimes hundreds) I don't know how this data was imported. It appears that I have 122000 accounts but they are spread across 3.5 million rows... I need to devise some way to take this data out.. Can anyone please give some advice??I'm having trouble understanding your explaination. Can you post the assignment as the instructor gave it to you?
-PatP|||Basically I've been asked to create an Intranet ASP.Net reporting feature for this data. I have to be able to split up this information into some sort of normalized relational structure. I need to be able to grab the start of each record (IE the practice Name row) then each row within this collection of rows. All of this data is in one column! there is no structure to it, as of now. I want to be able to seperate each account into other tables to give some structure... does that make sense?|||So you just have this text blob sitting all by its lonesome in a row, as though somebody dumped a chunk of XML in there? No database enforced schema at all?
Bleach!
-PatP|||I wish it was in one row...
Row 1: PRACTICE: Practice 1 COMPLETE HISTORY REPORT PAGE:
Row 2: DATE: AUG 18, 1973 TIME: 9:04:36
Row 3: NULL
See what I mean?! This is one field, one table.|||If at first you don't succeed, apply brute force. Cursor through the monstrosity, and make a structured schema from it. If worst comes to worst, plop the parts into a work table with pure character columns, then slice and dice them as needed.
What you've got now is a fiasco in the making. YOu have to enforce some structure onto it, soon!
-PatP|||Yes, structure has to be made of this. There are no updates being made to it. It is an old archive that someone imported from an Oracle DB from our pre-merger and it's just sitting there, needing connectivity for reporting. I had hoped to be able to do some types of nested queries to extract what I needed, adding them to another set of tables as I went. Mostly what I know about cursors are that I hate them and can't ever seem to get them to work correctly. As for making the structured schema from it.. would you be able to possibly point me towards any documentation on accomplishing that? Your answer seemed to be a bit above my head:)|||can't you use "text to columns" in excel and use <space> as the delimeter
or write a macro to do repeated keystrokes to structure the data as someone suggested previously|||The exact process varies a little bit, depending on what tools you are using to process your data heaps (the unstructured tables).
Hopefully you know how to interpret the heap. In other words there are either a fixed number of rows for each logical group, or there is some kind of "marker" that will show you where one group ends and another group begins. I assume that the sequence of the rows is fixed (they stay in the same order) and significant (that the order matters). Once you understand the structure of your heap, then you can start to make better sense of it.
You basically want to write some kind of loop that will process each group of related pieces of text (rows within your heap), and put them into a table with more meaningful rows and columns (probably one column for each row in the heap, unless some of them are useless and/or missing).
Without understanding exactly what you've got, I can't think of a way to come up with anything like a step-by-step solution. You'll have to find someone that understands the data to help you structure it.
-PatP|||Ok, from trying to work with this via TSQL using cursors, I've found too many potential errors caused by inconsistency of the original data input. I've now created a VB.Net Windows application to handle the data collection/transformation which encapsulates the transformed accounts into a class before attempting to insert into the new normalized relational database via stored procedure.
I have one question (for now) about form refreshing. The form that allows the user to begin the transformation counts the actual accounts to be worked, placing that number into a label. As each record is completed, I cast back to the form, reducing a label for accounts remaining and increasing a label for accounts complete, then refresh the form to reflect the changes. This process occurs rather quickly but seems to make an odd visual effect to the forms groupbox and listbox controls. They seem to shake as the transformation thread is working. Now, this application is really only to be used once, but I would like to know if this visual effect can be prevented when developing future reusable projects.|||Yes, it can, but I don't have an easy reference available. Maybe someone else has code or a URL they can post. If not, remind me next week and I'll find one for you.
-PatP|||Thanks Pat!
__________________________________________________ ______________
PRACTICE: Practice 1 COMPLETE HISTORY REPORT PAGE:
DATE: AUG 18, 1973 TIME: 9:04:36
NULL
NULL
12-53-48 DOE JOHN BALANCE: .00
123 Street DR
Somewhere ST 12345
DATE DR PROC.. MD DIAG.. DESCRIPTION........... TRN.AMOUNT TR.BALAN
NULL
JOHN DOE
08/12/94 13 73630 1 959.7 FOOT-COMPLETE 3+ VIEWS 23.35 .00
08/23/94 13 *INS. FILED WITH TRAVELERS
08/29/94 13 39 PAYMENT - WORKER'S COMP. -23.35 .00
TOTAL DUE: .00
__________________________________________________ ______________
Each line is a seperate row in the table!?! Also, the lines with the dates, near the bottom, can have different numbers of rows (Sometimes several, sometimes hundreds) I don't know how this data was imported. It appears that I have 122000 accounts but they are spread across 3.5 million rows... I need to devise some way to take this data out.. Can anyone please give some advice??I'm having trouble understanding your explaination. Can you post the assignment as the instructor gave it to you?
-PatP|||Basically I've been asked to create an Intranet ASP.Net reporting feature for this data. I have to be able to split up this information into some sort of normalized relational structure. I need to be able to grab the start of each record (IE the practice Name row) then each row within this collection of rows. All of this data is in one column! there is no structure to it, as of now. I want to be able to seperate each account into other tables to give some structure... does that make sense?|||So you just have this text blob sitting all by its lonesome in a row, as though somebody dumped a chunk of XML in there? No database enforced schema at all?
Bleach!
-PatP|||I wish it was in one row...
Row 1: PRACTICE: Practice 1 COMPLETE HISTORY REPORT PAGE:
Row 2: DATE: AUG 18, 1973 TIME: 9:04:36
Row 3: NULL
See what I mean?! This is one field, one table.|||If at first you don't succeed, apply brute force. Cursor through the monstrosity, and make a structured schema from it. If worst comes to worst, plop the parts into a work table with pure character columns, then slice and dice them as needed.
What you've got now is a fiasco in the making. YOu have to enforce some structure onto it, soon!
-PatP|||Yes, structure has to be made of this. There are no updates being made to it. It is an old archive that someone imported from an Oracle DB from our pre-merger and it's just sitting there, needing connectivity for reporting. I had hoped to be able to do some types of nested queries to extract what I needed, adding them to another set of tables as I went. Mostly what I know about cursors are that I hate them and can't ever seem to get them to work correctly. As for making the structured schema from it.. would you be able to possibly point me towards any documentation on accomplishing that? Your answer seemed to be a bit above my head:)|||can't you use "text to columns" in excel and use <space> as the delimeter
or write a macro to do repeated keystrokes to structure the data as someone suggested previously|||The exact process varies a little bit, depending on what tools you are using to process your data heaps (the unstructured tables).
Hopefully you know how to interpret the heap. In other words there are either a fixed number of rows for each logical group, or there is some kind of "marker" that will show you where one group ends and another group begins. I assume that the sequence of the rows is fixed (they stay in the same order) and significant (that the order matters). Once you understand the structure of your heap, then you can start to make better sense of it.
You basically want to write some kind of loop that will process each group of related pieces of text (rows within your heap), and put them into a table with more meaningful rows and columns (probably one column for each row in the heap, unless some of them are useless and/or missing).
Without understanding exactly what you've got, I can't think of a way to come up with anything like a step-by-step solution. You'll have to find someone that understands the data to help you structure it.
-PatP|||Ok, from trying to work with this via TSQL using cursors, I've found too many potential errors caused by inconsistency of the original data input. I've now created a VB.Net Windows application to handle the data collection/transformation which encapsulates the transformed accounts into a class before attempting to insert into the new normalized relational database via stored procedure.
I have one question (for now) about form refreshing. The form that allows the user to begin the transformation counts the actual accounts to be worked, placing that number into a label. As each record is completed, I cast back to the form, reducing a label for accounts remaining and increasing a label for accounts complete, then refresh the form to reflect the changes. This process occurs rather quickly but seems to make an odd visual effect to the forms groupbox and listbox controls. They seem to shake as the transformation thread is working. Now, this application is really only to be used once, but I would like to know if this visual effect can be prevented when developing future reusable projects.|||Yes, it can, but I don't have an easy reference available. Maybe someone else has code or a URL they can post. If not, remind me next week and I'll find one for you.
-PatP|||Thanks Pat!
advicce needed, which task to choose in DTS package
Hi guys, I need to bits of advice.
I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script
The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.
many thanks
SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?
If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.
HTH!
|||Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?
many thanks
|||I think you'd need a DTS package with the following flow:
SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task
Your T-SQL Task could be an SQL stored procedure which did something like the following:
Code Snippet
CREATE PROC AddRecords
AS
UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2
FROM DstTable Dst
INNER JOIN HoldingTable Hld
ON Dst.Col4 = Hld.Col4
INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld
WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)
DELETE
FROM HoldingTable
Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.
You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.
HTH!
|||Thanks for that.
advicce needed, which task to choose in DTS package
Hi guys, I need to bits of advice.
I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script
The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.
many thanks
SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?
If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.
HTH!
|||Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?
many thanks
|||I think you'd need a DTS package with the following flow:
SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task
Your T-SQL Task could be an SQL stored procedure which did something like the following:
Code Snippet
CREATE PROC AddRecords
AS
UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2
FROM DstTable Dst
INNER JOIN HoldingTable Hld
ON Dst.Col4 = Hld.Col4
INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld
WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)
DELETE
FROM HoldingTable
Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.
You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.
HTH!
|||Thanks for that.
Monday, March 19, 2012
Advanced T-SQL Question: Producing Backup Window Timeline Chart
I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroup
s.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroup
s.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>
Advanced T-SQL Question: Producing Backup Window Timeline Chart
I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>
Advanced T-SQL Question: Produce Backup Window Gantt Chart
I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookI'm not familiar with these tables but this should give you
the output you want
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
DATEPART(hour,DATEADD(second,
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobStart,
DATEPART(hour,DATEADD(second,
last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobEnd,
last_run_duration
FROM sysjobservers) s ON j.job_id = s.job_id|||> It might be nice to get historical
> information but I'm not sure where I'd find it.
You have 5 tables in msdb with historical information about backup. The name
of the tables all start
with backup. For instance, check out msdb.dbo.backupset.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message
news:1144417911.795292.287140@.z34g2000cwc.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>|||Corrected (and simplified) version that handles jobs spanning midnight
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
last_run_time / 10000 AS JobStart,
(last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600) / 3600 AS JobEnd
FROM sysjobservers) s ON j.job_id = s.job_id|||Roger,
yet another approach:
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = '('+replicate('_',24)+')'
set @.busy_mask = replicate('X',24)
return(
substring(@.idle_mask, 1, @.from_hour)+
substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
end
go
-- unit test --
select dbo.backup_mask(1,5)
select dbo.backup_mask(3,15)
select dbo.backup_mask(1,24)
select dbo.backup_mask(19,24)
(XXXXX___________________)
(1 row(s) affected)
(__XXXXXXXXXXXXX_________)
(1 row(s) affected)
(XXXXXXXXXXXXXXXXXXXXXXXX)
(1 row(s) affected)
(__________________XXXXXX)
(1 row(s) affected)
go
drop function backup_mask
go|||And another, using Alexander's function as a start,
just because we have QUOTENAME and STUFF.
(Note that the behavior of QUOTENAME when the
second parameter is '(' is not documented.)
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = replicate('_',24)
set @.busy_mask = replicate('X',24)
declare @.duration int
set @.duration = @.to_hour - @.from_hour + 1
if @.duration <= 0
return quotename(@.idle_mask,'(')
return
quotename(stuff(@.idle_mask,@.from_hour,@.d
uration,left(@.busy_mask,@.duration)),
'(')
end
Alexander Kuznetsov wrote:
>Roger,
>yet another approach:
>create function backup_mask(@.from_hour int, @.to_hour int)
>returns varchar(26)
>as
>begin
>declare @.idle_mask varchar(26)
>declare @.busy_mask varchar(24)
>set @.idle_mask = '('+replicate('_',24)+')'
>set @.busy_mask = replicate('X',24)
>return(
>substring(@.idle_mask, 1, @.from_hour)+
>substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
>substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
>end
>go
>-- unit test --
>select dbo.backup_mask(1,5)
>select dbo.backup_mask(3,15)
>select dbo.backup_mask(1,24)
>select dbo.backup_mask(19,24)
>
>--
>(XXXXX___________________)
>(1 row(s) affected)
>
>--
>(__XXXXXXXXXXXXX_________)
>(1 row(s) affected)
>
>--
>(XXXXXXXXXXXXXXXXXXXXXXXX)
>(1 row(s) affected)
>
>--
>(__________________XXXXXX)
>(1 row(s) affected)
>
>go
>drop function backup_mask
>go
>
>|||Thanks folks, I may have some follow up questions but for now I'm going
to digest the information you've sent me and see what I can come up
with. Thanks again for the assist!
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookI'm not familiar with these tables but this should give you
the output you want
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
DATEPART(hour,DATEADD(second,
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobStart,
DATEPART(hour,DATEADD(second,
last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600,
'19000101')) AS JobEnd,
last_run_duration
FROM sysjobservers) s ON j.job_id = s.job_id|||> It might be nice to get historical
> information but I'm not sure where I'd find it.
You have 5 tables in msdb with historical information about backup. The name
of the tables all start
with backup. For instance, check out msdb.dbo.backupset.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message
news:1144417911.795292.287140@.z34g2000cwc.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>|||Corrected (and simplified) version that handles jobs spanning midnight
SELECT j.name +
CASE WHEN 0 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 1 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 2 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 3 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 4 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 5 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 6 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 7 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 8 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 9 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 10 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 11 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 12 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 13 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 14 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 15 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 16 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 17 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 18 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 19 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 20 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 21 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 22 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END +
CASE WHEN 23 BETWEEN JobStart AND JobEnd THEN ',X' ELSE ',' END
FROM sysjobs j
INNER JOIN (SELECT job_id,
last_run_time / 10000 AS JobStart,
(last_run_duration +
last_run_time % 100 +
((last_run_time/100) % 100)*60 +
(last_run_time / 10000)*3600) / 3600 AS JobEnd
FROM sysjobservers) s ON j.job_id = s.job_id|||Roger,
yet another approach:
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = '('+replicate('_',24)+')'
set @.busy_mask = replicate('X',24)
return(
substring(@.idle_mask, 1, @.from_hour)+
substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
end
go
-- unit test --
select dbo.backup_mask(1,5)
select dbo.backup_mask(3,15)
select dbo.backup_mask(1,24)
select dbo.backup_mask(19,24)
(XXXXX___________________)
(1 row(s) affected)
(__XXXXXXXXXXXXX_________)
(1 row(s) affected)
(XXXXXXXXXXXXXXXXXXXXXXXX)
(1 row(s) affected)
(__________________XXXXXX)
(1 row(s) affected)
go
drop function backup_mask
go|||And another, using Alexander's function as a start,
just because we have QUOTENAME and STUFF.
(Note that the behavior of QUOTENAME when the
second parameter is '(' is not documented.)
create function backup_mask(@.from_hour int, @.to_hour int)
returns varchar(26)
as
begin
declare @.idle_mask varchar(26)
declare @.busy_mask varchar(24)
set @.idle_mask = replicate('_',24)
set @.busy_mask = replicate('X',24)
declare @.duration int
set @.duration = @.to_hour - @.from_hour + 1
if @.duration <= 0
return quotename(@.idle_mask,'(')
return
quotename(stuff(@.idle_mask,@.from_hour,@.d
uration,left(@.busy_mask,@.duration)),
'(')
end
Alexander Kuznetsov wrote:
>Roger,
>yet another approach:
>create function backup_mask(@.from_hour int, @.to_hour int)
>returns varchar(26)
>as
>begin
>declare @.idle_mask varchar(26)
>declare @.busy_mask varchar(24)
>set @.idle_mask = '('+replicate('_',24)+')'
>set @.busy_mask = replicate('X',24)
>return(
>substring(@.idle_mask, 1, @.from_hour)+
>substring(@.busy_mask, 1, @.to_hour - @.from_hour+1)+
>substring(@.idle_mask, 2 + @.to_hour,25-@.to_hour))
>end
>go
>-- unit test --
>select dbo.backup_mask(1,5)
>select dbo.backup_mask(3,15)
>select dbo.backup_mask(1,24)
>select dbo.backup_mask(19,24)
>
>--
>(XXXXX___________________)
>(1 row(s) affected)
>
>--
>(__XXXXXXXXXXXXX_________)
>(1 row(s) affected)
>
>--
>(XXXXXXXXXXXXXXXXXXXXXXXX)
>(1 row(s) affected)
>
>--
>(__________________XXXXXX)
>(1 row(s) affected)
>
>go
>drop function backup_mask
>go
>
>|||Thanks folks, I may have some follow up questions but for now I'm going
to digest the information you've sent me and see what I can come up
with. Thanks again for the assist!
Sunday, February 19, 2012
ADO Recordset from SQL Task issue
Dear Folks,
I have a Foreach Loop that enumerates a set of files from an ADO recordset variable which is populated by a preceding SQL task. The query from the task that populates the recordset returns about 200 rows with one varchar field(a file path). The loop is long running, and so far it errors on the connection string populated by the enumeration variable after about an hour. The timeout for the SQL task is set to zero. Could it be the source recordset variable timing out, or could it be that the recordset is too large?
Thanks,
Chris
Thanks,
Chris
|||
I have a Foreach Loop that enumerates a set of files from an ADO recordset variable which is populated by a preceding SQL task. The query from the task that populates the recordset returns about 200 rows with one varchar field(a file path). The loop is long running, and so far it errors on the connection string populated by the enumeration variable after about an hour. The timeout for the SQL task is set to zero. Could it be the source recordset variable timing out, or could it be that the recordset is too large?
Thanks,
Chris
If it is related to the number of records you could start by limiting the result to 10 records, and add 10 records until you see the problem again.
Also, do you have the latest service pack? Here's a post concerning an issue prior to the service pack.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277765&SiteID=1
|||
200 rows is not too large for a recordset.
I am confused. the sql task populates the recordset and the for each loop enumerates the results. I assume that the sql task is outside (before) the loop. so, which is taking a long time? executing the sql command or running over the recordset in the loop?
|||Thanksfor the replies, and yes, the SQL Task populates the ADO recordset
variable before the loop. The loop enumerates the results for about an
hour before it errors out. The error is the same every time, reporting
that the connection string has failed to have been acquired. The
variable for the connection string is populated by enumerating the
recordset. If i change the loop's collection enumerator to a directory,
the loop runs fine.
Thanks for your help,
Chris
|||I'm on a SQL Server 2k5 x86 ,SP1, windows 2000 platform if that helps.
Thanks,
Chris
|||Also, It's definitely not the recordset itself, as I've been able to use the same ADO variable result set to enumerate loops with fewer tasks\are shorter running. Any chance this is a memory leak?
|||
Well, what are you doing with the rows from the recordset? I think the enumerator gives you a fields collection.
in other words, what is in the loop
|||The (one field per) rows from the recordset enumerate the collection, a directory of Access databases. Inside the Foreach loop are several data flow tasks. A variable is mapped from the returned row and helps populate a connection string expression for an Access connection manager. The source connection for each data flow task uses the connection manager. It works great for about an hour when i use the recordset as a collection enumerator, and the loop does not error out if I use the directory as a collection enumerator.Thanks,
Chris
|||
This is odd. there certainly could be a bug somewhere in the recordset object itself (in the interest of full disclosure, I wrote the code for the ADO Recordset object back in '96 :) )
If you don't mind doing some experiments for me, I would ask you to try to separate the recordset usage from the pipeline usage as a way to try to narrow down the problem. it sounds like you have done this a bit.
instead of a dataflow task, put a script task in the loop. extract the value of the connection string and write it out somewhere so that you can see the values and make sure they all look rational. If this loop completes without error, then add some long delay (like a 5 minute wait) into the script task. this will tell us if their is some purely time related issue with the recordset (doubtful, but who knows could be a garbage collector kicking in and dropping references to the com objects of the recordset? crazy i know).
You say you can use a non-recordset list of the same exact connections and get this to succeed? that makes me think that this is not an issue with the access driver, but who knows. Exactly what error are you getting when it fails?
These two things just don't seem related.
Subscribe to:
Posts (Atom)