Lowering priority: 2012

Friday, March 30, 2012

Maintenance plan issue

My apologies if this is not the right group for this question, but I couldn'
t find a "maintenance plans"
newsgroup for SQL Server . . .
About 3 w

s ago I created maintenance plans on four databases: master, msd
b and two production databases
(I'll call them DB1 and DB2). I included:
1) update statistics used by the query optimizer
2) remove unused space from database files
3) check database integrity - include indexes - attempt to repair minor prob
lems
4) perform step 3 before backing up database and transaction log
5) backup database with verify
6) backup transaction log with verify
We run a nightly process wherein text files are downloaded from an AS/400 an
d fed into DB1.
This process runs on Tuesday through Saturday mornings, starts at 3:00 AM an
d finishes (under normal circumstances)
at about 5:30 AM. I scheduled the maintenance plans so that none of them ran
during this time.
As soon as the plans were in place and executing, I noticed a significant sl
owdown
in the abovementioned 2 hour download/import process. It started running at
least 1 hour longer;
on some days it would take up to 2 hours longer. I immediately disabled the
plan for DB1 -
no performance improvement whatsoever. Long story short - I finally disabled
all four plans,
and execution time was back to 2 hours again.
I will be doing some testing over the next few w

s; enabling parts of the
plans on master and msdb,
but in the meantime I needed to know if anyone out there has experienced thi
s type of behavior and if so,
how you resolved the issue.
We're running SQL Server version 7.0 SP2 (7.00.842)
Note: the maintenance plans were created using the Enterprise Manager versio
n that came with SQL Server 2000;
don't know if that makes a difference.
Let me know if you need additional information about the issue.
And, thanks in advance for any assistance you can offer.
CarlI suspect that your culprit is step 2) remove unecessary space from database
s.
If your are using DBCC SHRINKFILE to compact your database and log files,
when the import job runs those jobs will need to grow in order to accommodat
e
the new data (especially the log file, if your recovery model is Full). If
the server has to grow the files during the import, it will definitely slow
things down.
Try removing that step from your plan and see what happens.
"Carl Imthurn" wrote:

> My apologies if this is not the right group for this question, but I could
n't find a "maintenance plans"
> newsgroup for SQL Server . . .
> About 3 w

s ago I created maintenance plans on four databases: master, m
sdb and two production databases
> (I'll call them DB1 and DB2). I included:
> 1) update statistics used by the query optimizer
> 2) remove unused space from database files
> 3) check database integrity - include indexes - attempt to repair minor pr
oblems
> 4) perform step 3 before backing up database and transaction log
> 5) backup database with verify
> 6) backup transaction log with verify
> We run a nightly process wherein text files are downloaded from an AS/400
and fed into DB1.
> This process runs on Tuesday through Saturday mornings, starts at 3:00 AM
and finishes (under normal circumstances)
> at about 5:30 AM. I scheduled the maintenance plans so that none of them r
an during this time.
> As soon as the plans were in place and executing, I noticed a significant
slowdown
> in the abovementioned 2? hour download/import process. It started running
at least 1 hour longer;
> on some days it would take up to 2 hours longer. I immediately disabled th
e plan for DB1 -
> no performance improvement whatsoever. Long story short - I finally disabl
ed all four plans,
> and execution time was back to 2? hours again.
> I will be doing some testing over the next few w

s; enabling parts of th
e plans on master and msdb,
> but in the meantime I needed to know if anyone out there has experienced t
his type of behavior and if so,
> how you resolved the issue.
> We're running SQL Server version 7.0 SP2 (7.00.842)
> Note: the maintenance plans were created using the Enterprise Manager vers
ion that came with SQL Server 2000;
> don't know if that makes a difference.
> Let me know if you need additional information about the issue.
> And, thanks in advance for any assistance you can offer.
> Carl
>|||Sorry, let me translate my post into English:
If your are using DBCC SHRINKFILE to compact your database (mdf) and log
(ldf) files, when the import job runs those files will need to grow in order
to accommodate the new data (especially the log file, if your recovery model
is Full). If
the server has to grow the files during the import, it will definitely slow
things down.
Try removing that step from your plan and see what happens.
"Mark Williams" wrote:
> I suspect that your culprit is step 2) remove unecessary space from databa
ses.
> If your are using DBCC SHRINKFILE to compact your database and log files,
> when the import job runs those jobs will need to grow in order to accommod
ate
> the new data (especially the log file, if your recovery model is Full). If
> the server has to grow the files during the import, it will definitely slo
w
> things down.
> Try removing that step from your plan and see what happens.
> --
> "Carl Imthurn" wrote:
>|||Thanks Mark -- I will check that out and see what happens.
And, thanks for the English translation ;-)
Carl
Mark Williams wrote:

> Sorry, let me translate my post into English:
> If your are using DBCC SHRINKFILE to compact your database (mdf) and log
> (ldf) files, when the import job runs those files will need to grow in ord
er
> to accommodate the new data (especially the log file, if your recovery mod
el
> is Full). If
> the server has to grow the files during the import, it will definitely slo
w
> things down.
> Try removing that step from your plan and see what happens.
>|||An alternative approach to the troubleshooting efforts: Disable all
maintenance plans, then add one very simple maintenance plan. Make it for
DB1 and have it do only one thing (back up the database); don't have it do
anything else. See what happens. If life is good, then proceed to add
additional steps and maintenance plans (one step at a time of course) until
the problem reappears. If it reappears right away with the simple (backup
only) maintenance plan on DB1, then kill that maintenance plan and do an
equivalent but for master, or model, or DB2 or whatever other database.
Whatever you do, the suggestion I'm making here is to start small and simple
and incrementally add complexity until it breaks. Upgrading to SQL Server
2000 probably wouldn't hurt anything...
Once you get that all figured out you might take your consultant to task for
not setting up a maintenance plan and testing it to begin with : )
-HTH
"Carl Imthurn" <nospam@.all.com> wrote in message
news:%23EzkzSALGHA.3264@.TK2MSFTNGP11.phx.gbl...
> My apologies if this is not the right group for this question, but I
> couldn't find a "maintenance plans"
> newsgroup for SQL Server . . .
> About 3 w

s ago I created maintenance plans on four databases: master,
> msdb and two production databases
> (I'll call them DB1 and DB2). I included:
> 1) update statistics used by the query optimizer
> 2) remove unused space from database files
> 3) check database integrity - include indexes - attempt to repair minor
> problems
> 4) perform step 3 before backing up database and transaction log
> 5) backup database with verify
> 6) backup transaction log with verify
> We run a nightly process wherein text files are downloaded from an AS/400
> and fed into DB1.
> This process runs on Tuesday through Saturday mornings, starts at 3:00 AM
> and finishes (under normal circumstances)
> at about 5:30 AM. I scheduled the maintenance plans so that none of them
> ran during this time.
> As soon as the plans were in place and executing, I noticed a significant
> slowdown
> in the abovementioned 2 hour download/import process. It started running
> at least 1 hour longer;
> on some days it would take up to 2 hours longer. I immediately disabled
> the plan for DB1 -
> no performance improvement whatsoever. Long story short - I finally
> disabled all four plans,
> and execution time was back to 2 hours again.
> I will be doing some testing over the next few w

s; enabling parts of
> the plans on master and msdb,
> but in the meantime I needed to know if anyone out there has experienced
> this type of behavior and if so,
> how you resolved the issue.
> We're running SQL Server version 7.0 SP2 (7.00.842)
> Note: the maintenance plans were created using the Enterprise Manager
> version that came with SQL Server 2000;
> don't know if that makes a difference.
> Let me know if you need additional information about the issue.
> And, thanks in advance for any assistance you can offer.
> Carl
>

Maintenance Plan Issue

Sorry for the repost but I had an issue with my alias and had to repost per
customer service.
I'm running across a weird issue that I can't figure out. I trying to create
a maintenance plan on two 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 but not on
these two servers and really need to resolve the issue on these servers.
Hello,
Thank you for posting here.
Based on the symptom, it seems the "BackupDirectory " value of the
following registry key is corrupted or missing:
HKEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\<instance
name>\MSSQLServer (named instance)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer (default
instance)
If it is missing, please right click above Key->New->String Value, change
the name to BackupDirectory, and change it value to the proper path such as:
C:\Program Files\Microsoft SQL Server\MSSQL\backup
NOTE: Using Registry Editor incorrectly can cause serious problems that may
require you to reinstall Windows. Microsoft cannot guarantee that problems
resulting from the incorrect use of Registry Editor can be solved. Use
Registry Editor at your own risk.
256986 Description of the Microsoft Windows Registry
http://support.microsoft.com/?id=256986
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Maintenance Plan Issue
| thread-index: Acg26I1Tyaq8Y40fRC+Uz8AX1JIRtw==
| X-WBNR-Posting-Host: 207.46.19.168
| From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=
<georgedschneider@.noemail.noemail>
| Subject: Maintenance Plan Issue
| Date: Tue, 4 Dec 2007 18:43:01 -0800
| Lines: 12
| Message-ID: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32053
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sorry for the repost but I had an issue with my alias and had to repost
per
| customer service.
|
| I'm running across a weird issue that I can't figure out. I trying to
create
| a maintenance plan on two 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 but not
on
| these two servers and really need to resolve the issue on these servers.
|
|||Hello,
How's everything going?
I'm wondering if the suggestion has helped or if you have any further
questions. Please feel free to respond to the newsgroups if you need any
additional help.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 96426387
| References: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Wed, 05 Dec 2007 09:22:23 GMT
| Subject: RE: Maintenance Plan Issue
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <p9V2aByNIHA.7908@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 81
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32075
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| Based on the symptom, it seems the "BackupDirectory " value of the
| following registry key is corrupted or missing:
|
| HKEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\<instance
| name>\MSSQLServer (named instance)
|
| HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer (default
| instance)
|
| If it is missing, please right click above Key->New->String Value, change
| the name to BackupDirectory, and change it value to the proper path such
as:
|
| C:\Program Files\Microsoft SQL Server\MSSQL\backup
|
| NOTE: Using Registry Editor incorrectly can cause serious problems that
may
| require you to reinstall Windows. Microsoft cannot guarantee that
problems
| resulting from the incorrect use of Registry Editor can be solved. Use
| Registry Editor at your own risk.
|
| 256986 Description of the Microsoft Windows Registry
| http://support.microsoft.com/?id=256986
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu
| MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| ================================================== ===
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| ================================================== ===
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | Thread-Topic: Maintenance Plan Issue
| | thread-index: Acg26I1Tyaq8Y40fRC+Uz8AX1JIRtw==
| | X-WBNR-Posting-Host: 207.46.19.168
| | From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=
| <georgedschneider@.noemail.noemail>
| | Subject: Maintenance Plan Issue
| | Date: Tue, 4 Dec 2007 18:43:01 -0800
| | Lines: 12
| | Message-ID: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 7bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32053
| | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Sorry for the repost but I had an issue with my alias and had to repost
| per
| | customer service.
| |
| | I'm running across a weird issue that I can't figure out. I trying to
| create
| | a maintenance plan on two 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 but
not
| on
| | these two servers and really need to resolve the issue on these servers.
| |
|
|

Maintenance plan issue

I am using Enterprise manager on one server to manage SQL (MSDE) running on
another server. Am I OK so far?
When I create a maintenance plan for all databases on the MSDE server, and
set it for keeping the backup for X days, next the dropdown box which
should, I think, give options of hours, days, etc. is blank. Also, when I
put the ext3ension in as, say bak, save t6he plan then go back into it to
look at its properties, both my schedule and the extension are blank.
Any ideas ? Or, am I wrong to use EM for MSDE '
ThanksAs far as the license issue, I am still confused by when you can and cannot
use MSDE and Enterprise Mangler together. The safest thing is to either:
A) Do not use MSDE. Personally, I consolidate all back-end SQL stuff that
normally runs against MSDE onto a single Standard (soon to be Workgroup)
edition of SQL Server.
B) Use SQL Web Administrator
(http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en).
It's free and it's legal.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Adminstrator
"Dave Mc" <DaveMc@.discussions.microsoft.com> wrote in message
news:4EDBBC5E-C589-47E6-AF8B-54FE78F6C510@.microsoft.com...
>I am using Enterprise manager on one server to manage SQL (MSDE) running on
> another server. Am I OK so far?
> When I create a maintenance plan for all databases on the MSDE server, and
> set it for keeping the backup for X days, next the dropdown box which
> should, I think, give options of hours, days, etc. is blank. Also, when I
> put the ext3ension in as, say bak, save t6he plan then go back into it to
> look at its properties, both my schedule and the extension are blank.
> Any ideas ? Or, am I wrong to use EM for MSDE '
> Thanks|||I am not as concerned with licensing as I am with the technical feasibilty of
using EM to set up maintenance plans for a machine running MSDE as opposed to
full SQL. It seems like it just doesn't work with MSDE and that may be by
design.
"Geoff N. Hiten" wrote:
> As far as the license issue, I am still confused by when you can and cannot
> use MSDE and Enterprise Mangler together. The safest thing is to either:
> A) Do not use MSDE. Personally, I consolidate all back-end SQL stuff that
> normally runs against MSDE onto a single Standard (soon to be Workgroup)
> edition of SQL Server.
> B) Use SQL Web Administrator
> (http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en).
> It's free and it's legal.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Adminstrator
>
> "Dave Mc" <DaveMc@.discussions.microsoft.com> wrote in message
> news:4EDBBC5E-C589-47E6-AF8B-54FE78F6C510@.microsoft.com...
> >I am using Enterprise manager on one server to manage SQL (MSDE) running on
> > another server. Am I OK so far?
> >
> > When I create a maintenance plan for all databases on the MSDE server, and
> > set it for keeping the backup for X days, next the dropdown box which
> > should, I think, give options of hours, days, etc. is blank. Also, when I
> > put the ext3ension in as, say bak, save t6he plan then go back into it to
> > look at its properties, both my schedule and the extension are blank.
> > Any ideas ? Or, am I wrong to use EM for MSDE '
> >
> > Thanks
>
>|||Technically, you can use Enterprise Manager with MSDE. Even with EM, the
wizards may not work correctly with MSDE. The latest Books On-Line contains
much better content on MSDE than the original release BOL. You can download
it here:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Dave Mc" <DaveMc@.discussions.microsoft.com> wrote in message
news:5B0A3223-1555-4532-B012-9BF782154AA6@.microsoft.com...
>I am not as concerned with licensing as I am with the technical feasibilty
>of
> using EM to set up maintenance plans for a machine running MSDE as opposed
> to
> full SQL. It seems like it just doesn't work with MSDE and that may be by
> design.
>
> "Geoff N. Hiten" wrote:
>> As far as the license issue, I am still confused by when you can and
>> cannot
>> use MSDE and Enterprise Mangler together. The safest thing is to either:
>> A) Do not use MSDE. Personally, I consolidate all back-end SQL stuff
>> that
>> normally runs against MSDE onto a single Standard (soon to be Workgroup)
>> edition of SQL Server.
>> B) Use SQL Web Administrator
>> (http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en).
>> It's free and it's legal.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Adminstrator
>>
>> "Dave Mc" <DaveMc@.discussions.microsoft.com> wrote in message
>> news:4EDBBC5E-C589-47E6-AF8B-54FE78F6C510@.microsoft.com...
>> >I am using Enterprise manager on one server to manage SQL (MSDE) running
>> >on
>> > another server. Am I OK so far?
>> >
>> > When I create a maintenance plan for all databases on the MSDE server,
>> > and
>> > set it for keeping the backup for X days, next the dropdown box which
>> > should, I think, give options of hours, days, etc. is blank. Also,
>> > when I
>> > put the ext3ension in as, say bak, save t6he plan then go back into it
>> > to
>> > look at its properties, both my schedule and the extension are blank.
>> > Any ideas ? Or, am I wrong to use EM for MSDE '
>> >
>> > Thanks
>>

Maintenance Plan Issue

Sorry for the repost but I had an issue with my alias and had to repost per
customer service.
I'm running across a weird issue that I can't figure out. I trying to create
a maintenance plan on two 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 but not on
these two servers and really need to resolve the issue on these servers.Hello,
Thank you for posting here.
Based on the symptom, it seems the "BackupDirectory " value of the
following registry key is corrupted or missing:
HKEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\<instance
name>\MSSQLServer (named instance)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer (default
instance)
If it is missing, please right click above Key->New->String Value, change
the name to BackupDirectory, and change it value to the proper path such as:
C:\Program Files\Microsoft SQL Server\MSSQL\backup
NOTE: Using Registry Editor incorrectly can cause serious problems that may
require you to reinstall Windows. Microsoft cannot guarantee that problems
resulting from the incorrect use of Registry Editor can be solved. Use
Registry Editor at your own risk.
256986 Description of the Microsoft Windows Registry
http://support.microsoft.com/?id=256986
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Maintenance Plan Issue
| thread-index: Acg26I1Tyaq8Y40fRC+Uz8AX1JIRtw==| X-WBNR-Posting-Host: 207.46.19.168
| From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.noemail.noemail>
| Subject: Maintenance Plan Issue
| Date: Tue, 4 Dec 2007 18:43:01 -0800
| Lines: 12
| Message-ID: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32053
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sorry for the repost but I had an issue with my alias and had to repost
per
| customer service.
|
| I'm running across a weird issue that I can't figure out. I trying to
create
| a maintenance plan on two 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 but not
on
| these two servers and really need to resolve the issue on these servers.
||||Hello,
How's everything going?
I'm wondering if the suggestion has helped or if you have any further
questions. Please feel free to respond to the newsgroups if you need any
additional help.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 96426387
| References: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Wed, 05 Dec 2007 09:22:23 GMT
| Subject: RE: Maintenance Plan Issue
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <p9V2aByNIHA.7908@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 81
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32075
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| Based on the symptom, it seems the "BackupDirectory " value of the
| following registry key is corrupted or missing:
|
| HKEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\<instance
| name>\MSSQLServer (named instance)
|
| HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer (default
| instance)
|
| If it is missing, please right click above Key->New->String Value, change
| the name to BackupDirectory, and change it value to the proper path such
as:
|
| C:\Program Files\Microsoft SQL Server\MSSQL\backup
|
| NOTE: Using Registry Editor incorrectly can cause serious problems that
may
| require you to reinstall Windows. Microsoft cannot guarantee that
problems
| resulting from the incorrect use of Registry Editor can be solved. Use
| Registry Editor at your own risk.
|
| 256986 Description of the Microsoft Windows Registry
| http://support.microsoft.com/?id=256986
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu
| MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | Thread-Topic: Maintenance Plan Issue
| | thread-index: Acg26I1Tyaq8Y40fRC+Uz8AX1JIRtw==| | X-WBNR-Posting-Host: 207.46.19.168
| | From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=| <georgedschneider@.noemail.noemail>
| | Subject: Maintenance Plan Issue
| | Date: Tue, 4 Dec 2007 18:43:01 -0800
| | Lines: 12
| | Message-ID: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 7bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32053
| | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Sorry for the repost but I had an issue with my alias and had to repost
| per
| | customer service.
| |
| | I'm running across a weird issue that I can't figure out. I trying to
| create
| | a maintenance plan on two 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 but
not
| on
| | these two servers and really need to resolve the issue on these servers.
| |
|
|sql

Maintenance plan issue

Maintenance Plan Issue

Sorry for the repost but I had an issue with my alias and had to repost per
customer service.
I'm running across a weird issue that I can't figure out. I trying to creat
e
a maintenance plan on two 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 but not on
these two servers and really need to resolve the issue on these servers.Hello,
Thank you for posting here.
Based on the symptom, it seems the "BackupDirectory " value of the
following registry key is corrupted or missing:
HKEY_Local_Machine\Software\Microsoft\Mi
crosoft SQL Server\<instance
name>\MSSQLServer (named instance)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer (default
instance)
If it is missing, please right click above Key->New->String Value, change
the name to BackupDirectory, and change it value to the proper path such as:
C:\Program Files\Microsoft SQL Server\MSSQL\backup
NOTE: Using Registry Editor incorrectly can cause serious problems that may
require you to reinstall Windows. Microsoft cannot guarantee that problems
resulting from the incorrect use of Registry Editor can be solved. Use
Registry Editor at your own risk.
256986 Description of the Microsoft Windows Registry
http://support.microsoft.com/?id=256986
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Maintenance Plan Issue
| thread-index: Acg26I1Tyaq8Y40fRC+Uz8AX1JIRtw==
| X-WBNR-Posting-Host: 207.46.19.168
| From: examnotes
<georgedschneider@.noemail.noemail>
| Subject: Maintenance Plan Issue
| Date: Tue, 4 Dec 2007 18:43:01 -0800
| Lines: 12
| Message-ID: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32053
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sorry for the repost but I had an issue with my alias and had to repost
per
| customer service.
|
| I'm running across a weird issue that I can't figure out. I trying to
create
| a maintenance plan on two 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 but not
on
| these two servers and really need to resolve the issue on these servers.
||||Hello,
How's everything going?
I'm wondering if the suggestion has helped or if you have any further
questions. Please feel free to respond to the newsgroups if you need any
additional help.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 96426387
| References: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Wed, 05 Dec 2007 09:22:23 GMT
| Subject: RE: Maintenance Plan Issue
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <p9V2aByNIHA.7908@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 81
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32075
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| Based on the symptom, it seems the "BackupDirectory " value of the
| following registry key is corrupted or missing:
|
| HKEY_Local_Machine\Software\Microsoft\Mi
crosoft SQL Server\<instance
| name>\MSSQLServer (named instance)
|
| HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer (default
| instance)
|
| If it is missing, please right click above Key->New->String Value, change
| the name to BackupDirectory, and change it value to the proper path such
as:
|
| C:\Program Files\Microsoft SQL Server\MSSQL\backup
|
| NOTE: Using Registry Editor incorrectly can cause serious problems that
may
| require you to reinstall Windows. Microsoft cannot guarantee that
problems
| resulting from the incorrect use of Registry Editor can be solved. Use
| Registry Editor at your own risk.
|
| 256986 Description of the Microsoft Windows Registry
| http://support.microsoft.com/?id=256986
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu
| MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| ========================================
=============
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| ========================================
=============
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | Thread-Topic: Maintenance Plan Issue
| | thread-index: Acg26I1Tyaq8Y40fRC+Uz8AX1JIRtw==
| | X-WBNR-Posting-Host: 207.46.19.168
| | From: examnotes
| <georgedschneider@.noemail.noemail>
| | Subject: Maintenance Plan Issue
| | Date: Tue, 4 Dec 2007 18:43:01 -0800
| | Lines: 12
| | Message-ID: <468EAE1F-24C3-48EA-A45E-F942359AACC6@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 7bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32053
| | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Sorry for the repost but I had an issue with my alias and had to repost
| per
| | customer service.
| |
| | I'm running across a weird issue that I can't figure out. I trying to
| create
| | a maintenance plan on two 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 but
not
| on
| | these two servers and really need to resolve the issue on these servers.
| |
|
|

maintenance plan in enterprise manager not functioning quite right

hello,
wondering if anyone could give me some insight. i've created two
maintenance plans for each of two sql servers (production and test). the
first of each is supposed to check the master, model, and msdb dbs and then
do a complete backup of them as well as their transaction logs. the second
of each does the same for all my user dbs.
all dbs are set to recovery model full except for the master and msdb dbs.
the plan is basically working, except that it is skipping a few of the user
dbs, and it is also skipping the master and msdb dbs on both servers. that
is, i see no mention of them in the logs, and no backup files are created on
disk.
what am i doing wrong?
appreciated,
matthew
Well it can't issue a log backup on Master and MSDB since they are in simple
mode. My guess is the others are as well.
Andrew J. Kelly SQL MVP
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in
message news:8EC5F016-98C3-4712-8F4D-F1B84B2A20E5@.microsoft.com...
> hello,
> wondering if anyone could give me some insight. i've created two
> maintenance plans for each of two sql servers (production and test). the
> first of each is supposed to check the master, model, and msdb dbs and
then
> do a complete backup of them as well as their transaction logs. the
second
> of each does the same for all my user dbs.
> all dbs are set to recovery model full except for the master and msdb dbs.
> the plan is basically working, except that it is skipping a few of the
user
> dbs, and it is also skipping the master and msdb dbs on both servers.
that
> is, i see no mention of them in the logs, and no backup files are created
on
> disk.
> what am i doing wrong?
> appreciated,
> matthew

maintenance plan glitch

I have a maintenance plan that backs up a customer
database and its transactions. The plan is supposed to
delete backup files after 1 week. The database backup
files delete fine. However, the transaction backup files
are not deleting. I tried recreating the maintenance plan
to see if that would enforce the settings, thinking that
there could be a setting corruption issue. However, this
still didn't work. Any ideas?
Thanks,
JasonBelow KB might help:
http://support.microsoft.com/defaul...2&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
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"jason" <anonymous@.discussions.microsoft.com> wrote in message
news:9a5d01c3ea96$9922e220$a401280a@.phx.gbl...

quote:

> I have a maintenance plan that backs up a customer
> database and its transactions. The plan is supposed to
> delete backup files after 1 week. The database backup
> files delete fine. However, the transaction backup files
> are not deleting. I tried recreating the maintenance plan
> to see if that would enforce the settings, thinking that
> there could be a setting corruption issue. However, this
> still didn't work. Any ideas?
> Thanks,
> Jason

maintenance plan glitch

I have a maintenance plan that backs up a customer
database and its transactions. The plan is supposed to
delete backup files after 1 week. The database backup
files delete fine. However, the transaction backup files
are not deleting. I tried recreating the maintenance plan
to see if that would enforce the settings, thinking that
there could be a setting corruption issue. However, this
still didn't work. Any ideas?
Thanks,
JasonBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&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
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"jason" <anonymous@.discussions.microsoft.com> wrote in message
news:9a5d01c3ea96$9922e220$a401280a@.phx.gbl...
> I have a maintenance plan that backs up a customer
> database and its transactions. The plan is supposed to
> delete backup files after 1 week. The database backup
> files delete fine. However, the transaction backup files
> are not deleting. I tried recreating the maintenance plan
> to see if that would enforce the settings, thinking that
> there could be a setting corruption issue. However, this
> still didn't work. Any ideas?
> Thanks,
> Jasonsql

Maintenance Plan for SQL Server 2005 64-bit doesn't work!

Hi all,
We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night).

Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\\FileServer\TL\DBLogs"

NB: if using the local drives, it work

Check List
Security:
- The account that I used to create the plan is an sa account
- The location that I dump the TL files, I have full access to the folder

SQL Statement:
exec xp_cmdshell 'dir \FileServer\TL\DBLogs' (it list all files)

Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly

Resolved: permission problems, u need to give permission to the folder for SQL Server Agent account

Maintenance Plan for msde?

Hi,
Is the Maintenace Plan feature available for MSDE?
Thanks,
Ben
hi Ben,
"Ben" <Ben@.discussions.microsoft.com> ha scritto nel messaggio
news:347D0FC2-96D5-46DA-BA0B-5EBF82039D8A@.microsoft.com
> Hi,
> Is the Maintenace Plan feature available for MSDE?
> Thanks,
> Ben
yes, but with no graphical management UI... only the command line tool
sqlmaint.exe...
a little cryptic, but can be guided like
http://msdn.microsoft.com/library/de...maint_19ix.asp
explains..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

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 for all databases

Hi,
I would like to know if its better to create ONE maintenance plan for all
databases or create a maint plan for every database.
Thanks,
RamuRamu,
It depends on whether every database should have the same maintenance plan.
Don't make more plans than you need, that just adds more work for you to set
up and maintain, but don't try to cram everything into a single plan if they
should be treated differently.
I have 3 to 5 maintenance plans per server, depending on the class of
individual databases.
RLF
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:6B7A5A16-CC9C-4B3C-A1DC-19BFF4DD3AAF@.microsoft.com...
> Hi,
> I would like to know if its better to create ONE maintenance plan for all
> databases or create a maint plan for every database.
> Thanks,
> Ramu
>|||On Aug 9, 10:46 am, Ramu <R...@.discussions.microsoft.com> wrote:
> Hi,
> I would like to know if its better to create ONE maintenance plan for all
> databases or create a maint plan for every database.
> Thanks,
> Ramu
Often times I like to simply separate my base maintenance plans into
one that works on the system tables, while the other works on the user
tables.|||I do a plan for production (user databases), one for system, and if
needed one for transaction logs. I like to separate them. I don't
think SQL cares but it seems neater to me.
acorcoran wrote:
> On Aug 9, 10:46 am, Ramu <R...@.discussions.microsoft.com> wrote:
>> Hi,
>> I would like to know if its better to create ONE maintenance plan for all
>> databases or create a maint plan for every database.
>> Thanks,
>> Ramu
> Often times I like to simply separate my base maintenance plans into
> one that works on the system tables, while the other works on the user
> tables.
>

maintenance plan for all databases

Hi,
I would like to know if its better to create ONE maintenance plan for all
databases or create a maint plan for every database.
Thanks,
RamuRamu,
It depends on whether every database should have the same maintenance plan.
Don't make more plans than you need, that just adds more work for you to set
up and maintain, but don't try to cram everything into a single plan if they
should be treated differently.
I have 3 to 5 maintenance plans per server, depending on the class of
individual databases.
RLF
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:6B7A5A16-CC9C-4B3C-A1DC-19BFF4DD3AAF@.microsoft.com...
> Hi,
> I would like to know if its better to create ONE maintenance plan for all
> databases or create a maint plan for every database.
> Thanks,
> Ramu
>|||On Aug 9, 10:46 am, Ramu <R...@.discussions.microsoft.com> wrote:
> Hi,
> I would like to know if its better to create ONE maintenance plan for all
> databases or create a maint plan for every database.
> Thanks,
> Ramu
Often times I like to simply separate my base maintenance plans into
one that works on the system tables, while the other works on the user
tables.|||I do a plan for production (user databases), one for system, and if
needed one for transaction logs. I like to separate them. I don't
think SQL cares but it seems neater to me.
acorcoran wrote:
> On Aug 9, 10:46 am, Ramu <R...@.discussions.microsoft.com> wrote:
> Often times I like to simply separate my base maintenance plans into
> one that works on the system tables, while the other works on the user
> tables.
>

Maintenance Plan folder not shown in Management Folder

Hi,
I have a problem on just one server : The Management folder in
Enterprise Manager does not contain a folder for Maintenance plans. I
have created a plan by right clicking on a db, all tasks > maintenance
plan, but I have no way of editing this as it is not displayed
anywhere. I know I can edit the individual jobs that appear in the
Jobs folder, but I'd like to know why the maintenance plan folder is
not shown. It's not possible to re-install SQL, I would appreciate any
help.
Thanks
Kev.Perhaps just a refresh problem? Have you tried to restart Enterprise Manager?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kev smart" <kev@.earlshilton.com> wrote in message
news:13f1d9b4.0504040344.640c89ad@.posting.google.com...
> Hi,
> I have a problem on just one server : The Management folder in
> Enterprise Manager does not contain a folder for Maintenance plans. I
> have created a plan by right clicking on a db, all tasks > maintenance
> plan, but I have no way of editing this as it is not displayed
> anywhere. I know I can edit the individual jobs that appear in the
> Jobs folder, but I'd like to know why the maintenance plan folder is
> not shown. It's not possible to re-install SQL, I would appreciate any
> help.
> Thanks
> Kev.|||Yes, restarted em. Now I find out its SQL 2000 msde with no SP, am I
right in thinking that the maintenance plan option should be added when
applying sp3a?
Thanks
Kev|||I'm not sure, I haven't used EM against MSDE. Give it a spin and see...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nsiadmin" <kev@.earlshilton.com> wrote in message
news:1112621866.677876.74970@.l41g2000cwc.googlegroups.com...
> Yes, restarted em. Now I find out its SQL 2000 msde with no SP, am I
> right in thinking that the maintenance plan option should be added when
> applying sp3a?
> Thanks
> Kev
>|||I applied sp3adesk, wrote output to a log file which ends with
(snip)
Property(C): DATABASE = C:\DOCUME~1\NETWOR~1\LOCALS~1\Temp\6c801f.msi
Property(C): OriginalDatabase = D:\sql2ksp3\MSDE\Setup\SqlRun01.msi
Property(C): UILevel = 3
Property(C): QFEUpgrade = 1
Property(C): Preselected = 1
Property(C): ACTION = INSTALL
=== Logging stopped: 4/5/2005 7:18:49 ===MSI (c) (C4:CC): Note: 1: 1728
MSI (c) (C4:CC): Product: Microsoft SQL Server Desktop Engine --
Configuration completed successfully.
MSI (c) (C4:CC): Grabbed execution mutex.
MSI (c) (C4:CC): Cleaning up uninstalled install packages, if any exist
MSI (c) (C4:CC): MainEngineThread is returning 0
=== Verbose logging stopped: 4/5/2005 7:18:49 ===
Which looks like a successful install, but after rebooting the server and
going back into enterprise manager, there is still no Maintenance Plan folder
and the server properties ( and @.@.version ) show the version as sql2000
8.00.194 RTM as though no service pack installed.
Getting desperate....... Kev.
"Tibor Karaszi" wrote:
> I'm not sure, I haven't used EM against MSDE. Give it a spin and see...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "nsiadmin" <kev@.earlshilton.com> wrote in message
> news:1112621866.677876.74970@.l41g2000cwc.googlegroups.com...
> > Yes, restarted em. Now I find out its SQL 2000 msde with no SP, am I
> > right in thinking that the maintenance plan option should be added when
> > applying sp3a?
> >
> > Thanks
> >
> > Kev
> >
>
>|||Have you checked whether the maint wiz is included in msde? I'm not sure it is...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kevsmart" <kevsmart@.discussions.microsoft.com> wrote in message
news:711B7170-7B9F-4570-B40B-8023EECD1A56@.microsoft.com...
>I applied sp3adesk, wrote output to a log file which ends with
> (snip)
> Property(C): DATABASE = C:\DOCUME~1\NETWOR~1\LOCALS~1\Temp\6c801f.msi
> Property(C): OriginalDatabase = D:\sql2ksp3\MSDE\Setup\SqlRun01.msi
> Property(C): UILevel = 3
> Property(C): QFEUpgrade = 1
> Property(C): Preselected = 1
> Property(C): ACTION = INSTALL
> === Logging stopped: 4/5/2005 7:18:49 ===> MSI (c) (C4:CC): Note: 1: 1728
> MSI (c) (C4:CC): Product: Microsoft SQL Server Desktop Engine --
> Configuration completed successfully.
> MSI (c) (C4:CC): Grabbed execution mutex.
> MSI (c) (C4:CC): Cleaning up uninstalled install packages, if any exist
> MSI (c) (C4:CC): MainEngineThread is returning 0
> === Verbose logging stopped: 4/5/2005 7:18:49 ===>
> Which looks like a successful install, but after rebooting the server and
> going back into enterprise manager, there is still no Maintenance Plan folder
> and the server properties ( and @.@.version ) show the version as sql2000
> 8.00.194 RTM as though no service pack installed.
>
> Getting desperate....... Kev.
>
> "Tibor Karaszi" wrote:
>> I'm not sure, I haven't used EM against MSDE. Give it a spin and see...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "nsiadmin" <kev@.earlshilton.com> wrote in message
>> news:1112621866.677876.74970@.l41g2000cwc.googlegroups.com...
>> > Yes, restarted em. Now I find out its SQL 2000 msde with no SP, am I
>> > right in thinking that the maintenance plan option should be added when
>> > applying sp3a?
>> >
>> > Thanks
>> >
>> > Kev
>> >
>>|||The wizard is there because it is accessible by right clicking the db name,
all tasks, maintenance plan. However although this creates the plan and jobs,
it wont run because there is no sqlmaint.exe in the binn folder. Once this is
copied in (from any other SQL installation ) the next plan to be created will
generate the Maintenance Plan folder under Management. Problem solved!
Thanks
Kev
"Tibor Karaszi" wrote:
> Have you checked whether the maint wiz is included in msde? I'm not sure it is...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "kevsmart" <kevsmart@.discussions.microsoft.com> wrote in message
> news:711B7170-7B9F-4570-B40B-8023EECD1A56@.microsoft.com...
> >I applied sp3adesk, wrote output to a log file which ends with
> >
> > (snip)
> > Property(C): DATABASE = C:\DOCUME~1\NETWOR~1\LOCALS~1\Temp\6c801f.msi
> > Property(C): OriginalDatabase = D:\sql2ksp3\MSDE\Setup\SqlRun01.msi
> > Property(C): UILevel = 3
> > Property(C): QFEUpgrade = 1
> > Property(C): Preselected = 1
> > Property(C): ACTION = INSTALL
> > === Logging stopped: 4/5/2005 7:18:49 ===> > MSI (c) (C4:CC): Note: 1: 1728
> > MSI (c) (C4:CC): Product: Microsoft SQL Server Desktop Engine --
> > Configuration completed successfully.
> >
> > MSI (c) (C4:CC): Grabbed execution mutex.
> > MSI (c) (C4:CC): Cleaning up uninstalled install packages, if any exist
> > MSI (c) (C4:CC): MainEngineThread is returning 0
> > === Verbose logging stopped: 4/5/2005 7:18:49 ===> >
> >
> > Which looks like a successful install, but after rebooting the server and
> > going back into enterprise manager, there is still no Maintenance Plan folder
> > and the server properties ( and @.@.version ) show the version as sql2000
> > 8.00.194 RTM as though no service pack installed.
> >
> >
> > Getting desperate....... Kev.
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> I'm not sure, I haven't used EM against MSDE. Give it a spin and see...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "nsiadmin" <kev@.earlshilton.com> wrote in message
> >> news:1112621866.677876.74970@.l41g2000cwc.googlegroups.com...
> >> > Yes, restarted em. Now I find out its SQL 2000 msde with no SP, am I
> >> > right in thinking that the maintenance plan option should be added when
> >> > applying sp3a?
> >> >
> >> > Thanks
> >> >
> >> > Kev
> >> >
> >>
> >>
> >>
>
>sql

Maintenance Plan File Deletion Bug

I have a maintenance plan set to delete files after 1 week. Instead it is deleting everthing older then 1 day. When I view the tsql in the designer, the xp_delete_file string looks correct. Is there anyway to see the exact xp_delete_file string that is actually being executed at run time?

I found the problem.

There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

The patching procedure is a little complicated. Read this:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

What service pack you are on?

There were maintenane related bugs in SP2 so that MS released hot fix on 03/05/2007.

Check the hot fix if you are already on SP2 applied before the hot fix release. If you are on SP1 apply the SP2 down loaded after 03/06/2007 which is hot fix included.

|||

I found out what the problem is.

There is an "off by one" bug where the wrong time unit of measure is being used.

"Hours" are being converted to "Days", Days" are being converted to "Hours", and "Hours" will blow with an invalid index.

This problem exists for "Maintenance Cleanup" tasks and "Clean Up History" tasks.

The fix is a little complicated because there are two versions of sp2 out there.

Read this to figure out the proper fix:

http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

Maintenance Plan failures - Incorrect SET options

All,
I'm having trouble figuring this one out. I've got a maintenance plan
performing both integrity checks and optimizations on several user
dbs. The plan succeeds on 8 of the databases but fails on the other
six with the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed becau
se the
following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Now, here is the weird part, when I check the settings of the
databases that are succeding vs. the ones that are failing, the exact
same SET options are turned on. Here is an example:
Maintenance succeeds on this db:
The following options are set:
--
torn page detection
arithabort
ANSI null default
quoted identifier
auto create statistics
auto update statistics
Maintenance fails on this db:
The following options are set:
--
torn page detection
arithabort
ANSI null default
quoted identifier
auto create statistics
auto update statistics
Has anyone experienced this behavior?
Thanks in advance.The settings at the database level are confusing, and IMO worthless, as they
are overridden by the
applications (SQL Agent and sqlmaint in this case) anyhow. I suggest that yo
u either remove the stuff that
requires these settings, or write your own maint jobs and execute as TSQL jo
bsteps from where you can execute
the needed SET commands.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message news:49e85cd3.0406080459.3b2819e8@.pos
ting.google.com...
> All,
> I'm having trouble figuring this one out. I've got a maintenance plan
> performing both integrity checks and optimizations on several user
> dbs. The plan succeeds on 8 of the databases but fails on the other
> six with the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed bec
ause the
> following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Now, here is the weird part, when I check the settings of the
> databases that are succeding vs. the ones that are failing, the exact
> same SET options are turned on. Here is an example:
> Maintenance succeeds on this db:
> The following options are set:
> --
> torn page detection
> arithabort
> ANSI null default
> quoted identifier
> auto create statistics
> auto update statistics
>
> Maintenance fails on this db:
> The following options are set:
> --
> torn page detection
> arithabort
> ANSI null default
> quoted identifier
> auto create statistics
> auto update statistics
> Has anyone experienced this behavior?
> Thanks in advance.|||do you have any computed columns in those db's that it fails?
i've had a very similar problem where statistics were getting created on
computed columns causing problems with some of the dbmp jobs.
if you have computed columns, go make sure there are no indexes on them
and delete any statistics for those columns.
sqlboy2000 wrote:
> All,
> I'm having trouble figuring this one out. I've got a maintenance plan
> performing both integrity checks and optimizations on several user
> dbs. The plan succeeds on 8 of the databases but fails on the other
> six with the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed bec
ause the
> following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Now, here is the weird part, when I check the settings of the
> databases that are succeding vs. the ones that are failing, the exact
> same SET options are turned on. Here is an example:
> Maintenance succeeds on this db:
> The following options are set:
> --
> torn page detection
> arithabort
> ANSI null default
> quoted identifier
> auto create statistics
> auto update statistics
> Maintenance fails on this db:
> The following options are set:
> --
> torn page detection
> arithabort
> ANSI null default
> quoted identifier
> auto create statistics
> auto update statistics
> Has anyone experienced this behavior?
> Thanks in advance.

Friday, March 30, 2012

Maintenance plan issue

Maintenance Plan Issue

Maintenance plan issue

Maintenance plan issue

Maintenance Plan Issue

Maintenance plan issue

Maintenance Plan Issue

maintenance plan in enterprise manager not functioning quite right

maintenance plan glitch

maintenance plan glitch

Maintenance Plan for SQL Server 2005 64-bit doesn't work!

Maintenance Plan for msde?

Maintenance Plan for Compatibility Mode 7.0 databases

Maintenance Plan for Compatibility Mode 7.0 databases

maintenance plan for all databases

maintenance plan for all databases

maintenance plan for all databases

Maintenance Plan folder not shown in Management Folder

Maintenance Plan folder not shown in Management Folder

Maintenance Plan folder not shown in Management Folder

Maintenance Plan File Deletion Bug

Maintenance Plan failures - Incorrect SET options

Lowering priority

Blog Archive

About Me