sqlserver2000 date: increased

Showing posts with label increased. Show all posts

Friday, March 30, 2012

Increased replication latency

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
>
>

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

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...
>

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 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
> >
> >
> >

increase CPU time

Hi All,
I've a problem regarding increased CPU time.....
I've a stored procedure which joins approx 6
table and i'm using table variable to hold data. Stored procedure is working
fine and absolutely OK but when it runs on LIVE server through Web
Application, It increase CPU time and increase READS Per runs.
i added SET NOCOUNT ON and SET NOCOUNT OFF
as well as i added WITH RECOMPILE option, but no use it is still giving too
much reads per run.
is anybody having solution of this problem? Please
reply me ASAP...
Sword is hanging on my neck ';' help me
Manish SukhijaManish
What reads? Logical?
How about indexes defined on the tables? Do you see the optimizer is
available to create an efficient execution plan , i mean it uses the
indexes?
Actually if want more accurate answer please post DDL+ Sample data
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is
> working
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving
> too
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhija|||Hi Uri,
as i defined that i've stored procedure which joins approximately 6
table and when it runs om live server and when it is being checked by hosted
party in Profiler it shows
database is producing a high amount of cpu usage as well as page
reads in a perticular Stored procedure...
then i put a index on a column of one of joined table,
it reduce some time, now i want to know that is this the only solution to
decrease CPU time or is there any other way around, if this is only way then
on which table and ofcourse on which column should i put index. As you know
Stored procedure joins 6 table and they are having so may columns.
so should i put index on all columns of 6
table but as fas as i know it's not best practise to put index on all column
s
it can make adverse afftect on performance.
make me right if i'm wrong... or is there any other
way to decrease CPU time in that Stored procedure.
If you want code of that Stored procedure
i'll send it to you...
Manish
"Uri Dimant" wrote:

> Manish
> What reads? Logical?
> How about indexes defined on the tables? Do you see the optimizer is
> available to create an efficient execution plan , i mean it uses the
> indexes?
> Actually if want more accurate answer please post DDL+ Sample data
>
>
>
> "Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in messag
e
> news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
>
>|||Manish
Do you have FOREIGN KEY constraints? Does the key of referncing table have
an index?
http://www.sql-server-performance.c...or_counters.asp
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:1A433AA4-D9A0-47E2-82A4-1EEB092DD27B@.microsoft.com...
> Hi Uri,
> as i defined that i've stored procedure which joins approximately
> 6
> table and when it runs om live server and when it is being checked by
> hosted
> party in Profiler it shows
> database is producing a high amount of cpu usage as well as page
> reads in a perticular Stored procedure...
> then i put a index on a column of one of joined table,
> it reduce some time, now i want to know that is this the only solution to
> decrease CPU time or is there any other way around, if this is only way
> then
> on which table and ofcourse on which column should i put index. As you
> know
> Stored procedure joins 6 table and they are having so may columns.
> so should i put index on all columns of 6
> table but as fas as i know it's not best practise to put index on all
> columns
> it can make adverse afftect on performance.
> make me right if i'm wrong... or is there any other
> way to decrease CPU time in that Stored procedure.
> If you want code of that Stored procedure
> i'll send it to you...
> Manish
>
> "Uri Dimant" wrote:
>|||You may want to look at DBCC SHOWCONTIG if you aren't already running some
kind of defrag maintenence on your indexes.
--
Regards,
Jamie
"Manish Sukhija" wrote:

> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is worki
ng
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving to
o
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhijasql

Friday, March 30, 2012

Increased replication latency

Increased performance using 2 servers and a SAN

Increased Datetime Precision

Increased Backup size for unknown reason

increase in the size of tempDB

increase in the size of tempDB

increase in the size of tempDB

increase CPU time

sqlserver2000 date

Blog Archive

About Me