We have sql server 2000 ,we r dong transactional replication,out distribution
agent latency is around 1000000 from last 2 days before that,it was around
4000,please suggest us the solution immediately.
Thank you.
Assuming there hasn't been a massive increase in replicated commands, have a
look for blocking issues on the subscriber when the distribution agent runs
(sp_who2).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Have you verified your distribution agent is running? You also need to make
sure it is not stuck in the initializing state.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:80D4C0B5-0D51-438F-8674-8E8FE9DEFAE9@.microsoft.com...
> We have sql server 2000 ,we r dong transactional replication,out
> distribution
> agent latency is around 1000000 from last 2 days before that,it was around
> 4000,please suggest us the solution immediately.
> Thank you.
|||Our distribution agent is running properly Actually there are another
subscriber where the latency is 4000,so what will be the possible reason of
increased latencyin the other server.plese reply imediately.
"Hilary Cotter" wrote:
> Have you verified your distribution agent is running? You also need to make
> sure it is not stuck in the initializing state.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:80D4C0B5-0D51-438F-8674-8E8FE9DEFAE9@.microsoft.com...
>
>
|||Sir Thank you for your reply
"Paul Ibison" wrote:
> Assuming there hasn't been a massive increase in replicated commands, have a
> look for blocking issues on the subscriber when the distribution agent runs
> (sp_who2).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
Friday, March 30, 2012
Increased performance using 2 servers and a SAN
Hi
I need to increase performance of our SQL enviroment and i dont really want
to put one BIG server in.
What would be the best way of load balancing 1 SQL database over 2 servers?
My first thought was 2 servers clustered with a SAN and use SQL replication
between two differant Instances, is this possible or is there a better way?
Thanks
Andy
SQL 2000 can't not be load balanced for a single database. A database can
only be run from one server at a time. Sorry.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> Hi
> I need to increase performance of our SQL enviroment and i dont really
> want
> to put one BIG server in.
> What would be the best way of load balancing 1 SQL database over 2
> servers?
> My first thought was 2 servers clustered with a SAN and use SQL
> replication
> between two differant Instances, is this possible or is there a better
> way?
> Thanks
> Andy
|||Hi
Using replication is possible, but your application has to understand that
records can be inserted in different databases, so if any identity columns
exist, expect issues.
SQL Server can not be directly load balanced. Don't forget, a SAN may not
make IO quicker, it might actually be a lot slower (compared to local
storage).
What is your current hardware setup, DB sizes, transactions per minute and
what performance are you expecting?
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/
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> Hi
> I need to increase performance of our SQL enviroment and i dont really
want
> to put one BIG server in.
> What would be the best way of load balancing 1 SQL database over 2
servers?
> My first thought was 2 servers clustered with a SAN and use SQL
replication
> between two differant Instances, is this possible or is there a better
way?
> Thanks
> Andy
|||Hi
We currently have a HP DL380 with 2x Zeaon 2.8Ghz processors and 2GB RAM
we have 2 DB and all Logs on one set of RAID 5 10k DIsks and the Main DB on
its own Mirrored set of 10K Disks.
We are experiancing High Disk Queue lengths on the first set of Disk, so we
was looking to use a HP MSA500 SAN and split all DB and Logs onto seperate
Mirrored Disks?
The Main DB is 500mb and the other DB which is used for referance info is
1.3GB
We have about 180 users using the system and we seem to be getting slow
query times in peak load, i.e 400 - 1000ms query times.
If you have any suggestion then please let me know, would a MSA500 be a bad
idea?
Andy
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Using replication is possible, but your application has to understand that
> records can be inserted in different databases, so if any identity columns
> exist, expect issues.
> SQL Server can not be directly load balanced. Don't forget, a SAN may not
> make IO quicker, it might actually be a lot slower (compared to local
> storage).
> What is your current hardware setup, DB sizes, transactions per minute and
> what performance are you expecting?
> 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/
> "Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
> news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> want
> servers?
> replication
> way?
>
>
|||While I am unfamiliar with the specific hardware you are considering, the
general idea of separate physical devices for Data and Log is a fundamental
key for increasing performance. Avoid RAID-5 even in a SAN for transaction
log devices. Larger write cache in a SAN device will lead to increased SQL
performance. Check and see if the high queue lengths are for read or for
write operations. If they are for read requests, increasing the RAM (and
moving to Enterprise SQL) may result in more performance improvements than
you would get through disk subsystem changes. Of course, the ideas are not
mutually exclusive.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7B0503A3-DB49-4E1B-8D12-E2542575690B@.microsoft.com...
> Hi
> We currently have a HP DL380 with 2x Zeaon 2.8Ghz processors and 2GB RAM
> we have 2 DB and all Logs on one set of RAID 5 10k DIsks and the Main DB
on
> its own Mirrored set of 10K Disks.
> We are experiancing High Disk Queue lengths on the first set of Disk, so
we
> was looking to use a HP MSA500 SAN and split all DB and Logs onto seperate
> Mirrored Disks?
> The Main DB is 500mb and the other DB which is used for referance info is
> 1.3GB
> We have about 180 users using the system and we seem to be getting slow
> query times in peak load, i.e 400 - 1000ms query times.
> If you have any suggestion then please let me know, would a MSA500 be a
bad[vbcol=seagreen]
> idea?
> Andy
> "Mike Epprecht (SQL MVP)" wrote:
that[vbcol=seagreen]
columns[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
message[vbcol=seagreen]
sql
I need to increase performance of our SQL enviroment and i dont really want
to put one BIG server in.
What would be the best way of load balancing 1 SQL database over 2 servers?
My first thought was 2 servers clustered with a SAN and use SQL replication
between two differant Instances, is this possible or is there a better way?
Thanks
Andy
SQL 2000 can't not be load balanced for a single database. A database can
only be run from one server at a time. Sorry.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> Hi
> I need to increase performance of our SQL enviroment and i dont really
> want
> to put one BIG server in.
> What would be the best way of load balancing 1 SQL database over 2
> servers?
> My first thought was 2 servers clustered with a SAN and use SQL
> replication
> between two differant Instances, is this possible or is there a better
> way?
> Thanks
> Andy
|||Hi
Using replication is possible, but your application has to understand that
records can be inserted in different databases, so if any identity columns
exist, expect issues.
SQL Server can not be directly load balanced. Don't forget, a SAN may not
make IO quicker, it might actually be a lot slower (compared to local
storage).
What is your current hardware setup, DB sizes, transactions per minute and
what performance are you expecting?
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/
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> Hi
> I need to increase performance of our SQL enviroment and i dont really
want
> to put one BIG server in.
> What would be the best way of load balancing 1 SQL database over 2
servers?
> My first thought was 2 servers clustered with a SAN and use SQL
replication
> between two differant Instances, is this possible or is there a better
way?
> Thanks
> Andy
|||Hi
We currently have a HP DL380 with 2x Zeaon 2.8Ghz processors and 2GB RAM
we have 2 DB and all Logs on one set of RAID 5 10k DIsks and the Main DB on
its own Mirrored set of 10K Disks.
We are experiancing High Disk Queue lengths on the first set of Disk, so we
was looking to use a HP MSA500 SAN and split all DB and Logs onto seperate
Mirrored Disks?
The Main DB is 500mb and the other DB which is used for referance info is
1.3GB
We have about 180 users using the system and we seem to be getting slow
query times in peak load, i.e 400 - 1000ms query times.
If you have any suggestion then please let me know, would a MSA500 be a bad
idea?
Andy
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Using replication is possible, but your application has to understand that
> records can be inserted in different databases, so if any identity columns
> exist, expect issues.
> SQL Server can not be directly load balanced. Don't forget, a SAN may not
> make IO quicker, it might actually be a lot slower (compared to local
> storage).
> What is your current hardware setup, DB sizes, transactions per minute and
> what performance are you expecting?
> 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/
> "Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
> news:7CCD89F6-9AAA-4F49-A77B-095B809384AC@.microsoft.com...
> want
> servers?
> replication
> way?
>
>
|||While I am unfamiliar with the specific hardware you are considering, the
general idea of separate physical devices for Data and Log is a fundamental
key for increasing performance. Avoid RAID-5 even in a SAN for transaction
log devices. Larger write cache in a SAN device will lead to increased SQL
performance. Check and see if the high queue lengths are for read or for
write operations. If they are for read requests, increasing the RAM (and
moving to Enterprise SQL) may result in more performance improvements than
you would get through disk subsystem changes. Of course, the ideas are not
mutually exclusive.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew Gilbert" <AndrewGilbert@.discussions.microsoft.com> wrote in message
news:7B0503A3-DB49-4E1B-8D12-E2542575690B@.microsoft.com...
> Hi
> We currently have a HP DL380 with 2x Zeaon 2.8Ghz processors and 2GB RAM
> we have 2 DB and all Logs on one set of RAID 5 10k DIsks and the Main DB
on
> its own Mirrored set of 10K Disks.
> We are experiancing High Disk Queue lengths on the first set of Disk, so
we
> was looking to use a HP MSA500 SAN and split all DB and Logs onto seperate
> Mirrored Disks?
> The Main DB is 500mb and the other DB which is used for referance info is
> 1.3GB
> We have about 180 users using the system and we seem to be getting slow
> query times in peak load, i.e 400 - 1000ms query times.
> If you have any suggestion then please let me know, would a MSA500 be a
bad[vbcol=seagreen]
> idea?
> Andy
> "Mike Epprecht (SQL MVP)" wrote:
that[vbcol=seagreen]
columns[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
message[vbcol=seagreen]
sql
Increased Datetime Precision
Any ideas on how to get greater than the 3.33 ms precision currently
available in the DataTime type?
Thanks,
Randy LRandy,
What are your requirements? What do you need a better precision for?|||What are you trying to do? DateTime has 1ms increments. DateTime.Now is
something like +- 50ms accurate.
William Stacey [MVP]
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
| Any ideas on how to get greater than the 3.33 ms precision currently
| available in the DataTime type?
|
| Thanks,
| Randy L
|
||||Store the milliseconds separately, in a SMALLINT column perhaps. To balance
the increased storage you could use SMALLDATETIME and then the number of
seconds and milliseconds separately.
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L
>|||The precision you mention is the maximum precision of the datetime data
type.
If you need to store dates with times of greater precision in the time
portion, then you will have to program a solution yourself. One approach
is to use a smalldatetime (provided your dates fall within the range
supported by smalldatetime), and store the second and subsecond portion
of the timestamp in an int column as number of (remaining) milliseconds.
For example, to store the datetime 2006-03-01T22:19:03.999, you could
store it as:
CAST('2006-03-01T22:19:03.999' AS smalldatetime) AS
MostSignificantDateTimePart
,CAST(CAST(SUBSTRING('2006-03-01T22:19:03.999',18,5) AS decimal(5,3)) *
1000 AS int) AS LeastSignificantDateTimePart
HTH,
Gert-Jan
"Randy L." wrote:
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L|||The transactions for which we are collecting this data occur very rapidly.
When we then store that time in SQL, the times are then truncated further.
As a result, ordering data by datetime does not always give the true order
of the transaction.
For example:
Trans# Actual Time Stored Time
-- -- --
--
1 2006-03-01 16:00:00.590014 2006-03-01 16:00:00.590
2 2006-03-01 16:00:00.590018 2006-03-01 16:00:00.590
3 2006-03-01 16:00:00.590030 2006-03-01 16:00:00.590
The result in SQL would be 3 times that are exactly the same.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141247397.749764.161370@.u72g2000cwu.googlegroups.com...
> Randy,
> What are your requirements? What do you need a better precision for?
>|||Sorry, wrong ng. Thought I was on c# ng.
William Stacey [MVP]|||Randy,
just from the top of my head: I guess an identity column will give you
the true order of inserts...|||> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
But not necessarily the true order of transaction date/time. The exact
moment in time may be coming from different systems, without synchronized
atomic precision (which is arguably more important in this case than in the
general case), and different system components might have lag times in which
the transactions are recorded (either because they are farther away or on
slower connections, or because they queue up their inserts, etc). Also, if
the table is populated via INSERT...SELECT...FROM there is no guarantee that
the IDENTITY values will be assigned in the 'expected' order.
A|||Alexander,
The transactions we track do not get inserted into SQL as they occur.
They occur across a variety of other processes and are then delivered to the
database. So the order by which they are inserted into the DB has no
significance.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141248868.318368.227020@.i39g2000cwa.googlegroups.com...
> Randy,
> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
>
available in the DataTime type?
Thanks,
Randy LRandy,
What are your requirements? What do you need a better precision for?|||What are you trying to do? DateTime has 1ms increments. DateTime.Now is
something like +- 50ms accurate.
William Stacey [MVP]
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
| Any ideas on how to get greater than the 3.33 ms precision currently
| available in the DataTime type?
|
| Thanks,
| Randy L
|
||||Store the milliseconds separately, in a SMALLINT column perhaps. To balance
the increased storage you could use SMALLDATETIME and then the number of
seconds and milliseconds separately.
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L
>|||The precision you mention is the maximum precision of the datetime data
type.
If you need to store dates with times of greater precision in the time
portion, then you will have to program a solution yourself. One approach
is to use a smalldatetime (provided your dates fall within the range
supported by smalldatetime), and store the second and subsecond portion
of the timestamp in an int column as number of (remaining) milliseconds.
For example, to store the datetime 2006-03-01T22:19:03.999, you could
store it as:
CAST('2006-03-01T22:19:03.999' AS smalldatetime) AS
MostSignificantDateTimePart
,CAST(CAST(SUBSTRING('2006-03-01T22:19:03.999',18,5) AS decimal(5,3)) *
1000 AS int) AS LeastSignificantDateTimePart
HTH,
Gert-Jan
"Randy L." wrote:
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L|||The transactions for which we are collecting this data occur very rapidly.
When we then store that time in SQL, the times are then truncated further.
As a result, ordering data by datetime does not always give the true order
of the transaction.
For example:
Trans# Actual Time Stored Time
-- -- --
--
1 2006-03-01 16:00:00.590014 2006-03-01 16:00:00.590
2 2006-03-01 16:00:00.590018 2006-03-01 16:00:00.590
3 2006-03-01 16:00:00.590030 2006-03-01 16:00:00.590
The result in SQL would be 3 times that are exactly the same.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141247397.749764.161370@.u72g2000cwu.googlegroups.com...
> Randy,
> What are your requirements? What do you need a better precision for?
>|||Sorry, wrong ng. Thought I was on c# ng.
William Stacey [MVP]|||Randy,
just from the top of my head: I guess an identity column will give you
the true order of inserts...|||> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
But not necessarily the true order of transaction date/time. The exact
moment in time may be coming from different systems, without synchronized
atomic precision (which is arguably more important in this case than in the
general case), and different system components might have lag times in which
the transactions are recorded (either because they are farther away or on
slower connections, or because they queue up their inserts, etc). Also, if
the table is populated via INSERT...SELECT...FROM there is no guarantee that
the IDENTITY values will be assigned in the 'expected' order.
A|||Alexander,
The transactions we track do not get inserted into SQL as they occur.
They occur across a variety of other processes and are then delivered to the
database. So the order by which they are inserted into the DB has no
significance.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141248868.318368.227020@.i39g2000cwa.googlegroups.com...
> Randy,
> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
>
Increased Backup size for unknown reason
Does any one know what could be making the size of a backup get so big in SQL server?
I noticed if you back-up the same database in Enterprise Manager over and over (without making any changes to the database), the size of the backup gets bigger and bigger. To get around this I simply erase the backup and create a new one.
Now I'm experiencing the same kind of problem, different situation. I decided to make very few changes to my database. If anything, I shrunk the size of the tables and stored procedures... Now all of a sudden my database backup is 7 times larger.
What could be increasing the size so much, if I haven't increased the amount of tables or stored procedures?
What is the log file about? Mine is huge? Is there a way to reset it or clear it?
any help would be great.
Thank you,
AlecIf you're sure you're not making any changes are you adding/appending to the backup rather than overwriting?
Increase User connections
Can i increase the max user connections above 32767 ?
Thanks> Can i increase the max user connections above 32767 ?
What on earth for? Can your apps not utilize connection pooling? > 32K
unique users really need to maintain a persistent and active connection
indefinitely? Sounds like an architecture and/or design problem to me.|||Aaron, thats why i asked ;)
Can you please answer the other post on connection pooling for me ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eb3cj1Z6HHA.5424@.TK2MSFTNGP02.phx.gbl...
>> Can i increase the max user connections above 32767 ?
>
> What on earth for? Can your apps not utilize connection pooling? > 32K
> unique users really need to maintain a persistent and active connection
> indefinitely? Sounds like an architecture and/or design problem to me.
>|||> Can you please answer the other post on connection pooling for me ?
I really don't know how to answer the question. It sounds like a theory
problem to me, not an actual problem you are experiencing. Personally, I
don't do anything special with connection pooling. It is enabled on the web
application, and unless every single web user establishes a connection to
SQL Server using a different connection string (e.g. with asp session ID
embedded), it should just work.
Thanks> Can i increase the max user connections above 32767 ?
What on earth for? Can your apps not utilize connection pooling? > 32K
unique users really need to maintain a persistent and active connection
indefinitely? Sounds like an architecture and/or design problem to me.|||Aaron, thats why i asked ;)
Can you please answer the other post on connection pooling for me ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eb3cj1Z6HHA.5424@.TK2MSFTNGP02.phx.gbl...
>> Can i increase the max user connections above 32767 ?
>
> What on earth for? Can your apps not utilize connection pooling? > 32K
> unique users really need to maintain a persistent and active connection
> indefinitely? Sounds like an architecture and/or design problem to me.
>|||> Can you please answer the other post on connection pooling for me ?
I really don't know how to answer the question. It sounds like a theory
problem to me, not an actual problem you are experiencing. Personally, I
don't do anything special with connection pooling. It is enabled on the web
application, and unless every single web user establishes a connection to
SQL Server using a different connection string (e.g. with asp session ID
embedded), it should just work.
Increase Tooltip display time?
Hi All,
Is it possible to increase the amount of time a tooltip is displayed within report manager?
Regards,
JonNo I don′t think that this is customizable within the report.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Hello
can u tell me
How to put tool tip in My report.
thanx
ki
|||There is a setting for controls naming tooltip which can define any tooltip displayed if you hover over the specified item.HTH, jens Suessmeyer.
http://www.sqlserver2005.de|||Hi Ki,
In the properties box all objects, in the "General" tab is an option
for "Tooltip". You can put an expression in here to define a
tooltip.
Regards,
Jon
Increase Timeout ?
I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
>> Hi,
>> The stored procedure is very straightforward. I am currently using an
>> under powered test server. I never get the timeout on the production
>> server...
>> So how do I increase the timeout ?
>> Thanks
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>>
>sql
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
>> Hi,
>> The stored procedure is very straightforward. I am currently using an
>> under powered test server. I never get the timeout on the production
>> server...
>> So how do I increase the timeout ?
>> Thanks
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>>
>sql
Increase Timeout ?
I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?
Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>
|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
>
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?
Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>
|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
>
increase the speed of the report
Hello,
I am working on a report in SQL Server Reporting Services 2000.
[CODE]
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = case when @.callDate = '' then [Call Day] else @.callDate end
[/CODE]
>> I have apromt for the user to enter the date.
>> If the user does not enter any date, then the report will show all the first 200 records.
>> This query is running too slow.
To increase the speed of the report , could somebody help me build the where clause only when something is in the filters ?
Thank you,
This should do what you want:
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate
I tried using
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate
>> Output is blank.
>> I need the top 200 records to be returned by default. If the @.callday is blank.
Thank you
|||urpalshu wrote:
I tried using
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate>> Output is blank.
>> I need the top 200 records to be returned by default. If the @.callday is blank.
Thank you
You need to use boolean logic here, you stated in some cases no date is entered.
By the way I hope call day is of type date time...
In any even if you sometimes have a value for @.callDate and other times it is null the sproc should be this:
@.callDate datetime= NULL --do you need a default ?
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
Make sure Call Day is of the right type (datetime). If it is varchar, you will need to change the data type. You can strip the day time month year using various date functions.
Jon
|||Thank you,
I changed the Call Date to datetime,
if @.callDate IS NULL AND @.destNbr = '' AND @.origNbr = '' AND @.btn = '' AND @.invoiceNbr = '' AND @.destMobile = ''
BEGIN
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
END
ELSE
BEGIN
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND [Dest Nbr] = case when @.destNbr = '' then [Dest Nbr] else @.destNbr end
AND [Orig Nbr] = case when @.origNbr = '' then [Orig Nbr] else @.origNbr end
AND [BTN] = case when @.btn = '' then [BTN] else @.btn end
AND [invoice nbr] = case when @.invoiceNbr = '' then [invoice nbr] else @.invoiceNbr end
AND [dest mobile] = case when @.destMobile = '' then [dest mobile] else @.destMobile end
END
Can we improve the speed on this query?
Please help
|||This is a quite common type of query requirement when coding queries that do searches.
What you want to do is replicate the exact same logic you used for the @.callDate parameter for all the other parameters too:
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND (@.destNbr is null or @.destNbr = '' or [Dest Nbr] = @.destNbr)
AND (@.origNbr is null or @.origNbr = '' or [Orig Nbr] = @.origNbr)
... etc...
Notice the bracketing to group the expressions (it won't work correctly without it), and the ordering of the expressions: we are relying on shortcutting to ensure that the field vs. parameter check is only evaluated if the parameter contains a legitimate value (ie isn't empty).
If you do it this way then you can also get rid of the outer if @.callDate is null and @.destNbr = '' etc...
sluggy
|||This is a quite common type of query requirement when coding queries that do searches.
What you want to do is replicate the exact same logic you used for the @.callDate parameter for all the other parameters too:
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND (@.destNbr is null or @.destNbr = '' or [Dest Nbr] = @.destNbr)
AND (@.origNbr is null or @.origNbr = '' or [Orig Nbr] = @.origNbr)
... etc...Notice the bracketing to group the expressions (it won't work correctly without it), and the ordering of the expressions: we are relying on shortcutting to ensure that the field vs. parameter check is only evaluated if the parameter contains a legitimate value (ie isn't empty).
If you do it this way then you can also get rid of the outer if @.callDate is null and @.destNbr = '' etc...
sluggy
Its amazing how people dont listen, did I not just post this like the third post ?
|||You sure did, but the original poster was still stuck, so i expanded upon it for him. You will see i mentioned "what he had already done with the @.callDate parameter" - this acknowledges your post.
But this is not the place for a flame war, so let it go.
sluggy
Increase the Rendering Timing of Reports
Hi,
We are using SQL Server 2005 Reporting Services for creating Reports. But report execution is taking bit time to give results.
Is there any way around to increase the rendering timing ?
Thx
First step would be to find where the delay is. There is a table called ExecutionLog in the reportserver database catalog. you can query this table and look at columns - TimeDataRetrival, Time processing, time rendering for this report to find out where the delay is. If the delay is in TimeDataRetrival, it means that your SQL query performance is the one to be blamed. You can optimize the query which is used in the report and get over it. NOTE: Always open the ExecutionLog table with no lock hint.increase size of varchar column.. table being replicated..
I have a table that is being replicated.. i would like to
increase the size of this column.. Is there a way to do
this without dropping the subscription?
Thanks,
niv
It can be done indirectly but it's not nice! You could add a new column with
the new datatype (sp_repladdcolumn), do an update on the table to populate
the column, then drop the column (sp_repldropcolumn). Do this again to
create the column having the same original name.
Alternatively, as you say, you can drop the publication then recreate from
scratch.
We're hoping that such things will be simpler in SQL Server 2005.
Regards,
Paul Ibison
sql
increase the size of this column.. Is there a way to do
this without dropping the subscription?
Thanks,
niv
It can be done indirectly but it's not nice! You could add a new column with
the new datatype (sp_repladdcolumn), do an update on the table to populate
the column, then drop the column (sp_repldropcolumn). Do this again to
create the column having the same original name.
Alternatively, as you say, you can drop the publication then recreate from
scratch.
We're hoping that such things will be simpler in SQL Server 2005.
Regards,
Paul Ibison
sql
increase Ram?
i have DB 2GB on disk.
if i increase tha ram up to 4GB, the sql sever use with the
ram to his temporary table while process quiry?Hi
if i increase tha ram up to 4GB, the sql sever use with the
ram to his temporary table while process quiry?Hi
SQL 6.5 had an option to load tempdb in RAM but this is not available in
newer versions. In the later versions you can pin tables in memory but if
your table is temporary it would not be a candidate for this as small static
tables are more suitable for this option.
The ability to use more than 2GB of memory is dependent on the version of
SQL Server you are running and the version of Windows it is being run on. To
enable more than 2GB memory check out:
http://msdn.microsoft.com/library/d..._ar_sa_6b3k.asp
http://msdn.microsoft.com/library/d...server_1fnd.asp
John
"Mtcc" <m> wrote in message news:3fbdf127$1@.news.012.net.il...
> i have DB 2GB on disk.
> if i increase tha ram up to 4GB, the sql sever use with the
> ram to his temporary table while process quiry?
Increase Performance with two connections
HI all,
I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
In DB2 I use a view which uses some joins against DB1 like:
select * from MyTable left outer join DB1.dbo.users on ...
This works, but I get a time problem: the view is very slow.
The reason seems to be the connection to DB1.
In SQL Management Studio I can reproduce the situation: when I´m in DB2 and
try to "USE DB1" it takes something like >=2 seconds on my local machine.
After making the "USE DB1" the view works great - very fast (SQL Server
seemed to cache the connection).
But the connection caching works only for some minutes - when "falling back"
the view is slow again.
So, I´m looking for a solution for my problem (I can´t change the
database-concept, some data from DB1 is needed also in DB2). Maybe open the
connection DB1 for ... some hours? Or somebody has better ideas?
Thanks,
TonyI assume you have two databases in the same SQL Server instance (which is what it sounds like from
your description). Check if the database DB1 has the database property autoclose turned on. If so,
turn it off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
> HI all,
> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
> In DB2 I use a view which uses some joins against DB1 like:
> select * from MyTable left outer join DB1.dbo.users on ...
> This works, but I get a time problem: the view is very slow.
> The reason seems to be the connection to DB1.
> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
> takes something like >=2 seconds on my local machine.
> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
> connection).
> But the connection caching works only for some minutes - when "falling back" the view is slow
> again.
> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data from
> DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody has
> better ideas?
> Thanks,
> Tony
>|||HI Tibor,
thanks for your reply.
Yes, Autoclose=ON is standard.
Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume
it will work).
Does Autoclose=OFF have any other effects...?
Help of SQL2005 says:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature.
The AutoClose property exposes server behavior for databases not accessed by
a user.
Thanks, Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>I assume you have two databases in the same SQL Server instance (which is
>what it sounds like from your description). Check if the database DB1 has
>the database property autoclose turned on. If so, turn it off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>|||> Yes, Autoclose=ON is standard.
Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this turned
on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes your problem.
> Does Autoclose=OFF have any other effects...?
I hope you don't have other programs that tries to do anything with the database files while no user
is connected? If you do, then they will be affected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
> HI Tibor,
> thanks for your reply.
> Yes, Autoclose=ON is standard.
> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
> Does Autoclose=OFF have any other effects...?
> Help of SQL2005 says:
> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature
> in new development work, and plan to modify applications that currently use this feature.
> The AutoClose property exposes server behavior for databases not accessed by a user.
> Thanks, Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like from
>>your description). Check if the database DB1 has the database property autoclose turned on. If so,
>>turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
>> takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>|||HI Tibor,
yes, with Autoclose=OFF the App works perfect!
But I only don´t really understand
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
which all use database DB1 - and some other database-stored procs/views.
Will they be affected?
Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any
side-effects)?
Thanks again,
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
> Standard? In your shop or in SQL Server? I believe that some edition of
> SQL Server has this turned on by default, which was a bad (IMO) choice.
> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server.
>> Avoid using this feature in new development work, and plan to modify
>> applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not accessed
>> by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is
>>what it sounds like from your description). Check if the database DB1 has
>>the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>>
>|||The application uses the database *through* SQL Server. I.e., they don't access the database files
directly.
I recommend that you have autoclose set to off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
> HI Tibor,
> yes, with Autoclose=OFF the App works perfect!
> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0) which all use database
> DB1 - and some other database-stored procs/views. Will they be affected?
> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any side-effects)?
> Thanks again,
> Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this
>> turned on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes
>> your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this
>> feature in new development work, and plan to modify applications that currently use this
>> feature.
>> The AutoClose property exposes server behavior for databases not accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
>> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like
>>from your description). Check if the database DB1 has the database property autoclose turned on.
>>If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1"
>> it takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>>
>|||Hi Tibor,
yes, I also did a little reasearch on AutoClose - schould always be off on
server and productive environments!
Thanks again for your hint! This was my solution!
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:ePINj6U6HHA.5164@.TK2MSFTNGP05.phx.gbl...
> The application uses the database *through* SQL Server. I.e., they don't
> access the database files directly.
>
> I recommend that you have autoclose set to off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
>> HI Tibor,
>> yes, with Autoclose=OFF the App works perfect!
>> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
>> which all use database DB1 - and some other database-stored procs/views.
>> Will they be affected?
>> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about
>> any side-effects)?
>> Thanks again,
>> Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of
>> SQL Server has this turned on by default, which was a bad (IMO) choice.
>> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and plan to
>> modify applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not
>> accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which
>>is what it sounds like from your description). Check if the database
>>DB1 has the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in
>> DB2 and try to "USE DB1" it takes something like >=2 seconds on my
>> local machine.
>> After making the "USE DB1" the view works great - very fast (SQL
>> Server seemed to cache the connection).
>> But the connection caching works only for some minutes - when
>> "falling back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe
>> open the connection DB1 for ... some hours? Or somebody has better
>> ideas?
>> Thanks,
>> Tony
>>
>>
>
I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
In DB2 I use a view which uses some joins against DB1 like:
select * from MyTable left outer join DB1.dbo.users on ...
This works, but I get a time problem: the view is very slow.
The reason seems to be the connection to DB1.
In SQL Management Studio I can reproduce the situation: when I´m in DB2 and
try to "USE DB1" it takes something like >=2 seconds on my local machine.
After making the "USE DB1" the view works great - very fast (SQL Server
seemed to cache the connection).
But the connection caching works only for some minutes - when "falling back"
the view is slow again.
So, I´m looking for a solution for my problem (I can´t change the
database-concept, some data from DB1 is needed also in DB2). Maybe open the
connection DB1 for ... some hours? Or somebody has better ideas?
Thanks,
TonyI assume you have two databases in the same SQL Server instance (which is what it sounds like from
your description). Check if the database DB1 has the database property autoclose turned on. If so,
turn it off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
> HI all,
> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
> In DB2 I use a view which uses some joins against DB1 like:
> select * from MyTable left outer join DB1.dbo.users on ...
> This works, but I get a time problem: the view is very slow.
> The reason seems to be the connection to DB1.
> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
> takes something like >=2 seconds on my local machine.
> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
> connection).
> But the connection caching works only for some minutes - when "falling back" the view is slow
> again.
> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data from
> DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody has
> better ideas?
> Thanks,
> Tony
>|||HI Tibor,
thanks for your reply.
Yes, Autoclose=ON is standard.
Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume
it will work).
Does Autoclose=OFF have any other effects...?
Help of SQL2005 says:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature.
The AutoClose property exposes server behavior for databases not accessed by
a user.
Thanks, Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>I assume you have two databases in the same SQL Server instance (which is
>what it sounds like from your description). Check if the database DB1 has
>the database property autoclose turned on. If so, turn it off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>|||> Yes, Autoclose=ON is standard.
Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this turned
on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes your problem.
> Does Autoclose=OFF have any other effects...?
I hope you don't have other programs that tries to do anything with the database files while no user
is connected? If you do, then they will be affected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
> HI Tibor,
> thanks for your reply.
> Yes, Autoclose=ON is standard.
> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
> Does Autoclose=OFF have any other effects...?
> Help of SQL2005 says:
> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature
> in new development work, and plan to modify applications that currently use this feature.
> The AutoClose property exposes server behavior for databases not accessed by a user.
> Thanks, Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like from
>>your description). Check if the database DB1 has the database property autoclose turned on. If so,
>>turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
>> takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>|||HI Tibor,
yes, with Autoclose=OFF the App works perfect!
But I only don´t really understand
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
which all use database DB1 - and some other database-stored procs/views.
Will they be affected?
Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any
side-effects)?
Thanks again,
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
> Standard? In your shop or in SQL Server? I believe that some edition of
> SQL Server has this turned on by default, which was a bad (IMO) choice.
> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server.
>> Avoid using this feature in new development work, and plan to modify
>> applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not accessed
>> by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is
>>what it sounds like from your description). Check if the database DB1 has
>>the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>>
>|||The application uses the database *through* SQL Server. I.e., they don't access the database files
directly.
I recommend that you have autoclose set to off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
> HI Tibor,
> yes, with Autoclose=OFF the App works perfect!
> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0) which all use database
> DB1 - and some other database-stored procs/views. Will they be affected?
> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any side-effects)?
> Thanks again,
> Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this
>> turned on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes
>> your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this
>> feature in new development work, and plan to modify applications that currently use this
>> feature.
>> The AutoClose property exposes server behavior for databases not accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
>> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like
>>from your description). Check if the database DB1 has the database property autoclose turned on.
>>If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1"
>> it takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>>
>|||Hi Tibor,
yes, I also did a little reasearch on AutoClose - schould always be off on
server and productive environments!
Thanks again for your hint! This was my solution!
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:ePINj6U6HHA.5164@.TK2MSFTNGP05.phx.gbl...
> The application uses the database *through* SQL Server. I.e., they don't
> access the database files directly.
>
> I recommend that you have autoclose set to off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
>> HI Tibor,
>> yes, with Autoclose=OFF the App works perfect!
>> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
>> which all use database DB1 - and some other database-stored procs/views.
>> Will they be affected?
>> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about
>> any side-effects)?
>> Thanks again,
>> Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of
>> SQL Server has this turned on by default, which was a bad (IMO) choice.
>> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and plan to
>> modify applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not
>> accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which
>>is what it sounds like from your description). Check if the database
>>DB1 has the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in
>> DB2 and try to "USE DB1" it takes something like >=2 seconds on my
>> local machine.
>> After making the "USE DB1" the view works great - very fast (SQL
>> Server seemed to cache the connection).
>> But the connection caching works only for some minutes - when
>> "falling back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe
>> open the connection DB1 for ... some hours? Or somebody has better
>> ideas?
>> Thanks,
>> Tony
>>
>>
>
Increase performance of SQL Server by using RAM
Hi,
I have MSSQL-Server 2000 installed on a PIII 1.2 GHz server running with 256 MB of RAM.
The server is starting to run slower and slower at the peak times.
When ever I check the task manager performance, the processor is always bussy with red indicator, while the RAM is calm and running under 50%.
Is there any configuration in MSSQL-Server, I can do, so I can use the RAM to take some of the load on the processor?
I will really appreciate your help.
ThanksSQL server does all its actions in RAM. So if you insert more RAM the performance should (technically) increase. But first check how much RAM is available for SQL server and monitor the harddisk activity.|||Could you please help me, how to check for the amount of RAM reserved for MS SQL server?
Thanks|||Start SQL Enterprise manager
1
Connect to the appropiate SQL server
2
Right click on de name of the server in the right panel of the screen and select properties.
3
Choose the Tab memory
The memory must be configured as Dynamically with a minimum of 0 and a maximum of the total amount of RAM availleble.
You did determine that the process sqlservr.exe is consuming the bulk of the RAM and of the CPU time? If not than another proces is responsible for the slow response time!|||If you've sufficient memory allocated to SQL Server (Microsoft recommends don't restrict sql to a particular amount of memory, let it acquire memory on its own, hence it is preferred to allocated all memory dedicated to sql server.) If this is setted properly go to 'Performance Monitor', and under System object see '%Processor Time' which should be well below 80% . At the same time also see 'Processor Queue Length' which should be below 2 or equal to 2.
If above conditions are not satisfied you have processor bottleneck and must upgrade your processor
I have MSSQL-Server 2000 installed on a PIII 1.2 GHz server running with 256 MB of RAM.
The server is starting to run slower and slower at the peak times.
When ever I check the task manager performance, the processor is always bussy with red indicator, while the RAM is calm and running under 50%.
Is there any configuration in MSSQL-Server, I can do, so I can use the RAM to take some of the load on the processor?
I will really appreciate your help.
ThanksSQL server does all its actions in RAM. So if you insert more RAM the performance should (technically) increase. But first check how much RAM is available for SQL server and monitor the harddisk activity.|||Could you please help me, how to check for the amount of RAM reserved for MS SQL server?
Thanks|||Start SQL Enterprise manager
1
Connect to the appropiate SQL server
2
Right click on de name of the server in the right panel of the screen and select properties.
3
Choose the Tab memory
The memory must be configured as Dynamically with a minimum of 0 and a maximum of the total amount of RAM availleble.
You did determine that the process sqlservr.exe is consuming the bulk of the RAM and of the CPU time? If not than another proces is responsible for the slow response time!|||If you've sufficient memory allocated to SQL Server (Microsoft recommends don't restrict sql to a particular amount of memory, let it acquire memory on its own, hence it is preferred to allocated all memory dedicated to sql server.) If this is setted properly go to 'Performance Monitor', and under System object see '%Processor Time' which should be well below 80% . At the same time also see 'Processor Queue Length' which should be below 2 or equal to 2.
If above conditions are not satisfied you have processor bottleneck and must upgrade your processor
Increase Paging
I want to display everything on my report on the same screen without having
to go to any other pages. How can I increase what is shown on the first page
of a report?You could increase the PageHeight/PageWidth settings for the report. If you
are using RS 2005, you could set the InteractiveHeight value to 0 and leave
the PageHeight unchanged. Note: the InteractiveHeight property only affects
so called interactive renderers (such as Preview and HTML).
You may also want to read this blog article:
http://blogs.msdn.com/bwelcker/archive/2005/08/19/454043.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:BB5240E3-ED2D-4875-AB25-F57312B74428@.microsoft.com...
>I want to display everything on my report on the same screen without having
> to go to any other pages. How can I increase what is shown on the first
> page
> of a report?
to go to any other pages. How can I increase what is shown on the first page
of a report?You could increase the PageHeight/PageWidth settings for the report. If you
are using RS 2005, you could set the InteractiveHeight value to 0 and leave
the PageHeight unchanged. Note: the InteractiveHeight property only affects
so called interactive renderers (such as Preview and HTML).
You may also want to read this blog article:
http://blogs.msdn.com/bwelcker/archive/2005/08/19/454043.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:BB5240E3-ED2D-4875-AB25-F57312B74428@.microsoft.com...
>I want to display everything on my report on the same screen without having
> to go to any other pages. How can I increase what is shown on the first
> page
> of a report?
Increase or Decrease Texbox to accomadate text
How would I go about seting my text box so that it will increase or decrease
in size to fit my text. Here's what expression I got in my textbox:
= First(Fields!NAME.Value, "FIN") & vbcrlf &
First(Fields!ADDR_1.Value, "FIN") & vbcrlf &
First(Fields!ADDR_2.Value, "FIN") & vbcrlf &
First(Fields!ADDR_3.Value, "FIN") & vbcrlf &
First(Fields!CITY.Value, "FIN") & "," & First(Fields!STATE.Value, "FIN") & "
" & First(Fields!ZIPCODE.Value, "FIN") & vbcrlf &
First(Fields!COUNTRY.Value, "FIN")
The thing is that some contacts will not have address 2 and 3. This will
leave the result like this:
ABC Company
123 South Drive
AppleTown, MN 65343
This is the result that I want if there are no address 2 and 3:
ABC Company
123 South Drive
AppleTown, MN 65343Hi,
There is no property to achieve this
Instead you can use an if condition and display the value only if it exists.
Hope this helps.
Ponnurangam.
"chang" <chang@.discussions.microsoft.com> wrote in message
news:7E565BDD-B1C2-48B2-8A6A-81BEE00BA16D@.microsoft.com...
> How would I go about seting my text box so that it will increase or
> decrease
> in size to fit my text. Here's what expression I got in my textbox:
> = First(Fields!NAME.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_1.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_2.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_3.Value, "FIN") & vbcrlf &
> First(Fields!CITY.Value, "FIN") & "," & First(Fields!STATE.Value, "FIN") &
> "
> " & First(Fields!ZIPCODE.Value, "FIN") & vbcrlf &
> First(Fields!COUNTRY.Value, "FIN")
> The thing is that some contacts will not have address 2 and 3. This will
> leave the result like this:
> ABC Company
> 123 South Drive
>
> AppleTown, MN 65343
> This is the result that I want if there are no address 2 and 3:
> ABC Company
> 123 South Drive
> AppleTown, MN 65343|||Remember, the Height property can also be an expression.
Maybe you can come up with some smart logic.sql
in size to fit my text. Here's what expression I got in my textbox:
= First(Fields!NAME.Value, "FIN") & vbcrlf &
First(Fields!ADDR_1.Value, "FIN") & vbcrlf &
First(Fields!ADDR_2.Value, "FIN") & vbcrlf &
First(Fields!ADDR_3.Value, "FIN") & vbcrlf &
First(Fields!CITY.Value, "FIN") & "," & First(Fields!STATE.Value, "FIN") & "
" & First(Fields!ZIPCODE.Value, "FIN") & vbcrlf &
First(Fields!COUNTRY.Value, "FIN")
The thing is that some contacts will not have address 2 and 3. This will
leave the result like this:
ABC Company
123 South Drive
AppleTown, MN 65343
This is the result that I want if there are no address 2 and 3:
ABC Company
123 South Drive
AppleTown, MN 65343Hi,
There is no property to achieve this
Instead you can use an if condition and display the value only if it exists.
Hope this helps.
Ponnurangam.
"chang" <chang@.discussions.microsoft.com> wrote in message
news:7E565BDD-B1C2-48B2-8A6A-81BEE00BA16D@.microsoft.com...
> How would I go about seting my text box so that it will increase or
> decrease
> in size to fit my text. Here's what expression I got in my textbox:
> = First(Fields!NAME.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_1.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_2.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_3.Value, "FIN") & vbcrlf &
> First(Fields!CITY.Value, "FIN") & "," & First(Fields!STATE.Value, "FIN") &
> "
> " & First(Fields!ZIPCODE.Value, "FIN") & vbcrlf &
> First(Fields!COUNTRY.Value, "FIN")
> The thing is that some contacts will not have address 2 and 3. This will
> leave the result like this:
> ABC Company
> 123 South Drive
>
> AppleTown, MN 65343
> This is the result that I want if there are no address 2 and 3:
> ABC Company
> 123 South Drive
> AppleTown, MN 65343|||Remember, the Height property can also be an expression.
Maybe you can come up with some smart logic.sql
Increase one month
Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
MichaelTry with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> > Hi,
> > I have a date value of 4/1/2005, I wanted to increase just one month
> > to 5/1/2005. I have millions of records like that to increase just one
> > month. Is there a function I can use?
> > Thanks a lot!!
> > Michael
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
thanks a lot all!!!
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
MichaelTry with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> > Hi,
> > I have a date value of 4/1/2005, I wanted to increase just one month
> > to 5/1/2005. I have millions of records like that to increase just one
> > month. Is there a function I can use?
> > Thanks a lot!!
> > Michael
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
thanks a lot all!!!
Increase one month
Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael
Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegr oups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegr oups.com...
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
thanks a lot all!!!
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael
Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegr oups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegr oups.com...
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
thanks a lot all!!!
Increase one month
Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
>
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,
SQL.90).aspx
> --
thanks a lot all!!!
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
>
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,
SQL.90).aspx
> --
thanks a lot all!!!
Increase of Lock Waits per minute with Mirroring
Hey All:
About a month ago I setup mirroring with our DB, High safety with automatic failover. Ever since the lock waits per minute in the DB went from maybe 2-5 per minute to 22-25 per minute. I am not sure if this was expected or what; but sometimes it spikes even higher than that.
Does this sound off the charts to anyone or normal for mirroring?
Thanks,
Let me know if you need anymore info....
What is the latency between your primary server and the backup server?
This may be normal as transactions aren't commited until after the command is sent to the backup server and the ACK is returned to the primary server. This would cause the locks to be held longer.
|||Latency has be low. If I go back to the last four hours (typical production day) it was 0-5 ms. I have seen it spike more but rarely. Mainly the lock waits were my concern, because they have certainly gone up and stayed up since mirroring started.thanks
|||I should have been more clear. I was asking about network latency from your production server to the backup server and back. IE. Ping from one machine to the other, and multiply by two.|||oh ok, so if I ping from production server to the backup server; all four come back with less than 1ms. And if I go from the backup to the production it is the same.
|||Are your mirroring endpoints are set up with encryption. If you have encryption on and can remove it you will see a ten fold increase in transaction speed in a mirror session.|||There is a database mirroring counter "Transaction Delay" that can help you determine how much latency mirroring is adding to your transactions. Some applications can certainly notice it more than others in terms of lock waits.
Increase of Lock Waits per minute with Mirroring
Hey All:
About a month ago I setup mirroring with our DB, High safety with automatic failover. Ever since the lock waits per minute in the DB went from maybe 2-5 per minute to 22-25 per minute. I am not sure if this was expected or what; but sometimes it spikes even higher than that.
Does this sound off the charts to anyone or normal for mirroring?
Thanks,
Let me know if you need anymore info....
What is the latency between your primary server and the backup server?
This may be normal as transactions aren't commited until after the command is sent to the backup server and the ACK is returned to the primary server. This would cause the locks to be held longer.
|||Latency has be low. If I go back to the last four hours (typical production day) it was 0-5 ms. I have seen it spike more but rarely. Mainly the lock waits were my concern, because they have certainly gone up and stayed up since mirroring started.thanks
|||I should have been more clear. I was asking about network latency from your production server to the backup server and back. IE. Ping from one machine to the other, and multiply by two.|||oh ok, so if I ping from production server to the backup server; all four come back with less than 1ms. And if I go from the backup to the production it is the same.
|||Are your mirroring endpoints are set up with encryption. If you have encryption on and can remove it you will see a ten fold increase in transaction speed in a mirror session.|||There is a database mirroring counter "Transaction Delay" that can help you determine how much latency mirroring is adding to your transactions. Some applications can certainly notice it more than others in terms of lock waits.sql
Increase nvarchar field value like a num
Hi everybody,
i want write a stored procedure. This is increase NVARCHAR (7) field like a
number.
Example :
A00001
A00002
:
:
A99999
B00001
:
:
Z99999
AA00001
:
ZZ99999
:
Error
How can i do ? can i do this with t-sql?
thanksUse an insert trigger
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> i want write a stored procedure. This is increase NVARCHAR (7) field like
> a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
> thanks
>|||Stored procedure must return a value
"Martin" <x@.y.z>, haber iletisinde unlar
yazd:ORRgvV18FHA.476@.TK2MSFTNGP15.phx.gbl...
> Use an insert trigger
> "SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
> news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||The fastest way to to do this would be to use a lookup table; set a
bigint value to be the order-determinant (eg, 1, 2, 3,) and use the
other values as a lookup:
CREATE TABLE (ID bigint, Value NVARCHAR(7))
INSERT INTO TABLE (ID, Value)
--write a routine to populate this
VALUES (1, 'A00001')
Your stored procedure would then return the ID value bases on the
values you supply, increment the ID by one, and return the next value
in sequence. Kind of like a calendar table or a table of numbers.
HTH,
Stu|||SharkSpeed (sharkspeedtr@.yahoo.com) writes:
> i want write a stored procedure. This is increase NVARCHAR (7) field
> like a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
DECLARE @.letters varchar(2)
@.digits varchar(5)
SELECT @.digits = right(@.input, 5),
@.letters = substring(@.input, 1,
CASE len(@.input) WHEN 6 THEN 1 ELSE 2 END)
IF @.digits <> '99999'
BEGIN
SELECT @.digits = substring(convert(varchar(
convert(int, @.digits) + 100001)), 2, 5)
END
ELSE IF len(@.letters) = 1 and @.letters <> 'Z'
SELECT @.letters = char(ascii(@.letters) + 1))
ELSE IF @.letters = 'Z'
SELECT @.letters = 'AA'
ELSE IF @.letters NOT LIKE '_Z'
SELECT @.letters = substring(@.letters, 1, 1) +
char(ascii(substring(@.letters, 2, 1) + 1))
ELSE IF @.letters <> 'ZZ'
SELECT @.letters = char(ascii(substring(@.letters, 1, 1)) + 1) + 'A'
ELSE
RAISERROR ('Cannot compute a successor key to ZZ99999', 16, 1)
I did not test this, nor did I try to compile. You should be able to
make something out of it anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In line with what Erland posted I had started on something.
I have now also tested so you could implement this as it is.. no
warranties though.
first create this table:
CREATE TABLE nextIDTable (preChar varchar(2), postInt int)
INSERT INTO nextIDTable (preChar, postInt) values ('A', 1)
Then once you have the table and inserted the values above you can
implement the stored procedure, calling this will increment the varchar
"number" as you described you wanted:
CREATE PROC getNextID
@.nextID nvarchar(7) OUTPUT
AS
DECLARE @.MyCounter INT, @.LeadingZeros char(4), @.preChar varchar(2),
@.postInt int
-- Initialize the variable.
SET @.MyCounter = 0
SET @.postInt = (SELECT postInt FROM nextIDTable)
SET @.preChar = (SELECT RTRIM(preChar) FROM nextIDTable)
IF(@.postInt < 10) SET @.LeadingZeros = '0000'
IF(@.postInt >= 10 AND @.postInt < 100) SET @.LeadingZeros = '000'
IF(@.postInt >= 100 AND @.postInt < 1000) SET @.LeadingZeros = '00'
IF(@.postInt >= 1000 AND @.postInt < 90001) SET @.LeadingZeros = '0'
WHILE (@.MyCounter <= 51)
BEGIN
-- the loop is exited when @.MyCounter reaches -1
-- as all from ZZ to A have been checked
IF @.MyCounter = -1 return
-- for A through to Z
IF(@.MyCounter <= 25)
BEGIN
IF(@.postInt = 99999 and @.preChar = 'Z')
BEGIN
SET @.nextID = 'AA00001'
UPDATE nextIDTable SET preChar = 'AA', postInt = 1
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'Z')
BEGIN
IF(@.preChar = (CHAR(((@.MyCounter) + ASCII('A')))))
BEGIN
SET @.nextID = (CHAR(((@.MyCounter + 1) + ASCII('A')))) + '00001'
UPDATE nextIDTable SET preChar = CHAR(((@.MyCounter + 1) +
ASCII('A'))), postInt = 1
BREAK
END
END
ELSE
BEGIN
SET @.nextID = (@.preChar + RTRIM(@.LeadingZeros) + (CONVERT( char,
@.postInt)))
UPDATE nextIDTable SET postInt = postInt + 1
BREAK
END
END
-- for AA through to ZZ
IF(@.MyCounter > 25)
BEGIN
IF(@.postInt = 99999 AND @.preChar = 'ZZ')
BEGIN
-- reached the max value
RAISERROR('reached max val', 16, 1)
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
-- next char sequence + 00001
SET @.nextID = CHAR(((@.MyCounter - 26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + '00001'
UPDATE nextIDTable SET preChar = CHAR((@.MyCounter-26 + ASCII('A')))
+ CHAR((@.MyCounter-26 + ASCII('A'))), postInt = 1
BREAK
END
IF(@.postInt < 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
SET @.nextID = CHAR(((@.MyCounter-26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + RTRIM(@.LeadingZeros) + CONVERT(
char, @.postInt)
UPDATE nextIDTable SET postInt = @.postInt + 1
BREAK
END
END
SET @.MyCounter = @.MyCounter + 1
END
GO
good luck with it..
Gerard|||actually I just found there is a wee bug in the part after
IF(@.MyCounter > 25)
if your value is AA99999 it will jump to GG00001 but I think there's
enough here to make this work
Gerard
i want write a stored procedure. This is increase NVARCHAR (7) field like a
number.
Example :
A00001
A00002
:
:
A99999
B00001
:
:
Z99999
AA00001
:
ZZ99999
:
Error
How can i do ? can i do this with t-sql?
thanksUse an insert trigger
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> i want write a stored procedure. This is increase NVARCHAR (7) field like
> a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
> thanks
>|||Stored procedure must return a value
"Martin" <x@.y.z>, haber iletisinde unlar
yazd:ORRgvV18FHA.476@.TK2MSFTNGP15.phx.gbl...
> Use an insert trigger
> "SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
> news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||The fastest way to to do this would be to use a lookup table; set a
bigint value to be the order-determinant (eg, 1, 2, 3,) and use the
other values as a lookup:
CREATE TABLE (ID bigint, Value NVARCHAR(7))
INSERT INTO TABLE (ID, Value)
--write a routine to populate this
VALUES (1, 'A00001')
Your stored procedure would then return the ID value bases on the
values you supply, increment the ID by one, and return the next value
in sequence. Kind of like a calendar table or a table of numbers.
HTH,
Stu|||SharkSpeed (sharkspeedtr@.yahoo.com) writes:
> i want write a stored procedure. This is increase NVARCHAR (7) field
> like a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
DECLARE @.letters varchar(2)
@.digits varchar(5)
SELECT @.digits = right(@.input, 5),
@.letters = substring(@.input, 1,
CASE len(@.input) WHEN 6 THEN 1 ELSE 2 END)
IF @.digits <> '99999'
BEGIN
SELECT @.digits = substring(convert(varchar(
convert(int, @.digits) + 100001)), 2, 5)
END
ELSE IF len(@.letters) = 1 and @.letters <> 'Z'
SELECT @.letters = char(ascii(@.letters) + 1))
ELSE IF @.letters = 'Z'
SELECT @.letters = 'AA'
ELSE IF @.letters NOT LIKE '_Z'
SELECT @.letters = substring(@.letters, 1, 1) +
char(ascii(substring(@.letters, 2, 1) + 1))
ELSE IF @.letters <> 'ZZ'
SELECT @.letters = char(ascii(substring(@.letters, 1, 1)) + 1) + 'A'
ELSE
RAISERROR ('Cannot compute a successor key to ZZ99999', 16, 1)
I did not test this, nor did I try to compile. You should be able to
make something out of it anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In line with what Erland posted I had started on something.
I have now also tested so you could implement this as it is.. no
warranties though.
first create this table:
CREATE TABLE nextIDTable (preChar varchar(2), postInt int)
INSERT INTO nextIDTable (preChar, postInt) values ('A', 1)
Then once you have the table and inserted the values above you can
implement the stored procedure, calling this will increment the varchar
"number" as you described you wanted:
CREATE PROC getNextID
@.nextID nvarchar(7) OUTPUT
AS
DECLARE @.MyCounter INT, @.LeadingZeros char(4), @.preChar varchar(2),
@.postInt int
-- Initialize the variable.
SET @.MyCounter = 0
SET @.postInt = (SELECT postInt FROM nextIDTable)
SET @.preChar = (SELECT RTRIM(preChar) FROM nextIDTable)
IF(@.postInt < 10) SET @.LeadingZeros = '0000'
IF(@.postInt >= 10 AND @.postInt < 100) SET @.LeadingZeros = '000'
IF(@.postInt >= 100 AND @.postInt < 1000) SET @.LeadingZeros = '00'
IF(@.postInt >= 1000 AND @.postInt < 90001) SET @.LeadingZeros = '0'
WHILE (@.MyCounter <= 51)
BEGIN
-- the loop is exited when @.MyCounter reaches -1
-- as all from ZZ to A have been checked
IF @.MyCounter = -1 return
-- for A through to Z
IF(@.MyCounter <= 25)
BEGIN
IF(@.postInt = 99999 and @.preChar = 'Z')
BEGIN
SET @.nextID = 'AA00001'
UPDATE nextIDTable SET preChar = 'AA', postInt = 1
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'Z')
BEGIN
IF(@.preChar = (CHAR(((@.MyCounter) + ASCII('A')))))
BEGIN
SET @.nextID = (CHAR(((@.MyCounter + 1) + ASCII('A')))) + '00001'
UPDATE nextIDTable SET preChar = CHAR(((@.MyCounter + 1) +
ASCII('A'))), postInt = 1
BREAK
END
END
ELSE
BEGIN
SET @.nextID = (@.preChar + RTRIM(@.LeadingZeros) + (CONVERT( char,
@.postInt)))
UPDATE nextIDTable SET postInt = postInt + 1
BREAK
END
END
-- for AA through to ZZ
IF(@.MyCounter > 25)
BEGIN
IF(@.postInt = 99999 AND @.preChar = 'ZZ')
BEGIN
-- reached the max value
RAISERROR('reached max val', 16, 1)
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
-- next char sequence + 00001
SET @.nextID = CHAR(((@.MyCounter - 26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + '00001'
UPDATE nextIDTable SET preChar = CHAR((@.MyCounter-26 + ASCII('A')))
+ CHAR((@.MyCounter-26 + ASCII('A'))), postInt = 1
BREAK
END
IF(@.postInt < 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
SET @.nextID = CHAR(((@.MyCounter-26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + RTRIM(@.LeadingZeros) + CONVERT(
char, @.postInt)
UPDATE nextIDTable SET postInt = @.postInt + 1
BREAK
END
END
SET @.MyCounter = @.MyCounter + 1
END
GO
good luck with it..
Gerard|||actually I just found there is a wee bug in the part after
IF(@.MyCounter > 25)
if your value is AA99999 it will jump to GG00001 but I think there's
enough here to make this work
Gerard
Increase number of process(thread)
Hello all,
A software that connects SQL Server via ODBC uses 12 process at the same
time when I look at the process info(panel). Is it possible to increase
number of process (or thread) for a specific database? Is there any
parameter?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comDo Park via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> A software that connects SQL Server via ODBC uses 12 process at the same
> time when I look at the process info(panel). Is it possible to increase
> number of process (or thread) for a specific database? Is there any
> parameter?
You can change the number of permitted connections with
sp_configure 'user connections', 100 -- 100 is an example here
This is a server-wide setting. There is no per-database setting for this
(and neither would it be really meaningful).
However, the default for this option is 0, which means that the server
configures as it goes on.
Are you getting any error messages about running out of connections?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Do Park via SQLMonster.com wrote:
> Hello all,
> A software that connects SQL Server via ODBC uses 12 process at the same
> time when I look at the process info(panel). Is it possible to increase
> number of process (or thread) for a specific database? Is there any
> parameter?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
You will find that this is a programed function of the software itself,
not SQL Server.
See if there is a configuration setting you can use to adjust it.
You could also download a copy of the database monitor I have developed
which will tell you what load the software application is having on the
server. This will help determine if it is safe to up the number of
connections or not. It will also tell you if they are locking
themselves out.
You can download it from http://dbmonitor.tripod.com.
Increase number by 1
Hello all,
I have, what i think, is a unique problem that i'm hoping some of you can help me on.
I have, what i think, is a unique problem that i'm hoping some of you can help me on.
I need to create a record number that is incremented by 1 whenever someone adds a new record to the database. For example, records numbering 1,2,3 are in the database. When the users adds a new record, SQL takes the last recordno, 3 in this case, and adds 1 to it thus producing 4.
Also, i need to have the ability to replace deleted record numbers with new ones. Using the example above, say a user deletes record number 2. Whenever someone adds a new record, sql would see the missing number and assign the new record that number.
I hope i'm making sense here. Does anyone have any ideas about this? Any articles on the web that someone could point me to?
Thanks.
Richard M.hi richard,
i guess you need to do this by coding urself. u can use the feature in Sql server to increment the number by one but i don't think its possible to replace the deleted number.
so the better solution will be to add the incrementing number programatically. first declare int data type in sql server and assign 1 for the first record. for new records, check whether any number is missing and try to add new into that.
for eg, if you have 10 records, then no of 10th record should be 10 else some record is deleted. so u can use loop to check which number is missing.
i hope u can do the coding.
Increase Indentity value
How do I increase the current Indentity value for an existing Indentity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want it to return: 99999
dbcc checkident
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>
|||Ken,
DBCC CHECKIDENT (Quote, RESEED, 99999)
GO
Cheers,
Paul
|||http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>
|||Excellent, I have bookmarked this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Aaron [SQL Server MVP] wrote:
> http://www.aspfaq.com/5003
>
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want it to return: 99999
dbcc checkident
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>
|||Ken,
DBCC CHECKIDENT (Quote, RESEED, 99999)
GO
Cheers,
Paul
|||http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>
|||Excellent, I have bookmarked this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Aaron [SQL Server MVP] wrote:
> http://www.aspfaq.com/5003
>
Increase Indentity value
How do I increase the current Indentity value for an existing Indentity colu
mn?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want it to return: 99999dbcc checkident
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>|||Ken,
DBCC CHECKIDENT (Quote, RESEED, 99999)
GO
Cheers,
Paul|||http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>|||Excellent, I have bookmarked this.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Aaron [SQL Server MVP] wrote:
> http://www.aspfaq.com/5003
>sql
mn?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want it to return: 99999dbcc checkident
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>|||Ken,
DBCC CHECKIDENT (Quote, RESEED, 99999)
GO
Cheers,
Paul|||http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>|||Excellent, I have bookmarked this.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Aaron [SQL Server MVP] wrote:
> http://www.aspfaq.com/5003
>sql
increase in the size of tempDB
Hi,
the size of the tempDB increased drastically all of a sudden.
How do i find out the cause for the same
thnks
anu
It could be just about anything. You can have a Profiler trace running and correlate what happened
at the time the size increased, but I don't know of a way to determine this after the fact (without
that profiler trace).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu
|||thnks tibor...
im new to sql dba works...
can you help me out with some url / link / info on how i can reset the
size... itz grown to arond 130 GB now...
thnks in advance
anu
"Tibor Karaszi" wrote:
> It could be just about anything. You can have a Profiler trace running and correlate what happened
> at the time the size increased, but I don't know of a way to determine this after the fact (without
> that profiler trace).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
>
>
|||Ahuradha
The reason for growing temdbd database might be a long running query / lots
of sort operations that have done by the query
I think Aaron has written a great article at his site
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu
|||Easiest way is to restart SQL Server, as tempdb is re-created each time SQL server starts (size for
tempdb at startup is drawn from master..sysaltfiles, which you can set using ALTER DATABASE)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C415A0D1-6EDF-4D91-B878-205CE63A03F5@.microsoft.com...[vbcol=seagreen]
> thnks tibor...
> im new to sql dba works...
> can you help me out with some url / link / info on how i can reset the
> size... itz grown to arond 130 GB now...
>
> thnks in advance
> anu
> "Tibor Karaszi" wrote:
happened[vbcol=seagreen]
(without[vbcol=seagreen]
|||thnks uri,
can u pls mail me the url ?
"Uri Dimant" wrote:
> Ahuradha
> The reason for growing temdbd database might be a long running query / lots
> of sort operations that have done by the query
> I think Aaron has written a great article at his site
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
>
>
|||http://www.aspfaq.com/show.asp?id=2446
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:780932A8-2D49-4092-9D91-5C24E0D6AA43@.microsoft.com...[vbcol=seagreen]
> thnks uri,
> can u pls mail me the url ?
>
> "Uri Dimant" wrote:
the size of the tempDB increased drastically all of a sudden.
How do i find out the cause for the same
thnks
anu
It could be just about anything. You can have a Profiler trace running and correlate what happened
at the time the size increased, but I don't know of a way to determine this after the fact (without
that profiler trace).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu
|||thnks tibor...
im new to sql dba works...
can you help me out with some url / link / info on how i can reset the
size... itz grown to arond 130 GB now...
thnks in advance
anu
"Tibor Karaszi" wrote:
> It could be just about anything. You can have a Profiler trace running and correlate what happened
> at the time the size increased, but I don't know of a way to determine this after the fact (without
> that profiler trace).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
>
>
|||Ahuradha
The reason for growing temdbd database might be a long running query / lots
of sort operations that have done by the query
I think Aaron has written a great article at his site
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu
|||Easiest way is to restart SQL Server, as tempdb is re-created each time SQL server starts (size for
tempdb at startup is drawn from master..sysaltfiles, which you can set using ALTER DATABASE)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C415A0D1-6EDF-4D91-B878-205CE63A03F5@.microsoft.com...[vbcol=seagreen]
> thnks tibor...
> im new to sql dba works...
> can you help me out with some url / link / info on how i can reset the
> size... itz grown to arond 130 GB now...
>
> thnks in advance
> anu
> "Tibor Karaszi" wrote:
happened[vbcol=seagreen]
(without[vbcol=seagreen]
|||thnks uri,
can u pls mail me the url ?
"Uri Dimant" wrote:
> Ahuradha
> The reason for growing temdbd database might be a long running query / lots
> of sort operations that have done by the query
> I think Aaron has written a great article at his site
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
>
>
|||http://www.aspfaq.com/show.asp?id=2446
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:780932A8-2D49-4092-9D91-5C24E0D6AA43@.microsoft.com...[vbcol=seagreen]
> thnks uri,
> can u pls mail me the url ?
>
> "Uri Dimant" wrote:
increase in the size of tempDB
Hi,
the size of the tempDB increased drastically all of a sudden.
How do i find out the cause for the same
thnks
anuIt could be just about anything. You can have a Profiler trace running and c
orrelate what happened
at the time the size increased, but I don't know of a way to determine this
after the fact (without
that profiler trace).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||thnks tibor...
im new to sql dba works...
can you help me out with some url / link / info on how i can reset the
size... itz grown to arond 130 GB now...
thnks in advance
anu
"Tibor Karaszi" wrote:
> It could be just about anything. You can have a Profiler trace running and
correlate what happened
> at the time the size increased, but I don't know of a way to determine thi
s after the fact (without
> that profiler trace).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
>
>|||Ahuradha
The reason for growing temdbd database might be a long running query / lots
of sort operations that have done by the query
I think Aaron has written a great article at his site
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||Easiest way is to restart SQL Server, as tempdb is re-created each time SQL
server starts (size for
tempdb at startup is drawn from master..sysaltfiles, which you can set using
ALTER DATABASE)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C415A0D1-6EDF-4D91-B878-205CE63A03F5@.microsoft.com...[vbcol=seagreen]
> thnks tibor...
> im new to sql dba works...
> can you help me out with some url / link / info on how i can reset the
> size... itz grown to arond 130 GB now...
>
> thnks in advance
> anu
> "Tibor Karaszi" wrote:
>
happened[vbcol=seagreen]
(without[vbcol=seagreen]|||thnks uri,
can u pls mail me the url ?
"Uri Dimant" wrote:
> Ahuradha
> The reason for growing temdbd database might be a long running query / lot
s
> of sort operations that have done by the query
> I think Aaron has written a great article at his site
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
>
>|||http://www.aspfaq.com/show.asp?id=2446
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:780932A8-2D49-4092-9D91-5C24E0D6AA43@.microsoft.com...[vbcol=seagreen]
> thnks uri,
> can u pls mail me the url ?
>
> "Uri Dimant" wrote:
>
the size of the tempDB increased drastically all of a sudden.
How do i find out the cause for the same
thnks
anuIt could be just about anything. You can have a Profiler trace running and c
orrelate what happened
at the time the size increased, but I don't know of a way to determine this
after the fact (without
that profiler trace).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||thnks tibor...
im new to sql dba works...
can you help me out with some url / link / info on how i can reset the
size... itz grown to arond 130 GB now...
thnks in advance
anu
"Tibor Karaszi" wrote:
> It could be just about anything. You can have a Profiler trace running and
correlate what happened
> at the time the size increased, but I don't know of a way to determine thi
s after the fact (without
> that profiler trace).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
>
>|||Ahuradha
The reason for growing temdbd database might be a long running query / lots
of sort operations that have done by the query
I think Aaron has written a great article at his site
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||Easiest way is to restart SQL Server, as tempdb is re-created each time SQL
server starts (size for
tempdb at startup is drawn from master..sysaltfiles, which you can set using
ALTER DATABASE)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C415A0D1-6EDF-4D91-B878-205CE63A03F5@.microsoft.com...[vbcol=seagreen]
> thnks tibor...
> im new to sql dba works...
> can you help me out with some url / link / info on how i can reset the
> size... itz grown to arond 130 GB now...
>
> thnks in advance
> anu
> "Tibor Karaszi" wrote:
>
happened[vbcol=seagreen]
(without[vbcol=seagreen]|||thnks uri,
can u pls mail me the url ?
"Uri Dimant" wrote:
> Ahuradha
> The reason for growing temdbd database might be a long running query / lot
s
> of sort operations that have done by the query
> I think Aaron has written a great article at his site
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
>
>|||http://www.aspfaq.com/show.asp?id=2446
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:780932A8-2D49-4092-9D91-5C24E0D6AA43@.microsoft.com...[vbcol=seagreen]
> thnks uri,
> can u pls mail me the url ?
>
> "Uri Dimant" wrote:
>
increase in the size of tempDB
Hi,
the size of the tempDB increased drastically all of a sudden.
How do i find out the cause for the same
thnks
anuIt could be just about anything. You can have a Profiler trace running and correlate what happened
at the time the size increased, but I don't know of a way to determine this after the fact (without
that profiler trace).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||thnks tibor...
im new to sql dba works...
can you help me out with some url / link / info on how i can reset the
size... itz grown to arond 130 GB now...
thnks in advance
anu
"Tibor Karaszi" wrote:
> It could be just about anything. You can have a Profiler trace running and correlate what happened
> at the time the size increased, but I don't know of a way to determine this after the fact (without
> that profiler trace).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > Hi,
> >
> > the size of the tempDB increased drastically all of a sudden.
> >
> > How do i find out the cause for the same
> >
> > thnks
> >
> > anu
>
>|||Ahuradha
The reason for growing temdbd database might be a long running query / lots
of sort operations that have done by the query
I think Aaron has written a great article at his site
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||Easiest way is to restart SQL Server, as tempdb is re-created each time SQL server starts (size for
tempdb at startup is drawn from master..sysaltfiles, which you can set using ALTER DATABASE)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C415A0D1-6EDF-4D91-B878-205CE63A03F5@.microsoft.com...
> thnks tibor...
> im new to sql dba works...
> can you help me out with some url / link / info on how i can reset the
> size... itz grown to arond 130 GB now...
>
> thnks in advance
> anu
> "Tibor Karaszi" wrote:
> > It could be just about anything. You can have a Profiler trace running and correlate what
happened
> > at the time the size increased, but I don't know of a way to determine this after the fact
(without
> > that profiler trace).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> > news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > > Hi,
> > >
> > > the size of the tempDB increased drastically all of a sudden.
> > >
> > > How do i find out the cause for the same
> > >
> > > thnks
> > >
> > > anu
> >
> >
> >|||thnks uri,
can u pls mail me the url ?
"Uri Dimant" wrote:
> Ahuradha
> The reason for growing temdbd database might be a long running query / lots
> of sort operations that have done by the query
> I think Aaron has written a great article at his site
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > Hi,
> >
> > the size of the tempDB increased drastically all of a sudden.
> >
> > How do i find out the cause for the same
> >
> > thnks
> >
> > anu
>
>|||http://www.aspfaq.com/show.asp?id=2446
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:780932A8-2D49-4092-9D91-5C24E0D6AA43@.microsoft.com...
> thnks uri,
> can u pls mail me the url ?
>
> "Uri Dimant" wrote:
> > Ahuradha
> > The reason for growing temdbd database might be a long running query / lots
> > of sort operations that have done by the query
> > I think Aaron has written a great article at his site
> >
> >
> >
> > "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> > news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > > Hi,
> > >
> > > the size of the tempDB increased drastically all of a sudden.
> > >
> > > How do i find out the cause for the same
> > >
> > > thnks
> > >
> > > anu
> >
> >
> >
the size of the tempDB increased drastically all of a sudden.
How do i find out the cause for the same
thnks
anuIt could be just about anything. You can have a Profiler trace running and correlate what happened
at the time the size increased, but I don't know of a way to determine this after the fact (without
that profiler trace).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||thnks tibor...
im new to sql dba works...
can you help me out with some url / link / info on how i can reset the
size... itz grown to arond 130 GB now...
thnks in advance
anu
"Tibor Karaszi" wrote:
> It could be just about anything. You can have a Profiler trace running and correlate what happened
> at the time the size increased, but I don't know of a way to determine this after the fact (without
> that profiler trace).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > Hi,
> >
> > the size of the tempDB increased drastically all of a sudden.
> >
> > How do i find out the cause for the same
> >
> > thnks
> >
> > anu
>
>|||Ahuradha
The reason for growing temdbd database might be a long running query / lots
of sort operations that have done by the query
I think Aaron has written a great article at his site
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||Easiest way is to restart SQL Server, as tempdb is re-created each time SQL server starts (size for
tempdb at startup is drawn from master..sysaltfiles, which you can set using ALTER DATABASE)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C415A0D1-6EDF-4D91-B878-205CE63A03F5@.microsoft.com...
> thnks tibor...
> im new to sql dba works...
> can you help me out with some url / link / info on how i can reset the
> size... itz grown to arond 130 GB now...
>
> thnks in advance
> anu
> "Tibor Karaszi" wrote:
> > It could be just about anything. You can have a Profiler trace running and correlate what
happened
> > at the time the size increased, but I don't know of a way to determine this after the fact
(without
> > that profiler trace).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> > news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > > Hi,
> > >
> > > the size of the tempDB increased drastically all of a sudden.
> > >
> > > How do i find out the cause for the same
> > >
> > > thnks
> > >
> > > anu
> >
> >
> >|||thnks uri,
can u pls mail me the url ?
"Uri Dimant" wrote:
> Ahuradha
> The reason for growing temdbd database might be a long running query / lots
> of sort operations that have done by the query
> I think Aaron has written a great article at his site
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > Hi,
> >
> > the size of the tempDB increased drastically all of a sudden.
> >
> > How do i find out the cause for the same
> >
> > thnks
> >
> > anu
>
>|||http://www.aspfaq.com/show.asp?id=2446
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:780932A8-2D49-4092-9D91-5C24E0D6AA43@.microsoft.com...
> thnks uri,
> can u pls mail me the url ?
>
> "Uri Dimant" wrote:
> > Ahuradha
> > The reason for growing temdbd database might be a long running query / lots
> > of sort operations that have done by the query
> > I think Aaron has written a great article at his site
> >
> >
> >
> > "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> > news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > > Hi,
> > >
> > > the size of the tempDB increased drastically all of a sudden.
> > >
> > > How do i find out the cause for the same
> > >
> > > thnks
> > >
> > > anu
> >
> >
> >
Subscribe to:
Posts (Atom)