Sql ANSI Padding: configured

显示标签为“configured”的博文。显示所有博文

2012年3月19日星期一

Any way to be notified if database grows?

Our databases are configured for automatic growth by varying increments of MB.
Is there a way (an alert?) to find out when the growth has actualy happened?
Our customers are complaining of "random" poor performance - I would like to
rule out database growth.
TYIAYou can catch it using a Profiler trace. And probably also using an event notification (see CREATE
EVENT NOTIFICATION), if you are on 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
> Our databases are configured for automatic growth by varying increments of MB.
> Is there a way (an alert?) to find out when the growth has actualy happened?
> Our customers are complaining of "random" poor performance - I would like to
> rule out database growth.
> TYIA|||Thank you for your response.
Unfortunately, we are sill on 2000.
Are there any alerts SQL that would capture this?
Any way to write to the Event logs?
I don't know when it will happen and can't have a trace running 24 x 7.
Thank you .
"Tibor Karaszi" wrote:
> You can catch it using a Profiler trace. And probably also using an event notification (see CREATE
> EVENT NOTIFICATION), if you are on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
> news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
> > Our databases are configured for automatic growth by varying increments of MB.
> > Is there a way (an alert?) to find out when the growth has actualy happened?
> > Our customers are complaining of "random" poor performance - I would like to
> > rule out database growth.
> >
> > TYIA
>|||No and no, I'm afraid. It isn't a big deal to have a *server side* profiler trace running
continually. Of you can poll for the file size as frequently as you need and based on that log when
you notice a change in size.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:3AC5067C-9E8D-4564-BDD9-1F365BB8661D@.microsoft.com...
> Thank you for your response.
> Unfortunately, we are sill on 2000.
> Are there any alerts SQL that would capture this?
> Any way to write to the Event logs?
> I don't know when it will happen and can't have a trace running 24 x 7.
> Thank you .
> "Tibor Karaszi" wrote:
>> You can catch it using a Profiler trace. And probably also using an event notification (see
>> CREATE
>> EVENT NOTIFICATION), if you are on 2005.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
>> news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
>> > Our databases are configured for automatic growth by varying increments of MB.
>> > Is there a way (an alert?) to find out when the growth has actualy happened?
>> > Our customers are complaining of "random" poor performance - I would like to
>> > rule out database growth.
>> >
>> > TYIA
>>|||On Aug 15, 9:45 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> No and no, I'm afraid. It isn't a big deal to have a *server side* profiler trace running
> continually. Of you can poll for the file size as frequently as you need and based on that log when
> you notice a change in size.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "RobinMC" <Robi...@.discussions.microsoft.com> wrote in message
> news:3AC5067C-9E8D-4564-BDD9-1F365BB8661D@.microsoft.com...
>
> > Thank you for your response.
> > Unfortunately, we are sill on 2000.
> > Are there any alerts SQL that would capture this?
> > Any way to write to the Event logs?
> > I don't know when it will happen and can't have a trace running 24 x 7.
> > Thank you .
> > "Tibor Karaszi" wrote:
> >> You can catch it using a Profiler trace. And probably also using an event notification (see
> >> CREATE
> >> EVENT NOTIFICATION), if you are on 2005.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "RobinMC" <Robi...@.discussions.microsoft.com> wrote in message
> >>news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
> >> > Our databases are configured for automatic growth by varying increments of MB.
> >> > Is there a way (an alert?) to find out when the growth has actualy happened?
> >> > Our customers are complaining of "random" poor performance - I would like to
> >> > rule out database growth.
> >> > TYIA- Hide quoted text -
> - Show quoted text -
Instead of a profiler trace, start a perfmon (administrative tools-
>performance) trace on the server, tracing the following counters:
Performance Object: MsSQL:Databases
Counters: Data File Size KB, Log File Size KB
Instances: Select the appropriate database(s) that you'd like to
collect.
A relatively low sampling interval (once per every minute or few)
should be enough to give you the data that you're looking for.
Whenever any of these counters increase, you'll know that a file
growth occured and the amount that the file was expanded.
MS|||On Wed, 15 Aug 2007 13:10:01 -0700, RobinMC
<RobinMC@.discussions.microsoft.com> wrote:
>Our databases are configured for automatic growth by varying increments of MB.
>Is there a way (an alert?) to find out when the growth has actualy happened?
>Our customers are complaining of "random" poor performance - I would like to
>rule out database growth.
You could write a short app that checks the .mdf filesize every five
seconds or so.
J.

Any way to be notified if database grows?

2012年3月11日星期日

any tips for testing a san?

we finally got our san going here on a test db server.
any tips on testing it?
old disks are raid 5, san is configured raid 5.
so far i've moved some db's data and log files to the san. no problems.
changed the backups to point to the san.
saw significant improvements in backup times.
no improvement on full dbreindex.
nearly all of my databases are small (50mb to 500mb) which sort of
prevents large scale testing. i've got one 5gb db that i'll move over
(should be able to get some good read tests from it). i've also got a
sql stress test tool that i downloaded from somewhere (forget the name
of it though) that inserts millions of records and then reads them.
any other ideas or things to watch out for?when we first implemented SAN we were told to limit size
of individual files to 40gb, since then we have made sure
to set maximum growth of data / log files to 40gb, and add
additional files as needed. I am not sure if this is the
limit of SAN or Windows operating system or sql server.
Out experience is that if you have very large files then
you may have issues restoring them.
We never had any significant issues with SAN, and I am
glad that burden of managing 30+ spindles and few disk
cages is off my shoulders.
hth.
>--Original Message--
>we finally got our san going here on a test db server.
>any tips on testing it?
>old disks are raid 5, san is configured raid 5.
>so far i've moved some db's data and log files to the
san. no problems.
>changed the backups to point to the san.
>saw significant improvements in backup times.
>no improvement on full dbreindex.
>nearly all of my databases are small (50mb to 500mb)
which sort of
>prevents large scale testing. i've got one 5gb db that
i'll move over
>(should be able to get some good read tests from it).
i've also got a
>sql stress test tool that i downloaded from somewhere
(forget the name
>of it though) that inserts millions of records and then
reads them.
>any other ideas or things to watch out for?
>
>.
>

2012年3月6日星期二

Any problems when table isn't recreated during initialization?

Hi
I've configured push Merge replication between MS SQL Server 2005 Standard
and MS SQL Server 2005 Express. What kind of difficulties will I experience
if I don't recreate replicated tables during the reinitialization of
subscribers?
-- Thanks, Oskar.
Oskar - nosync initializations are fairly standard. However are you saying
that there is non-convergence of data? If so, then potentialy you'll be
plagued with errors.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks Paul. I don't really know what do you mean by non-convergence of data?
Could you please shed some light on that for me? By the way I use
non-overlapping partitions.
"Paul Ibison" wrote:

> Oskar - nosync initializations are fairly standard. However are you saying
> that there is non-convergence of data? If so, then potentialy you'll be
> plagued with errors.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||What I'm thinking of is the possibility that someone on the publisher or
subscriber could change the data after the last synchronization and before
the reinitialization. You could prevent this with securite etc and use
RedGate's DataCompare or TableDiff to check if this is a problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks for making that clear. But wait, isn't the "upload pending changes
before reinitialization" option supposed to solve that? Actually I've tried
that and it doesn't seem to be uploading any pending cahanges from my
subscriber. Is that a feature or a bug?
-- Thanks, Oskar.
"Paul Ibison" wrote:

> What I'm thinking of is the possibility that someone on the publisher or
> subscriber could change the data after the last synchronization and before
> the reinitialization. You could prevent this with securite etc and use
> RedGate's DataCompare or TableDiff to check if this is a problem.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
|||If the publication is still active you are 100% correct. I have taken
advantage of this option before and it worked fine - can you explain a
little more about your setup - if the subscriber changes aren't getting
uploaded there must be something particular about the publication (eg is it
filtered statically or dynamically).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Yes, I have parameterized (dynamic) filters for articles and non-overlapping
partitions. Is that a known limitation?
-- Thanks, Oskar
"Paul Ibison" wrote:

> If the publication is still active you are 100% correct. I have taken
> advantage of this option before and it worked fine - can you explain a
> little more about your setup - if the subscriber changes aren't getting
> uploaded there must be something particular about the publication (eg is it
> filtered statically or dynamically).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||By the way I didn't reinitialize the subscription because of adding,
dropping, or changing a parameterized filter in case you suspected that.
"Oskar" wrote:
[vbcol=seagreen]
> Yes, I have parameterized (dynamic) filters for articles and non-overlapping
> partitions. Is that a known limitation?
> -- Thanks, Oskar
> "Paul Ibison" wrote:
|||OK - I'll try to repro tomorrow. Just so I can do it exactly the same way as
you are, the changes on the subscriber which don't get uploaded - are they
'standard' changes or are they changes which make a record change
partitions?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||No, there are no out-of-partition rows. Few more details about the setup I
have:
- filtering is done by "fake" HOST_NAME();
- 2 push subscriptions on MS SQL Server 2005 Express machines
(9.00.2047.00), 1 publisher on MS SQL Server 2005 Standard (9.00.1399.06);
- rows are only inserted;
- 2 MS Active Directory users in Users group: one for snapshot and the other
for all merge agents, both sysadmins on the publisher and one of them
sysadmin on the subscriber;
- non-overlapping partitions;
- tables are either dropped & recreated or kept unchanged during
initialization (I've tried both of these options);
- automatic identity range management;
- subscriptions never expire;
- other publication options more or less at their defaults;
I did an experiment. Stop a merge agent, add some data on a subscriber,
start the merge agent, all added data appears at the publisher. Then I did
another one. Stop a merge agent, add some data on a subscriber, reinitialize
the subscription with the "upload_first" option and generate a new snapshot,
start the merge agent. Depeneding on the "pre_creation_command" publication
option, which in my case was either "drop" or "none", added data is lost or
retained at the subscriber but doesn't appear at the publisher.
Thanks for taking a close look at this.
-- Oskar
"Paul Ibison" wrote:

> OK - I'll try to repro tomorrow. Just so I can do it exactly the same way as
> you are, the changes on the subscriber which don't get uploaded - are they
> 'standard' changes or are they changes which make a record change
> partitions?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

订阅：博文 (Atom)

2012年3月19日星期一

Any way to be notified if database grows?

Any way to be notified if database grows?

Any way to be notified if database grows?

2012年3月11日星期日

any tips for testing a san?

2012年3月6日星期二

Any problems when table isn't recreated during initialization?

Sql ANSI Padding

Blog Archive

About Me