DB Maintenenace Plan, Optimizations

Hi All,
I have a database that has not recieved any type of maintenance apart from
daily backups. It has grown to over 30GB. I created some jobs to reindex
tables that I thought were high activity. Query performance increased
substantially but I believe it could be better. Database has also shruck
about 4GB.
After running a showcontig on the table, I could still see lots of
fragmentation on tables that were not included in the job above. So I
created a DB Maintenance Plan to for only optimizations and chose to
reorganize data and index pages, and keep original fillfactor that the index
was created with.
The job ran last night but did not finish because it failed on an unknown
step after 27 mins. I ran this job manually and it ran for 50 mins. before
I
manually stopped the job. The actual database space decreased another 7GB.
The transaction log backup could not be performed becuase there was not
enough disk space, the trans log was 23GB.
My questions are:
How can I enure this optimization job will successfully run in the future?
Is there a way to make the trans log smaller after the optimization job is
run?
Should I be doing a Full DB Backup AFTER the optimization job?
Can I shrink the allocated size of the database by adding to the
optimization job to "remove unused space from database files" safely? I wan
t
to ensure the database will still grow automatically if space is required.
Cheers!First off you should undo the shrink part of the maintenance plan. When you
reindex the tables you need lots of free space in the data and log files in
order for it to do it's job properly. If there isn't it has to grow the
files which is resource intensive. That process will leave free space which
the shrink part of the maintenance plan will not like and then it will
shrink the files. The shrinking process will fragment your tables and
indexes all over again. See here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
You should leave the size at what ever it needs to be to do what it has to
do. The exception would be the log file. If it just keeps growing you
probably aren't backing up the log files on a regular basis. Full backups
are not enough if you are in the full recovery mode.
Andrew J. Kelly SQL MVP
"AKing" <AKing@.discussions.microsoft.com> wrote in message
news:F9CBB7CC-A53C-498A-A678-E974558EF4C9@.microsoft.com...
> Hi All,
> I have a database that has not recieved any type of maintenance apart from
> daily backups. It has grown to over 30GB. I created some jobs to reindex
> tables that I thought were high activity. Query performance increased
> substantially but I believe it could be better. Database has also shruck
> about 4GB.
> After running a showcontig on the table, I could still see lots of
> fragmentation on tables that were not included in the job above. So I
> created a DB Maintenance Plan to for only optimizations and chose to
> reorganize data and index pages, and keep original fillfactor that the
> index
> was created with.
> The job ran last night but did not finish because it failed on an unknown
> step after 27 mins. I ran this job manually and it ran for 50 mins.
> before I
> manually stopped the job. The actual database space decreased another
> 7GB.
> The transaction log backup could not be performed becuase there was not
> enough disk space, the trans log was 23GB.
> My questions are:
> How can I enure this optimization job will successfully run in the future?
> Is there a way to make the trans log smaller after the optimization job
> is
> run?
> Should I be doing a Full DB Backup AFTER the optimization job?
> Can I shrink the allocated size of the database by adding to the
> optimization job to "remove unused space from database files" safely? I
> want
> to ensure the database will still grow automatically if space is required.
> Cheers!|||Thank you for your reply. You have provided some helpful insight.
I tried to run the optimization job (w/out shrinking db) and I got the same
behaviour. I had to cancel the job after it had been running for 50 minutes
.
Once again the translog was 21GB.
I understand that the reindex job can be intensive, this wil only multiply
if the translog grows from its usual size (~1gb) to 21GB.
Is there a way to run this job w/out the side effect above?
Will the translogs always be this big when running the optimization?
Maybe the only way is to have nightly reindex of portions of the database
until it has reindexed the entire database.
Cheers
"Andrew J. Kelly" wrote:

> First off you should undo the shrink part of the maintenance plan. When y
ou
> reindex the tables you need lots of free space in the data and log files i
n
> order for it to do it's job properly. If there isn't it has to grow the
> files which is resource intensive. That process will leave free space whi
ch
> the shrink part of the maintenance plan will not like and then it will
> shrink the files. The shrinking process will fragment your tables and
> indexes all over again. See here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> You should leave the size at what ever it needs to be to do what it has to
> do. The exception would be the log file. If it just keeps growing you
> probably aren't backing up the log files on a regular basis. Full backups
> are not enough if you are in the full recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "AKing" <AKing@.discussions.microsoft.com> wrote in message
> news:F9CBB7CC-A53C-498A-A678-E974558EF4C9@.microsoft.com...
>
>|||How large are the tables in the db? You might want to skip the actual
maintenance plan and just create a job with DBCC DBREINDEX instead to just
rebuild the ones that are fragmented above a certain level. Or just do a
portion of them each night. There is a script under DBCC SHOWCONTIG in
BooksOnLine to allow you to choose which tables will be reindexed or even
defragged.
Andrew J. Kelly SQL MVP
"AKing" <AKing@.discussions.microsoft.com> wrote in message
news:F06D716A-0ECA-4EDE-8EBD-547715C543CC@.microsoft.com...
> Thank you for your reply. You have provided some helpful insight.
> I tried to run the optimization job (w/out shrinking db) and I got the
> same
> behaviour. I had to cancel the job after it had been running for 50
> minutes.
> Once again the translog was 21GB.
> I understand that the reindex job can be intensive, this wil only multiply
> if the translog grows from its usual size (~1gb) to 21GB.
> Is there a way to run this job w/out the side effect above?
> Will the translogs always be this big when running the optimization?
> Maybe the only way is to have nightly reindex of portions of the database
> until it has reindexed the entire database.
> Cheers
>
> "Andrew J. Kelly" wrote:
>|||Hi Andrew,
The database allocation is ~36GB and at one time only had 50MB of free
space. Although through reindexing some high activity tables I have managed
to reduce the actual size of the database to ~25GB. I plan to shrink some o
f
the free space as I believe it is space wasted.
"Andrew J. Kelly" wrote:

> How large are the tables in the db? You might want to skip the actual
> maintenance plan and just create a job with DBCC DBREINDEX instead to just
> rebuild the ones that are fragmented above a certain level. Or just do a
> portion of them each night. There is a script under DBCC SHOWCONTIG in
> BooksOnLine to allow you to choose which tables will be reindexed or even
> defragged.
> --
> Andrew J. Kelly SQL MVP
>
> "AKing" <AKing@.discussions.microsoft.com> wrote in message
> news:F06D716A-0ECA-4EDE-8EBD-547715C543CC@.microsoft.com...
>
>

DB Maintenance Planner - Jobs not running

I set up 12 DB maintenance jobs on 3 separate DB's through the DB Maintenance
Planner - each job has a daily time to run.
All the jobs ran last night EXCEPT:
MSDB db trans log backup / MSDB db Integrity Checks
Master db trans log backup / Master db Integrity Checks
I am assuming that it is not necessary to run these jobs and I should delete
them. Am I right?
Thanks in advance.
bill
Hi,
I agree; Since there is no much data changes, it is not required to perform
transaction log backup on MSDB and Master databases.
Thanks
Hari
"WCM" <WCM@.discussions.microsoft.com> wrote in message
news:CC358189-EEC8-4FF3-91B0-F91972909ABE@.microsoft.com...
>I set up 12 DB maintenance jobs on 3 separate DB's through the DB
>Maintenance
> Planner - each job has a daily time to run.
> All the jobs ran last night EXCEPT:
> MSDB db trans log backup / MSDB db Integrity Checks
> Master db trans log backup / Master db Integrity Checks
> I am assuming that it is not necessary to run these jobs and I should
> delete
> them. Am I right?
> Thanks in advance.
> bill
|||Hari, thank you for the confirmation. b.
"Hari Prasad" wrote:

> Hi,
> I agree; Since there is no much data changes, it is not required to perform
> transaction log backup on MSDB and Master databases.
> Thanks
> Hari
> "WCM" <WCM@.discussions.microsoft.com> wrote in message
> news:CC358189-EEC8-4FF3-91B0-F91972909ABE@.microsoft.com...
>
>
|||Thank you, Tibor. I was concerned about those particular job failures. I
will set them up again and see if they work. If not, I am told that setting
up a new user (same username in both Windows and Sql Server) with permission
to run jobs may be necessary. Thanks again.
"Tibor Karaszi" wrote:

> But you definitely want to do integrity checks on these databases... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%233r43%23CFHHA.4580@.TK2MSFTNGP05.phx.gbl...
>

DB Maintenance Plan seems corrupted

Friends,
Does anyone know why a DB Maintenance Plan works fine with
scheduled jobs. Once a notification step is added to the
scheduled job and notification script uses the following
operating system command (cmdExec):
mailsend -d Nextmell.net -smtp NMell-mail -t
rumfell.paul@.nextmell.net,2229999999@.myairmail.com -f
sqladmin@.nextmell.net -sub "landmark- SystemDB DBCC
failed" -m SqladminMail.txt
The maintenance plan looks corrupted although the
scheduled jobs still run fine.
When you click on a plan, it tells you: "One or more jobs
created for this plan has had additional steps added to
it. It is not recommended that jobs created by the
maintenance plan bemodified in anyway." ...The job could
not be parsed. Do you want to ignore it and continue?
Help with above please...This is due to the poor way in which maint plans work. It relies in part on
parsing the strings in the scheduled jobs to determine how to display the
information in the GUI each time you open the wizard. If you change the
wrong thing in the job it can indeed mess up the GUI. In your case it
shouldn't bother anything other than the fact it will warn you since you
added a new step and didn't modify the existing one. Just one of many
reasons why I recommend creating your own jobs and dumping the wizard.
Andrew J. Kelly SQL MVP
"Paul Denver O." <anonymous@.discussions.microsoft.com> wrote in message
news:137d801c412a7$c98806f0$a401280a@.phx
.gbl...
> Friends,
> Does anyone know why a DB Maintenance Plan works fine with
> scheduled jobs. Once a notification step is added to the
> scheduled job and notification script uses the following
> operating system command (cmdExec):
> mailsend -d Nextmell.net -smtp NMell-mail -t
> rumfell.paul@.nextmell.net,2229999999@.myairmail.com -f
> sqladmin@.nextmell.net -sub "landmark- SystemDB DBCC
> failed" -m SqladminMail.txt
> The maintenance plan looks corrupted although the
> scheduled jobs still run fine.
> When you click on a plan, it tells you: "One or more jobs
> created for this plan has had additional steps added to
> it. It is not recommended that jobs created by the
> maintenance plan bemodified in anyway." ...The job could
> not be parsed. Do you want to ignore it and continue?
> Help with above please...

DB maintenance jobs not deleted

Hi
I had created a DB maintenace job that used to backup
full backup and trans log backup.
Recently we deleted those jobs and recreated the backup
jobs with manual SQL scripting.
The DB maintenance job keeps on appearing although there
is no corresponding plan for it.
We have checked all servers but with no help
Any idea what where that job might be coming from ?
Thanks> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
Can you elaborate a little bit? Did you remove a job that was realeted to MP
or first MP?
If you delete MP as far as I know all related jobs will be removed as well.
"ap" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6001c53e21$619206d0$a601280a@.phx.gbl...
> Hi
> I had created a DB maintenace job that used to backup
> full backup and trans log backup.
> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
> We have checked all servers but with no help
> Any idea what where that job might be coming from ?
> Thanks

DB maintenance jobs not deleted

Hi
I had created a DB maintenace job that used to backup
full backup and trans log backup.
Recently we deleted those jobs and recreated the backup
jobs with manual SQL scripting.
The DB maintenance job keeps on appearing although there
is no corresponding plan for it.
We have checked all servers but with no help
Any idea what where that job might be coming from ?
Thanks
> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
Can you elaborate a little bit? Did you remove a job that was realeted to MP
or first MP?
If you delete MP as far as I know all related jobs will be removed as well.
"ap" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6001c53e21$619206d0$a601280a@.phx.gbl...
> Hi
> I had created a DB maintenace job that used to backup
> full backup and trans log backup.
> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
> We have checked all servers but with no help
> Any idea what where that job might be coming from ?
> Thanks

DB maintenance jobs not deleted

Hi
I had created a DB maintenace job that used to backup
full backup and trans log backup.
Recently we deleted those jobs and recreated the backup
jobs with manual SQL scripting.
The DB maintenance job keeps on appearing although there
is no corresponding plan for it.
We have checked all servers but with no help
Any idea what where that job might be coming from ?
Thanks> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
Can you elaborate a little bit? Did you remove a job that was realeted to MP
or first MP?
If you delete MP as far as I know all related jobs will be removed as well.
"ap" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6001c53e21$619206d0$a601280a@.phx.gbl...
> Hi
> I had created a DB maintenace job that used to backup
> full backup and trans log backup.
> Recently we deleted those jobs and recreated the backup
> jobs with manual SQL scripting.
> The DB maintenance job keeps on appearing although there
> is no corresponding plan for it.
> We have checked all servers but with no help
> Any idea what where that job might be coming from ?
> Thanks

db maintenance issues with connection pooling

Hi,
Is there a way to do maintenance like integrity checks if there is still
a (sleeping)connection to a database? My maintenance jobs where you need
to be in single user mode fails. In our multi-tier environment we use an
applicationserver which uses connection pooling and a databaseserver
(SQL2K).
I've looked at dbcc opentran, but that doesn't work for me. The solution
i'm looking for is to check if there are any connections for a
particular database. If so, i want to disconnect it, but leave it in a
state so that the applicationserver doesn't have to restart it's
services (this is a manual proces).You could
SELECT cntr_value AS UsersConnected FROM master..sysperfinfo as p
WHERE p.object_name = 'SQLServer:General Statistics' And p.counter_name =
'User Connections'
this though will not give you the db upon which they are connected.
If you use -- sp_who 'active' this will give a more detailed breakdown of
active users and the db they are connected
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:exafy5ujGHA.2200@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Is there a way to do maintenance like integrity checks if there is still
> a (sleeping)connection to a database? My maintenance jobs where you need
> to be in single user mode fails. In our multi-tier environment we use an
> applicationserver which uses connection pooling and a databaseserver
> (SQL2K).
> I've looked at dbcc opentran, but that doesn't work for me. The solution
> i'm looking for is to check if there are any connections for a
> particular database. If so, i want to disconnect it, but leave it in a
> state so that the applicationserver doesn't have to restart it's
> services (this is a manual proces).