maintenance plan wizard: maintenance

Showing posts with label maintenance. Show all posts

Friday, March 9, 2012

Maitnenance Plans start, but don't do anything

Hi,
This is an SQL2005 issue.
We are having a strange problem where maintenance plans will start,
but not do anything and just stay in the "Executing" stage forever. I
have created a simple plan that just does a tran log backup for a test
database which should be done in half a second, but starts and thats
it. Also, no tran log is created so it isn;t doing anything. I have
created a job that runs sql script and it runs with no problem, it
just seems to be with the maintence plans that has the issue.
there are no errors being generated and the logs all look fine.
Anyone have any ideas what is going wrong or what I can try to do to
figure this out'
Thanks!Ed,
You don't mention the version and build of SQL2005.
Chris
"Ed" <ede@.nait.ab.ca> wrote in message
news:1193673452.504643.227330@.z24g2000prh.googlegroups.com...
> Hi,
> This is an SQL2005 issue.
> We are having a strange problem where maintenance plans will start,
> but not do anything and just stay in the "Executing" stage forever. I
> have created a simple plan that just does a tran log backup for a test
> database which should be done in half a second, but starts and thats
> it. Also, no tran log is created so it isn;t doing anything. I have
> created a job that runs sql script and it runs with no problem, it
> just seems to be with the maintence plans that has the issue.
> there are no errors being generated and the logs all look fine.
> Anyone have any ideas what is going wrong or what I can try to do to
> figure this out'
> Thanks!
>|||Try right-clicking on the job and select 'refresh job', or 'refresh' on
SQLagent.
It's not the job that's going on forever, it's the fact that MS (like EM
before it) has to be manually refreshed.
PITA, but what is, is.
Good luck,
Jay
"Ed" <ede@.nait.ab.ca> wrote in message
news:1193673452.504643.227330@.z24g2000prh.googlegroups.com...
> Hi,
> This is an SQL2005 issue.
> We are having a strange problem where maintenance plans will start,
> but not do anything and just stay in the "Executing" stage forever. I
> have created a simple plan that just does a tran log backup for a test
> database which should be done in half a second, but starts and thats
> it. Also, no tran log is created so it isn;t doing anything. I have
> created a job that runs sql script and it runs with no problem, it
> just seems to be with the maintence plans that has the issue.
> there are no errors being generated and the logs all look fine.
> Anyone have any ideas what is going wrong or what I can try to do to
> figure this out'
> Thanks!
>

Maitenance Job

In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
BAK files are delted that are older than this, however the TRN backups are
not deleted. Why is this and how can I resolve it?Hi ,
Typically, the size of the transaction log file stabilizes when it can hold
the maximum number of transactions that can occur between transaction log
truncations that either checkpoints or transaction log backups trigger.
However, in some situations the transaction log may become very large and
run out of space or become full.
For more information, please refer to:
317375 A transaction log grows unexpectedly or becomes full on a computer
that is running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================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 this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Maitenance Job
>>thread-index: AcZn26C5VxQ5EMvgQt6cXAn7hAS36A==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>Subject: Maitenance Job
>>Date: Mon, 24 Apr 2006 13:14:01 -0700
>>Lines: 3
>>Message-ID: <E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.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.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:429048
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>In my Maintenance job I specify to keep the BAK and TRN backups for 1
week.
>>BAK files are delted that are older than this, however the TRN backups
are
>>not deleted. Why is this and how can I resolve it?|||Did you include databases in simple recovery model in the maint plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||what do you mean by simple recovery model
"Tibor Karaszi" wrote:
> Did you include databases in simple recovery model in the maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Schneider" <georgedschneider@.news.postalias> wrote in message
> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> > In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> > BAK files are delted that are older than this, however the TRN backups are
> > not deleted. Why is this and how can I resolve it?
>
>|||The tranactions logs are only like 84 kb when they are backuped up.
"George Schneider" wrote:
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||This is very well documented in Books Online (BOL), and I suggest you take the time to learn those
concepts:
BOL 2000, search for "Selecting a Recovery Model"
BOL 2005, search for "Choosing the Recovery Model for a Database"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:1EE7026E-7AB5-41E7-B5E6-F474B3B6A9E0@.microsoft.com...
> what do you mean by simple recovery model
> "Tibor Karaszi" wrote:
>> Did you include databases in simple recovery model in the maint plan?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "George Schneider" <georgedschneider@.news.postalias> wrote in message
>> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
>> > In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
>> > BAK files are delted that are older than this, however the TRN backups are
>> > not deleted. Why is this and how can I resolve it?
>>|||Hi George ,
How is everything going? Let me know if you still have concerns.
Have a good day.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================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 this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Maitenance Job
>>thread-index: AcZoZcp6c/Q/UTZhQfOLwJIjZnhIqA==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>References: <E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com>
>>Subject: RE: Maitenance Job
>>Date: Tue, 25 Apr 2006 05:43:02 -0700
>>Lines: 7
>>Message-ID: <C88CCAF2-A7E1-409B-96BA-11C7F6B060F0@.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.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:429121
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>The tranactions logs are only like 84 kb when they are backuped up.
>>"George Schneider" wrote:
>> In my Maintenance job I specify to keep the BAK and TRN backups for 1
week.
>> BAK files are delted that are older than this, however the TRN backups
are
>> not deleted. Why is this and how can I resolve it?

Wednesday, March 7, 2012

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

MaintenancePlan - task order

Hello,
I created one maintenance plan with one schedule, it includes several
tasks. How I can change order on with they are executed, graphical
moving task didn't change order.
--
Best regardsYou have lines with arrows between the tasks. They define the flow. Remove your current lines (the
one you need to re-arrange) and draw new lines, to your liking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1194381069.963177.83560@.50g2000hsm.googlegroups.com...
> Hello,
> I created one maintenance plan with one schedule, it includes several
> tasks. How I can change order on with they are executed, graphical
> moving task didn't change order.
> --
> Best regards
>|||Hello Tibor :)
> You have lines with arrows between the tasks. They define the flow. Remove your current lines (the
> one you need to re-arrange) and draw new lines, to your liking.
Are you sure this working, I tray arrange flow between the tasks use
lines, but logs show me that tasks are executed on different order.
--
Best regards|||Yes, it should work. I can't say why it isn't working for you, except for guesses like your not
editing the correct package... Sorry :-(
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1194388800.134252.68680@.o38g2000hse.googlegroups.com...
> Hello Tibor :)
>> You have lines with arrows between the tasks. They define the flow. Remove your current lines
>> (the
>> one you need to re-arrange) and draw new lines, to your liking.
> Are you sure this working, I tray arrange flow between the tasks use
> lines, but logs show me that tasks are executed on different order.
> --
> Best regards
>
>|||You are right, it is working. Sorry and thanks, my mistake. I looked
at order on log files. SQL Server wrote on wrong order, but when I
looked at individual start and stop date for each tasks, everything is
on right order.
Thank for your help.
--
Regards,
anxcomp|||Glad you got it working.. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1194459342.715819.182790@.d55g2000hsg.googlegroups.com...
> You are right, it is working. Sorry and thanks, my mistake. I looked
> at order on log files. SQL Server wrote on wrong order, but when I
> looked at individual start and stop date for each tasks, everything is
> on right order.
> Thank for your help.
> --
> Regards,
> anxcomp
>

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 error...

Hi,

I'm just trying to use the Maintenance Wizard for the first time, but the SQL Server Management Studio shows me an error message about 'Agent XPs' is not running on my server; and I should activate it through 'sp_configure'?

I've tried to understand 'sp_configure' by searching it on MS TechNet, etc. But of course, because I'm not a SQL Server expert user; I don't even know how to use 'sp_configure' at all....

Anyone know how to work this thing around? Appreciate all the help...

PS: I almost forgot to mention, that I'm using MS Windows Small Business Server R2 2003 with SQL Server 2005 Workgroup Edition.

sp_configure is used to view/change global settings for the current server. To enable them, connect to Management Studio, start a new query, and view the current configuration. For more options with sp_configure, check out:

http://msdn2.microsoft.com/en-us/library/ms188787.aspx

To enable Agent XP's, you can try something like this:

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
go
RECONFIGURE
GO

Thanks,
Sam Lester (MSFT)

|||

Sam,

Thanks for your reply there...

But unfortunately, I am a very novice at SQL Server, is there a step-by-step documentation on how to start using sp_configure?

Thank you again.

|||

The link I supplied explains the entire syntax for sp_configure, one of many system stored procedures used in managing your server. I'd suggest taking a look at the tutorials included in books online (the documentation portion of SQL Server 2005). Here is a good one on getting familiar with Management Studio, including writing T-SQL statements (such as sp_configure):

http://msdn2.microsoft.com/en-us/library/ms167593.aspx

Thanks,
Sam

|||

Hello -

I'm not an expert on SQL Server Management Studio, but I just ran into the same issue and here's how I got around it:

I loaded up SQL Server Management Studio|||

Chris,

Is it really true, you did have the similar problem with Maintenance Wizard like I do? Then perhaps it's like I'm most affraid of, Microsoft actually de-activate it by default settings in SQL Server 2005 Workgroup Edition... The question is why?

I did try to look up in the Object Explorer in SQL Server Management Studio first before post a thread in here, because I didn't find anything that said about Agent XPs. But maybe it's just that missed it, I'll take a look it again when I got back to my office then (too bad right now I'm on vacation, gives me a jeepers to leave the server's database un-protected like that ).

Thanks for the feedback Chris! Hopefully others who has the same problem with Maintenance Wizard will post too, so Microsoft could give a good explanation why there's such issue in the first place.

Maintenance Wizard

Hi
After running maintenance wizard, I getting a backup file that is the same
size like the DB but with .bak.
Is there a way to compress this file automaticly?
The original size is 11GB after compress the size is 700MB.
Any idea?
Than'x
ShayHi
You could compress the OS directory to save space, or if you wish to move
the backup elsewhere winrar has command line capabilities, so you could call
that as an extra step within the job. Products such as SQL Litespeed will
also do this for you http://www.quest.com/litespeed_for_sql_server/
John
"S" wrote:
> Hi
> After running maintenance wizard, I getting a backup file that is the same
> size like the DB but with .bak.
> Is there a way to compress this file automaticly?
> The original size is 11GB after compress the size is 700MB.
> Any idea?
> Than'x
> Shay
>
>

Maintenance Wizard

Hi
After running maintenance wizard, I getting a backup file that is the same
size like the DB but with .bak.
Is there a way to compress this file automaticly?
The original size is 11GB after compress the size is 700MB.
Any idea?
Than'x
ShayHi
You could compress the OS directory to save space, or if you wish to move
the backup elsewhere winrar has command line capabilities, so you could call
that as an extra step within the job. Products such as SQL Litespeed will
also do this for you http://www.quest.com/litespeed_for_sql_server/
John
"S" wrote:

> Hi
> After running maintenance wizard, I getting a backup file that is the same
> size like the DB but with .bak.
> Is there a way to compress this file automaticly?
> The original size is 11GB after compress the size is 700MB.
> Any idea?
> Than'x
> Shay
>
>

Maintenance Wizard

Hi
After running maintenance wizard, I getting a backup file that is the same
size like the DB but with .bak.
Is there a way to compress this file automaticly?
The original size is 11GB after compress the size is 700MB.
Any idea?
Than'x
Shay
Hi
You could compress the OS directory to save space, or if you wish to move
the backup elsewhere winrar has command line capabilities, so you could call
that as an extra step within the job. Products such as SQL Litespeed will
also do this for you http://www.quest.com/litespeed_for_sql_server/
John
"S" wrote:

> Hi
> After running maintenance wizard, I getting a backup file that is the same
> size like the DB but with .bak.
> Is there a way to compress this file automaticly?
> The original size is 11GB after compress the size is 700MB.
> Any idea?
> Than'x
> Shay
>
>

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 utility acting on its own??

Hello

I've set up a maintenance plan for one of the SQL servers in the network I'm administering. When I checked the logs this morning, I was surprised to find that all backups had failed. I was about to throw my computer out the window, when I thought about actually checking the folders where the backup files are supposed to be. I was even more surprised to find that a succesful backup actually had taken place! It seems that this backup had occured about 10 minutes later than the one that failed. When I checked the jobs, they all seemed to have failed (Last Run Status was failed).

How is this possible? Is SQL Server running it's own backups, or is it possible that there's some backup job somewhere that is listed with the jobs for the server?

Any help and suggestions is very welcome.

MNJA lot of things can cause a job to fail, and it may fail partway through so you would see backups for many of your databases (or even all of your databases if it failed on a step after the backup).

Check to see what step you job failed on, and it is good practice to log the output from your backup job. Editing the step(s) that may cause trouble, go to the Advanced tab, and set the Output file location. You can also view previous output from here.

blindman|||Yea, it seems that some of the backups failed because the file sizes were too big, and therefore failed to back up across a network share. I still haven't figured out why those databases have been backed up anyway though, because they're all set to no retries and the log doesn't mention anything about a succesful backup of those DBs.

Now I've decided to do local backups instead.

MNJ|||Originally posted by kilobyte
Yea, it seems that some of the backups failed because the file sizes were too big, and therefore failed to back up across a network share. I still haven't figured out why those databases have been backed up anyway though, because they're all set to no retries and the log doesn't mention anything about a succesful backup of those DBs.

Now I've decided to do local backups instead.

MNJ

I found in my situation that the best thing to do is break out into 4 maint plans. I save them on the local disk and have an automatic delete of them after X number of days.

[list=1]
Get all the system databases in one plan w/ nightly backups. You can just default this one.
Get the critical and rapidly changing application database (the ones using transaction logs) as separate maint plan. The ones you may have to rollback to a point in time.
Get the semi-static databases as a third. Low users or mostly lookup data.
The 4th is for our WebSweeper DB. The thing is huge and we don't have a significant worry if we lose some of the web access info.
[/list=1]

I then depend on the nightly diferentials and weekly fulls to take care of long term recovery and use what is on the local disk for short term. At one point in time I did try to save them to a network location, but I consistently (at least once a week) would lose connectivity to that drive and my b/u were dead.

Another option, is publishing and distribution. If you have another server off-site or can get one with a decent bandwidth connection, you can publish your apps database(s) nightly to the other server. We had (it was only a P200 with 20GB HD and 1GB of RAM :( ) and will soon have again a SQL box at our recovery site that does nothing but tick over and catches the databases nightly. :) That makes life a lot easier from recovery planning.

I have found that most open file agents of b/u software are crap, in my opinion. If you are running out of room on your server's disk, take a look at the plans above. Cut transaction logs for only the most critical and changing databases. And in the maint plans I have described the backups for the critical databases are kept 4 days, but the transaction logs are only kept for 2 days. If need I can always go to tape earlier logs. But most times a major point-in-time restore is going to be less than 48 hours. I know the exception is on a Monday, but how often does it happen? I've had 4 PIT restores in the past 3 years and of those it has generally been to 6AM the current day.

The semi-static databases are kept local for 2 days and then are cycled out. The system databases, I just back up for grins. If the SQL SW crashes that hard, you'll probably just re-install SQL and then reattach your DB's in place. If it was the WinXX software, same thing.

Also when you set up your maint plans, have them do data integrity and DB shrink procedures daily. That will probably buy some disk space.

And sometimes, you just have to tell the boss "We need some more drives for this POS server." :) If he doesn't listen, just smile during the autopsy after the crash, and say "I told you so."|||kilobyte: What do you go by saying that a successful backup actually occurred? A failed backup will leave a backup file on disk that cannot be used for restore operation. Can you try to do a restore to a new database from that backup file if space permits?|||jimpen: A very compelling plan! BTW, do you really believe that system databases can be omitted from maintenance plans? Also, I don't think it's a good idea to just shrink logs of all databases accross the board. Those with Full Recovery mode should have log device pre-allocated to avoid growth overhead during business hours.|||Originally posted by rdjabarov
jimpen: A very compelling plan! BTW, do you really believe that system databases can be omitted from maintenance plans? Also, I don't think it's a good idea to just shrink logs of all databases accross the board. Those with Full Recovery mode should have log device pre-allocated to avoid growth overhead during business hours.

As I said, I do the system DBs for grins. I have kept our server very vanilla. There is only one stored procedure, and a table that I would like to retain after a crash. And even then, that table's data is kept elsewhere. I also changed the model a little for standards sake (min 50MB log file). If it is a minor crash, SQL server will generally either recover itself, or need a little work from the SysAdm and it will come up. But I've had mine go down on Apps install so bad that I had MS's support in one ear and a consultant in the other. After a day, they said just reinstall and reattach the other databases. Nothing could be recovered from the master, model, etc.

As for the log files, I already have them set up for a larger minimum size where needed. The rollback DBs get their transactions backed up and then are truncated. The slow database are just automatic trunc on checkpoint so they never really grow.|||wow, that's pretty brave! Good luck!|||What's brave about it?

As long as the tape backups are working, even if someone walked in and ran an axe through the hard drives the worst loss is about 24 hours of data.

We are primarily a daylight business - about 7A-7P Mon-Fri with some weekend dedicated types.

If the server crashes at on Sunday at midnight and W2K Server has to be reinstalled as well as the SQL 7, the model, master, msdb and tempdb will all be replaced anyway. It is virtually impossible to restore a master database. Sometime, build a baby server and try it.

After the reinstall on the same server, if the DB files are still on the volume, you can run a sp_attach_db to get them back online. If you lost the volume and are starting with a blank HD, then you have the files restored from tape to the \mssql\backup directory and then proceed to restore the database to it's original place.

If someone went bogus on the data and multiplied every table by 5 times then you have a PIT recovery. And if it was done Saturday then you get back to Friday after hours.

And this is not even considering that you publish the database.|||Thanks for the help guys, this has turned out to become an interesting discussion of a vital subject. I consider myself a bit of a newbie in this area, so I'm glad about the input.

About the backups. No, I haven't tried restoring from the files, I just sort assumed they were ok. The file size seemed all right, so I figured they were just fine. The log files produced by the maint. job said something like this:

'Backup can not be performed on database 'msdb'. This sub task is ignored.'

, but I'm suspecting this might be about the transaction log backups Those log files are not always as helpful as I'd wish...

Anyway, I'm now doing local backups now and that seems to work a lot better. Today the log file said succes at the bottom! Except for what seems to be the log file for the transaction log backups. I'll have to delve into that a little deeper.

Btw, the backup plan I use is the following:
1. Weekly full and daily incremental backups on tape for major database servers
2. Daily full backups on disk for all database servers

I like to have backups on tape in case the office burns down or whatever. I don't differentiate too much between different databases within the same server. The reasons for this are twofold. I don't feel I know enough about it, and I don't feel I have the time. Also, people tend to create new databases as often as they change their underwear here, and that does not make administering the servers any eaiser.

Again, thanks for the input.

MNJ|||Btw, I use Veritas Backup Exec 9.0 and that seems to work just fine with the agents and all. And yes, I have done test restores.

MNJ|||Hello again.

Just wanted to say that backups work fine now that they're being done locally. Except for the transaction log backups which still fail.

I think this is an unrelated problem though, so I've started a new thread at: http://65.61.175.198/showthread.php?threadid=889573

Again, any help and comments will be greatly appreciated.

MNJ|||Originally posted by kilobyte
I don't differentiate too much between different databases within the same server. The reasons for this are twofold. I don't feel I know enough about it, and I don't feel I have the time. Also, people tend to create new databases as often as they change their underwear here, and that does not make administering the servers any eaiser.
MNJ

If they are just adding database(s) and developing/testing on your production servers you need to slap some people around :( . They can and will at some point crash your SQL and/or server with some process that goes wild or access the disk and delete an important file.

You may need to speak up at some point and make them realize that they need to have test/development servers versus daily production.

I work for a relatively small company and even we have scraped an older server into using as a test box before we fire it up onto the production SQL server.|||Originally posted by jimpen
If they are just adding database(s) and developing/testing on your production servers you need to slap some people around :( . They can and will at some point crash your SQL and/or server with some process that goes wild or access the disk and delete an important file.

You may need to speak up at some point and make them realize that they need to have test/development servers versus daily production.

I know, I know. We are in that phase now in fact, trying to have test servers for each production server. There have been times, though, where it's been necessary to change something really quickly, and I am fully aware that it is not a good strategy.

I've begun thinking about how to come up with a new maintenance plan, and I just might start a new thread on that subject, to get some input from outside.

Anyways, thanks for the help so far, you've all saved me hours of work!

MNJ|||kilobyte: You're getting transaction log backup errors probably because you included databases with simple recovery mode. In one of your posts you mentioned msdb. This database will always start up in simple recovery mode, which will fail the entire step even if the rest of db's got backed up successfully.

jimpen: you said that "It is virtually impossible to restore a master database." - I have no more comments :)|||Originally posted by rdjabarov
kilobyte: You're getting transaction log backup errors probably because you included databases with simple recovery mode. In one of your posts you mentioned msdb. This database will always start up in simple recovery mode, which will fail the entire step even if the rest of db's got backed up successfully.

You're right, that is the problem.

MNJ

Friday, March 9, 2012

Maitnenance Plans start, but don't do anything

Maitenance Job

Maitenance Job

Wednesday, March 7, 2012

Mainteneance plans BAK TRN

Mainteneance plans BAK TRN

MaintenancePlan - task order

Maintenance, indexes, fragmentation & file groups

Maintenance, indexes, fragmentation & file groups

Maintenance, indexes, fragmentation & file groups

Maintenance Wizard error...

Maintenance Wizard

Maintenance Wizard

Maintenance Wizard

Maintenance Wizard

Maintenance utility acting on its own??

maintenance plan wizard

Blog Archive

About Me