Lowering priority: run

Showing posts with label run. Show all posts

Friday, March 30, 2012

Maintenance Plan for Compatibility Mode 7.0 databases

Hi,
we maintain several databases which must run in Compatibility Mode 7.0.
How can I create a non script-based Maintenance Plan for these databases
in SQL Server 2005 (DBs don't show up in the Wizard).
Regards
Axel Bender> How can I create a non script-based Maintenance Plan for these databases in SQL Server 200
5 (DBs
> don't show up in the Wizard).
Short answer: you can't. A database need to be in 2005 compat mode in order
for most (all?) maint
tasks to be available.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel_bender@.t-online.de> wrote in message news:einlo5$bsr$01$1@.news.t-online.
com...
> Hi,
> we maintain several databases which must run in Compatibility Mode 7.0. Ho
w can I create a non
> script-based Maintenance Plan for these databases in SQL Server 2005 (DBs
don't show up in the
> Wizard).
> Regards
> Axel Bender|||Tibor Karaszi wrote:
> Short answer: you can't. A database need to be in 2005 compat mode in
> order for most (all?) maint tasks to be available.
>
Thanks so far, Tibor,
is there possibly a workaround?
Remark: MPs also work with CM 80 databases.
Kind regards
Axel|||> is there possibly a workaround?
I guess that would be to up the compat level to 80 or 90, but your apps need
to support that of
course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel.bender@.cip-kommunal.de> wrote in message
news:%23hhV%23UkAHHA.204@.TK2MSFTNGP04.phx.gbl...
> Tibor Karaszi wrote:
> Thanks so far, Tibor,
> is there possibly a workaround?
> Remark: MPs also work with CM 80 databases.
> Kind regards
> Axel|||Hi Tibor,
unfortunately this is not a solution; due to the changes MS made to the
parser in SQL 2000, we (until we're able to redesign our app) need to
run in CM 70. Is there - to your knowledge - any reason for MS not to
support MPs on SQL 2005?
Kind regards
Axel|||> Is there - to your knowledge - any reason for MS not to support MPs on SQL
2005?
I don't know. Perhaps one of the priority things that we all have to deal wi
th in out projects?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel.bender@.cip-kommunal.de> wrote in message
news:ujhfvonAHHA.4256@.TK2MSFTNGP04.phx.gbl...
> Hi Tibor,
> unfortunately this is not a solution; due to the changes MS made to the pa
rser in SQL 2000, we
> (until we're able to redesign our app) need to run in CM 70. Is there - to
your knowledge - any
> reason for MS not to support MPs on SQL 2005?
> Kind regards
> Axel

Maintenance Plan for Compatibility Mode 7.0 databases

Hi,
we maintain several databases which must run in Compatibility Mode 7.0.
How can I create a non script-based Maintenance Plan for these databases
in SQL Server 2005 (DBs don't show up in the Wizard).
Regards
Axel Bender> How can I create a non script-based Maintenance Plan for these databases in SQL Server 2005 (DBs
> don't show up in the Wizard).
Short answer: you can't. A database need to be in 2005 compat mode in order for most (all?) maint
tasks to be available.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel_bender@.t-online.de> wrote in message news:einlo5$bsr$01$1@.news.t-online.com...
> Hi,
> we maintain several databases which must run in Compatibility Mode 7.0. How can I create a non
> script-based Maintenance Plan for these databases in SQL Server 2005 (DBs don't show up in the
> Wizard).
> Regards
> Axel Bender|||Tibor Karaszi wrote:
>> How can I create a non script-based Maintenance Plan for these
>> databases in SQL Server 2005 (DBs don't show up in the Wizard).
> Short answer: you can't. A database need to be in 2005 compat mode in
> order for most (all?) maint tasks to be available.
>
Thanks so far, Tibor,
is there possibly a workaround?
Remark: MPs also work with CM 80 databases.
Kind regards
Axel|||> is there possibly a workaround?
I guess that would be to up the compat level to 80 or 90, but your apps need to support that of
course.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel.bender@.cip-kommunal.de> wrote in message
news:%23hhV%23UkAHHA.204@.TK2MSFTNGP04.phx.gbl...
> Tibor Karaszi wrote:
>> How can I create a non script-based Maintenance Plan for these databases in SQL Server 2005 (DBs
>> don't show up in the Wizard).
>> Short answer: you can't. A database need to be in 2005 compat mode in order for most (all?) maint
>> tasks to be available.
> Thanks so far, Tibor,
> is there possibly a workaround?
> Remark: MPs also work with CM 80 databases.
> Kind regards
> Axel|||Hi Tibor,
unfortunately this is not a solution; due to the changes MS made to the
parser in SQL 2000, we (until we're able to redesign our app) need to
run in CM 70. Is there - to your knowledge - any reason for MS not to
support MPs on SQL 2005?
Kind regards
Axel|||> Is there - to your knowledge - any reason for MS not to support MPs on SQL 2005?
I don't know. Perhaps one of the priority things that we all have to deal with in out projects?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Axel Bender" <axel.bender@.cip-kommunal.de> wrote in message
news:ujhfvonAHHA.4256@.TK2MSFTNGP04.phx.gbl...
> Hi Tibor,
> unfortunately this is not a solution; due to the changes MS made to the parser in SQL 2000, we
> (until we're able to redesign our app) need to run in CM 70. Is there - to your knowledge - any
> reason for MS not to support MPs on SQL 2005?
> Kind regards
> Axel

Maintenance plan failed to start

We are having intermittent episodes where the daily maintenance plan does not
run. There are no entries in the sql logs nor eventvwr. Ideas'Hi,
During those instances verify the SQLAgent service is running. All the SQL
Jobs are scduled and activated by SQL Agent service.
Thanks
Hari
SQL Server MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:740DE9AB-8876-488A-A13E-3AF884444E62@.microsoft.com...
> We are having intermittent episodes where the daily maintenance plan does
> not
> run. There are no entries in the sql logs nor eventvwr. Ideas'

Wednesday, March 28, 2012

Maintenance plan does not run

Hi - I have a strange situation where the Maintenance plan on a SQL 7, build 961 will not generate .BAK and .TRN files based on the maintenance plan (or at all). I have tried deleteing and recreating the maintenance plan, but, according to the system log, and to maintenance plan logs, the entire maintenance plan is not launching any tasks. However the application log whoes all the jobs as failing, but gives no furhter details.

However I have another server running build 623 that is working fine.

Any ideas from anyone? All help appreciated.Why use a maintenance plan - they give lots of problems.
Just schedule a backup job or jobs.
Have them call a stored procedure and put the backup statement in that.|||Here's the error from Eventlog:

SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'Webmon1'' (0x42E6C45B360CD611935D0002B330ABF9) - Status: Failed - Invoked on: 2/8/02 4:10:46 PM - Message: The job failed. The Job was invoked by User XXXXXX\Administrator. The last step to run was step 1 (Step 1).|||What happens if you try the same backup using a backup command?

Monday, March 26, 2012

Maintenance Plan

Friday, March 23, 2012

Maintenance Plan

Using SQL2000
I run a daily back up for DB and Logs as part of maintenance plan. I set the
option to delete files older than 7 days for both Dats and Logs. It does this
for the Dat file but not for the log. Is this a bug or is there something
that I am neglecting to do or is there a reason why SQL keeps old transaction
logs?
Thanks in advance.
Are you trying to backup log for databases that are in simple recovery mode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AndyB" <AndyB@.discussions.microsoft.com> wrote in message
news:C9D4CCD0-57B7-4AC8-87AD-2D7AE5C49697@.microsoft.com...
> Using SQL2000
> I run a daily back up for DB and Logs as part of maintenance plan. I set the
> option to delete files older than 7 days for both Dats and Logs. It does this
> for the Dat file but not for the log. Is this a bug or is there something
> that I am neglecting to do or is there a reason why SQL keeps old transaction
> logs?
> Thanks in advance.
|||I am using Full recovery mode.
"Tibor Karaszi" wrote:

> Are you trying to backup log for databases that are in simple recovery mode?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AndyB" <AndyB@.discussions.microsoft.com> wrote in message
> news:C9D4CCD0-57B7-4AC8-87AD-2D7AE5C49697@.microsoft.com...
>
>
|||Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AndyB" <AndyB@.discussions.microsoft.com> wrote in message
news:92030856-752D-4733-9F66-9FCF71E3B009@.microsoft.com...[vbcol=seagreen]
>I am using Full recovery mode.
> "Tibor Karaszi" wrote:

Maintenance Plan

After I run the maintainence plan, check db index...
the log will grow up from zero size to 20 GB size.
i know how to truncate
but the point is why it grow so large.
is it any problems?I assume that the maint plan includes DBCC DBREINDEX. This will rebuild all your indexes and those
rebuilds are logged to the transaction log. I suggest you read this articles regarding reindexing:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"inamori" <test@.test.com> wrote in message news:cqt9t7$s003@.imsp212.netvigator.com...
> After I run the maintainence plan, check db index...
> the log will grow up from zero size to 20 GB size.
> i know how to truncate
> but the point is why it grow so large.
> is it any problems?
>|||how about the data file
i shrink the data file from 30Gb to 15GB
after 3 hours, it grow up suddenly
can u tell me why?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl ¤¤¼¶¼g...
> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
all your indexes and those
> rebuilds are logged to the transaction log. I suggest you read this
articles regarding reindexing:
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "inamori" <test@.test.com> wrote in message
news:cqt9t7$s003@.imsp212.netvigator.com...
> > After I run the maintainence plan, check db index...
> >
> > the log will grow up from zero size to 20 GB size.
> >
> > i know how to truncate
> >
> > but the point is why it grow so large.
> >
> > is it any problems?
> >
> >
>|||What actions did you perform during those three hours?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"inamori" <test@.test.com> wrote in message news:cqucqh$pvv1@.imsp212.netvigator.com...
> how about the data file
> i shrink the data file from 30Gb to 15GB
> after 3 hours, it grow up suddenly
> can u tell me why?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l¥ó
> news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl ¤¤¼¶¼g...
>> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
> all your indexes and those
>> rebuilds are logged to the transaction log. I suggest you read this
> articles regarding reindexing:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "inamori" <test@.test.com> wrote in message
> news:cqt9t7$s003@.imsp212.netvigator.com...
>> > After I run the maintainence plan, check db index...
>> >
>> > the log will grow up from zero size to 20 GB size.
>> >
>> > i know how to truncate
>> >
>> > but the point is why it grow so large.
>> >
>> > is it any problems?
>> >
>> >
>>
>|||just do truncate and compress the data file
lets say
15GB used + 15 Free space
after i trucncate, 15 free space deleted
that mean 15 GB currently
but dont know why
3 hours later, 15 GB free sapce come out again
that mean grow back 30GB(15GB free + 15 used)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dSKeWb7EHA.3644@.TK2MSFTNGP09.phx.gbl...
> What actions did you perform during those three hours?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "inamori" <test@.test.com> wrote in message
news:cqucqh$pvv1@.imsp212.netvigator.com...
> > how about the data file
> >
> > i shrink the data file from 30Gb to 15GB
> >
> > after 3 hours, it grow up suddenly
> >
> > can u tell me why?
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l
¥ó
> > news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl ¤¤¼¶¼g...
> >> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
> > all your indexes and those
> >> rebuilds are logged to the transaction log. I suggest you read this
> > articles regarding reindexing:
> >>
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "inamori" <test@.test.com> wrote in message
> > news:cqt9t7$s003@.imsp212.netvigator.com...
> >> > After I run the maintainence plan, check db index...
> >> >
> >> > the log will grow up from zero size to 20 GB size.
> >> >
> >> > i know how to truncate
> >> >
> >> > but the point is why it grow so large.
> >> >
> >> > is it any problems?
> >> >
> >> >
> >>
> >>
> >
> >
>|||SQL Server doesn't grow the database files by itself. You need to find out what commands were
executed during these three hours that causes the database file size to grow. My guess is that you
are doing a DBREINDEX during this time period.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Test" <TEst@.test.com> wrote in message news:es1Jo4g7EHA.2572@.tk2msftngp13.phx.gbl...
> just do truncate and compress the data file
> lets say
> 15GB used + 15 Free space
> after i trucncate, 15 free space deleted
> that mean 15 GB currently
> but dont know why
> 3 hours later, 15 GB free sapce come out again
> that mean grow back 30GB(15GB free + 15 used)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23dSKeWb7EHA.3644@.TK2MSFTNGP09.phx.gbl...
>> What actions did you perform during those three hours?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "inamori" <test@.test.com> wrote in message
> news:cqucqh$pvv1@.imsp212.netvigator.com...
>> > how about the data file
>> >
>> > i shrink the data file from 30Gb to 15GB
>> >
>> > after 3 hours, it grow up suddenly
>> >
>> > can u tell me why?
>> >
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¦b¶l
> ¥ó
>> > news:eENS$uX7EHA.2032@.tk2msftngp13.phx.gbl ¤¤¼¶¼g...
>> >> I assume that the maint plan includes DBCC DBREINDEX. This will rebuild
>> > all your indexes and those
>> >> rebuilds are logged to the transaction log. I suggest you read this
>> > articles regarding reindexing:
>> >>
>> >
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "inamori" <test@.test.com> wrote in message
>> > news:cqt9t7$s003@.imsp212.netvigator.com...
>> >> > After I run the maintainence plan, check db index...
>> >> >
>> >> > the log will grow up from zero size to 20 GB size.
>> >> >
>> >> > i know how to truncate
>> >> >
>> >> > but the point is why it grow so large.
>> >> >
>> >> > is it any problems?
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Maintenance Paln Issue

I'm running across a weird issue that I can't figure out. I trying to create
a maintenance plan on one of my SQL 2000 Servers. I run throught the wizard
and get to the screen to setup the backup job and when I go to select the
delete files older than option I notice that that this blank. Typically it
has dyas, hours, and weeks here. The file extension is blank as well. Aslo
the number scroll for number of files does not work correctly down is up.
Any thought on this. This is my only SQl server I've seen this on. I have 5
other SQL servers that work fine when creating a maintenance job.Hi,
This could be problem with dll files of enterprise manager, I think for now
you can create Maintenance plan for this database server on other server.
Regards,
Amol Lembhe
"George Schneider" wrote:
> I'm running across a weird issue that I can't figure out. I trying to create
> a maintenance plan on one of my SQL 2000 Servers. I run throught the wizard
> and get to the screen to setup the backup job and when I go to select the
> delete files older than option I notice that that this blank. Typically it
> has dyas, hours, and weeks here. The file extension is blank as well. Aslo
> the number scroll for number of files does not work correctly down is up.
> Any thought on this. This is my only SQl server I've seen this on. I have 5
> other SQL servers that work fine when creating a maintenance job.|||Is there anyway to resolve this issue?
"Amol Lembhe" wrote:
> Hi,
> This could be problem with dll files of enterprise manager, I think for now
> you can create Maintenance plan for this database server on other server.
> Regards,
> Amol Lembhe
> "George Schneider" wrote:
> > I'm running across a weird issue that I can't figure out. I trying to create
> > a maintenance plan on one of my SQL 2000 Servers. I run throught the wizard
> > and get to the screen to setup the backup job and when I go to select the
> > delete files older than option I notice that that this blank. Typically it
> > has dyas, hours, and weeks here. The file extension is blank as well. Aslo
> > the number scroll for number of files does not work correctly down is up.
> > Any thought on this. This is my only SQl server I've seen this on. I have 5
> > other SQL servers that work fine when creating a maintenance job.

Monday, March 19, 2012

Maintaining Replication

Is there any nightly maintenance tasks I can run that will keep my
replication going smooth? I remember reading somewhere about re-indexing
replication tables, but could somebody offer some more insight as to what I
could do to keep everything afloat?
Best Regards,
Brad
There is no real maintenance you can do. You might want to check for
expiring subscriptions, query the history tables for this info.
For merge replication you may wish to reindex contents, tombstone, and
genhistory.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:%23inwUB2pFHA.1096@.TK2MSFTNGP11.phx.gbl...
> Is there any nightly maintenance tasks I can run that will keep my
> replication going smooth? I remember reading somewhere about re-indexing
> replication tables, but could somebody offer some more insight as to what
I
> could do to keep everything afloat?
> Best Regards,
> Brad
>
|||I have always found replication (snapshot & transactional) to be a very
fussy animal. It is fine if it works however if it comes across
something it doesn't like that takes system resources (i.e. - db
backup, index rebuild, etc) it stops working and sulks like a petulant
child!
To solve most of these replication "tantrums" I have used the Query
timeout setting on the Distribution Agent and set it to 30 mins (or
more) and basically letting the system sort it self out and telling
replication to wait then try again - bit like giving a child a
lollipop ;o)
Obviously everyone has a different replication systems but I have found
that this has worked well for me.
Rgds,
qh

Maintain Jobs, View Logs

I have a user who needs to check whether 2 jobs have run on a SQL Server 200
0 instance, and be able to view logs.
Is there a way of doing this, and not allowing them access to anything else?Hi
Does the use have EM on his/her workstation?
Does the user have permissions to run the job?
See this example written by Neil
/*** Scheduled task reporter for SQL 2000
**** Copyright Neil Boyle 2003.
**** Use entirely at your own risk
***/
use MSDB
go
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) --
Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday
of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes
or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"JamesNZ" <JamesNZ.24lo0y@.mail.webservertalk.com> wrote in message
news:JamesNZ.24lo0y@.mail.webservertalk.com...
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?
>
> --
> JamesNZ
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1430463.html
>|||Here is one solution which should work for you:
1. Create a SP using the following script. Just mention your job names in
the following line of the script:
--sj.name in ('Job1', 'Job2') and
CREATE PROCEDURE dbo.read_jobhistory
as
select sjh.server, sj.name, case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as 'Job Status',
sjh.sql_message_id, sjh.sql_severity,sjh.message,
convert(datetime, cast (sjh.run_date as char(8)) + ' ' +
left(right('000000' + cast (sjh.run_time as varchar(6)),6),2) + ':' +
substring(right('000000' + cast (sjh.run_time as varchar(6)),6),3,2) + ':' +
right('000000' + cast (sjh.run_time as varchar(6)),2))as 'Run Date',
sjh.run_duration, sjh.operator_id_emailed,
sjh.operator_id_netsent, sjh.operator_id_paged, sjh.retries_attempted
from
msdb..sysjobhistory sjh
join msdb..sysjobs sj
on sjh.job_id = sj.job_id
where
sj.name in ('Job1', 'Job2') and
step_id = 0
and sjh.run_date >= convert(char(10),getdate(),112)
order by sjh.server, sj.name, sjh.run_date, sjh.run_time
2. Grant Execution on this SP to the concerned user.
3. The concerned user can get the report for the today's date just by
executing the SP.
"JamesNZ" wrote:

> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?

Maintain Jobs, View Logs

I have a user who needs to check whether 2 jobs have run on a SQL Server
2000 instance, and be able to view logs.
Is there a way of doing this, and not allowing them access to anything
else?
--
JamesNZ
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message1430463.htmlHi
Does the use have EM on his/her workstation?
Does the user have permissions to run the job?
See this example written by Neil
/*** Scheduled task reporter for SQL 2000
**** Copyright Neil Boyle 2003.
**** Use entirely at your own risk
***/
use MSDB
go
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) --
Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday
of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes
or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"JamesNZ" <JamesNZ.24lo0y@.mail.webservertalk.com> wrote in message
news:JamesNZ.24lo0y@.mail.webservertalk.com...
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?
>
> --
> JamesNZ
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1430463.html
>|||Here is one solution which should work for you:
1. Create a SP using the following script. Just mention your job names in
the following line of the script:
--sj.name in ('Job1', 'Job2') and
CREATE PROCEDURE dbo.read_jobhistory
as
select sjh.server, sj.name, case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as 'Job Status',
sjh.sql_message_id, sjh.sql_severity,sjh.message,
convert(datetime, cast (sjh.run_date as char(8)) + ' ' +
left(right('000000' + cast (sjh.run_time as varchar(6)),6),2) + ':' +
substring(right('000000' + cast (sjh.run_time as varchar(6)),6),3,2) + ':' +
right('000000' + cast (sjh.run_time as varchar(6)),2))as 'Run Date',
sjh.run_duration, sjh.operator_id_emailed,
sjh.operator_id_netsent, sjh.operator_id_paged, sjh.retries_attempted
from
msdb..sysjobhistory sjh
join msdb..sysjobs sj
on sjh.job_id = sj.job_id
where
sj.name in ('Job1', 'Job2') and
step_id = 0
and sjh.run_date >= convert(char(10),getdate(),112)
order by sjh.server, sj.name, sjh.run_date, sjh.run_time
2. Grant Execution on this SP to the concerned user.
3. The concerned user can get the report for the today's date just by
executing the SP.
"JamesNZ" wrote:
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?

Maintain Jobs, View Logs

I have a user who needs to check whether 2 jobs have run on a SQL Server
2000 instance, and be able to view logs.
Is there a way of doing this, and not allowing them access to anything
else?
JamesNZ
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1430463.html
Hi
Does the use have EM on his/her workstation?
Does the user have permissions to run the job?
See this example written by Neil
/*** Scheduled task reporter for SQL 2000
**** Copyright Neil Boyle 2003.
**** Use entirely at your own risk
***/
use MSDB
go
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) --
Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday
of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes
or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"JamesNZ" <JamesNZ.24lo0y@.mail.webservertalk.com> wrote in message
news:JamesNZ.24lo0y@.mail.webservertalk.com...
> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?
>
> --
> JamesNZ
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1430463.html
>
|||Here is one solution which should work for you:
1. Create a SP using the following script. Just mention your job names in
the following line of the script:
--sj.name in ('Job1', 'Job2') and
CREATE PROCEDURE dbo.read_jobhistory
as
select sjh.server, sj.name, case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as 'Job Status',
sjh.sql_message_id, sjh.sql_severity,sjh.message,
convert(datetime, cast (sjh.run_date as char(8)) + ' ' +
left(right('000000' + cast (sjh.run_time as varchar(6)),6),2) + ':' +
substring(right('000000' + cast (sjh.run_time as varchar(6)),6),3,2) + ':' +
right('000000' + cast (sjh.run_time as varchar(6)),2))as 'Run Date',
sjh.run_duration, sjh.operator_id_emailed,
sjh.operator_id_netsent, sjh.operator_id_paged, sjh.retries_attempted
from
msdb..sysjobhistory sjh
join msdb..sysjobs sj
on sjh.job_id = sj.job_id
where
sj.name in ('Job1', 'Job2') and
step_id = 0
and sjh.run_date >= convert(char(10),getdate(),112)
order by sjh.server, sj.name, sjh.run_date, sjh.run_time
2. Grant Execution on this SP to the concerned user.
3. The concerned user can get the report for the today's date just by
executing the SP.
"JamesNZ" wrote:

> I have a user who needs to check whether 2 jobs have run on a SQL Server
> 2000 instance, and be able to view logs.
> Is there a way of doing this, and not allowing them access to anything
> else?

Monday, March 12, 2012

Maint. Plan Help

I need to set up a Maintenance Plan on a 6.5 DB. I know the version is old but the customer cannot upgrade at this time. When I try and run the wizard to set it up I get a warning about running a plan on a DB that is larger that 400MB. Whats the deal? Thanks for the help!!I need to set up a Maintenance Plan on a 6.5 DB. I know the version is old but the customer cannot upgrade at this time. When I try and run the wizard to set it up I get a warning about running a plan on a DB that is larger that 400MB. Whats the deal? Thanks for the help!!

As I recall (and it has been a while), one of the things that the maintenance plan will try to set up is an index reorg and (possibly) space reclamation. You don't want to touch either of these with your database. At least, I don't think you do. I would probably skip the maintenance plan wizard altogether and set up backup jobs to make sure your database is being backed up.

When I did this, I had to set up 5 backup devices (named FULL, 0600, 1200, 1800 and 0000). I then scheduled the full backup to write to the FULL backup device and four transaction log backups to write to the other four devices (at midnight, 6:00am, noon and 6:00pm). The backups would overwrite the files (so another process would have to copy the backup files to a remote location).

SQL 6.5. Ugh. It's the reason I drink so much now.

Regards,

hmscott

Maint. plan data optimization on system DBs

Hello,
Does it really make sense to run maint. plan data optimization jobs on
SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
(e.g. when rebuilding an index fails in the middle of the process)?
Many thanks,
Pablo
No it doesn't - unless you're storing user data in them. You cannot rebuild
the indexes of the critical system tables (sysindexes, syscolumns,
sysobjects) anyway.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"msnews.microsoft.com" <pablo@.this_isnt_my_mail.cc> wrote in message
news:udEsTKpPEHA.308@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Does it really make sense to run maint. plan data optimization jobs on
> SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
> (e.g. when rebuilding an index fails in the middle of the process)?
> --
> Many thanks,
> Pablo

Maint. plan data optimization on system DBs

Hello,
Does it really make sense to run maint. plan data optimization jobs on
SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
(e.g. when rebuilding an index fails in the middle of the process)?
--
Many thanks,
PabloNo it doesn't - unless you're storing user data in them. You cannot rebuild
the indexes of the critical system tables (sysindexes, syscolumns,
sysobjects) anyway.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"msnews.microsoft.com" <pablo@.this_isnt_my_mail.cc> wrote in message
news:udEsTKpPEHA.308@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Does it really make sense to run maint. plan data optimization jobs on
> SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
> (e.g. when rebuilding an index fails in the middle of the process)?
> --
> Many thanks,
> Pablo

Maint. plan data optimization on system DBs

Hello,
Does it really make sense to run maint. plan data optimization jobs on
SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
(e.g. when rebuilding an index fails in the middle of the process)?
Many thanks,
PabloNo it doesn't - unless you're storing user data in them. You cannot rebuild
the indexes of the critical system tables (sysindexes, syscolumns,
sysobjects) anyway.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"msnews.microsoft.com" <pablo@.this_isnt_my_mail.cc> wrote in message
news:udEsTKpPEHA.308@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Does it really make sense to run maint. plan data optimization jobs on
> SQL Server 2K system DBs (master,msdb)? If yes, why? Isn't it risky
> (e.g. when rebuilding an index fails in the middle of the process)?
> --
> Many thanks,
> Pablo

Maint Plans

I created a Maintenence Plan, I tried to run them, but this error always makes then fail.
services. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)
Whats up?
Thanks
Hi,
Do you run maintanace plan on default instance or named instance?
Check article's 326485 and 288577.
Regards.
"Robert Salazar" wrote:

> I created a Maintenence Plan, I tried to run them, but this error always makes then fail.
> services. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)
> Whats up?
> Thanks
>

Maint Plans

I created a Maintenence Plan, I tried to run them, but this error always makes then fail.
services. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)
Whats up?
ThanksHi,
Do you run maintanace plan on default instance or named instance?
Check article's 326485 and 288577.
Regards.
"Robert Salazar" wrote:
> I created a Maintenence Plan, I tried to run them, but this error always makes then fail.
> services. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)
> Whats up?
> Thanks
>

Maint Plans

I created a Maintenence Plan, I tried to run them, but this error always mak
es then fail.
services. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)
Whats up?
ThanksHi,
Do you run maintanace plan on default instance or named instance?
Check article's 326485 and 288577.
Regards.
"Robert Salazar" wrote:

> I created a Maintenence Plan, I tried to run them, but this error always m
akes then fail.
> services. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)
> Whats up?
> Thanks
>

Friday, March 30, 2012

Maintenance Plan for Compatibility Mode 7.0 databases

Maintenance Plan for Compatibility Mode 7.0 databases

Maintenance plan failed to start

Wednesday, March 28, 2012

Maintenance plan does not run

Monday, March 26, 2012

Maintenance Plan

Maintenance Plan

Friday, March 23, 2012

Maintenance Plan

Maintenance Plan

Maintenance Paln Issue

Monday, March 19, 2012

Maintaining Replication

Maintain Jobs, View Logs

Maintain Jobs, View Logs

Maintain Jobs, View Logs

Monday, March 12, 2012

Maint. Plan Help

Maint. plan data optimization on system DBs

Maint. plan data optimization on system DBs

Maint. plan data optimization on system DBs

Maint Plans

Maint Plans

Maint Plans

Lowering priority

Blog Archive

About Me