Manage SQL Server 2000 database from new workstation?

I'm currently using a new workstation and need to manage some databases on
my ISPs SQL 2000 server.
I've tried installing SSMSEE, but it won't install since I don't have
SQL2005 (or any SQL server) installed on this machine.
My host does not have a control panel that I can use to maintain SQL
databases.
What can I use to modify our online databases?Hi,
you can use any third party tool:
http://www.aspfaq.com/2442
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1144223204.270214.319200@.e56g2000cwe.googlegroups.com...
> Hi,
> you can use any third party tool:
> http://www.aspfaq.com/2442
Thanks!
Looks like I'm going to need a web based app due to firewall constraints.
Several to check out on that page.sql

Manage SQL Server 2000 database from new workstation?

I'm currently using a new workstation and need to manage some databases on
my ISPs SQL 2000 server.
I've tried installing SSMSEE, but it won't install since I don't have
SQL2005 (or any SQL server) installed on this machine.
My host does not have a control panel that I can use to maintain SQL
databases.
What can I use to modify our online databases?
Hi,
you can use any third party tool:
http://www.aspfaq.com/2442
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1144223204.270214.319200@.e56g2000cwe.googlegr oups.com...
> Hi,
> you can use any third party tool:
> http://www.aspfaq.com/2442
Thanks!
Looks like I'm going to need a web based app due to firewall constraints.
Several to check out on that page.

Manage server in single user mode

Hello-
I am trying to use SQL server in single user mode to move the system
databases to a different drive as described in KB article
http://support.microsoft.com/kb/224071/
I have sql server started up in single user mode with the other startup
options configured as required. However I can not seem to find a way that it
will let me log in in order to run the procedures. The SQL server agent is
stopped as described in order to not use up the available connection.
Whenever I try to connect with SQL Server management studio it says "Login
failed for user xxxx. Reason: Server is in single user mode. Only one
administrator can connect at this time (Error 18461)."
I am the only person who even knows about this server so I know no one else
is conecting to it. I thought it would work to try and do this procedure
through the command line but i cant figure out how to connect to the server
using the command line (im doing this all local on the server).
Can anyone give me any suggestions on how to go about this or what I could
be doing wrong?
Thanks!It's probably from Object Explorer - you probably have SSMS
configured to open Object Explorer at startup. Close object
explorer and try to connect. You can hit cancel when the
Login dialog comes up and and SSMS opens with no connection.
Then close object explorer. Then connect using New Query.
-Sue
On Mon, 23 Jul 2007 12:26:02 -0700, Ehren
<Ehren@.discussions.microsoft.com> wrote:
>Hello-
>I am trying to use SQL server in single user mode to move the system
>databases to a different drive as described in KB article
>http://support.microsoft.com/kb/224071/
>I have sql server started up in single user mode with the other startup
>options configured as required. However I can not seem to find a way that it
>will let me log in in order to run the procedures. The SQL server agent is
>stopped as described in order to not use up the available connection.
>Whenever I try to connect with SQL Server management studio it says "Login
>failed for user xxxx. Reason: Server is in single user mode. Only one
>administrator can connect at this time (Error 18461)."
>I am the only person who even knows about this server so I know no one else
>is conecting to it. I thought it would work to try and do this procedure
>through the command line but i cant figure out how to connect to the server
>using the command line (im doing this all local on the server).
>Can anyone give me any suggestions on how to go about this or what I could
>be doing wrong?
>Thanks!|||On Jul 24, 2:25 am, Sue Hoegemeier <Su...@.nomail.please> wrote:
> It's probably from Object Explorer - you probably have SSMS
> configured to open Object Explorer at startup. Close object
> explorer and try toconnect. Youcanhit cancel when the
> Login dialog comes up and and SSMS opens with no connection.
> Then close object explorer. Thenconnectusing New Query.
> -Sue
> On Mon, 23 Jul 2007 12:26:02 -0700, Ehren
>
> <Eh...@.discussions.microsoft.com> wrote:
> >Hello-
> >I am trying to use SQLserverinsingleusermodeto move the system
> >databases to a different drive as described in KB article
> >http://support.microsoft.com/kb/224071/
> >I have sqlserverstarted up insingleusermodewith the other startup
> >options configured as required. However Icannot seem to find a way that it
> >will let me log in in order to run the procedures. The SQLserveragent is
> >stopped as described in order to not use up the available connection.
> >Whenever I try toconnectwith SQLServermanagement studio it says "Login
> >failed foruserxxxx. Reason:Serveris insingleusermode. Onlyone
> >administratorcanconnectat thistime(Error 18461)."
> >I am theonlyperson who even knows about thisserverso I know nooneelse
> >is conecting to it. I thought it would work to try and do this procedure
> >through the command line but i cant figure out how toconnectto theserver
> >using the command line (im doing this all local on theserver).
> >Cananyone give me any suggestions on how to go about this or what I could
> >be doing wrong?
> >Thanks!- Hide quoted text -
> - Show quoted text -
The process is
NET START MSSQLSERVER /f /T3608 (as per MS article)
Make sure no other SQL tools are running (config manager etc)
Start SQL Server management studio
When it prompts you to connect to the server press cancel (if you
press connect it is THIS connection which prevents you from running
the alter database query)
At this point you are not connected to anything (it will say "No
Server Connection" in big letters)
Press the New Query button
Paste your amended alter database code into thew query pane and
execute
Continue as per article
Cheers
Barry

Manage MSDE databases with SSMS?

We have an XP workstation containing a very important MSDE-based
application. Its MSDE database isn't likely to change for quite a
while.
The app contains an internal backup method (File, Backup, Backup) and
an auxiliary app/script for shrinking the database and logs. I would
like to automate the backup and shrinking so end users don't have to
remember to do them.
The workstation has a static IP so it can go out to the Internet when
necessary. The workstation is presently not joined to the domain.
1. Can I install SSMS Express *only* as a local app (without SQL
Express 2005) and thus use SSMS to set up nightly backups and periodic
shrink database/logs jobs?
2. We have SSMS running on SQL 2005 server box in the domain. If I
join the workstation only to the domain (local account logon is not
changed, still used), could I use SSMS from the SQL 2005 box? Would I
need a domain login on the XP box to make this work?
Thank you, Tom
I stand corrected...I know one is not supposed to double-post.
I found this NG after I had posted to the other groups, and I could
not figure out how active the other two groups are, and this NG had
more past posts about SSMS/MSDE than the other groups, so it seemed
appropriate to also inquire here.
Thank you, Tom
|||FYI, you can add as many newsgroups as you like to the Newsgroups header and
the post appears in all of them as does the replies. He wasn't suggesting
you shouldn't post in more than one group, just don't do it individually.
Andrew J. Kelly SQL MVP
"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1185491191.280988.292210@.19g2000hsx.googlegro ups.com...
>I stand corrected...I know one is not supposed to double-post.
> I found this NG after I had posted to the other groups, and I could
> not figure out how active the other two groups are, and this NG had
> more past posts about SSMS/MSDE than the other groups, so it seemed
> appropriate to also inquire here.
> Thank you, Tom
>
|||Andrew J. Kelly (sqlmvpnooospam@.shadhawk.com) writes:
> FYI, you can add as many newsgroups as you like to the Newsgroups header
> and the post appears in all of them as does the replies. He wasn't
> suggesting you shouldn't post in more than one group, just don't do it
> individually .
Since tlyczko seems to have found this group after his first post, that
appears a little difficult to do. But he could have had some patience
before he tried this forum.
I saw that tlyczko had another question about activity in
comp.databases.ms-sqlserver this morning. It seems to me that while
comp.databases.ms-sqlserver has a fairly low traffic, it still more active
than the .tools newsgroup, which is kind of funny. But many tools questions
are asked in the "big" groups .programming and .server. It appears that
Microsoft has been a bit over-zealous when set up the various sets of
groups.
And I should explain the reason double-posting is bad, is that it could
lead to people wasting time on answering a question that already has a good
answer elsewhere.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Yeah...I really don't think people understand this. To them,
they are getting feedback from several different sources so
they think it's good.
An indirect bad effect - I encourage customers to search
newsgroups to find answers. I just got an email from
someone who said most of the answers to the question they
searched on were "already answered in another
newsgroup...please don't multipost" and they couldn't find
where it was actually answered.
-Sue
On Fri, 27 Jul 2007 09:36:29 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>And I should explain the reason double-posting is bad, is that it could
>lead to people wasting time on answering a question that already has a good
>answer elsewhere.

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.
Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:

>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.
|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
to[vbcol=seagreen]
also[vbcol=seagreen]
that
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
> >Quick question, I have a maintinence job that optimizes (reorganizes back
to
> >original size my databases back to the original amount of free space). I
also
> >have jobs that defrag specific tables.
> >
> >Will the maintinence job defrag as well as reorgainize.
> >
> >I would like to get rid of my defrag jobs and have one maintinence job
that
> >does both.
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I als
o
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:

>Quick question, I have a maintinence job that optimizes (reorganizes back t
o
>original size my databases back to the original amount of free space). I al
so
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.
4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
>
to[vbcol=seagreen]
also[vbcol=seagreen]
that[vbcol=seagreen]
>

Mainteneance plans BAK TRN

Hi All,
Ive created a maintenance plan which does the following (all in one plan)
* All Databases
* Reorganise data,
Change free space per page percentage to 10%
Remove unused space from database files
Shrink database when it grows beyond 50 Mb
Amound of free space to remain after shrink 10%
*Check database integrity
include indexes
*Back up the database as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension BAK
*Back up the Transaction log as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension TRN
*Write a report file in the directory
delete report files after 14 days
We also have one other plan which simply checks the integrity on all DBs
once a week. Does this solution / plan seem ok? Its the first one I have
created so Im not 100% on it.
Also what rights/user does the plan run under?
I find that sometimes it deletes the files over 5 days and then sometimes it
just doesnt, or it will delete the TRN files but not the BAK files or vice
versa. I cant really make sense of it. Any ideas?
Also would I be better off having seperate plans for removing the BAK and
the TRN files?
Any help would be appreciated.
Thank youAdrin,
If you are using Sql Server 2000, then check your script and please
specify any pattern that you see in not removing old files.
You should be able to remove old files without any issues.
In Sql 2005 removing old files from sub-directory was not possible.
Even that is fixed after applying SP1.
Hope this helps,
Sameer Raval
[DBA-Developer]
Augusta,GA,USA
"Adrian" wrote:
> Hi All,
> Ive created a maintenance plan which does the following (all in one plan)
> * All Databases
> * Reorganise data,
> Change free space per page percentage to 10%
> Remove unused space from database files
> Shrink database when it grows beyond 50 Mb
> Amound of free space to remain after shrink 10%
> *Check database integrity
> include indexes
> *Back up the database as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension BAK
> *Back up the Transaction log as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension TRN
> *Write a report file in the directory
> delete report files after 14 days
> We also have one other plan which simply checks the integrity on all DBs
> once a week. Does this solution / plan seem ok? Its the first one I have
> created so Im not 100% on it.
> Also what rights/user does the plan run under?
> I find that sometimes it deletes the files over 5 days and then sometimes it
> just doesnt, or it will delete the TRN files but not the BAK files or vice
> versa. I cant really make sense of it. Any ideas?
> Also would I be better off having seperate plans for removing the BAK and
> the TRN files?
> Any help would be appreciated.
> Thank you|||In addition, I've seen a plan stopping execution if something goes wrong. This can lead to old
backup files not being removed. Read the report file carefully. Also, don't have databases in simple
recovery mode in a plan where you do log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Raval" <SameerRaval@.discussions.microsoft.com> wrote in message
news:ED2CC0C2-6E36-4AA7-A6B4-8BD0AFD05934@.microsoft.com...
> Adrin,
> If you are using Sql Server 2000, then check your script and please
> specify any pattern that you see in not removing old files.
> You should be able to remove old files without any issues.
> In Sql 2005 removing old files from sub-directory was not possible.
> Even that is fixed after applying SP1.
> Hope this helps,
>
> Sameer Raval
> [DBA-Developer]
> Augusta,GA,USA
>
> "Adrian" wrote:
>> Hi All,
>> Ive created a maintenance plan which does the following (all in one plan)
>> * All Databases
>> * Reorganise data,
>> Change free space per page percentage to 10%
>> Remove unused space from database files
>> Shrink database when it grows beyond 50 Mb
>> Amound of free space to remain after shrink 10%
>> *Check database integrity
>> include indexes
>> *Back up the database as part of the maintenance plan
>> Verify the integrity of the database upon completion
>> Backup to Disk
>> Use this Directory
>> Remove files older than 5 days
>> Backup file extension BAK
>> *Back up the Transaction log as part of the maintenance plan
>> Verify the integrity of the database upon completion
>> Backup to Disk
>> Use this Directory
>> Remove files older than 5 days
>> Backup file extension TRN
>> *Write a report file in the directory
>> delete report files after 14 days
>> We also have one other plan which simply checks the integrity on all DBs
>> once a week. Does this solution / plan seem ok? Its the first one I have
>> created so Im not 100% on it.
>> Also what rights/user does the plan run under?
>> I find that sometimes it deletes the files over 5 days and then sometimes it
>> just doesnt, or it will delete the TRN files but not the BAK files or vice
>> versa. I cant really make sense of it. Any ideas?
>> Also would I be better off having seperate plans for removing the BAK and
>> the TRN files?
>> Any help would be appreciated.
>> Thank you|||Hey guys,
sorry for the late response I made some changes and wanted to wait a gew
days to see the results.
I changed all dbs to full recovery mode this seemed to fix the problem with
the BAK files and now they are getting backed up and removed successfully
after 5 days.
however the TRN files are still not being removed, I get the following
errors in my log file.
Backup can not be performed on database 'master'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
SQLMAINT.EXE Process Exit Code: 1 (Failed)
On different days I seem to get different dbs showing the same type of error
Backup can not be performed on database 'msdb'. This sub task is ignored.
Backup can not be performed on database 'pubs'. This sub task is ignored.
Any suggestions?|||You really need two separate plans. One for the databases for which you intend to do log backups,
and one for the databases that you don't intent to do log backups.
You cannot do log backups on master regardless of recovery model.
Agent will set msdb to simple recovery on startup.
Why do you backup pubs?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:97F75DCF-B984-4644-BD93-DE4798C81B4B@.microsoft.com...
> Hey guys,
> sorry for the late response I made some changes and wanted to wait a gew
> days to see the results.
> I changed all dbs to full recovery mode this seemed to fix the problem with
> the BAK files and now they are getting backed up and removed successfully
> after 5 days.
> however the TRN files are still not being removed, I get the following
> errors in my log file.
> Backup can not be performed on database 'master'. This sub task is ignored.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> On different days I seem to get different dbs showing the same type of error
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Backup can not be performed on database 'pubs'. This sub task is ignored.
> Any suggestions?
>|||Hi Tibor,
Im not quite sure I understand, I thought it was good practice to backup and
log all database's?
Am I right now in thinking that I should not backup or log the system
databases
Master, model, msdb, pubs, tempdb ?
Does my initial plan look ok, if I remove the system databases and just have
it for our business database's?
Thanks|||You should always do a FULL backup on Master, Model & MSDB but there is no
need for Log backups since they are either in Simple mode to begin with or
in the case of Model there are no changes made. Tempdb is rebuilt from
scratch each time you start up SQL Server so there is no need to back it up.
--
Andrew J. Kelly SQL MVP
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
> Hi Tibor,
> Im not quite sure I understand, I thought it was good practice to backup
> and
> log all database's?
> Am I right now in thinking that I should not backup or log the system
> databases
> Master, model, msdb, pubs, tempdb ?
> Does my initial plan look ok, if I remove the system databases and just
> have
> it for our business database's?
> Thanks
>|||Thanks Andrew,
I have created a third Maintenance plan to back up the system dbs, Master,
Msdb and model.
I have also changed the Initial Plan to backup all user databases, I will
check this tomorrow and see the results.
"Andrew J. Kelly" wrote:
> You should always do a FULL backup on Master, Model & MSDB but there is no
> need for Log backups since they are either in Simple mode to begin with or
> in the case of Model there are no changes made. Tempdb is rebuilt from
> scratch each time you start up SQL Server so there is no need to back it up.
> --
> Andrew J. Kelly SQL MVP
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
> > Hi Tibor,
> >
> > Im not quite sure I understand, I thought it was good practice to backup
> > and
> > log all database's?
> >
> > Am I right now in thinking that I should not backup or log the system
> > databases
> > Master, model, msdb, pubs, tempdb ?
> >
> > Does my initial plan look ok, if I remove the system databases and just
> > have
> > it for our business database's?
> >
> > Thanks
> >
>
>|||Adrian wrote:
> Thanks Andrew,
> I have created a third Maintenance plan to back up the system dbs, Master,
> Msdb and model.
> I have also changed the Initial Plan to backup all user databases, I will
> check this tomorrow and see the results.
>
Sounds like you have a solution, but if you decide that managing three
seperate maintenance plans is too much work, have a look at this script
of mine:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Mainteneance plans BAK TRN

Hi All,
Ive created a maintenance plan which does the following (all in one plan)
* All Databases
* Reorganise data,
Change free space per page percentage to 10%
Remove unused space from database files
Shrink database when it grows beyond 50 Mb
Amound of free space to remain after shrink 10%
*Check database integrity
include indexes
*Back up the database as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension BAK
*Back up the Transaction log as part of the maintenance plan
Verify the integrity of the database upon completion
Backup to Disk
Use this Directory
Remove files older than 5 days
Backup file extension TRN
*Write a report file in the directory
delete report files after 14 days
We also have one other plan which simply checks the integrity on all DBs
once a week. Does this solution / plan seem ok? Its the first one I have
created so Im not 100% on it.
Also what rights/user does the plan run under?
I find that sometimes it deletes the files over 5 days and then sometimes it
just doesnt, or it will delete the TRN files but not the BAK files or vice
versa. I cant really make sense of it. Any ideas?
Also would I be better off having seperate plans for removing the BAK and
the TRN files?
Any help would be appreciated.
Thank youAdrin,
If you are using Sql Server 2000, then check your script and please
specify any pattern that you see in not removing old files.
You should be able to remove old files without any issues.
In Sql 2005 removing old files from sub-directory was not possible.
Even that is fixed after applying SP1.
Hope this helps,
Sameer Raval
[DBA-Developer]
Augusta,GA,USA
"Adrian" wrote:

> Hi All,
> Ive created a maintenance plan which does the following (all in one plan)
> * All Databases
> * Reorganise data,
> Change free space per page percentage to 10%
> Remove unused space from database files
> Shrink database when it grows beyond 50 Mb
> Amound of free space to remain after shrink 10%
> *Check database integrity
> include indexes
> *Back up the database as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension BAK
> *Back up the Transaction log as part of the maintenance plan
> Verify the integrity of the database upon completion
> Backup to Disk
> Use this Directory
> Remove files older than 5 days
> Backup file extension TRN
> *Write a report file in the directory
> delete report files after 14 days
> We also have one other plan which simply checks the integrity on all DBs
> once a week. Does this solution / plan seem ok? Its the first one I have
> created so Im not 100% on it.
> Also what rights/user does the plan run under?
> I find that sometimes it deletes the files over 5 days and then sometimes
it
> just doesnt, or it will delete the TRN files but not the BAK files or vice
> versa. I cant really make sense of it. Any ideas?
> Also would I be better off having seperate plans for removing the BAK and
> the TRN files?
> Any help would be appreciated.
> Thank you|||In addition, I've seen a plan stopping execution if something goes wrong. Th
is can lead to old
backup files not being removed. Read the report file carefully. Also, don't
have databases in simple
recovery mode in a plan where you do log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Raval" <SameerRaval@.discussions.microsoft.com> wrote in message
news:ED2CC0C2-6E36-4AA7-A6B4-8BD0AFD05934@.microsoft.com...[vbcol=seagreen]
> Adrin,
> If you are using Sql Server 2000, then check your script and please
> specify any pattern that you see in not removing old files.
> You should be able to remove old files without any issues.
> In Sql 2005 removing old files from sub-directory was not possible.
> Even that is fixed after applying SP1.
> Hope this helps,
>
> Sameer Raval
> [DBA-Developer]
> Augusta,GA,USA
>
> "Adrian" wrote:
>|||Hey guys,
sorry for the late response I made some changes and wanted to wait a gew
days to see the results.
I changed all dbs to full recovery mode this seemed to fix the problem with
the BAK files and now they are getting backed up and removed successfully
after 5 days.
however the TRN files are still not being removed, I get the following
errors in my log file.
Backup can not be performed on database 'master'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
SQLMAINT.EXE Process Exit Code: 1 (Failed)
On different days I seem to get different dbs showing the same type of error
Backup can not be performed on database 'msdb'. This sub task is ignored.
Backup can not be performed on database 'pubs'. This sub task is ignored.
Any suggestions?|||You really need two separate plans. One for the databases for which you inte
nd to do log backups,
and one for the databases that you don't intent to do log backups.
You cannot do log backups on master regardless of recovery model.
Agent will set msdb to simple recovery on startup.
Why do you backup pubs?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:97F75DCF-B984-4644-BD93-DE4798C81B4B@.microsoft.com...
> Hey guys,
> sorry for the late response I made some changes and wanted to wait a gew
> days to see the results.
> I changed all dbs to full recovery mode this seemed to fix the problem wit
h
> the BAK files and now they are getting backed up and removed successfully
> after 5 days.
> however the TRN files are still not being removed, I get the following
> errors in my log file.
> Backup can not be performed on database 'master'. This sub task is ignored
.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Backup All DBs' on 01/08/2006 00:00:44
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> On different days I seem to get different dbs showing the same type of err
or
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Backup can not be performed on database 'pubs'. This sub task is ignored.
> Any suggestions?
>|||Hi Tibor,
Im not quite sure I understand, I thought it was good practice to backup and
log all database's?
Am I right now in thinking that I should not backup or log the system
databases
Master, model, msdb, pubs, tempdb ?
Does my initial plan look ok, if I remove the system databases and just have
it for our business database's?
Thanks|||You should always do a FULL backup on Master, Model & MSDB but there is no
need for Log backups since they are either in Simple mode to begin with or
in the case of Model there are no changes made. Tempdb is rebuilt from
scratch each time you start up SQL Server so there is no need to back it up.
Andrew J. Kelly SQL MVP
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
> Hi Tibor,
> Im not quite sure I understand, I thought it was good practice to backup
> and
> log all database's?
> Am I right now in thinking that I should not backup or log the system
> databases
> Master, model, msdb, pubs, tempdb ?
> Does my initial plan look ok, if I remove the system databases and just
> have
> it for our business database's?
> Thanks
>|||Thanks Andrew,
I have created a third Maintenance plan to back up the system dbs, Master,
Msdb and model.
I have also changed the Initial Plan to backup all user databases, I will
check this tomorrow and see the results.
"Andrew J. Kelly" wrote:

> You should always do a FULL backup on Master, Model & MSDB but there is no
> need for Log backups since they are either in Simple mode to begin with or
> in the case of Model there are no changes made. Tempdb is rebuilt from
> scratch each time you start up SQL Server so there is no need to back it u
p.
> --
> Andrew J. Kelly SQL MVP
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:12366730-7681-43DB-80BE-A838D1188209@.microsoft.com...
>
>|||Adrian wrote:
> Thanks Andrew,
> I have created a third Maintenance plan to back up the system dbs, Master,
> Msdb and model.
> I have also changed the Initial Plan to backup all user databases, I will
> check this tomorrow and see the results.
>
Sounds like you have a solution, but if you decide that managing three
seperate maintenance plans is too much work, have a look at this script
of mine:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com

Maintenance, indexes, fragmentation & file groups

I am trying to get my head around many of the issues related to
maintaining some of our larger databases.
Our largest database is roughly 10 GB. Our largest table in the
database has 15,783,725 rows with a size of 9.8 GB and an index size of
2.8 GB according to the taskpad view. It has one clustered index and
13 indexes. The clustered index has a prefix of PK_*** Four of the
indexes have a prefix of IX_*** and the remaining nine indexes have a
prefix of _WA_Sys_***.
I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
First question, my current rough idea for our new maintenance plan
looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backupdb
3) shrinkfile db
4) backup tlog
5) shrinkfile tlog
6) file system defrag
7) reindex (not an indexdefrag)
I keep reading that shrinking the database files is wasted I/O but, we
are renting SAN space, so we need to be as efficient as possible with
disk space. We plan to shrink the db before the tlog backup so that we
can reclaim the tlog space created during shrinking the db.
I have read that putting your indexes into separate filegroups can
reduce fragmentation and thus improve performance. Given the size of
our indexes, this seems to make sense to me. Should I move all
indexes, clustered and nonclustered to the separate filegroup?
Also, someone previous to me had created a second db file for this
database, but they are both in the same file group. Should I leave
that as is, or do I need to do something about the second file?
Is there anything I am missing? I think I have caught all the main
points for performing regular maintenance. I have already collected,
created and modified a set of stored procs to do most of these tasks,
so it will just be a matter of scripting a few jobs.
Thank you,
KevinSee in-Line:
--
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140623640.392540.197050@.g43g2000cwa.googlegroups.com...
>I am trying to get my head around many of the issues related to
> maintaining some of our larger databases.
> Our largest database is roughly 10 GB. Our largest table in the
> database has 15,783,725 rows with a size of 9.8 GB and an index size of
> 2.8 GB according to the taskpad view. It has one clustered index and
> 13 indexes. The clustered index has a prefix of PK_*** Four of the
> indexes have a prefix of IX_*** and the remaining nine indexes have a
> prefix of _WA_Sys_***.
_WA... means that these are statistics and not actual indexes. You can
ignore them for all practical purposes.
> I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
> First question, my current rough idea for our new maintenance plan
> looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backupdb
> 3) shrinkfile db
> 4) backup tlog
> 5) shrinkfile tlog
> 6) file system defrag
> 7) reindex (not an indexdefrag)
No need for shrink as I will explain below. You should not have to defrag
the file system if you don't continuously grow and shrink.
> I keep reading that shrinking the database files is wasted I/O but, we
> are renting SAN space, so we need to be as efficient as possible with
> disk space. We plan to shrink the db before the tlog backup so that we
> can reclaim the tlog space created during shrinking the db.
If the file grows again after it is shrunk that is a PURE indication that it
needs the space at one time or another. This is especially true when you
reindex. You must have free space in the files in order for the db to
operate properly. Shrinking just prolongs the inevitable and will hurt
overall performance. Find the size in which it no longer grows and leave it
there because that is what it requires. There is no getting around this so
accept it and move on.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> I have read that putting your indexes into separate filegroups can
> reduce fragmentation and thus improve performance. Given the size of
> our indexes, this seems to make sense to me. Should I move all
> indexes, clustered and nonclustered to the separate filegroup?
It's probably not worth the trouble. Make sure you have a proper fill
factor to reduce fragmentation and page splits.
> Also, someone previous to me had created a second db file for this
> database, but they are both in the same file group. Should I leave
> that as is, or do I need to do something about the second file?
Having more than one file is often a good thing just make sure they are both
the same size when you create them.
> Is there anything I am missing? I think I have caught all the main
> points for performing regular maintenance. I have already collected,
> created and modified a set of stored procs to do most of these tasks,
> so it will just be a matter of scripting a few jobs.
There is usually no need to rebuild indexes every night. Look in
BooksOnLine under DBCC SHOWCONTIG for a script to reindex only the indexes
that actually are fragmented beyond a certain amount.
> Thank you,
> Kevin
>|||OK, I have been sold on the no shrinking. We were originally concerned
because both our db and tlog have been as much as 50% larger than what
is currently in our db and tlog. For example, our two files are broken
down like this:
Size In Use Free
--
2.7GB 1.5GB 1.2GB
10.4GB 5.5GB 4.9GB
If I am understanding reindexing properly and we have a 2.9 GB index on
one table, then it will use much of that free space during reindexing.
Our updated maintenance plan looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backup db
3) backup tlog
4) file system defrag
5) reindex (not an indexdefrag, using a stored proc >20% or so)
One note about our database usage. Our database has relatively few
writes and new rows being added on a daily basis. This database is
primarily used for many SELECT's and our primary table is usually being
joined to other tables in these SELECT's.
I don't know if it is possible to physically order out data so that
common rows are contiguous within the files. Our data is generally
time dependant and tied to different organizations. So a SELECT
statement will generally read all rows that are within the last four
months and belong to organization x.
So I think the goal of fragmentation in our case is to keep the index
contiguous to make reads of the index faster and more efficient. I
think I am understanding this correctly.
As noted above, our two files are quite a bit different in size. Do I
need to do something to correct this?
I have to read up more on fill factors. I don't understand them well
enough yet to understand their impact on fragmentation. Any tips while
I research this more thouroughly?
Thank you,
Kevin|||If you use DBCC DBREINDEX and just specify the table name it will rebuild
ALL indexes on that table in one big transaction. You will need at least 1.2
times the total size of all the indexes including the clustered index. I
assume you have a clustered index and if not you should. The differences in
the size of the file is due to the fact the second file was not made the
same as the first. SQL Server splits the data evenly across multiple files
in the same file group if they are of the same size and have the same amount
of free space. In reality it is the amount of free space in each file that
determines how much data is placed in each file. If you have twice as much
free space in one file then the other it will put twice as much data in the
one that has more free space and so on. The goal is to create all the files
the same size to begin with and then data gets evenly distributed across
them from the beginning. In your case if you make them both say 7GB and run
DBCC DBREINDEX a few times they should even out. Then you can see how much
free space is left and shrink slightly if needed. Back to the clustered
index. In general every table should have one. They are the only things
that help to control fragmentation in the table itself. But choosing the
right column(s) for the clustered index is not always so straight forward.
They are great for times when you always search in ranges or on values that
have a lot of repeating rows. In your case the datetime column or the
organization may be good choices. These should help.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140637582.856303.277700@.g14g2000cwa.googlegroups.com...
> OK, I have been sold on the no shrinking. We were originally concerned
> because both our db and tlog have been as much as 50% larger than what
> is currently in our db and tlog. For example, our two files are broken
> down like this:
> Size In Use Free
> --
> 2.7GB 1.5GB 1.2GB
> 10.4GB 5.5GB 4.9GB
> If I am understanding reindexing properly and we have a 2.9 GB index on
> one table, then it will use much of that free space during reindexing.
> Our updated maintenance plan looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backup db
> 3) backup tlog
> 4) file system defrag
> 5) reindex (not an indexdefrag, using a stored proc >20% or so)
> One note about our database usage. Our database has relatively few
> writes and new rows being added on a daily basis. This database is
> primarily used for many SELECT's and our primary table is usually being
> joined to other tables in these SELECT's.
> I don't know if it is possible to physically order out data so that
> common rows are contiguous within the files. Our data is generally
> time dependant and tied to different organizations. So a SELECT
> statement will generally read all rows that are within the last four
> months and belong to organization x.
> So I think the goal of fragmentation in our case is to keep the index
> contiguous to make reads of the index faster and more efficient. I
> think I am understanding this correctly.
> As noted above, our two files are quite a bit different in size. Do I
> need to do something to correct this?
> I have to read up more on fill factors. I don't understand them well
> enough yet to understand their impact on fragmentation. Any tips while
> I research this more thouroughly?
> Thank you,
> Kevin
>|||kghammond a écrit :
[...]
> 6) file system defrag
Never do that !
In fact impossible on data and log files, while the database is hot.
But the OS defrag does not did a optimized defrag, just a logical one.
If you created your db with large fixed files, the SQL Server OS try to
find the tracks on the differents plates of the disk wich must reduce
the moving of the head on the disk surface. So the cluster wont be
logically continuous.
Il you do a OS defrag you will broke this optimization.
A +
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I don't agree with that at all. First off you can in fact do on-line defrag
at the OS level with tools like DiskKeeper. While I never recommend that
when it can be avoided it is in fact possible. As far as where sql server
looks for the data on disk that too is not true. SQL Server has absolutely
no control over the actual fetching of the data in terms of moving the heads
etc. It simply makes a call to the OS and requests a certain piece of data
but it is the responsibility of the I/O driver and the storage foundation to
do the work as it sees fit. SQL Server knows of coarse that a particular
page lives in a particular file in a particular slot. But that is as far as
it goes.
--
Andrew J. Kelly SQL MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:uf%23mgMFOGHA.1360@.TK2MSFTNGP10.phx.gbl...
> kghammond a écrit :
> [...]
>> 6) file system defrag
> Never do that !
> In fact impossible on data and log files, while the database is hot.
> But the OS defrag does not did a optimized defrag, just a logical one.
> If you created your db with large fixed files, the SQL Server OS try to
> find the tracks on the differents plates of the disk wich must reduce the
> moving of the head on the disk surface. So the cluster wont be logically
> continuous.
> Il you do a OS defrag you will broke this optimization.
> A +
> A +
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

Maintenance, indexes, fragmentation & file groups

I am trying to get my head around many of the issues related to
maintaining some of our larger databases.
Our largest database is roughly 10 GB. Our largest table in the
database has 15,783,725 rows with a size of 9.8 GB and an index size of
2.8 GB according to the taskpad view. It has one clustered index and
13 indexes. The clustered index has a prefix of PK_*** Four of the
indexes have a prefix of IX_*** and the remaining nine indexes have a
prefix of _WA_Sys_***.
I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
First question, my current rough idea for our new maintenance plan
looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backupdb
3) shrinkfile db
4) backup tlog
5) shrinkfile tlog
6) file system defrag
7) reindex (not an indexdefrag)
I keep reading that shrinking the database files is wasted I/O but, we
are renting SAN space, so we need to be as efficient as possible with
disk space. We plan to shrink the db before the tlog backup so that we
can reclaim the tlog space created during shrinking the db.
I have read that putting your indexes into separate filegroups can
reduce fragmentation and thus improve performance. Given the size of
our indexes, this seems to make sense to me. Should I move all
indexes, clustered and nonclustered to the separate filegroup?
Also, someone previous to me had created a second db file for this
database, but they are both in the same file group. Should I leave
that as is, or do I need to do something about the second file?
Is there anything I am missing? I think I have caught all the main
points for performing regular maintenance. I have already collected,
created and modified a set of stored procs to do most of these tasks,
so it will just be a matter of scripting a few jobs.
Thank you,
KevinSee in-Line:
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140623640.392540.197050@.g43g2000cwa.googlegroups.com...
>I am trying to get my head around many of the issues related to
> maintaining some of our larger databases.
> Our largest database is roughly 10 GB. Our largest table in the
> database has 15,783,725 rows with a size of 9.8 GB and an index size of
> 2.8 GB according to the taskpad view. It has one clustered index and
> 13 indexes. The clustered index has a prefix of PK_*** Four of the
> indexes have a prefix of IX_*** and the remaining nine indexes have a
> prefix of _WA_Sys_***.
_WA... means that these are statistics and not actual indexes. You can
ignore them for all practical purposes.

> I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
> First question, my current rough idea for our new maintenance plan
> looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backupdb
> 3) shrinkfile db
> 4) backup tlog
> 5) shrinkfile tlog
> 6) file system defrag
> 7) reindex (not an indexdefrag)
No need for shrink as I will explain below. You should not have to defrag
the file system if you don't continuously grow and shrink.

> I keep reading that shrinking the database files is wasted I/O but, we
> are renting SAN space, so we need to be as efficient as possible with
> disk space. We plan to shrink the db before the tlog backup so that we
> can reclaim the tlog space created during shrinking the db.
If the file grows again after it is shrunk that is a PURE indication that it
needs the space at one time or another. This is especially true when you
reindex. You must have free space in the files in order for the db to
operate properly. Shrinking just prolongs the inevitable and will hurt
overall performance. Find the size in which it no longer grows and leave it
there because that is what it requires. There is no getting around this so
accept it and move on.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> I have read that putting your indexes into separate filegroups can
> reduce fragmentation and thus improve performance. Given the size of
> our indexes, this seems to make sense to me. Should I move all
> indexes, clustered and nonclustered to the separate filegroup?
It's probably not worth the trouble. Make sure you have a proper fill
factor to reduce fragmentation and page splits.

> Also, someone previous to me had created a second db file for this
> database, but they are both in the same file group. Should I leave
> that as is, or do I need to do something about the second file?
Having more than one file is often a good thing just make sure they are both
the same size when you create them.

> Is there anything I am missing? I think I have caught all the main
> points for performing regular maintenance. I have already collected,
> created and modified a set of stored procs to do most of these tasks,
> so it will just be a matter of scripting a few jobs.
There is usually no need to rebuild indexes every night. Look in
BooksOnLine under DBCC SHOWCONTIG for a script to reindex only the indexes
that actually are fragmented beyond a certain amount.

> Thank you,
> Kevin
>|||OK, I have been sold on the no shrinking. We were originally concerned
because both our db and tlog have been as much as 50% larger than what
is currently in our db and tlog. For example, our two files are broken
down like this:
Size In Use Free
--
2.7GB 1.5GB 1.2GB
10.4GB 5.5GB 4.9GB
If I am understanding reindexing properly and we have a 2.9 GB index on
one table, then it will use much of that free space during reindexing.
Our updated maintenance plan looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backup db
3) backup tlog
4) file system defrag
5) reindex (not an indexdefrag, using a stored proc >20% or so)
One note about our database usage. Our database has relatively few
writes and new rows being added on a daily basis. This database is
primarily used for many SELECT's and our primary table is usually being
joined to other tables in these SELECT's.
I don't know if it is possible to physically order out data so that
common rows are contiguous within the files. Our data is generally
time dependant and tied to different organizations. So a SELECT
statement will generally read all rows that are within the last four
months and belong to organization x.
So I think the goal of fragmentation in our case is to keep the index
contiguous to make reads of the index faster and more efficient. I
think I am understanding this correctly.
As noted above, our two files are quite a bit different in size. Do I
need to do something to correct this?
I have to read up more on fill factors. I don't understand them well
enough yet to understand their impact on fragmentation. Any tips while
I research this more thouroughly?
Thank you,
Kevin|||If you use DBCC DBREINDEX and just specify the table name it will rebuild
ALL indexes on that table in one big transaction. You will need at least 1.2
times the total size of all the indexes including the clustered index. I
assume you have a clustered index and if not you should. The differences in
the size of the file is due to the fact the second file was not made the
same as the first. SQL Server splits the data evenly across multiple files
in the same file group if they are of the same size and have the same amount
of free space. In reality it is the amount of free space in each file that
determines how much data is placed in each file. If you have twice as much
free space in one file then the other it will put twice as much data in the
one that has more free space and so on. The goal is to create all the files
the same size to begin with and then data gets evenly distributed across
them from the beginning. In your case if you make them both say 7GB and run
DBCC DBREINDEX a few times they should even out. Then you can see how much
free space is left and shrink slightly if needed. Back to the clustered
index. In general every table should have one. They are the only things
that help to control fragmentation in the table itself. But choosing the
right column(s) for the clustered index is not always so straight forward.
They are great for times when you always search in ranges or on values that
have a lot of repeating rows. In your case the datetime column or the
organization may be good choices. These should help.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140637582.856303.277700@.g14g2000cwa.googlegroups.com...
> OK, I have been sold on the no shrinking. We were originally concerned
> because both our db and tlog have been as much as 50% larger than what
> is currently in our db and tlog. For example, our two files are broken
> down like this:
> Size In Use Free
> --
> 2.7GB 1.5GB 1.2GB
> 10.4GB 5.5GB 4.9GB
> If I am understanding reindexing properly and we have a 2.9 GB index on
> one table, then it will use much of that free space during reindexing.
> Our updated maintenance plan looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backup db
> 3) backup tlog
> 4) file system defrag
> 5) reindex (not an indexdefrag, using a stored proc >20% or so)
> One note about our database usage. Our database has relatively few
> writes and new rows being added on a daily basis. This database is
> primarily used for many SELECT's and our primary table is usually being
> joined to other tables in these SELECT's.
> I don't know if it is possible to physically order out data so that
> common rows are contiguous within the files. Our data is generally
> time dependant and tied to different organizations. So a SELECT
> statement will generally read all rows that are within the last four
> months and belong to organization x.
> So I think the goal of fragmentation in our case is to keep the index
> contiguous to make reads of the index faster and more efficient. I
> think I am understanding this correctly.
> As noted above, our two files are quite a bit different in size. Do I
> need to do something to correct this?
> I have to read up more on fill factors. I don't understand them well
> enough yet to understand their impact on fragmentation. Any tips while
> I research this more thouroughly?
> Thank you,
> Kevin
>|||kghammond a crit :
[...]
> 6) file system defrag
Never do that !
In fact impossible on data and log files, while the database is hot.
But the OS defrag does not did a optimized defrag, just a logical one.
If you created your db with large fixed files, the SQL Server OS try to
find the tracks on the differents plates of the disk wich must reduce
the moving of the head on the disk surface. So the cluster wont be
logically continuous.
Il you do a OS defrag you will broke this optimization.
A +
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I don't agree with that at all. First off you can in fact do on-line defrag
at the OS level with tools like DiskKeeper. While I never recommend that
when it can be avoided it is in fact possible. As far as where sql server
looks for the data on disk that too is not true. SQL Server has absolutely
no control over the actual fetching of the data in terms of moving the heads
etc. It simply makes a call to the OS and requests a certain piece of data
but it is the responsibility of the I/O driver and the storage foundation to
do the work as it sees fit. SQL Server knows of coarse that a particular
page lives in a particular file in a particular slot. But that is as far as
it goes.
Andrew J. Kelly SQL MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:uf%23mgMFOGHA.1360@.TK2MSFTNGP10.phx.gbl...
> kghammond a crit :
> [...]
> Never do that !
> In fact impossible on data and log files, while the database is hot.
> But the OS defrag does not did a optimized defrag, just a logical one.
> If you created your db with large fixed files, the SQL Server OS try to
> find the tracks on the differents plates of the disk wich must reduce the
> moving of the head on the disk surface. So the cluster wont be logically
> continuous.
> Il you do a OS defrag you will broke this optimization.
> A +
> A +
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

Maintenance, indexes, fragmentation & file groups

I am trying to get my head around many of the issues related to
maintaining some of our larger databases.
Our largest database is roughly 10 GB. Our largest table in the
database has 15,783,725 rows with a size of 9.8 GB and an index size of
2.8 GB according to the taskpad view. It has one clustered index and
13 indexes. The clustered index has a prefix of PK_*** Four of the
indexes have a prefix of IX_*** and the remaining nine indexes have a
prefix of _WA_Sys_***.
I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
First question, my current rough idea for our new maintenance plan
looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backupdb
3) shrinkfile db
4) backup tlog
5) shrinkfile tlog
6) file system defrag
7) reindex (not an indexdefrag)
I keep reading that shrinking the database files is wasted I/O but, we
are renting SAN space, so we need to be as efficient as possible with
disk space. We plan to shrink the db before the tlog backup so that we
can reclaim the tlog space created during shrinking the db.
I have read that putting your indexes into separate filegroups can
reduce fragmentation and thus improve performance. Given the size of
our indexes, this seems to make sense to me. Should I move all
indexes, clustered and nonclustered to the separate filegroup?
Also, someone previous to me had created a second db file for this
database, but they are both in the same file group. Should I leave
that as is, or do I need to do something about the second file?
Is there anything I am missing? I think I have caught all the main
points for performing regular maintenance. I have already collected,
created and modified a set of stored procs to do most of these tasks,
so it will just be a matter of scripting a few jobs.
Thank you,
Kevin
See in-Line:
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140623640.392540.197050@.g43g2000cwa.googlegr oups.com...
>I am trying to get my head around many of the issues related to
> maintaining some of our larger databases.
> Our largest database is roughly 10 GB. Our largest table in the
> database has 15,783,725 rows with a size of 9.8 GB and an index size of
> 2.8 GB according to the taskpad view. It has one clustered index and
> 13 indexes. The clustered index has a prefix of PK_*** Four of the
> indexes have a prefix of IX_*** and the remaining nine indexes have a
> prefix of _WA_Sys_***.
_WA... means that these are statistics and not actual indexes. You can
ignore them for all practical purposes.

> I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
> First question, my current rough idea for our new maintenance plan
> looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backupdb
> 3) shrinkfile db
> 4) backup tlog
> 5) shrinkfile tlog
> 6) file system defrag
> 7) reindex (not an indexdefrag)
No need for shrink as I will explain below. You should not have to defrag
the file system if you don't continuously grow and shrink.

> I keep reading that shrinking the database files is wasted I/O but, we
> are renting SAN space, so we need to be as efficient as possible with
> disk space. We plan to shrink the db before the tlog backup so that we
> can reclaim the tlog space created during shrinking the db.
If the file grows again after it is shrunk that is a PURE indication that it
needs the space at one time or another. This is especially true when you
reindex. You must have free space in the files in order for the db to
operate properly. Shrinking just prolongs the inevitable and will hurt
overall performance. Find the size in which it no longer grows and leave it
there because that is what it requires. There is no getting around this so
accept it and move on.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> I have read that putting your indexes into separate filegroups can
> reduce fragmentation and thus improve performance. Given the size of
> our indexes, this seems to make sense to me. Should I move all
> indexes, clustered and nonclustered to the separate filegroup?
It's probably not worth the trouble. Make sure you have a proper fill
factor to reduce fragmentation and page splits.

> Also, someone previous to me had created a second db file for this
> database, but they are both in the same file group. Should I leave
> that as is, or do I need to do something about the second file?
Having more than one file is often a good thing just make sure they are both
the same size when you create them.

> Is there anything I am missing? I think I have caught all the main
> points for performing regular maintenance. I have already collected,
> created and modified a set of stored procs to do most of these tasks,
> so it will just be a matter of scripting a few jobs.
There is usually no need to rebuild indexes every night. Look in
BooksOnLine under DBCC SHOWCONTIG for a script to reindex only the indexes
that actually are fragmented beyond a certain amount.

> Thank you,
> Kevin
>
|||OK, I have been sold on the no shrinking. We were originally concerned
because both our db and tlog have been as much as 50% larger than what
is currently in our db and tlog. For example, our two files are broken
down like this:
Size In Use Free
2.7GB 1.5GB 1.2GB
10.4GB 5.5GB 4.9GB
If I am understanding reindexing properly and we have a 2.9 GB index on
one table, then it will use much of that free space during reindexing.
Our updated maintenance plan looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backup db
3) backup tlog
4) file system defrag
5) reindex (not an indexdefrag, using a stored proc >20% or so)
One note about our database usage. Our database has relatively few
writes and new rows being added on a daily basis. This database is
primarily used for many SELECT's and our primary table is usually being
joined to other tables in these SELECT's.
I don't know if it is possible to physically order out data so that
common rows are contiguous within the files. Our data is generally
time dependant and tied to different organizations. So a SELECT
statement will generally read all rows that are within the last four
months and belong to organization x.
So I think the goal of fragmentation in our case is to keep the index
contiguous to make reads of the index faster and more efficient. I
think I am understanding this correctly.
As noted above, our two files are quite a bit different in size. Do I
need to do something to correct this?
I have to read up more on fill factors. I don't understand them well
enough yet to understand their impact on fragmentation. Any tips while
I research this more thouroughly?
Thank you,
Kevin
|||If you use DBCC DBREINDEX and just specify the table name it will rebuild
ALL indexes on that table in one big transaction. You will need at least 1.2
times the total size of all the indexes including the clustered index. I
assume you have a clustered index and if not you should. The differences in
the size of the file is due to the fact the second file was not made the
same as the first. SQL Server splits the data evenly across multiple files
in the same file group if they are of the same size and have the same amount
of free space. In reality it is the amount of free space in each file that
determines how much data is placed in each file. If you have twice as much
free space in one file then the other it will put twice as much data in the
one that has more free space and so on. The goal is to create all the files
the same size to begin with and then data gets evenly distributed across
them from the beginning. In your case if you make them both say 7GB and run
DBCC DBREINDEX a few times they should even out. Then you can see how much
free space is left and shrink slightly if needed. Back to the clustered
index. In general every table should have one. They are the only things
that help to control fragmentation in the table itself. But choosing the
right column(s) for the clustered index is not always so straight forward.
They are great for times when you always search in ranges or on values that
have a lot of repeating rows. In your case the datetime column or the
organization may be good choices. These should help.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.co...showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/colu...illfactors.asp
Fill Factors
http://www.sql-server-performance.co...ed_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140637582.856303.277700@.g14g2000cwa.googlegr oups.com...
> OK, I have been sold on the no shrinking. We were originally concerned
> because both our db and tlog have been as much as 50% larger than what
> is currently in our db and tlog. For example, our two files are broken
> down like this:
> Size In Use Free
> --
> 2.7GB 1.5GB 1.2GB
> 10.4GB 5.5GB 4.9GB
> If I am understanding reindexing properly and we have a 2.9 GB index on
> one table, then it will use much of that free space during reindexing.
> Our updated maintenance plan looks like this:
> Daily:
> 1) checkdb
> 2) backup db
> 3) backup tlog (tlog backups will run throughout the day as well)
> Weekly:
> 1) checkdb
> 2) backup db
> 3) backup tlog
> 4) file system defrag
> 5) reindex (not an indexdefrag, using a stored proc >20% or so)
> One note about our database usage. Our database has relatively few
> writes and new rows being added on a daily basis. This database is
> primarily used for many SELECT's and our primary table is usually being
> joined to other tables in these SELECT's.
> I don't know if it is possible to physically order out data so that
> common rows are contiguous within the files. Our data is generally
> time dependant and tied to different organizations. So a SELECT
> statement will generally read all rows that are within the last four
> months and belong to organization x.
> So I think the goal of fragmentation in our case is to keep the index
> contiguous to make reads of the index faster and more efficient. I
> think I am understanding this correctly.
> As noted above, our two files are quite a bit different in size. Do I
> need to do something to correct this?
> I have to read up more on fill factors. I don't understand them well
> enough yet to understand their impact on fragmentation. Any tips while
> I research this more thouroughly?
> Thank you,
> Kevin
>
|||kghammond a crit :
[...]
> 6) file system defrag
Never do that !
In fact impossible on data and log files, while the database is hot.
But the OS defrag does not did a optimized defrag, just a logical one.
If you created your db with large fixed files, the SQL Server OS try to
find the tracks on the differents plates of the disk wich must reduce
the moving of the head on the disk surface. So the cluster wont be
logically continuous.
Il you do a OS defrag you will broke this optimization.
A +
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||I don't agree with that at all. First off you can in fact do on-line defrag
at the OS level with tools like DiskKeeper. While I never recommend that
when it can be avoided it is in fact possible. As far as where sql server
looks for the data on disk that too is not true. SQL Server has absolutely
no control over the actual fetching of the data in terms of moving the heads
etc. It simply makes a call to the OS and requests a certain piece of data
but it is the responsibility of the I/O driver and the storage foundation to
do the work as it sees fit. SQL Server knows of coarse that a particular
page lives in a particular file in a particular slot. But that is as far as
it goes.
Andrew J. Kelly SQL MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:uf%23mgMFOGHA.1360@.TK2MSFTNGP10.phx.gbl...
> kghammond a crit :
> [...]
> Never do that !
> In fact impossible on data and log files, while the database is hot.
> But the OS defrag does not did a optimized defrag, just a logical one.
> If you created your db with large fixed files, the SQL Server OS try to
> find the tracks on the differents plates of the disk wich must reduce the
> moving of the head on the disk surface. So the cluster wont be logically
> continuous.
> Il you do a OS defrag you will broke this optimization.
> A +
> A +
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

Maintenance Wizard

I'm trying to backup several databases to UNC folders. I'm able to do it
manually for each database, but when I set up the maintenance plan it fails
because SQL tries to use the SQL administrator account to push the data.

The million dollar question is how does one change which account is running
the maintenance?"GTM" <gmckaigeATworkerstempDOTcom> wrote in message
news:gJScnQFltqR2TcmiXTWJkw@.fdn.com...
> I'm trying to backup several databases to UNC folders. I'm able to do it
> manually for each database, but when I set up the maintenance plan it
fails
> because SQL tries to use the SQL administrator account to push the data.
>
> The million dollar question is how does one change which account is
running
> the maintenance?

Change the account the SQL Server Agent runs under to a domain account.
|||Hi

Gregs advice is probably the root cause of your problem if the account is
set to local system.

If it is already a local/domain account the the permissions to the
shares/directories/trusts etc... need to be in place.

John

"GTM" <gmckaigeATworkerstempDOTcom> wrote in message
news:gJScnQFltqR2TcmiXTWJkw@.fdn.com...
> I'm trying to backup several databases to UNC folders. I'm able to do it
> manually for each database, but when I set up the maintenance plan it
fails
> because SQL tries to use the SQL administrator account to push the data.
>
> The million dollar question is how does one change which account is
running
> the maintenance?

Maintenance Plans Have "Disappeared"

Help!!
My maintenance plans have disapeared from View, although
according to the Databases that use the Plans they still
exist..
To test this I created a quick Maintenance Plan and sure
enough it created but it cannot be seen!!
Does anybody have any ideas or should I call the
Ghostbusters?
I am using SQL Server V7.0, using Enterpise Manager on my
PC to Administer the Server.
TIA
Tony C.
Tony,
Maybe the tasks have been created under say the SA account, but you're
connected and browsing the server under a different account that does not
have permissions to view those tasks.?
Immy
"Tony C" <anonymous@.discussions.microsoft.com> wrote in message
news:1a0ff01c41d7c$0f74f590$a401280a@.phx.gbl...
> Help!!
> My maintenance plans have disapeared from View, although
> according to the Databases that use the Plans they still
> exist..
> To test this I created a quick Maintenance Plan and sure
> enough it created but it cannot be seen!!
> Does anybody have any ideas or should I call the
> Ghostbusters?
> I am using SQL Server V7.0, using Enterpise Manager on my
> PC to Administer the Server.
> TIA
>
> Tony C.

Maintenance Plans Disappear

I recently restored 3 databases. The maintenance plans responsible for
creating the backups for those databases appear to have disappeared after th
e
restoration. I deleted the databases prior to restoration, but did not touc
h
the plans.
Is there a way to make sure that the next time I have to restore the
databases that the plans do not get removed? Is there a way to backup or
export the maintenance plans?
Thanks
MarkDid you restore msdb? Or did you perform a rebuild of the system databases?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Coffman" <MarkCoffman@.discussions.microsoft.com> wrote in message
news:970287BA-384C-49A8-BA53-26E1BE124F57@.microsoft.com...
>I recently restored 3 databases. The maintenance plans responsible for
> creating the backups for those databases appear to have disappeared after
the
> restoration. I deleted the databases prior to restoration, but did not to
uch
> the plans.
> Is there a way to make sure that the next time I have to restore the
> databases that the plans do not get removed? Is there a way to backup or
> export the maintenance plans?
> Thanks
> Mark
>

Maintenance Plans Disappear

I recently restored 3 databases. The maintenance plans responsible for
creating the backups for those databases appear to have disappeared after the
restoration. I deleted the databases prior to restoration, but did not touch
the plans.
Is there a way to make sure that the next time I have to restore the
databases that the plans do not get removed? Is there a way to backup or
export the maintenance plans?
Thanks
MarkDid you restore msdb? Or did you perform a rebuild of the system databases?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Coffman" <MarkCoffman@.discussions.microsoft.com> wrote in message
news:970287BA-384C-49A8-BA53-26E1BE124F57@.microsoft.com...
>I recently restored 3 databases. The maintenance plans responsible for
> creating the backups for those databases appear to have disappeared after the
> restoration. I deleted the databases prior to restoration, but did not touch
> the plans.
> Is there a way to make sure that the next time I have to restore the
> databases that the plans do not get removed? Is there a way to backup or
> export the maintenance plans?
> Thanks
> Mark
>

Maintenance Plans Disappear

I recently restored 3 databases. The maintenance plans responsible for
creating the backups for those databases appear to have disappeared after the
restoration. I deleted the databases prior to restoration, but did not touch
the plans.
Is there a way to make sure that the next time I have to restore the
databases that the plans do not get removed? Is there a way to backup or
export the maintenance plans?
Thanks
Mark
Did you restore msdb? Or did you perform a rebuild of the system databases?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Coffman" <MarkCoffman@.discussions.microsoft.com> wrote in message
news:970287BA-384C-49A8-BA53-26E1BE124F57@.microsoft.com...
>I recently restored 3 databases. The maintenance plans responsible for
> creating the backups for those databases appear to have disappeared after the
> restoration. I deleted the databases prior to restoration, but did not touch
> the plans.
> Is there a way to make sure that the next time I have to restore the
> databases that the plans do not get removed? Is there a way to backup or
> export the maintenance plans?
> Thanks
> Mark
>

Maintenance Plans - Master, MSDB, etc.

I currently have a maintenance plan setup to do typical
optimizations/reindexing of our user databases. Is it a good practice to do
this on the Master database or the MSDB database? The MSDB seems like the
better candidate of the two since we work with DTS quite a bit. BUT, by a
"bit" I mean we have a MSDB database around 200 MB.
Thanks in advance.
MarkOptimizations do not really apply to the system dbs with the MP since it
uses DBCC DBREINDEX and that is not supported on system tables.
Andrew J. Kelly SQL MVP
"Mark" <Mark@.nowhere.com> wrote in message
news:e1kwcG$$EHA.3416@.TK2MSFTNGP09.phx.gbl...
>I currently have a maintenance plan setup to do typical
> optimizations/reindexing of our user databases. Is it a good practice to
> do
> this on the Master database or the MSDB database? The MSDB seems like the
> better candidate of the two since we work with DTS quite a bit. BUT, by a
> "bit" I mean we have a MSDB database around 200 MB.
> Thanks in advance.
> Mark
>|||Oh, but do make sure you do the integrity checks!
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:efErdhAAFHA.3616@.TK2MSFTNGP11.phx.gbl...
Optimizations do not really apply to the system dbs with the MP since it
uses DBCC DBREINDEX and that is not supported on system tables.
Andrew J. Kelly SQL MVP
"Mark" <Mark@.nowhere.com> wrote in message
news:e1kwcG$$EHA.3416@.TK2MSFTNGP09.phx.gbl...
>I currently have a maintenance plan setup to do typical
> optimizations/reindexing of our user databases. Is it a good practice to
> do
> this on the Master database or the MSDB database? The MSDB seems like the
> better candidate of the two since we work with DTS quite a bit. BUT, by a
> "bit" I mean we have a MSDB database around 200 MB.
> Thanks in advance.
> Mark
>

Maintenance Plans - Master, MSDB, etc.

I currently have a maintenance plan setup to do typical
optimizations/reindexing of our user databases. Is it a good practice to do
this on the Master database or the MSDB database? The MSDB seems like the
better candidate of the two since we work with DTS quite a bit. BUT, by a
"bit" I mean we have a MSDB database around 200 MB.
Thanks in advance.
Mark
Optimizations do not really apply to the system dbs with the MP since it
uses DBCC DBREINDEX and that is not supported on system tables.
Andrew J. Kelly SQL MVP
"Mark" <Mark@.nowhere.com> wrote in message
news:e1kwcG$$EHA.3416@.TK2MSFTNGP09.phx.gbl...
>I currently have a maintenance plan setup to do typical
> optimizations/reindexing of our user databases. Is it a good practice to
> do
> this on the Master database or the MSDB database? The MSDB seems like the
> better candidate of the two since we work with DTS quite a bit. BUT, by a
> "bit" I mean we have a MSDB database around 200 MB.
> Thanks in advance.
> Mark
>
|||Oh, but do make sure you do the integrity checks!
Anthony Thomas

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:efErdhAAFHA.3616@.TK2MSFTNGP11.phx.gbl...
Optimizations do not really apply to the system dbs with the MP since it
uses DBCC DBREINDEX and that is not supported on system tables.
Andrew J. Kelly SQL MVP
"Mark" <Mark@.nowhere.com> wrote in message
news:e1kwcG$$EHA.3416@.TK2MSFTNGP09.phx.gbl...
>I currently have a maintenance plan setup to do typical
> optimizations/reindexing of our user databases. Is it a good practice to
> do
> this on the Master database or the MSDB database? The MSDB seems like the
> better candidate of the two since we work with DTS quite a bit. BUT, by a
> "bit" I mean we have a MSDB database around 200 MB.
> Thanks in advance.
> Mark
>