Backup to Network Drive: monitor

Showing posts with label monitor. Show all posts

Tuesday, March 20, 2012

balloning t-log

Every Satuday evening my t-log balloons and consume my available drive
space. What can I do to monitor the og so i can try to see what is
causing this to keep happening.
I have interrogated the application support ema to see if they do any
large amount of work that would cause the problem but only "not me"
answers
any help/suggestions are greatly appreciated.Can you set up a trace to see who is doing what? My guess is something like
index rebuilds.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
> Every Satuday evening my t-log balloons and consume my available drive
> space. What can I do to monitor the og so i can try to see what is
> causing this to keep happening.
> I have interrogated the application support ema to see if they do any
> large amount of work that would cause the problem but only "not me"
> answers
> any help/suggestions are greatly appreciated.|||On Jan 20, 7:41=A0pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Can you set up a trace to see who is doing what? My guess is something lik=e
> index rebuilds.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>
> > Every Satuday evening my t-log balloons and consume my available drive
> > space. What can I do to monitor the og so i can try to see what is
> > causing this to keep happening.
> > I have interrogated the application support ema to see if they do any
> > large amount of work =A0that would cause the problem but only "not me"
> > answers
> > any help/suggestions are greatly appreciated.- Hide quoted text -
> - Show quoted text -
I do perform maintenance at 1am on Sunday mornings which does an index
reorg but not a rebuild. would performing maintenance more often help
or will that balloon the logs suring the production week which I don't
want to happen.
Any suggestions are welcomed.|||Yes, re-organising OR rebuilding databases does cause massive transaction
logging.
Instead of actually rebuilding or re-organising your indexes, you might be
better off just rebuilding index statistics (which doesn't generate such
massive log entries). You should also look into tuing the queries which are
scanning your tables - these queries are the ones benefiting from the
re-orgs / rebuilds but they'd probably run much better if they just had a
good index.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Can you set up a trace to see who is doing what? My guess is something
> like
> index rebuilds.
> --
> HTH
> Kalen Delaney, SQL Server
> MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>
> > Every Satuday evening my t-log balloons and consume my available drive
> > space. What can I do to monitor the og so i can try to see what is
> > causing this to keep happening.
> > I have interrogated the application support ema to see if they do any
> > large amount of work that would cause the problem but only "not me"
> > answers
> > any help/suggestions are greatly appreciated.- Hide quoted text -
> - Show quoted text -
I do perform maintenance at 1am on Sunday mornings which does an index
reorg but not a rebuild. would performing maintenance more often help
or will that balloon the logs suring the production week which I don't
want to happen.
Any suggestions are welcomed.|||Also use the script from BOL that checks fragmentation first and only redoes
stuff that needs it. Also, if you avoid clustered indexes (unless severely
fragmented) that could keep logging down too.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OBPTWM9WIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Yes, re-organising OR rebuilding databases does cause massive transaction
> logging.
> Instead of actually rebuilding or re-organising your indexes, you might be
> better off just rebuilding index statistics (which doesn't generate such
> massive log entries). You should also look into tuing the queries which
> are scanning your tables - these queries are the ones benefiting from the
> re-orgs / rebuilds but they'd probably run much better if they just had a
> good index.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "NC3" <ncoleman3@.yahoo.com> wrote in message
> news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
> On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> Can you set up a trace to see who is doing what? My guess is something
>> like
>> index rebuilds.
>> --
>> HTH
>> Kalen Delaney, SQL Server
>> MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
>> "NC3" <ncolem...@.yahoo.com> wrote in message
>> news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...
>>
>> > Every Satuday evening my t-log balloons and consume my available drive
>> > space. What can I do to monitor the og so i can try to see what is
>> > causing this to keep happening.
>> > I have interrogated the application support ema to see if they do any
>> > large amount of work that would cause the problem but only "not me"
>> > answers
>> > any help/suggestions are greatly appreciated.- Hide quoted text -
>> - Show quoted text -
> I do perform maintenance at 1am on Sunday mornings which does an index
> reorg but not a rebuild. would performing maintenance more often help
> or will that balloon the logs suring the production week which I don't
> want to happen.
> Any suggestions are welcomed.
>|||On Jan 20, 9:14=A0pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> Yes, re-organising OR rebuilding databases does cause massive transaction
> logging.
> Instead of actually rebuilding or re-organising your indexes, you might be=
> better off just rebuilding index statistics (which doesn't generate such
> massive log entries). You should also look into tuing the queries which ar=e
> scanning your tables - these queries are the ones benefiting from the
> re-orgs / rebuilds but they'd probably run much better if they just had a
> good index.
> Regards,
> Greg Linwood
> SQL Server MVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performancehttp://www.SQLBenchmarkPro.com
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:f69cf48c-bdeb-4026-9bb0-bd01ae9627b5@.c23g2000hsa.googlegroups.com...
> On Jan 20, 7:41 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>
>
> > Can you set up a trace to see who is doing what? My guess is something
> > like
> > index rebuilds.
> > --
> > HTH
> > Kalen Delaney, SQL Server
> > MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> > "NC3" <ncolem...@.yahoo.com> wrote in message
> >news:ecd7839d-fec3-4378-8502-ca2d1cfb11ce@.s12g2000prg.googlegroups.com...=
> > > Every Satuday evening my t-log balloons and consume my available drive=
> > > space. What can I do to monitor the og so i can try to see what is
> > > causing this to keep happening.
> > > I have interrogated the application support ema to see if they do any
> > > large amount of work that would cause the problem but only "not me"
> > > answers
> > > any help/suggestions are greatly appreciated.- Hide quoted text -
> > - Show quoted text -
> I do perform maintenance at 1am on Sunday mornings which does an index
> reorg but not a rebuild. would performing maintenance more often help
> or will that balloon the logs suring the production week which I don't
> want to happen.
> Any suggestions are welcomed.- Hide quoted text -
> - Show quoted text -
I appreciate your comments and will look into the suggestions that I
received. Tuning the queries are a problem since they belong to a
third party application. However I will learn how to use the SQL
traces suggested by Kalen Delaney, and reveiw BOL for fragmentation
queries as well as the rebuild stats.
New things to learn are always exciting (and frustrating)!
Thanks again.sql

balloning t-log

Thursday, March 8, 2012

backups through EM on remote registration

I have installed Sql Server 2000 evaluation copy on Windows XP, which I use to monitor other sql server 2000 installations (on Windows 2000) through Enterprise manager version 8.

What has been noticed is that if you try and set up a backup job through enterprise manager you do not get the option to remove inactive transaction log entries (yes the database's use full recovery models). Also if you select the verify backup option, it is not included within the job.

This is the same if you try the above on the local instance. This is not a problem as you can use the server installations, but what I am wondering is:

1. Has anyone come across this before?
2. Is it sql server 2000 on XP?
3. Is it the evaluation copy that is causing the problem?
4. Is it none of the above but indeed a far more complex reason which will mess with my head?

As always your help / knowledge is very much appreciated.As to verify, take a look at Database Maintanace Plans - there you can make it a part of your scheduled job. As to Inactive entries, actually it gets into your scheduled job, it is a default option when backing up transaction log - so you cannot see it in script that was generated by EM. Test for yourself - set up job without checking Remove inactive entries option and you can see that NO_TRUNCATE option is included in your command. Hope that it clears matters up a bit. Regards mojza|||Thanks for the reply mojza. I have no problems setting them up on the actual server, just on my PC through EM.

The verify problem is still an issue (i.e. You select it on your PC through EM but it is not part of the TSQL in the job).

However, the remove inactive transactions has got slightly stranger. On the server itself through EM, it is an option when creating a job and not touching the backup option (i.e. Database - complete is ticked by default). If you reselect it (click on it again or select another option and go back to complete) the option dissappears (which it should). Odd!!

As I said this is not really an issue but if anyone knows why I would appreciate the knowledge.|||I believe that Verify option in EM simply runs RESTORE VERIFYONLY command against backup set just completed so you can include this command plus some logic if you choose backup by T-SQL, otherwise you can set up Backup job in Database Maintanance Plan Wizard (it uses sqlmaint utility to perform its tasks which has -VrfyBackup switch to achieve backup set validation).

If 'remove inactive transactions' option is enabled in other backup type than transaction log, it's strange behavior and I've never seen that or heard about it. Evaluation edition or SQL on XP shouldn't be the cause. mojza|||The actual command it uses to berify if created on the server is:

select @.i = position from msdb..backupset where database_name='??' and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='??')
RESTORE VERIFYONLY FROM [??] WITH FILE = @.i

I would be interested to know if anyone else can reproduce the 'remove inactive transactions' with a complete backup problem. Essentially all I did was right-click on the database, select backup database and under the options tab the option is there.

Tuesday, March 20, 2012

balloning t-log

balloning t-log

Thursday, March 8, 2012

backups through EM on remote registration

Backup to Network Drive

Blog Archive

About Me