Hi ,
I am using SQL 2000 SP3 2 CPU (Intel II ~1230) with 4 GB RAM .
The server is data warehouse server that load data and served reports.
I am loading data every 15 minute with Balk Insert command into temporarty tables and than insert the data to two fact tables with logic implemented by store procedure.
Each time I am insert new data ( every 15 minutes , for around ~ 4 minute of ~ 250,000 lines) the Disk time is 100% .
I am using RAID 5 with logical partitions for system ,SQL Data file and SQL transaction log file .
In order to solve the Disk Time bottleneck ( the CPU is normal during the insert ~ 40 % but still the duration is too long due to the disk time problem) I though to create separate file group to each fact table and change the Disk configuration from RAID 5 to double RAID 1 ,each one for each File group ( and other disks for the transaction log and system ) .I though that in this way I will be able to use each of the physical disks rather than current RAID 5 .
Any idea ? do you know other option to solve this problem ? what is the reaon for the fact that the Data file that store on RAID 5 do not use the 5 physical Disk headers ?
Thanks in advance
EyalYou didn't mention the "C" word...but I imagine it's in play...where's the data coming from? INSERTS are a logged operation...and 250k of rows, every 15 minutes...is a lot of data...
I would try to figure out a way to perform a nonlogged operation (bulk insert?) and apply the logic against the database...
My own opinion (MOO)
What type of data is this?|||Howdy
Worth removing any indexes on the load tables & then recreating the indexes once data has been loaded?
FWIW
Cheers,
SG.|||Hi no one response regard the Disk configuration .This is my best solution to the Disk time - separate the two Fact table to different phiscal disk means working with two disk header in parallel . !!??|||And you mentioned that you're interested in speed...if you're doing insert a row at a time then that'll be slow, if you're using a cursor, that'll be slow, if your modifying data on import, that'll be slow...
If you want to split the data across attached drives, go ahead, make a patitioned view and go nuts...
Is that the root cause of your problem?
Hard for us to tell until the mind reading machine comes back online...|||I agree with Brett 100%...more info required please
Cheers,
SG
2012年3月29日星期四
2012年3月25日星期日
disk IO and buffer cache hit ratio
Hi Everyone,
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
AramidIf the buffer hit cache ratio dips down that means you're getting cache
misses (cache misses/hits are inversely proportional). That is, the
data page(s) you're looking for in cache is not there so it has to be
retrieved from disk (hence the disk spikes).
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Aramid wrote:
>Hi Everyone,
>I always notice that the buffer cache hit ratio suddenly dips down if
>there's a spike in disk IO (indicated by % disk time or disk queue
>length) and immediately recovers. What's the connection between the
>two or the possible explanation of this behavior?
>Thanks.
>Aramid
>|||Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
Thanks again.
Aramid
On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>If the buffer hit cache ratio dips down that means you're getting cache
>misses (cache misses/hits are inversely proportional). That is, the
>data page(s) you're looking for in cache is not there so it has to be
>retrieved from disk (hence the disk spikes).
>This indicates to me, if it's happening quite often, that you're a bit
>short on memory. You should also watch the "Buffer Manager | Page Life
>Expectancy" perfmon counter (measured in seconds). This will give you
>an idea of roughly how long SQL Server expects the average data page to
>remain in cache before needing to be swapped out (usually due to memory
>pressure). I'd want to keep that figure to be at least 5 minutes but
>ideally it should be higher, like an hour or more, IMO.|||That too indicates you are short on memory. When SQL Server needs to bring
in more data from disk it has to free up existing cache to hold it. This is
reflected in the large dip in PLE as it clears out portions of the cache.
This is normal on an occasional basis but if you see it often that is a sure
sign of lack of memory or poorly tuned queries and database.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.
4ax.com...
> Thanks Mike, that was really helpful. But is it also possible for an
> increase in disk IO (not related to cache lookups) to affect the
> buffer cache ratio?
> Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
> and I noticed that it is constantly increasing over time, then there
> would be a sudden surge of disk IO activity accompanied by a sudden
> drop of the Page Life Expectancy value. Does this indicate SQL's
> process of swapping out infrequently accessed information from cache
> to disk?
> Thanks again.
> Aramid
> On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
> <mike.hodgson@.mallesons.nospam.com> wrote:
>
>|||Aramid wrote:
>Thanks Mike, that was really helpful. But is it also possible for an
>increase in disk IO (not related to cache lookups) to affect the
>buffer cache ratio?
>
No. The buffer cache hit ratio only indicates cache hits or misses -
memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.
>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>and I noticed that it is constantly increasing over time, then there
>would be a sudden surge of disk IO activity accompanied by a sudden
>drop of the Page Life Expectancy value. Does this indicate SQL's
>process of swapping out infrequently accessed information from cache
>to disk?
>
Pretty much, yeah.
*mike hodgson*
blog: http://sqlnerd.blogspot.com|||Hi Andrew,
Thanks for your reply.
In short, sudden clearing of the cache is not really done in regular
intervals (or perhaps in very small amounts?), but rather on an ad-hoc
basis, whenever SQL needs to move in chunks of data from disk. Is
this correct?
How often then is acceptable? Or how can I make this a basis for lack
of memory?
Aramid
On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>That too indicates you are short on memory. When SQL Server needs to bring
>in more data from disk it has to free up existing cache to hold it. This i
s
>reflected in the large dip in PLE as it clears out portions of the cache.
>This is normal on an occasional basis but if you see it often that is a sur
e
>sign of lack of memory or poorly tuned queries and database.|||Thanks Mike, your answers are really helpful.
On Wed, 14 Sep 2005 22:35:21 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>Aramid wrote:
>
>No. The buffer cache hit ratio only indicates cache hits or misses -
>memory only. Sudden non-related disk I/O would probably affect the
>response time from your SQL server but it shouldn't affect the hit/miss
>ratio of the buffer cache.
>
>Pretty much, yeah.|||Well it is normal to see sudden dips occasionally but not on a regular
basis. And the closer the PLE counter gets to 0 when it dips the more dire
the situation. If the buffer cache hit ratio and PLE have frequent dips or
average low values it is a sure sign of memory pressure. But there are lots
of things that can cause this. One is poorly tuned database and queries.
Another is poor plan reuse so you get internal pressure from the procedure
cache. And some others. You need to investigate to see what your system is
really doing. You may have plenty of memory just need to optimize your code
and db calls.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:tkagi1l91cga3tr04s5ropji289drmljjg@.
4ax.com...
> Hi Andrew,
> Thanks for your reply.
> In short, sudden clearing of the cache is not really done in regular
> intervals (or perhaps in very small amounts?), but rather on an ad-hoc
> basis, whenever SQL needs to move in chunks of data from disk. Is
> this correct?
> How often then is acceptable? Or how can I make this a basis for lack
> of memory?
> Aramid
> On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
>
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
AramidIf the buffer hit cache ratio dips down that means you're getting cache
misses (cache misses/hits are inversely proportional). That is, the
data page(s) you're looking for in cache is not there so it has to be
retrieved from disk (hence the disk spikes).
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Aramid wrote:
>Hi Everyone,
>I always notice that the buffer cache hit ratio suddenly dips down if
>there's a spike in disk IO (indicated by % disk time or disk queue
>length) and immediately recovers. What's the connection between the
>two or the possible explanation of this behavior?
>Thanks.
>Aramid
>|||Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
Thanks again.
Aramid
On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>If the buffer hit cache ratio dips down that means you're getting cache
>misses (cache misses/hits are inversely proportional). That is, the
>data page(s) you're looking for in cache is not there so it has to be
>retrieved from disk (hence the disk spikes).
>This indicates to me, if it's happening quite often, that you're a bit
>short on memory. You should also watch the "Buffer Manager | Page Life
>Expectancy" perfmon counter (measured in seconds). This will give you
>an idea of roughly how long SQL Server expects the average data page to
>remain in cache before needing to be swapped out (usually due to memory
>pressure). I'd want to keep that figure to be at least 5 minutes but
>ideally it should be higher, like an hour or more, IMO.|||That too indicates you are short on memory. When SQL Server needs to bring
in more data from disk it has to free up existing cache to hold it. This is
reflected in the large dip in PLE as it clears out portions of the cache.
This is normal on an occasional basis but if you see it often that is a sure
sign of lack of memory or poorly tuned queries and database.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.
4ax.com...
> Thanks Mike, that was really helpful. But is it also possible for an
> increase in disk IO (not related to cache lookups) to affect the
> buffer cache ratio?
> Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
> and I noticed that it is constantly increasing over time, then there
> would be a sudden surge of disk IO activity accompanied by a sudden
> drop of the Page Life Expectancy value. Does this indicate SQL's
> process of swapping out infrequently accessed information from cache
> to disk?
> Thanks again.
> Aramid
> On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
> <mike.hodgson@.mallesons.nospam.com> wrote:
>
>|||Aramid wrote:
>Thanks Mike, that was really helpful. But is it also possible for an
>increase in disk IO (not related to cache lookups) to affect the
>buffer cache ratio?
>
No. The buffer cache hit ratio only indicates cache hits or misses -
memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.
>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>and I noticed that it is constantly increasing over time, then there
>would be a sudden surge of disk IO activity accompanied by a sudden
>drop of the Page Life Expectancy value. Does this indicate SQL's
>process of swapping out infrequently accessed information from cache
>to disk?
>
Pretty much, yeah.
*mike hodgson*
blog: http://sqlnerd.blogspot.com|||Hi Andrew,
Thanks for your reply.
In short, sudden clearing of the cache is not really done in regular
intervals (or perhaps in very small amounts?), but rather on an ad-hoc
basis, whenever SQL needs to move in chunks of data from disk. Is
this correct?
How often then is acceptable? Or how can I make this a basis for lack
of memory?
Aramid
On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>That too indicates you are short on memory. When SQL Server needs to bring
>in more data from disk it has to free up existing cache to hold it. This i
s
>reflected in the large dip in PLE as it clears out portions of the cache.
>This is normal on an occasional basis but if you see it often that is a sur
e
>sign of lack of memory or poorly tuned queries and database.|||Thanks Mike, your answers are really helpful.
On Wed, 14 Sep 2005 22:35:21 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>Aramid wrote:
>
>No. The buffer cache hit ratio only indicates cache hits or misses -
>memory only. Sudden non-related disk I/O would probably affect the
>response time from your SQL server but it shouldn't affect the hit/miss
>ratio of the buffer cache.
>
>Pretty much, yeah.|||Well it is normal to see sudden dips occasionally but not on a regular
basis. And the closer the PLE counter gets to 0 when it dips the more dire
the situation. If the buffer cache hit ratio and PLE have frequent dips or
average low values it is a sure sign of memory pressure. But there are lots
of things that can cause this. One is poorly tuned database and queries.
Another is poor plan reuse so you get internal pressure from the procedure
cache. And some others. You need to investigate to see what your system is
really doing. You may have plenty of memory just need to optimize your code
and db calls.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:tkagi1l91cga3tr04s5ropji289drmljjg@.
4ax.com...
> Hi Andrew,
> Thanks for your reply.
> In short, sudden clearing of the cache is not really done in regular
> intervals (or perhaps in very small amounts?), but rather on an ad-hoc
> basis, whenever SQL needs to move in chunks of data from disk. Is
> this correct?
> How often then is acceptable? Or how can I make this a basis for lack
> of memory?
> Aramid
> On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
>
disk IO and buffer cache hit ratio
Hi Everyone,
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
Aramid
If the buffer hit cache ratio dips down that means you're getting cache
misses (cache misses/hits are inversely proportional). That is, the
data page(s) you're looking for in cache is not there so it has to be
retrieved from disk (hence the disk spikes).
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Aramid wrote:
>Hi Everyone,
>I always notice that the buffer cache hit ratio suddenly dips down if
>there's a spike in disk IO (indicated by % disk time or disk queue
>length) and immediately recovers. What's the connection between the
>two or the possible explanation of this behavior?
>Thanks.
>Aramid
>
|||Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
Thanks again.
Aramid
On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>If the buffer hit cache ratio dips down that means you're getting cache
>misses (cache misses/hits are inversely proportional). That is, the
>data page(s) you're looking for in cache is not there so it has to be
>retrieved from disk (hence the disk spikes).
>This indicates to me, if it's happening quite often, that you're a bit
>short on memory. You should also watch the "Buffer Manager | Page Life
>Expectancy" perfmon counter (measured in seconds). This will give you
>an idea of roughly how long SQL Server expects the average data page to
>remain in cache before needing to be swapped out (usually due to memory
>pressure). I'd want to keep that figure to be at least 5 minutes but
>ideally it should be higher, like an hour or more, IMO.
|||That too indicates you are short on memory. When SQL Server needs to bring
in more data from disk it has to free up existing cache to hold it. This is
reflected in the large dip in PLE as it clears out portions of the cache.
This is normal on an occasional basis but if you see it often that is a sure
sign of lack of memory or poorly tuned queries and database.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com...
> Thanks Mike, that was really helpful. But is it also possible for an
> increase in disk IO (not related to cache lookups) to affect the
> buffer cache ratio?
> Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
> and I noticed that it is constantly increasing over time, then there
> would be a sudden surge of disk IO activity accompanied by a sudden
> drop of the Page Life Expectancy value. Does this indicate SQL's
> process of swapping out infrequently accessed information from cache
> to disk?
> Thanks again.
> Aramid
> On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
> <mike.hodgson@.mallesons.nospam.com> wrote:
>
|||Aramid wrote:
>Thanks Mike, that was really helpful. But is it also possible for an
>increase in disk IO (not related to cache lookups) to affect the
>buffer cache ratio?
>
No. The buffer cache hit ratio only indicates cache hits or misses -
memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.
>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>and I noticed that it is constantly increasing over time, then there
>would be a sudden surge of disk IO activity accompanied by a sudden
>drop of the Page Life Expectancy value. Does this indicate SQL's
>process of swapping out infrequently accessed information from cache
>to disk?
>
Pretty much, yeah.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
|||Hi Andrew,
Thanks for your reply.
In short, sudden clearing of the cache is not really done in regular
intervals (or perhaps in very small amounts?), but rather on an ad-hoc
basis, whenever SQL needs to move in chunks of data from disk. Is
this correct?
How often then is acceptable? Or how can I make this a basis for lack
of memory?
Aramid
On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>That too indicates you are short on memory. When SQL Server needs to bring
>in more data from disk it has to free up existing cache to hold it. This is
>reflected in the large dip in PLE as it clears out portions of the cache.
>This is normal on an occasional basis but if you see it often that is a sure
>sign of lack of memory or poorly tuned queries and database.
|||Thanks Mike, your answers are really helpful.
On Wed, 14 Sep 2005 22:35:21 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>Aramid wrote:
>No. The buffer cache hit ratio only indicates cache hits or misses -
>memory only. Sudden non-related disk I/O would probably affect the
>response time from your SQL server but it shouldn't affect the hit/miss
>ratio of the buffer cache.
>Pretty much, yeah.
|||Well it is normal to see sudden dips occasionally but not on a regular
basis. And the closer the PLE counter gets to 0 when it dips the more dire
the situation. If the buffer cache hit ratio and PLE have frequent dips or
average low values it is a sure sign of memory pressure. But there are lots
of things that can cause this. One is poorly tuned database and queries.
Another is poor plan reuse so you get internal pressure from the procedure
cache. And some others. You need to investigate to see what your system is
really doing. You may have plenty of memory just need to optimize your code
and db calls.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:tkagi1l91cga3tr04s5ropji289drmljjg@.4ax.com...
> Hi Andrew,
> Thanks for your reply.
> In short, sudden clearing of the cache is not really done in regular
> intervals (or perhaps in very small amounts?), but rather on an ad-hoc
> basis, whenever SQL needs to move in chunks of data from disk. Is
> this correct?
> How often then is acceptable? Or how can I make this a basis for lack
> of memory?
> Aramid
> On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
Aramid
If the buffer hit cache ratio dips down that means you're getting cache
misses (cache misses/hits are inversely proportional). That is, the
data page(s) you're looking for in cache is not there so it has to be
retrieved from disk (hence the disk spikes).
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Aramid wrote:
>Hi Everyone,
>I always notice that the buffer cache hit ratio suddenly dips down if
>there's a spike in disk IO (indicated by % disk time or disk queue
>length) and immediately recovers. What's the connection between the
>two or the possible explanation of this behavior?
>Thanks.
>Aramid
>
|||Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
Thanks again.
Aramid
On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>If the buffer hit cache ratio dips down that means you're getting cache
>misses (cache misses/hits are inversely proportional). That is, the
>data page(s) you're looking for in cache is not there so it has to be
>retrieved from disk (hence the disk spikes).
>This indicates to me, if it's happening quite often, that you're a bit
>short on memory. You should also watch the "Buffer Manager | Page Life
>Expectancy" perfmon counter (measured in seconds). This will give you
>an idea of roughly how long SQL Server expects the average data page to
>remain in cache before needing to be swapped out (usually due to memory
>pressure). I'd want to keep that figure to be at least 5 minutes but
>ideally it should be higher, like an hour or more, IMO.
|||That too indicates you are short on memory. When SQL Server needs to bring
in more data from disk it has to free up existing cache to hold it. This is
reflected in the large dip in PLE as it clears out portions of the cache.
This is normal on an occasional basis but if you see it often that is a sure
sign of lack of memory or poorly tuned queries and database.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com...
> Thanks Mike, that was really helpful. But is it also possible for an
> increase in disk IO (not related to cache lookups) to affect the
> buffer cache ratio?
> Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
> and I noticed that it is constantly increasing over time, then there
> would be a sudden surge of disk IO activity accompanied by a sudden
> drop of the Page Life Expectancy value. Does this indicate SQL's
> process of swapping out infrequently accessed information from cache
> to disk?
> Thanks again.
> Aramid
> On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
> <mike.hodgson@.mallesons.nospam.com> wrote:
>
|||Aramid wrote:
>Thanks Mike, that was really helpful. But is it also possible for an
>increase in disk IO (not related to cache lookups) to affect the
>buffer cache ratio?
>
No. The buffer cache hit ratio only indicates cache hits or misses -
memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.
>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>and I noticed that it is constantly increasing over time, then there
>would be a sudden surge of disk IO activity accompanied by a sudden
>drop of the Page Life Expectancy value. Does this indicate SQL's
>process of swapping out infrequently accessed information from cache
>to disk?
>
Pretty much, yeah.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
|||Hi Andrew,
Thanks for your reply.
In short, sudden clearing of the cache is not really done in regular
intervals (or perhaps in very small amounts?), but rather on an ad-hoc
basis, whenever SQL needs to move in chunks of data from disk. Is
this correct?
How often then is acceptable? Or how can I make this a basis for lack
of memory?
Aramid
On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>That too indicates you are short on memory. When SQL Server needs to bring
>in more data from disk it has to free up existing cache to hold it. This is
>reflected in the large dip in PLE as it clears out portions of the cache.
>This is normal on an occasional basis but if you see it often that is a sure
>sign of lack of memory or poorly tuned queries and database.
|||Thanks Mike, your answers are really helpful.
On Wed, 14 Sep 2005 22:35:21 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>Aramid wrote:
>No. The buffer cache hit ratio only indicates cache hits or misses -
>memory only. Sudden non-related disk I/O would probably affect the
>response time from your SQL server but it shouldn't affect the hit/miss
>ratio of the buffer cache.
>Pretty much, yeah.
|||Well it is normal to see sudden dips occasionally but not on a regular
basis. And the closer the PLE counter gets to 0 when it dips the more dire
the situation. If the buffer cache hit ratio and PLE have frequent dips or
average low values it is a sure sign of memory pressure. But there are lots
of things that can cause this. One is poorly tuned database and queries.
Another is poor plan reuse so you get internal pressure from the procedure
cache. And some others. You need to investigate to see what your system is
really doing. You may have plenty of memory just need to optimize your code
and db calls.
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:tkagi1l91cga3tr04s5ropji289drmljjg@.4ax.com...
> Hi Andrew,
> Thanks for your reply.
> In short, sudden clearing of the cache is not really done in regular
> intervals (or perhaps in very small amounts?), but rather on an ad-hoc
> basis, whenever SQL needs to move in chunks of data from disk. Is
> this correct?
> How often then is acceptable? Or how can I make this a basis for lack
> of memory?
> Aramid
> On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
disk IO and buffer cache hit ratio
Hi Everyone,
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
AramidThis is a multi-part message in MIME format.
--000001080309010709050007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
If the buffer hit cache ratio dips down that means you're getting cache
misses (cache misses/hits are inversely proportional). That is, the
data page(s) you're looking for in cache is not there so it has to be
retrieved from disk (hence the disk spikes).
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Aramid wrote:
>Hi Everyone,
>I always notice that the buffer cache hit ratio suddenly dips down if
>there's a spike in disk IO (indicated by % disk time or disk queue
>length) and immediately recovers. What's the connection between the
>two or the possible explanation of this behavior?
>Thanks.
>Aramid
>
--000001080309010709050007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>If the buffer hit cache ratio dips down that means you're getting
cache misses (cache misses/hits are inversely proportional). That is,
the data page(s) you're looking for in cache is not there so it has to
be retrieved from disk (hence the disk spikes).<br>
<br>
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Aramid wrote:
<blockquote cite="mid4k2gi1hl9m577tgoh4vo0n230hg3qes43g@.4ax.com"
type="cite">
<pre wrap="">Hi Everyone,
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
Aramid
</pre>
</blockquote>
</body>
</html>
--000001080309010709050007--|||Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
Thanks again.
Aramid
On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>If the buffer hit cache ratio dips down that means you're getting cache
>misses (cache misses/hits are inversely proportional). That is, the
>data page(s) you're looking for in cache is not there so it has to be
>retrieved from disk (hence the disk spikes).
>This indicates to me, if it's happening quite often, that you're a bit
>short on memory. You should also watch the "Buffer Manager | Page Life
>Expectancy" perfmon counter (measured in seconds). This will give you
>an idea of roughly how long SQL Server expects the average data page to
>remain in cache before needing to be swapped out (usually due to memory
>pressure). I'd want to keep that figure to be at least 5 minutes but
>ideally it should be higher, like an hour or more, IMO.|||That too indicates you are short on memory. When SQL Server needs to bring
in more data from disk it has to free up existing cache to hold it. This is
reflected in the large dip in PLE as it clears out portions of the cache.
This is normal on an occasional basis but if you see it often that is a sure
sign of lack of memory or poorly tuned queries and database.
--
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com...
> Thanks Mike, that was really helpful. But is it also possible for an
> increase in disk IO (not related to cache lookups) to affect the
> buffer cache ratio?
> Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
> and I noticed that it is constantly increasing over time, then there
> would be a sudden surge of disk IO activity accompanied by a sudden
> drop of the Page Life Expectancy value. Does this indicate SQL's
> process of swapping out infrequently accessed information from cache
> to disk?
> Thanks again.
> Aramid
> On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
> <mike.hodgson@.mallesons.nospam.com> wrote:
>>If the buffer hit cache ratio dips down that means you're getting cache
>>misses (cache misses/hits are inversely proportional). That is, the
>>data page(s) you're looking for in cache is not there so it has to be
>>retrieved from disk (hence the disk spikes).
>>This indicates to me, if it's happening quite often, that you're a bit
>>short on memory. You should also watch the "Buffer Manager | Page Life
>>Expectancy" perfmon counter (measured in seconds). This will give you
>>an idea of roughly how long SQL Server expects the average data page to
>>remain in cache before needing to be swapped out (usually due to memory
>>pressure). I'd want to keep that figure to be at least 5 minutes but
>>ideally it should be higher, like an hour or more, IMO.
>|||This is a multi-part message in MIME format.
--050004050204040201060302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Aramid wrote:
>Thanks Mike, that was really helpful. But is it also possible for an
>increase in disk IO (not related to cache lookups) to affect the
>buffer cache ratio?
>
No. The buffer cache hit ratio only indicates cache hits or misses -
memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.
>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>and I noticed that it is constantly increasing over time, then there
>would be a sudden surge of disk IO activity accompanied by a sudden
>drop of the Page Life Expectancy value. Does this indicate SQL's
>process of swapping out infrequently accessed information from cache
>to disk?
>
Pretty much, yeah.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
--050004050204040201060302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
Aramid wrote:
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<pre wrap="">Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
</pre>
</blockquote>
<tt>No. The buffer cache hit ratio only indicates cache hits or misses
- memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.<br>
</tt><br>
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<pre wrap="">Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
</pre>
</blockquote>
<tt>Pretty much, yeah.</tt><br>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--</font></span><br>
<span lang="en-au"></span> <b><span lang="en-au"><font face="Tahoma"
size="2">mike hodgson</font></span></b><span lang="en-au"></span><br>
<span lang="en-au"> <font face="Tahoma" size="2">blog:</font><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span></p>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span></p>
</div>
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<div class="moz-signature">
<p> </p>
</div>
<pre wrap=""></pre>
</blockquote>
</body>
</html>
--050004050204040201060302--|||Hi Andrew,
Thanks for your reply.
In short, sudden clearing of the cache is not really done in regular
intervals (or perhaps in very small amounts?), but rather on an ad-hoc
basis, whenever SQL needs to move in chunks of data from disk. Is
this correct?
How often then is acceptable? Or how can I make this a basis for lack
of memory?
Aramid
On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>That too indicates you are short on memory. When SQL Server needs to bring
>in more data from disk it has to free up existing cache to hold it. This is
>reflected in the large dip in PLE as it clears out portions of the cache.
>This is normal on an occasional basis but if you see it often that is a sure
>sign of lack of memory or poorly tuned queries and database.|||Thanks Mike, your answers are really helpful.
On Wed, 14 Sep 2005 22:35:21 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>Aramid wrote:
>>Thanks Mike, that was really helpful. But is it also possible for an
>>increase in disk IO (not related to cache lookups) to affect the
>>buffer cache ratio?
>>
>No. The buffer cache hit ratio only indicates cache hits or misses -
>memory only. Sudden non-related disk I/O would probably affect the
>response time from your SQL server but it shouldn't affect the hit/miss
>ratio of the buffer cache.
>>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>>and I noticed that it is constantly increasing over time, then there
>>would be a sudden surge of disk IO activity accompanied by a sudden
>>drop of the Page Life Expectancy value. Does this indicate SQL's
>>process of swapping out infrequently accessed information from cache
>>to disk?
>>
>Pretty much, yeah.|||Well it is normal to see sudden dips occasionally but not on a regular
basis. And the closer the PLE counter gets to 0 when it dips the more dire
the situation. If the buffer cache hit ratio and PLE have frequent dips or
average low values it is a sure sign of memory pressure. But there are lots
of things that can cause this. One is poorly tuned database and queries.
Another is poor plan reuse so you get internal pressure from the procedure
cache. And some others. You need to investigate to see what your system is
really doing. You may have plenty of memory just need to optimize your code
and db calls.
--
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:tkagi1l91cga3tr04s5ropji289drmljjg@.4ax.com...
> Hi Andrew,
> Thanks for your reply.
> In short, sudden clearing of the cache is not really done in regular
> intervals (or perhaps in very small amounts?), but rather on an ad-hoc
> basis, whenever SQL needs to move in chunks of data from disk. Is
> this correct?
> How often then is acceptable? Or how can I make this a basis for lack
> of memory?
> Aramid
> On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>That too indicates you are short on memory. When SQL Server needs to
>>bring
>>in more data from disk it has to free up existing cache to hold it. This
>>is
>>reflected in the large dip in PLE as it clears out portions of the cache.
>>This is normal on an occasional basis but if you see it often that is a
>>sure
>>sign of lack of memory or poorly tuned queries and database.
>
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
AramidThis is a multi-part message in MIME format.
--000001080309010709050007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
If the buffer hit cache ratio dips down that means you're getting cache
misses (cache misses/hits are inversely proportional). That is, the
data page(s) you're looking for in cache is not there so it has to be
retrieved from disk (hence the disk spikes).
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Aramid wrote:
>Hi Everyone,
>I always notice that the buffer cache hit ratio suddenly dips down if
>there's a spike in disk IO (indicated by % disk time or disk queue
>length) and immediately recovers. What's the connection between the
>two or the possible explanation of this behavior?
>Thanks.
>Aramid
>
--000001080309010709050007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>If the buffer hit cache ratio dips down that means you're getting
cache misses (cache misses/hits are inversely proportional). That is,
the data page(s) you're looking for in cache is not there so it has to
be retrieved from disk (hence the disk spikes).<br>
<br>
This indicates to me, if it's happening quite often, that you're a bit
short on memory. You should also watch the "Buffer Manager | Page Life
Expectancy" perfmon counter (measured in seconds). This will give you
an idea of roughly how long SQL Server expects the average data page to
remain in cache before needing to be swapped out (usually due to memory
pressure). I'd want to keep that figure to be at least 5 minutes but
ideally it should be higher, like an hour or more, IMO.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Aramid wrote:
<blockquote cite="mid4k2gi1hl9m577tgoh4vo0n230hg3qes43g@.4ax.com"
type="cite">
<pre wrap="">Hi Everyone,
I always notice that the buffer cache hit ratio suddenly dips down if
there's a spike in disk IO (indicated by % disk time or disk queue
length) and immediately recovers. What's the connection between the
two or the possible explanation of this behavior?
Thanks.
Aramid
</pre>
</blockquote>
</body>
</html>
--000001080309010709050007--|||Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
Thanks again.
Aramid
On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>If the buffer hit cache ratio dips down that means you're getting cache
>misses (cache misses/hits are inversely proportional). That is, the
>data page(s) you're looking for in cache is not there so it has to be
>retrieved from disk (hence the disk spikes).
>This indicates to me, if it's happening quite often, that you're a bit
>short on memory. You should also watch the "Buffer Manager | Page Life
>Expectancy" perfmon counter (measured in seconds). This will give you
>an idea of roughly how long SQL Server expects the average data page to
>remain in cache before needing to be swapped out (usually due to memory
>pressure). I'd want to keep that figure to be at least 5 minutes but
>ideally it should be higher, like an hour or more, IMO.|||That too indicates you are short on memory. When SQL Server needs to bring
in more data from disk it has to free up existing cache to hold it. This is
reflected in the large dip in PLE as it clears out portions of the cache.
This is normal on an occasional basis but if you see it often that is a sure
sign of lack of memory or poorly tuned queries and database.
--
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com...
> Thanks Mike, that was really helpful. But is it also possible for an
> increase in disk IO (not related to cache lookups) to affect the
> buffer cache ratio?
> Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
> and I noticed that it is constantly increasing over time, then there
> would be a sudden surge of disk IO activity accompanied by a sudden
> drop of the Page Life Expectancy value. Does this indicate SQL's
> process of swapping out infrequently accessed information from cache
> to disk?
> Thanks again.
> Aramid
> On Wed, 14 Sep 2005 21:51:40 +1000, Mike Hodgson
> <mike.hodgson@.mallesons.nospam.com> wrote:
>>If the buffer hit cache ratio dips down that means you're getting cache
>>misses (cache misses/hits are inversely proportional). That is, the
>>data page(s) you're looking for in cache is not there so it has to be
>>retrieved from disk (hence the disk spikes).
>>This indicates to me, if it's happening quite often, that you're a bit
>>short on memory. You should also watch the "Buffer Manager | Page Life
>>Expectancy" perfmon counter (measured in seconds). This will give you
>>an idea of roughly how long SQL Server expects the average data page to
>>remain in cache before needing to be swapped out (usually due to memory
>>pressure). I'd want to keep that figure to be at least 5 minutes but
>>ideally it should be higher, like an hour or more, IMO.
>|||This is a multi-part message in MIME format.
--050004050204040201060302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Aramid wrote:
>Thanks Mike, that was really helpful. But is it also possible for an
>increase in disk IO (not related to cache lookups) to affect the
>buffer cache ratio?
>
No. The buffer cache hit ratio only indicates cache hits or misses -
memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.
>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>and I noticed that it is constantly increasing over time, then there
>would be a sudden surge of disk IO activity accompanied by a sudden
>drop of the Page Life Expectancy value. Does this indicate SQL's
>process of swapping out infrequently accessed information from cache
>to disk?
>
Pretty much, yeah.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
--050004050204040201060302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
Aramid wrote:
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<pre wrap="">Thanks Mike, that was really helpful. But is it also possible for an
increase in disk IO (not related to cache lookups) to affect the
buffer cache ratio?
</pre>
</blockquote>
<tt>No. The buffer cache hit ratio only indicates cache hits or misses
- memory only. Sudden non-related disk I/O would probably affect the
response time from your SQL server but it shouldn't affect the hit/miss
ratio of the buffer cache.<br>
</tt><br>
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<pre wrap="">Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
and I noticed that it is constantly increasing over time, then there
would be a sudden surge of disk IO activity accompanied by a sudden
drop of the Page Life Expectancy value. Does this indicate SQL's
process of swapping out infrequently accessed information from cache
to disk?
</pre>
</blockquote>
<tt>Pretty much, yeah.</tt><br>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--</font></span><br>
<span lang="en-au"></span> <b><span lang="en-au"><font face="Tahoma"
size="2">mike hodgson</font></span></b><span lang="en-au"></span><br>
<span lang="en-au"> <font face="Tahoma" size="2">blog:</font><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span></p>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span></p>
</div>
<blockquote cite="mid5j4gi1h4o85hl8to4k2tqou8f5jeet99sp@.4ax.com"
type="cite">
<div class="moz-signature">
<p> </p>
</div>
<pre wrap=""></pre>
</blockquote>
</body>
</html>
--050004050204040201060302--|||Hi Andrew,
Thanks for your reply.
In short, sudden clearing of the cache is not really done in regular
intervals (or perhaps in very small amounts?), but rather on an ad-hoc
basis, whenever SQL needs to move in chunks of data from disk. Is
this correct?
How often then is acceptable? Or how can I make this a basis for lack
of memory?
Aramid
On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>That too indicates you are short on memory. When SQL Server needs to bring
>in more data from disk it has to free up existing cache to hold it. This is
>reflected in the large dip in PLE as it clears out portions of the cache.
>This is normal on an occasional basis but if you see it often that is a sure
>sign of lack of memory or poorly tuned queries and database.|||Thanks Mike, your answers are really helpful.
On Wed, 14 Sep 2005 22:35:21 +1000, Mike Hodgson
<mike.hodgson@.mallesons.nospam.com> wrote:
>Aramid wrote:
>>Thanks Mike, that was really helpful. But is it also possible for an
>>increase in disk IO (not related to cache lookups) to affect the
>>buffer cache ratio?
>>
>No. The buffer cache hit ratio only indicates cache hits or misses -
>memory only. Sudden non-related disk I/O would probably affect the
>response time from your SQL server but it shouldn't affect the hit/miss
>ratio of the buffer cache.
>>Second, I added the "Buffer Manager | Page Life Expectancy" monitor,
>>and I noticed that it is constantly increasing over time, then there
>>would be a sudden surge of disk IO activity accompanied by a sudden
>>drop of the Page Life Expectancy value. Does this indicate SQL's
>>process of swapping out infrequently accessed information from cache
>>to disk?
>>
>Pretty much, yeah.|||Well it is normal to see sudden dips occasionally but not on a regular
basis. And the closer the PLE counter gets to 0 when it dips the more dire
the situation. If the buffer cache hit ratio and PLE have frequent dips or
average low values it is a sure sign of memory pressure. But there are lots
of things that can cause this. One is poorly tuned database and queries.
Another is poor plan reuse so you get internal pressure from the procedure
cache. And some others. You need to investigate to see what your system is
really doing. You may have plenty of memory just need to optimize your code
and db calls.
--
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:tkagi1l91cga3tr04s5ropji289drmljjg@.4ax.com...
> Hi Andrew,
> Thanks for your reply.
> In short, sudden clearing of the cache is not really done in regular
> intervals (or perhaps in very small amounts?), but rather on an ad-hoc
> basis, whenever SQL needs to move in chunks of data from disk. Is
> this correct?
> How often then is acceptable? Or how can I make this a basis for lack
> of memory?
> Aramid
> On Wed, 14 Sep 2005 08:28:34 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>That too indicates you are short on memory. When SQL Server needs to
>>bring
>>in more data from disk it has to free up existing cache to hold it. This
>>is
>>reflected in the large dip in PLE as it clears out portions of the cache.
>>This is normal on an occasional basis but if you see it often that is a
>>sure
>>sign of lack of memory or poorly tuned queries and database.
>
2012年3月20日星期二
Discover relationships between existing tables of a database
Hello Friends,
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.Problem is, without any relational integrity and constraints designed into the database, it probably already contains a lot of data that violates the logical relationships. That makes it impossible for any tool to definitively say what the relationships should be based solely upon the existing data.
I have a script that finds natural keys within a table, which you can use to set the primary key, but that's about it.
Chances are, 10% of your time is going to be occupied with finding out what the relationships are supposed to be, while 90% will involve fixing the bad data you find.
And this: "Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables" is total bull. They are just bad developers. I can set a constraint or a foreign key in 30 seconds. They just didn't want to be bothered taking the time to make sure their code submitted correct data to the database, and so they allowed the database to accept any old crap that is sent to it. That's why you have a mess on your hands.|||I used ERWin in order to deduce references. But unfortunately even that didn't suggest much. ERWin tries to deduce what would be relationships between tables. I guess now I have to use logic in order to figure out what the relationships would be.
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.Problem is, without any relational integrity and constraints designed into the database, it probably already contains a lot of data that violates the logical relationships. That makes it impossible for any tool to definitively say what the relationships should be based solely upon the existing data.
I have a script that finds natural keys within a table, which you can use to set the primary key, but that's about it.
Chances are, 10% of your time is going to be occupied with finding out what the relationships are supposed to be, while 90% will involve fixing the bad data you find.
And this: "Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables" is total bull. They are just bad developers. I can set a constraint or a foreign key in 30 seconds. They just didn't want to be bothered taking the time to make sure their code submitted correct data to the database, and so they allowed the database to accept any old crap that is sent to it. That's why you have a mess on your hands.|||I used ERWin in order to deduce references. But unfortunately even that didn't suggest much. ERWin tries to deduce what would be relationships between tables. I guess now I have to use logic in order to figure out what the relationships would be.
Disconnects from Sql Server
I have a VPN connection to my Sql Server at work.
It works fine most of the time. I actually use my Windows 2000 Server as my
client from home.
What happens is that I will usually have 2 instances of Sql Server EM ,
Query Analyser and the Profiler running at one time. Normally, there is no
problem. But then I will lose all my connections. Even if I restart the
programs it won't reconnect for about 3 or 4 minutes. Then it does and
everything is fine for awhile.
I find that on XP, I can't seem to get it to reconnect until I reboot the
machine.
It has nothing to do with actual connection between my machine at home and
the work machines through the VPN, as I can ping the machines at work fine.
Anyone know what could be causing this problem?
Thanks,
TomHi
Check the SQL Server Event log for any entries at the time.
What happens when you open a terminal server session against your server,
keep it open and use the SQL Server tools on your machine?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tshad" <tfs@.dslextreme.com> wrote in message
news:%23UvrGF7oFHA.1048@.tk2msftngp13.phx.gbl...
>I have a VPN connection to my Sql Server at work.
> It works fine most of the time. I actually use my Windows 2000 Server as
> my
> client from home.
> What happens is that I will usually have 2 instances of Sql Server EM ,
> Query Analyser and the Profiler running at one time. Normally, there is
> no
> problem. But then I will lose all my connections. Even if I restart the
> programs it won't reconnect for about 3 or 4 minutes. Then it does and
> everything is fine for awhile.
> I find that on XP, I can't seem to get it to reconnect until I reboot the
> machine.
> It has nothing to do with actual connection between my machine at home and
> the work machines through the VPN, as I can ping the machines at work
> fine.
> Anyone know what could be causing this problem?
> Thanks,
> Tom
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OLwc%23N7oFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hi
> Check the SQL Server Event log for any entries at the time.
> What happens when you open a terminal server session against your server,
> keep it open and use the SQL Server tools on your machine?
Actually, I have another program (or 2) also accessing the server fine, just
Sql Server seems to have a problem.
Tom
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "tshad" <tfs@.dslextreme.com> wrote in message
> news:%23UvrGF7oFHA.1048@.tk2msftngp13.phx.gbl...
as
the
the
and
>sql
It works fine most of the time. I actually use my Windows 2000 Server as my
client from home.
What happens is that I will usually have 2 instances of Sql Server EM ,
Query Analyser and the Profiler running at one time. Normally, there is no
problem. But then I will lose all my connections. Even if I restart the
programs it won't reconnect for about 3 or 4 minutes. Then it does and
everything is fine for awhile.
I find that on XP, I can't seem to get it to reconnect until I reboot the
machine.
It has nothing to do with actual connection between my machine at home and
the work machines through the VPN, as I can ping the machines at work fine.
Anyone know what could be causing this problem?
Thanks,
TomHi
Check the SQL Server Event log for any entries at the time.
What happens when you open a terminal server session against your server,
keep it open and use the SQL Server tools on your machine?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tshad" <tfs@.dslextreme.com> wrote in message
news:%23UvrGF7oFHA.1048@.tk2msftngp13.phx.gbl...
>I have a VPN connection to my Sql Server at work.
> It works fine most of the time. I actually use my Windows 2000 Server as
> my
> client from home.
> What happens is that I will usually have 2 instances of Sql Server EM ,
> Query Analyser and the Profiler running at one time. Normally, there is
> no
> problem. But then I will lose all my connections. Even if I restart the
> programs it won't reconnect for about 3 or 4 minutes. Then it does and
> everything is fine for awhile.
> I find that on XP, I can't seem to get it to reconnect until I reboot the
> machine.
> It has nothing to do with actual connection between my machine at home and
> the work machines through the VPN, as I can ping the machines at work
> fine.
> Anyone know what could be causing this problem?
> Thanks,
> Tom
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OLwc%23N7oFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hi
> Check the SQL Server Event log for any entries at the time.
> What happens when you open a terminal server session against your server,
> keep it open and use the SQL Server tools on your machine?
Actually, I have another program (or 2) also accessing the server fine, just
Sql Server seems to have a problem.
Tom
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "tshad" <tfs@.dslextreme.com> wrote in message
> news:%23UvrGF7oFHA.1048@.tk2msftngp13.phx.gbl...
as
the
the
and
>sql
2012年3月7日星期三
disappearing data
I am not sure where to put this but correct me if i'm in the wrong forum.
I've been working on this database for some time now. It's an Access 2003 database with a SQL backend. Everything was going well until one of the users noticed her data was disappearing after she send an email. The database has a memo field that gets pasted in the body of an email when it is created. Sometimes, when the user sends an email and goes back into the record, the memo field is blank. I know the field has data in it at the time of the email because it will give you an error if the field is null and you won't be able to send it. And, it goes into a history table with everything the user does with each record. I've checked this history table and it shows that all records in question, an email was sent. I have no idea why the data would disappear in this field. It does not happen all the time. I've performed the same steps and I can't duplicate the problem. There's no code associated with the memo field that deletes it after sending the email. Can anyone think of anything that would cause this?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||I don't know what that is?|||Hi,
you mentioned that the data from the record is pasted into an email, this sounds like manual work for me, so i suggested that they do that on their own and not doing it automatically with an application. Therefore I assumed that they might not copied the data (STRG-C) from the memo field as they cut the data (STRG-X) from the memo field to paste it (STRG-V) within the email. Did I understand you right, or what is the actual problem ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Disappearing data
We have been running an ETL process to load data into a SQL Server database for quite some time. Sometimes, for no logical reason, entire tables will get truncated. We took all ETL processes out of the scheduler and instead loaded the tables manually.
The tables are still getting truncated, even with no one on the box, which leads to the notion it was not the ETL process doing the truncating. We're having trouble looking at the logs to determine why these tables are getting truncated.
Has anyone experienced a spontaneous truncating/deletion of data within a subset of tables? Could this be a virus? We thought about attaching a trigger to one of the tables, writing some associated info into another table, to see if we could get a bette
r probe. Any ideas? This is driving us nuts!
DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
The tables are still getting truncated, even with no one on the box, which leads to the notion it was not the ETL process doing the truncating. We're having trouble looking at the logs to determine why these tables are getting truncated.
Has anyone experienced a spontaneous truncating/deletion of data within a subset of tables? Could this be a virus? We thought about attaching a trigger to one of the tables, writing some associated info into another table, to see if we could get a bette
r probe. Any ideas? This is driving us nuts!
DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
Disappearing data
We have been running an ETL process to load data into a SQL Server database for quite some time. Sometimes, for no logical reason, entire tables will get truncated. We took all ETL processes out of the scheduler and instead loaded the tables manually. The tables are still getting truncated, even with no one on the box, which leads to the notion it was not the ETL process doing the truncating. We're having trouble looking at the logs to determine why these tables are getting truncated.
Has anyone experienced a spontaneous truncating/deletion of data within a subset of tables? Could this be a virus? We thought about attaching a trigger to one of the tables, writing some associated info into another table, to see if we could get a better probe. Any ideas? This is driving us nuts!DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
Has anyone experienced a spontaneous truncating/deletion of data within a subset of tables? Could this be a virus? We thought about attaching a trigger to one of the tables, writing some associated info into another table, to see if we could get a better probe. Any ideas? This is driving us nuts!DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
Disappearing data
We have been running an ETL process to load data into a SQL Server database
for quite some time. Sometimes, for no logical reason, entire tables will g
et truncated. We took all ETL processes out of the scheduler and instead lo
aded the tables manually.
The tables are still getting truncated, even with no one on the box, which l
eads to the notion it was not the ETL process doing the truncating. We're h
aving trouble looking at the logs to determine why these tables are getting
truncated.
Has anyone experienced a spontaneous truncating/deletion of data within a su
bset of tables? Could this be a virus? We thought about attaching a trigge
r to one of the tables, writing some associated info into another table, to
see if we could get a bette
r probe. Any ideas? This is driving us nuts!DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
for quite some time. Sometimes, for no logical reason, entire tables will g
et truncated. We took all ETL processes out of the scheduler and instead lo
aded the tables manually.
The tables are still getting truncated, even with no one on the box, which l
eads to the notion it was not the ETL process doing the truncating. We're h
aving trouble looking at the logs to determine why these tables are getting
truncated.
Has anyone experienced a spontaneous truncating/deletion of data within a su
bset of tables? Could this be a virus? We thought about attaching a trigge
r to one of the tables, writing some associated info into another table, to
see if we could get a bette
r probe. Any ideas? This is driving us nuts!DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
2012年2月25日星期六
Disabling 'Export to excel' option when record count >5000 in a re
Hi,
I have a report that has two drill downs that drill to about 5000 records
and due to this I am having time out problems while exporting to excel. For
now, we have worked around the timeout problem but what I would like to know
is whether or not there is a way to disable the excel option in the export
drop down list - only when the SQL report generates like more than 5000 rows.
The reason to disable being, the RS/RM is consuming CPU like crazy and its
repurcussions are unbearable.Hi,
The only thing you can do is to disable Excel export server wide. This you
can do in the RSReportServer.config and add Visible="false" to the element of
the Excel export.
Unfortunately it isn't possible to set it per report or even dynamic.
Jan Pieter Posthuma
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.|||"Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com> wrote in
message news:B18CDAAB-35F2-4CA6-AC13-EC30640558EA@.microsoft.com...
> Hi,
> The only thing you can do is to disable Excel export server wide. This you
> can do in the RSReportServer.config and add Visible="false" to the element
> of
> the Excel export.
> Unfortunately it isn't possible to set it per report or even dynamic.
>
If you take a short peek into the generated script when you activate the
toolbar, you can create your own 'export' button with or without excel
export very easily (rs:format=EXCEL)
I modified the ReportViewer sample to display a small popup toolbar with:
export the Excel, PDF and Print on it (takes up way less space then the
standard toolbar)
Alfred|||Hey Alfred,
Can you tell me how you achieved creating your own export to excel bar:
Thanks
Balaji
"Smeenk, A.D." wrote:
> "Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com> wrote in
> message news:B18CDAAB-35F2-4CA6-AC13-EC30640558EA@.microsoft.com...
> > Hi,
> >
> > The only thing you can do is to disable Excel export server wide. This you
> > can do in the RSReportServer.config and add Visible="false" to the element
> > of
> > the Excel export.
> >
> > Unfortunately it isn't possible to set it per report or even dynamic.
> >
> If you take a short peek into the generated script when you activate the
> toolbar, you can create your own 'export' button with or without excel
> export very easily (rs:format=EXCEL)
> I modified the ReportViewer sample to display a small popup toolbar with:
> export the Excel, PDF and Print on it (takes up way less space then the
> standard toolbar)
> Alfred
>
>|||"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:29713B57-0C78-4AB9-B7BE-8E5234D59809@.microsoft.com...
> Hey Alfred,
> Can you tell me how you achieved creating your own export to excel bar:
>
Like I said, I modifed the ReportViewer sample, its (by default) installed
here:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Applications\ReportViewer
I modified the properties and added 'small' to the ToolBar options, and when
selected I generate different code. I add an extra table around the iframe
and use a div to display a popup menu like when you hover over an image in
IE. The 'onclick' of the images activates a new window with the requested
export of the report (actually a re-render of the report I think, I didn't
add the session ID)
Alfred|||Instead of disabling export to Excel you might want to add a link at the top
of the page that says Export Data. Use jump to URL and specify CSV and ASCII
(the CSV export does Unicode and Excel doesn't handle unicode CSV). By
rendering in ASCII CSV Excel comes up automatically and this is very very
fast. I had an Excel export that took 5 minutes and brought the server to
its knees go to 10 seconds.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smeenk, A.D." <alfreds@.dbs-at-nospam-at-nl> wrote in message
news:uX66yHuVFHA.2196@.TK2MSFTNGP09.phx.gbl...
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:29713B57-0C78-4AB9-B7BE-8E5234D59809@.microsoft.com...
>> Hey Alfred,
>> Can you tell me how you achieved creating your own export to excel bar:
> Like I said, I modifed the ReportViewer sample, its (by default) installed
> here:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Applications\ReportViewer
> I modified the properties and added 'small' to the ToolBar options, and
> when selected I generate different code. I add an extra table around the
> iframe and use a div to display a popup menu like when you hover over an
> image in IE. The 'onclick' of the images activates a new window with the
> requested export of the report (actually a re-render of the report I
> think, I didn't add the session ID)
> Alfred
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23HdntxuVFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Instead of disabling export to Excel you might want to add a link at the
> top of the page that says Export Data. Use jump to URL and specify CSV and
> ASCII (the CSV export does Unicode and Excel doesn't handle unicode CSV).
> By rendering in ASCII CSV Excel comes up automatically and this is very
> very fast. I had an Excel export that took 5 minutes and brought the
> server to its knees go to 10 seconds.
>
That 'export to CSV' should 'officially' be on the right-click menu when you
view a table layout report part, now for some reason that does not function
on all machines... any idea's?
Yes same report, all machines Windows XP SP2, IE 6.
With one person the option re-appeared after a reset of the local intranet
security to default...
Alfred|||No idea about that. Note that the export to CSV you are talking about
exports as Unicode and Excel will bring it all together in a single column.
Bruce L-C
"Smeenk, A.D." <alfreds@.dbs-at-nospam-at-nl> wrote in message
news:OFAlFu4VFHA.1452@.TK2MSFTNGP14.phx.gbl...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23HdntxuVFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Instead of disabling export to Excel you might want to add a link at the
>> top of the page that says Export Data. Use jump to URL and specify CSV
>> and ASCII (the CSV export does Unicode and Excel doesn't handle unicode
>> CSV). By rendering in ASCII CSV Excel comes up automatically and this is
>> very very fast. I had an Excel export that took 5 minutes and brought the
>> server to its knees go to 10 seconds.
> That 'export to CSV' should 'officially' be on the right-click menu when
> you view a table layout report part, now for some reason that does not
> function on all machines... any idea's?
> Yes same report, all machines Windows XP SP2, IE 6.
> With one person the option re-appeared after a reset of the local intranet
> security to default...
> Alfred
>|||Why don't you increase the timeout?
Farahmand
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.|||Why don't you increase the timeout?
Farahmand
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.
I have a report that has two drill downs that drill to about 5000 records
and due to this I am having time out problems while exporting to excel. For
now, we have worked around the timeout problem but what I would like to know
is whether or not there is a way to disable the excel option in the export
drop down list - only when the SQL report generates like more than 5000 rows.
The reason to disable being, the RS/RM is consuming CPU like crazy and its
repurcussions are unbearable.Hi,
The only thing you can do is to disable Excel export server wide. This you
can do in the RSReportServer.config and add Visible="false" to the element of
the Excel export.
Unfortunately it isn't possible to set it per report or even dynamic.
Jan Pieter Posthuma
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.|||"Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com> wrote in
message news:B18CDAAB-35F2-4CA6-AC13-EC30640558EA@.microsoft.com...
> Hi,
> The only thing you can do is to disable Excel export server wide. This you
> can do in the RSReportServer.config and add Visible="false" to the element
> of
> the Excel export.
> Unfortunately it isn't possible to set it per report or even dynamic.
>
If you take a short peek into the generated script when you activate the
toolbar, you can create your own 'export' button with or without excel
export very easily (rs:format=EXCEL)
I modified the ReportViewer sample to display a small popup toolbar with:
export the Excel, PDF and Print on it (takes up way less space then the
standard toolbar)
Alfred|||Hey Alfred,
Can you tell me how you achieved creating your own export to excel bar:
Thanks
Balaji
"Smeenk, A.D." wrote:
> "Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com> wrote in
> message news:B18CDAAB-35F2-4CA6-AC13-EC30640558EA@.microsoft.com...
> > Hi,
> >
> > The only thing you can do is to disable Excel export server wide. This you
> > can do in the RSReportServer.config and add Visible="false" to the element
> > of
> > the Excel export.
> >
> > Unfortunately it isn't possible to set it per report or even dynamic.
> >
> If you take a short peek into the generated script when you activate the
> toolbar, you can create your own 'export' button with or without excel
> export very easily (rs:format=EXCEL)
> I modified the ReportViewer sample to display a small popup toolbar with:
> export the Excel, PDF and Print on it (takes up way less space then the
> standard toolbar)
> Alfred
>
>|||"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:29713B57-0C78-4AB9-B7BE-8E5234D59809@.microsoft.com...
> Hey Alfred,
> Can you tell me how you achieved creating your own export to excel bar:
>
Like I said, I modifed the ReportViewer sample, its (by default) installed
here:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Applications\ReportViewer
I modified the properties and added 'small' to the ToolBar options, and when
selected I generate different code. I add an extra table around the iframe
and use a div to display a popup menu like when you hover over an image in
IE. The 'onclick' of the images activates a new window with the requested
export of the report (actually a re-render of the report I think, I didn't
add the session ID)
Alfred|||Instead of disabling export to Excel you might want to add a link at the top
of the page that says Export Data. Use jump to URL and specify CSV and ASCII
(the CSV export does Unicode and Excel doesn't handle unicode CSV). By
rendering in ASCII CSV Excel comes up automatically and this is very very
fast. I had an Excel export that took 5 minutes and brought the server to
its knees go to 10 seconds.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smeenk, A.D." <alfreds@.dbs-at-nospam-at-nl> wrote in message
news:uX66yHuVFHA.2196@.TK2MSFTNGP09.phx.gbl...
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:29713B57-0C78-4AB9-B7BE-8E5234D59809@.microsoft.com...
>> Hey Alfred,
>> Can you tell me how you achieved creating your own export to excel bar:
> Like I said, I modifed the ReportViewer sample, its (by default) installed
> here:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Applications\ReportViewer
> I modified the properties and added 'small' to the ToolBar options, and
> when selected I generate different code. I add an extra table around the
> iframe and use a div to display a popup menu like when you hover over an
> image in IE. The 'onclick' of the images activates a new window with the
> requested export of the report (actually a re-render of the report I
> think, I didn't add the session ID)
> Alfred
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23HdntxuVFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Instead of disabling export to Excel you might want to add a link at the
> top of the page that says Export Data. Use jump to URL and specify CSV and
> ASCII (the CSV export does Unicode and Excel doesn't handle unicode CSV).
> By rendering in ASCII CSV Excel comes up automatically and this is very
> very fast. I had an Excel export that took 5 minutes and brought the
> server to its knees go to 10 seconds.
>
That 'export to CSV' should 'officially' be on the right-click menu when you
view a table layout report part, now for some reason that does not function
on all machines... any idea's?
Yes same report, all machines Windows XP SP2, IE 6.
With one person the option re-appeared after a reset of the local intranet
security to default...
Alfred|||No idea about that. Note that the export to CSV you are talking about
exports as Unicode and Excel will bring it all together in a single column.
Bruce L-C
"Smeenk, A.D." <alfreds@.dbs-at-nospam-at-nl> wrote in message
news:OFAlFu4VFHA.1452@.TK2MSFTNGP14.phx.gbl...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23HdntxuVFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Instead of disabling export to Excel you might want to add a link at the
>> top of the page that says Export Data. Use jump to URL and specify CSV
>> and ASCII (the CSV export does Unicode and Excel doesn't handle unicode
>> CSV). By rendering in ASCII CSV Excel comes up automatically and this is
>> very very fast. I had an Excel export that took 5 minutes and brought the
>> server to its knees go to 10 seconds.
> That 'export to CSV' should 'officially' be on the right-click menu when
> you view a table layout report part, now for some reason that does not
> function on all machines... any idea's?
> Yes same report, all machines Windows XP SP2, IE 6.
> With one person the option re-appeared after a reset of the local intranet
> security to default...
> Alfred
>|||Why don't you increase the timeout?
Farahmand
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.|||Why don't you increase the timeout?
Farahmand
"Jammer" wrote:
> Hi,
> I have a report that has two drill downs that drill to about 5000 records
> and due to this I am having time out problems while exporting to excel. For
> now, we have worked around the timeout problem but what I would like to know
> is whether or not there is a way to disable the excel option in the export
> drop down list - only when the SQL report generates like more than 5000 rows.
> The reason to disable being, the RS/RM is consuming CPU like crazy and its
> repurcussions are unbearable.
2012年2月24日星期五
Disabling AutoRendering for Reports with Default Parameters
I have a report with default parameters. Every time the report is selected,
it automatically renders the report using the defaults. Is there any way to
disable the AutoRendering feature so my users can choose to override the
report's defaults and then render the report manually?
--
Jay P. Meredith
Senior Software Engineer
Columbia Helicopters, Inc.
PO Box 3500
Portland, OR 97208Just a stab, but maybe worth trying... What if you add a hidden parameter
which has no default value, and is not used... That might prevent
auto-render, and force the user to select the view report button... ( I
don't know what potential bad side effects might occur tho...)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jay Meredith" <JayMeredith@.discussions.microsoft.com> wrote in message
news:5E0D389A-8EE3-417B-A658-8DFCBD01D483@.microsoft.com...
>I have a report with default parameters. Every time the report is
>selected,
> it automatically renders the report using the defaults. Is there any way
> to
> disable the AutoRendering feature so my users can choose to override the
> report's defaults and then render the report manually?
> --
> Jay P. Meredith
> Senior Software Engineer
> Columbia Helicopters, Inc.
> PO Box 3500
> Portland, OR 97208|||Wayne,
A hidden parameter with no default value will cause the report not to render
at all. After all, if it has no default, it has to be told what its value
is. And if the user can't see it to choose a value, then the report will
just sit there, waiting for input.
The only way I've found against AutoRender when the report is first open is
to leave one of my parameters with no default, forcing the user to make a
choice on it.
BLARGH... It would be nice if RS was old enough that someone else had all
the answers and we didn't have to be the ones to find out the answers the
hard way. @.=/
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Wayne Snyder" wrote:
> Just a stab, but maybe worth trying... What if you add a hidden parameter
> which has no default value, and is not used... That might prevent
> auto-render, and force the user to select the view report button... ( I
> don't know what potential bad side effects might occur tho...)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jay Meredith" <JayMeredith@.discussions.microsoft.com> wrote in message
> news:5E0D389A-8EE3-417B-A658-8DFCBD01D483@.microsoft.com...
> >I have a report with default parameters. Every time the report is
> >selected,
> > it automatically renders the report using the defaults. Is there any way
> > to
> > disable the AutoRendering feature so my users can choose to override the
> > report's defaults and then render the report manually?
> > --
> > Jay P. Meredith
> > Senior Software Engineer
> > Columbia Helicopters, Inc.
> > PO Box 3500
> > Portland, OR 97208
>
>|||Leaving one or more unhidden parameters without defaults seems to be the best
compromise. In defaulting all parameters, I was trying to save the user a
few keystrokes. Thanks.
--
Jay P. Meredith
Senior Software Engineer
Columbia Helicopters, Inc.
PO Box 3500
Portland, OR 97208
"Catadmin" wrote:
> Wayne,
> A hidden parameter with no default value will cause the report not to render
> at all. After all, if it has no default, it has to be told what its value
> is. And if the user can't see it to choose a value, then the report will
> just sit there, waiting for input.
> The only way I've found against AutoRender when the report is first open is
> to leave one of my parameters with no default, forcing the user to make a
> choice on it.
> BLARGH... It would be nice if RS was old enough that someone else had all
> the answers and we didn't have to be the ones to find out the answers the
> hard way. @.=/
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "Wayne Snyder" wrote:
> > Just a stab, but maybe worth trying... What if you add a hidden parameter
> > which has no default value, and is not used... That might prevent
> > auto-render, and force the user to select the view report button... ( I
> > don't know what potential bad side effects might occur tho...)
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Jay Meredith" <JayMeredith@.discussions.microsoft.com> wrote in message
> > news:5E0D389A-8EE3-417B-A658-8DFCBD01D483@.microsoft.com...
> > >I have a report with default parameters. Every time the report is
> > >selected,
> > > it automatically renders the report using the defaults. Is there any way
> > > to
> > > disable the AutoRendering feature so my users can choose to override the
> > > report's defaults and then render the report manually?
> > > --
> > > Jay P. Meredith
> > > Senior Software Engineer
> > > Columbia Helicopters, Inc.
> > > PO Box 3500
> > > Portland, OR 97208
> >
> >
> >|||Always glad to be of assistance. @.=)
Catadmin
"Jay Meredith" wrote:
> Leaving one or more unhidden parameters without defaults seems to be the best
> compromise. In defaulting all parameters, I was trying to save the user a
> few keystrokes. Thanks.
> --
> Jay P. Meredith
> Senior Software Engineer
> Columbia Helicopters, Inc.
> PO Box 3500
> Portland, OR 97208
>
> "Catadmin" wrote:
> > Wayne,
> >
> > A hidden parameter with no default value will cause the report not to render
> > at all. After all, if it has no default, it has to be told what its value
> > is. And if the user can't see it to choose a value, then the report will
> > just sit there, waiting for input.
> >
> > The only way I've found against AutoRender when the report is first open is
> > to leave one of my parameters with no default, forcing the user to make a
> > choice on it.
> >
> > BLARGH... It would be nice if RS was old enough that someone else had all
> > the answers and we didn't have to be the ones to find out the answers the
> > hard way. @.=/
> >
> > Catadmin
> > --
> > MCDBA, MCSA
> > Random Thoughts: If a person is Microsoft Certified, does that mean that
> > Microsoft pays the bills for the funny white jackets that tie in the back?
> > @.=)
> >
> >
> > "Wayne Snyder" wrote:
> >
> > > Just a stab, but maybe worth trying... What if you add a hidden parameter
> > > which has no default value, and is not used... That might prevent
> > > auto-render, and force the user to select the view report button... ( I
> > > don't know what potential bad side effects might occur tho...)
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Jay Meredith" <JayMeredith@.discussions.microsoft.com> wrote in message
> > > news:5E0D389A-8EE3-417B-A658-8DFCBD01D483@.microsoft.com...
> > > >I have a report with default parameters. Every time the report is
> > > >selected,
> > > > it automatically renders the report using the defaults. Is there any way
> > > > to
> > > > disable the AutoRendering feature so my users can choose to override the
> > > > report's defaults and then render the report manually?
> > > > --
> > > > Jay P. Meredith
> > > > Senior Software Engineer
> > > > Columbia Helicopters, Inc.
> > > > PO Box 3500
> > > > Portland, OR 97208
> > >
> > >
> > >
it automatically renders the report using the defaults. Is there any way to
disable the AutoRendering feature so my users can choose to override the
report's defaults and then render the report manually?
--
Jay P. Meredith
Senior Software Engineer
Columbia Helicopters, Inc.
PO Box 3500
Portland, OR 97208Just a stab, but maybe worth trying... What if you add a hidden parameter
which has no default value, and is not used... That might prevent
auto-render, and force the user to select the view report button... ( I
don't know what potential bad side effects might occur tho...)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jay Meredith" <JayMeredith@.discussions.microsoft.com> wrote in message
news:5E0D389A-8EE3-417B-A658-8DFCBD01D483@.microsoft.com...
>I have a report with default parameters. Every time the report is
>selected,
> it automatically renders the report using the defaults. Is there any way
> to
> disable the AutoRendering feature so my users can choose to override the
> report's defaults and then render the report manually?
> --
> Jay P. Meredith
> Senior Software Engineer
> Columbia Helicopters, Inc.
> PO Box 3500
> Portland, OR 97208|||Wayne,
A hidden parameter with no default value will cause the report not to render
at all. After all, if it has no default, it has to be told what its value
is. And if the user can't see it to choose a value, then the report will
just sit there, waiting for input.
The only way I've found against AutoRender when the report is first open is
to leave one of my parameters with no default, forcing the user to make a
choice on it.
BLARGH... It would be nice if RS was old enough that someone else had all
the answers and we didn't have to be the ones to find out the answers the
hard way. @.=/
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Wayne Snyder" wrote:
> Just a stab, but maybe worth trying... What if you add a hidden parameter
> which has no default value, and is not used... That might prevent
> auto-render, and force the user to select the view report button... ( I
> don't know what potential bad side effects might occur tho...)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jay Meredith" <JayMeredith@.discussions.microsoft.com> wrote in message
> news:5E0D389A-8EE3-417B-A658-8DFCBD01D483@.microsoft.com...
> >I have a report with default parameters. Every time the report is
> >selected,
> > it automatically renders the report using the defaults. Is there any way
> > to
> > disable the AutoRendering feature so my users can choose to override the
> > report's defaults and then render the report manually?
> > --
> > Jay P. Meredith
> > Senior Software Engineer
> > Columbia Helicopters, Inc.
> > PO Box 3500
> > Portland, OR 97208
>
>|||Leaving one or more unhidden parameters without defaults seems to be the best
compromise. In defaulting all parameters, I was trying to save the user a
few keystrokes. Thanks.
--
Jay P. Meredith
Senior Software Engineer
Columbia Helicopters, Inc.
PO Box 3500
Portland, OR 97208
"Catadmin" wrote:
> Wayne,
> A hidden parameter with no default value will cause the report not to render
> at all. After all, if it has no default, it has to be told what its value
> is. And if the user can't see it to choose a value, then the report will
> just sit there, waiting for input.
> The only way I've found against AutoRender when the report is first open is
> to leave one of my parameters with no default, forcing the user to make a
> choice on it.
> BLARGH... It would be nice if RS was old enough that someone else had all
> the answers and we didn't have to be the ones to find out the answers the
> hard way. @.=/
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "Wayne Snyder" wrote:
> > Just a stab, but maybe worth trying... What if you add a hidden parameter
> > which has no default value, and is not used... That might prevent
> > auto-render, and force the user to select the view report button... ( I
> > don't know what potential bad side effects might occur tho...)
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Jay Meredith" <JayMeredith@.discussions.microsoft.com> wrote in message
> > news:5E0D389A-8EE3-417B-A658-8DFCBD01D483@.microsoft.com...
> > >I have a report with default parameters. Every time the report is
> > >selected,
> > > it automatically renders the report using the defaults. Is there any way
> > > to
> > > disable the AutoRendering feature so my users can choose to override the
> > > report's defaults and then render the report manually?
> > > --
> > > Jay P. Meredith
> > > Senior Software Engineer
> > > Columbia Helicopters, Inc.
> > > PO Box 3500
> > > Portland, OR 97208
> >
> >
> >|||Always glad to be of assistance. @.=)
Catadmin
"Jay Meredith" wrote:
> Leaving one or more unhidden parameters without defaults seems to be the best
> compromise. In defaulting all parameters, I was trying to save the user a
> few keystrokes. Thanks.
> --
> Jay P. Meredith
> Senior Software Engineer
> Columbia Helicopters, Inc.
> PO Box 3500
> Portland, OR 97208
>
> "Catadmin" wrote:
> > Wayne,
> >
> > A hidden parameter with no default value will cause the report not to render
> > at all. After all, if it has no default, it has to be told what its value
> > is. And if the user can't see it to choose a value, then the report will
> > just sit there, waiting for input.
> >
> > The only way I've found against AutoRender when the report is first open is
> > to leave one of my parameters with no default, forcing the user to make a
> > choice on it.
> >
> > BLARGH... It would be nice if RS was old enough that someone else had all
> > the answers and we didn't have to be the ones to find out the answers the
> > hard way. @.=/
> >
> > Catadmin
> > --
> > MCDBA, MCSA
> > Random Thoughts: If a person is Microsoft Certified, does that mean that
> > Microsoft pays the bills for the funny white jackets that tie in the back?
> > @.=)
> >
> >
> > "Wayne Snyder" wrote:
> >
> > > Just a stab, but maybe worth trying... What if you add a hidden parameter
> > > which has no default value, and is not used... That might prevent
> > > auto-render, and force the user to select the view report button... ( I
> > > don't know what potential bad side effects might occur tho...)
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Jay Meredith" <JayMeredith@.discussions.microsoft.com> wrote in message
> > > news:5E0D389A-8EE3-417B-A658-8DFCBD01D483@.microsoft.com...
> > > >I have a report with default parameters. Every time the report is
> > > >selected,
> > > > it automatically renders the report using the defaults. Is there any way
> > > > to
> > > > disable the AutoRendering feature so my users can choose to override the
> > > > report's defaults and then render the report manually?
> > > > --
> > > > Jay P. Meredith
> > > > Senior Software Engineer
> > > > Columbia Helicopters, Inc.
> > > > PO Box 3500
> > > > Portland, OR 97208
> > >
> > >
> > >
Disabling a Step While a Job is Running
Hi,
Kind of a weird question. If I have a SQL job that runs for a long
time and while it is running I realize I want to skip one of the steps
which is still to come, can I update the job while it's running to
make it's On Success action either be, Stop the Job Reporting Success
or Go To Step X? Or does the job essentially get "loaded" when it
starts and no changes will take effect until the next run?
Thanks,
BillMy gut says no, but you could test it easilly by setting job steps with
'WAITFOR' .
"zinck74" <bkelly3@.gmail.com> wrote in message
news:1190238399.358816.128400@.k35g2000prh.googlegroups.com...
> Hi,
> Kind of a weird question. If I have a SQL job that runs for a long
> time and while it is running I realize I want to skip one of the steps
> which is still to come, can I update the job while it's running to
> make it's On Success action either be, Stop the Job Reporting Success
> or Go To Step X? Or does the job essentially get "loaded" when it
> starts and no changes will take effect until the next run?
> Thanks,
> Bill
>
Kind of a weird question. If I have a SQL job that runs for a long
time and while it is running I realize I want to skip one of the steps
which is still to come, can I update the job while it's running to
make it's On Success action either be, Stop the Job Reporting Success
or Go To Step X? Or does the job essentially get "loaded" when it
starts and no changes will take effect until the next run?
Thanks,
BillMy gut says no, but you could test it easilly by setting job steps with
'WAITFOR' .
"zinck74" <bkelly3@.gmail.com> wrote in message
news:1190238399.358816.128400@.k35g2000prh.googlegroups.com...
> Hi,
> Kind of a weird question. If I have a SQL job that runs for a long
> time and while it is running I realize I want to skip one of the steps
> which is still to come, can I update the job while it's running to
> make it's On Success action either be, Stop the Job Reporting Success
> or Go To Step X? Or does the job essentially get "loaded" when it
> starts and no changes will take effect until the next run?
> Thanks,
> Bill
>
2012年2月19日星期日
disable sql 2005 mixed mode password complexity?
I installed SQL Server 2005 for the first time today, and I set it to
mixed mode because that is how we have always had to configure 2000 in
the past for certain applications. I used a weak password for the sa
account, and it caught that, so I changed it.
The problem came when I attempted to install some applications that
previously worked under SQL Server 2000. The first one installed
without errors, but when I attempted to login, there was a connection
error and it said that the database login (which it creates during
install) was invalid. I set the connection properties to use the sa
login, and that worked (as this is a demo, I'm not particularly
concerned with security at the moment).
The next application I attempted to install actually errored out on me,
with some timeout error. When I tried to launch it after the failed
install, I again get connection errors. When I looked at the user
accounts in the Enterprise Manager-like addin (I forget what it's
called), I didn't see any of the accounts which should have been
created during the setup of either application.
Please let me know if I'm way off base here, but what seems to be
happening is that the applications I'm installing use weak passwords
for the SQL user accounts they configure, so they never get created.
Since the accounts are created by an installer, I have no control over
what password they use. So I just need to know if there's some way I
can disable the password complexity check for new accounts and get this
software installed. I'll lecture the developers on security later.You can modify Windows security policy that disable password policy.
"bcochranmsa@.gmail.com"?? ??? ??:
> I installed SQL Server 2005 for the first time today, and I set it to
> mixed mode because that is how we have always had to configure 2000 in
> the past for certain applications. I used a weak password for the sa
> account, and it caught that, so I changed it.
> The problem came when I attempted to install some applications that
> previously worked under SQL Server 2000. The first one installed
> without errors, but when I attempted to login, there was a connection
> error and it said that the database login (which it creates during
> install) was invalid. I set the connection properties to use the sa
> login, and that worked (as this is a demo, I'm not particularly
> concerned with security at the moment).
> The next application I attempted to install actually errored out on me,
> with some timeout error. When I tried to launch it after the failed
> install, I again get connection errors. When I looked at the user
> accounts in the Enterprise Manager-like addin (I forget what it's
> called), I didn't see any of the accounts which should have been
> created during the setup of either application.
> Please let me know if I'm way off base here, but what seems to be
> happening is that the applications I'm installing use weak passwords
> for the SQL user accounts they configure, so they never get created.
> Since the accounts are created by an installer, I have no control over
> what password they use. So I just need to know if there's some way I
> can disable the password complexity check for new accounts and get this
> software installed. I'll lecture the developers on security later.
>|||ALTER LOGIN Username
SET CHECK_POLICY = OFF
mixed mode because that is how we have always had to configure 2000 in
the past for certain applications. I used a weak password for the sa
account, and it caught that, so I changed it.
The problem came when I attempted to install some applications that
previously worked under SQL Server 2000. The first one installed
without errors, but when I attempted to login, there was a connection
error and it said that the database login (which it creates during
install) was invalid. I set the connection properties to use the sa
login, and that worked (as this is a demo, I'm not particularly
concerned with security at the moment).
The next application I attempted to install actually errored out on me,
with some timeout error. When I tried to launch it after the failed
install, I again get connection errors. When I looked at the user
accounts in the Enterprise Manager-like addin (I forget what it's
called), I didn't see any of the accounts which should have been
created during the setup of either application.
Please let me know if I'm way off base here, but what seems to be
happening is that the applications I'm installing use weak passwords
for the SQL user accounts they configure, so they never get created.
Since the accounts are created by an installer, I have no control over
what password they use. So I just need to know if there's some way I
can disable the password complexity check for new accounts and get this
software installed. I'll lecture the developers on security later.You can modify Windows security policy that disable password policy.
"bcochranmsa@.gmail.com"?? ??? ??:
> I installed SQL Server 2005 for the first time today, and I set it to
> mixed mode because that is how we have always had to configure 2000 in
> the past for certain applications. I used a weak password for the sa
> account, and it caught that, so I changed it.
> The problem came when I attempted to install some applications that
> previously worked under SQL Server 2000. The first one installed
> without errors, but when I attempted to login, there was a connection
> error and it said that the database login (which it creates during
> install) was invalid. I set the connection properties to use the sa
> login, and that worked (as this is a demo, I'm not particularly
> concerned with security at the moment).
> The next application I attempted to install actually errored out on me,
> with some timeout error. When I tried to launch it after the failed
> install, I again get connection errors. When I looked at the user
> accounts in the Enterprise Manager-like addin (I forget what it's
> called), I didn't see any of the accounts which should have been
> created during the setup of either application.
> Please let me know if I'm way off base here, but what seems to be
> happening is that the applications I'm installing use weak passwords
> for the SQL user accounts they configure, so they never get created.
> Since the accounts are created by an installer, I have no control over
> what password they use. So I just need to know if there's some way I
> can disable the password complexity check for new accounts and get this
> software installed. I'll lecture the developers on security later.
>|||ALTER LOGIN Username
SET CHECK_POLICY = OFF
Disable Save Changes prompt
Is there a way in SQL Mgt Studio to disable the "Save changes to the following items?" prompt that comes up every time I close a query window?
No, currently there is no way to disable the prompt to save unsaved changes in the T-SQL script editor.
You can submit suggestions for Management Studio functionality at http://connect.microsoft.com/sqlserver. We use customer feedback like this when we are prioritizing future work.
Thanks,
Steve
Disable Save Changes prompt
Is there a way in SQL Mgt Studio to disable the "Save changes to the following items?" prompt that comes up every time I close a query window?
No, currently there is no way to disable the prompt to save unsaved changes in the T-SQL script editor.
You can submit suggestions for Management Studio functionality at http://connect.microsoft.com/sqlserver. We use customer feedback like this when we are prioritizing future work.
Thanks,
Steve
2012年2月14日星期二
disable and enable trigger on schedule
How do you disable and enable the trigger on a table automatically at some
time daily?Hi,
Identify the triggers which needs to be disabled. Then use the below Alter
command to disable
1. Create a SQL Agent job to disable the trigger and schedule a time
ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
2. Create another SQL Agent job to enable the trigger and schedule a time
ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
Make sure data integrity is not lost when you disable the trigger.
Thanks
Hari
SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
> How do you disable and enable the trigger on a table automatically at some
> time daily?
>|||Hi,
Thanks Hari for greate information. I just like to provide following link
for detailed information:
http://msdn2.microsoft.com/en-us/library/ms182706.aspx
http://msdn2.microsoft.com/en-us/library/ms189748.aspx
Hope this helps.
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.
======================================================>>From: "Hari Prasad" <hari_prasad_k@.hotmail.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Tue, 5 Sep 2006 20:56:58 -0500
>>Lines: 26
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>>Message-ID: <#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: cpe-65-27-44-57.kc.res.rr.com 65.27.44.57
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444272
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi,
>>Identify the triggers which needs to be disabled. Then use the below
Alter
>>command to disable
>>1. Create a SQL Agent job to disable the trigger and schedule a time
>>ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>>2. Create another SQL Agent job to enable the trigger and schedule a time
>>ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>>Make sure data integrity is not lost when you disable the trigger.
>>Thanks
>>Hari
>>SQL Server MVP
>>"00KobeBrian" <a@.b.com> wrote in message
>>news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
some
>> time daily?
>>
>>|||How can I know if the trigger is enabled or disabled? Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Identify the triggers which needs to be disabled. Then use the below Alter
> command to disable
> 1. Create a SQL Agent job to disable the trigger and schedule a time
> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
> 2. Create another SQL Agent job to enable the trigger and schedule a time
> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
> Make sure data integrity is not lost when you disable the trigger.
> Thanks
> Hari
> SQL Server MVP
> "00KobeBrian" <a@.b.com> wrote in message
> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>|||Hi 00KobeBrian
Use the function OBJECTPROPERTY
SELECT OBJECTPROPERTY(object_id('name of trigger')',
'ExecIsTriggerDisabled')
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
> How can I know if the trigger is enabled or disabled? Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>|||Hi,
If you are using SQL 2005, you can refer to following article:
<http://msdn2.microsoft.com/en-us/library/ms188746.aspx>
Thanks.
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.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
<#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Wed, 6 Sep 2006 12:52:34 +0800
>>Lines: 33
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 202.40.134.130
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444283
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I know if the trigger is enabled or disabled? Thanks.
>>
>>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>>news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
Alter
>> command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>|||Can you please give me an example? I am using SQL 2000. Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
> Hi 00KobeBrian
> Use the function OBJECTPROPERTY
> SELECT OBJECTPROPERTY(object_id('name of trigger')',
> 'ExecIsTriggerDisabled')
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>|||I gave you an example of checking for whether a trigger is disabled. If the
SELECT OBJECTPROPERTY returns 1, the trigger is disabled, if it returns 0,
the trigger is enabled, if it returns NULL, you typed something incorrectly.
If you need more details than that, you'll have to be more specific about
what you don't understand.
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:O5vt5%23X0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Can you please give me an example? I am using SQL 2000. Thanks.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
>> Hi 00KobeBrian
>> Use the function OBJECTPROPERTY
>> SELECT OBJECTPROPERTY(object_id('name of trigger')',
>> 'ExecIsTriggerDisabled')
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>
>
time daily?Hi,
Identify the triggers which needs to be disabled. Then use the below Alter
command to disable
1. Create a SQL Agent job to disable the trigger and schedule a time
ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
2. Create another SQL Agent job to enable the trigger and schedule a time
ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
Make sure data integrity is not lost when you disable the trigger.
Thanks
Hari
SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
> How do you disable and enable the trigger on a table automatically at some
> time daily?
>|||Hi,
Thanks Hari for greate information. I just like to provide following link
for detailed information:
http://msdn2.microsoft.com/en-us/library/ms182706.aspx
http://msdn2.microsoft.com/en-us/library/ms189748.aspx
Hope this helps.
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.
======================================================>>From: "Hari Prasad" <hari_prasad_k@.hotmail.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Tue, 5 Sep 2006 20:56:58 -0500
>>Lines: 26
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>>Message-ID: <#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: cpe-65-27-44-57.kc.res.rr.com 65.27.44.57
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444272
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi,
>>Identify the triggers which needs to be disabled. Then use the below
Alter
>>command to disable
>>1. Create a SQL Agent job to disable the trigger and schedule a time
>>ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>>2. Create another SQL Agent job to enable the trigger and schedule a time
>>ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>>Make sure data integrity is not lost when you disable the trigger.
>>Thanks
>>Hari
>>SQL Server MVP
>>"00KobeBrian" <a@.b.com> wrote in message
>>news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
some
>> time daily?
>>
>>|||How can I know if the trigger is enabled or disabled? Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Identify the triggers which needs to be disabled. Then use the below Alter
> command to disable
> 1. Create a SQL Agent job to disable the trigger and schedule a time
> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
> 2. Create another SQL Agent job to enable the trigger and schedule a time
> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
> Make sure data integrity is not lost when you disable the trigger.
> Thanks
> Hari
> SQL Server MVP
> "00KobeBrian" <a@.b.com> wrote in message
> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>|||Hi 00KobeBrian
Use the function OBJECTPROPERTY
SELECT OBJECTPROPERTY(object_id('name of trigger')',
'ExecIsTriggerDisabled')
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
> How can I know if the trigger is enabled or disabled? Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>|||Hi,
If you are using SQL 2005, you can refer to following article:
<http://msdn2.microsoft.com/en-us/library/ms188746.aspx>
Thanks.
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.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
<#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Wed, 6 Sep 2006 12:52:34 +0800
>>Lines: 33
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 202.40.134.130
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444283
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I know if the trigger is enabled or disabled? Thanks.
>>
>>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>>news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
Alter
>> command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>|||Can you please give me an example? I am using SQL 2000. Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
> Hi 00KobeBrian
> Use the function OBJECTPROPERTY
> SELECT OBJECTPROPERTY(object_id('name of trigger')',
> 'ExecIsTriggerDisabled')
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>|||I gave you an example of checking for whether a trigger is disabled. If the
SELECT OBJECTPROPERTY returns 1, the trigger is disabled, if it returns 0,
the trigger is enabled, if it returns NULL, you typed something incorrectly.
If you need more details than that, you'll have to be more specific about
what you don't understand.
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:O5vt5%23X0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Can you please give me an example? I am using SQL 2000. Thanks.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
>> Hi 00KobeBrian
>> Use the function OBJECTPROPERTY
>> SELECT OBJECTPROPERTY(object_id('name of trigger')',
>> 'ExecIsTriggerDisabled')
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>
>
Disable a job schedule
Hi,
I have a job with three steps and four schedule time per day.
I schedule a job to disable one of schedule time, such as 5:00PM schedule.
The job is still enable, but this schedule time show enabled =NO before
5:00PM. However, the 5:00PM schedule still run regardless schedule
enabled=NO.
Is this flag in sysjobschedules play any role for enable and disable a
schedule? Or, I have to disable and enable from the job level (sysjobs).
Please advise.
Best Regards,
Lynn
Hmm, I disable job in the job level (sysjobs) at 4:45PM and the 5:00PM
schedule is enabled. And, the job still run at 5:00PM even though the job is
disabled. I don't understand what's wrong in my procedure. From the
Enterprise Manager, I see the job was disabled.
Looks like either way (from job level or schedule level) won't work.
Please help.
Best Regards,
Lynn
"Lynn" wrote:
> Hi,
> I have a job with three steps and four schedule time per day.
> I schedule a job to disable one of schedule time, such as 5:00PM schedule.
> The job is still enable, but this schedule time show enabled =NO before
> 5:00PM. However, the 5:00PM schedule still run regardless schedule
> enabled=NO.
> Is this flag in sysjobschedules play any role for enable and disable a
> schedule? Or, I have to disable and enable from the job level (sysjobs).
> Please advise.
> --
> Best Regards,
> Lynn
I have a job with three steps and four schedule time per day.
I schedule a job to disable one of schedule time, such as 5:00PM schedule.
The job is still enable, but this schedule time show enabled =NO before
5:00PM. However, the 5:00PM schedule still run regardless schedule
enabled=NO.
Is this flag in sysjobschedules play any role for enable and disable a
schedule? Or, I have to disable and enable from the job level (sysjobs).
Please advise.
Best Regards,
Lynn
Hmm, I disable job in the job level (sysjobs) at 4:45PM and the 5:00PM
schedule is enabled. And, the job still run at 5:00PM even though the job is
disabled. I don't understand what's wrong in my procedure. From the
Enterprise Manager, I see the job was disabled.
Looks like either way (from job level or schedule level) won't work.
Please help.
Best Regards,
Lynn
"Lynn" wrote:
> Hi,
> I have a job with three steps and four schedule time per day.
> I schedule a job to disable one of schedule time, such as 5:00PM schedule.
> The job is still enable, but this schedule time show enabled =NO before
> 5:00PM. However, the 5:00PM schedule still run regardless schedule
> enabled=NO.
> Is this flag in sysjobschedules play any role for enable and disable a
> schedule? Or, I have to disable and enable from the job level (sysjobs).
> Please advise.
> --
> Best Regards,
> Lynn
订阅：
博文 (Atom)