Mirror Goes Down... Affect Client Transactions?
Jan 2, 2007If the mirror server goes down for any reason, then what happens to client transactions against the principle?
Will any client transactions fail?
Thanks in advance
ADVERTISEMENT
Does Service Pack 2 Affect Client Tools?
Jun 5, 2007Hello,
Does Service Pack 2 affect client tools? In other words, if you have a client machine with just the tools installed (management studio, BIDS, etc) do I need to run the SP2 package on this client as well?
Regards, Jeroen
Suspend All Transactions For A Split-Mirror Backup Strategy?
May 11, 2006We currently use a split-mirror backup strategy for our Sybase database, which has a "quiesce database" command to suspend all transactions. By quiescing the database before splitting the mirror, we suspend all transactions to ensure we get a stable backup of the environment. It works very well for us and I'm trying to understand how we could implement this with our SQL Server 2005 DB.
(I'm aware of SQL Server mirroring and that there are other ways of possibly backing up the DB. In this post however, I'm only interested in how I would make the split-mirror strategy work if I wanted to pursue it. I'm trying to avoid paying for software that uses the VDI as it's quite costly.)
Can someone help me with how I would accomplish a split-mirror backup strategy in SQL Server 2005 (without using a vendor's software that uses the VDI)? I have to imagine there's something similar to the "quiesce database" command in SQL Server...
I appreciate the help!
Grouping Transactions Quarterly By Client
Dec 5, 2007I have transaction table I am pulling transactions from and can't quite figure out how to get a sum based on a few items.
I am trying to figure out how to show the sum of the InvestorAssumption and group it based on the DealId, then DealTransType and show the sum of the InvestorAssumption.
So I want to see a total of the InvestorAssumption by quarter split up by DealTransType, then DealId.
Not sure how to do this and any help would be greatly appreciated.
Here is my current view.
Code:
SELECT TOP 100 PERCENT dbo.DealTransactions.DealTransId, dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, DATEADD(quarter,
DATEDIFF(quarter, 0, dbo.DealTransactions.DealTransDate), 0) AS TransDateQuart, dbo.DealTransactions.DealTransType,
dbo.DealTransTypes.DealTransTypeName AS TransactionType, dbo.DealTransactions.DealTransAmount,
dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100 AS InvestorAssumption,
dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransDate AS TransDateActual
FROM dbo.DealTransactions INNER JOIN
dbo.DSGvwInvestorPercentage ON dbo.DealTransactions.DealId = dbo.DSGvwInvestorPercentage.DealID INNER JOIN
dbo.DealTransTypes ON dbo.DealTransactions.DealTransType = dbo.DealTransTypes.DealTransTypeId
GROUP BY dbo.DealTransTypes.DealTransTypeName, dbo.DealTransactions.DealTransAmount, dbo.DealTransactions.DealId,
dbo.DSGvwInvestorPercentage.InvestorID, dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100,
dbo.DealTransactions.DealTransType, dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransId,
dbo.DealTransactions.DealTransDate
ORDER BY dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, dbo.DealTransTypes.DealTransTypeName,
dbo.DealTransactions.DealTransAmount
Using Join To Match Transactions To Client Data
Apr 10, 2001I am running SQL2k on Win2k sp1. I need to match a transaction record (using txn_date) to client data as it would have appeared on the date of the transaction (using client_last_update).
A new row is inserted into my client table every time any part of the clients information changes, thereby leaving the change history in tact. The client_last_update field holds the date the change took place. It is required that the user be provided the client information that was current on the day of the transaction.
Example: A client record (name, address, accountno) was inserted to the client table on 2/1/2000 (the last_update field is empty because it's a new record).
The client record is modified on 4/15/2000 for a name change (making the last_update = 4/15/2000), and again on 12/5/2000 (making the last_update = 12/5/2000).
Transactions are made by the client on 2/5/2000, 6/10/2000, and 3/25/2001. These dates are held in the txn_date field in the transaction table.
The client now has 3 transaction records in the transaction table for his/her account number and 3 client records in the client table.
The transaction on 2/5/2000 should be joined to the initial client record ((2/5/2000 > empty) AND (2/5/2000 < 4/15/2000))
The transaction on 6/10/2000 should be joined to the client record having the last_update = 4/15/2000 (6/10/2000 > 4/15/2000) AND (6/10/2000 < 12/5/2000))
The last transaction, executed on 3/25/2001 should be joined to the most current client record, which has last_update = 12/5/2000 (3/25/2001 > 12/5/2000).
I have been experimenting with the code below, and found it to only return client data where the client record update_date = '4/18/2000'.
select * from transaction t Left Outer Join client c On t.SEC_ACCT = c.Account_No and t.Txn_Date > coalesce(c.Last_Update_Date, '1/1/1900') and t.Txn_Date < (select min(c2.Last_Update_Date) from client c2 where c2.Last_Update_Date > coalesce(c.Last_Update_Date, '1/1/1900'))
An example of a record that DID get returned carried a txn_date of '1/2/2001', and possible client update_dates of: NULL, '5/13/1999', and '4/18/2000'. The row returned carried a client update_date of '4/18/2001', and rightly so.
An example of a record that DID NOT get returned carried a txn_date = '1/2/2001' and possible client update_dates of: NULL, and '6/11/1999. This row was not returned.
Transactions/Client-Side Redirect Issue
Mar 23, 2006Hi there.
With Client-Side Redirect, connections can be dinamically redirected in case of failover. But if a long transaction is running on the principal, when it fails the redirect doesn´t work with the current ado.net connection, connected to the database running that transaction.
So if the principal fails, current running transaction will be lost right ?
Thanks in advance.
Mirroring :: How To Reduce LDF Size While Mirror Enabled With Mirror And Witness Server
Jun 14, 2015I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies View RelatedMirror And Witness Connection In A Disconnected State Immediately After Adding Witness Server To Mirror Session.
Jan 31, 2008
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
Thanks,
Mirror Database-How Do I Remove It From Being The Mirror
Dec 27, 2006I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.
View 1 Replies View RelatedChanging Connection Transactions To Database Transactions
May 22, 2005Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
What Could Affect 'allow Updates' Option
Mar 19, 2002every morning I have 7-10 identical messages in error log
1.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2.
Error: 15457, Severity: 0, State: 1
3.
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
It is standby server with custom log shipping and DTS transfering logins every 15 min.
What could cause this error ?
Tempdb - Does It Affect Performance ?
Aug 7, 2001We think we're having performance problems, and among the areas of investigations is the tempdb database. Since it resets itself after SQL is restarted, is there a way to find out how big it has grown in the past ? Does leaving it at the default size cause a performace hit ?? Right now it's 8.75 MB, with 7.38 MB available, which sounds pretty harmless.
Any thoughts ?
SQL Profiler - How Does It Affect SQL Performance?
Jun 23, 2006Everywhere I read, it states that running SQL Profiler can affect performance of your SQL Server. My question is - how much of an impact will it really make? Will I see a 1% degredation in peformance? 5%? 50%? I haven't been able to find a good answer. We currently have SQL Profiler running all day long for almost 3 years, and the databases are still humming.
Is it the amount of data you are requesting from the trace that affects performance? There are some compliance tools out there (Idera Compliance Manager, IPLocks, etc) that run a profiler trace to get data. There are other DBAs in my organization who don't want to use them because "profiler traces will degrade my SQL Server performance". How true is this really.
Any help I can get would be extremely appreciated.
Thanks.
Does Multiplication With 1 Affect Query Performance?
May 20, 2008Does multiplication with 1 affect query performance?I have a a stored procedure that converts results to another unit if required. In alternative 1 below, the results are returned with a separate select statement if no conversion is necessary - in other words, no multiplication with a conversion factor is required. However, the code is not very nice since I need to repeat the select statement again in case a conversion is required, this time including the conversion factor.Alternative 2 uses cleaner-looking code. The conversion factor is set to 1 if no conversion is required, and a single SELECT statement is used to return the data. The @factor variable is defined as a float.I would rather use alternative 2, but I wonder if there is any performance penalty for doing that if no conversion is required since the results are always multiplied with the @factor? Or can SQL server somehow understand that @factor = 1 and no multiplication is required?--- Alternative 1: ---IF @fromunit_sid = @tounit_sid-- Return unconverted results
SELECT ISNULL(ls_totalWaterConsumption,0) AS ls_totalWaterConsumption,ls_theoreticalWaterConsumption AS ls_theoretical_WaterConsumption,ls_totalWaterConsumption - ls_theoreticalWaterConsumption AS ls_extra_WaterConsumption FROM Results WHERE scenario_id = @scenario_idELSEBEGIN
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT -- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_idEND --- Alternative 2: ---IF @fromunit_sid = @tounit_sidSET @factor = 1ELSE
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_id And another question: is using an IF function considerably faster than making a call to another stored procedure?In alternative 2 above I use an IF statement to check if @fromunit_sid = @tounit_sid, and . But in fact the function getConversionFactor that I'm calling does exactly the same thing: if I pass in identical from- and to-values, it simply returns 1, so I could omit the IF statement completely and just use alternative 3. But is it slower?--- Alternative 3 -- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
...
If One Of The BCP Fail. Does It Affect The Rest Of The Process??
Dec 8, 1998Hi, I am wrote the following code in one store procedure called p_bcp_all.
and then scheduled it to run over night. what if the first two bcp were successful but the third one failed. Is the whole procedure going to fail? also what if the first one failed, is the rest of the code going to be executed have the bcp process going for the second and the third table?
thanks for your adivce
regards Ali
create procedure p_bcp_all as
Exec master..xp_cmdshell "bcp servername..tblone in d:blone.txt /fd:formatfileblone.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tbltwo in d:bltwo.txt /fd:formatfilebltwo.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tblthree in d:blone.txt /fd:formatfileblthree .fmt /servername /Usa /password/b250000 /a8000"
GO
When Or Where Does To May Columns Or Tables Affect Performance?
May 4, 2001I have a db which I have little control over most of it's makeup because of the vendor supplied tools. We currently have over 700 tables and 19000 columns. Has anyone seen a problem or saturation pont with these kinds of numbers? The database delivered to the clients will be from 2-50 gig depending on the site. I can probably through hardware at problems, but if anyone has been down this road any suggestions are appreciated.
View 1 Replies View RelatedWill Database Shrinking Affect The Data?
Aug 6, 2004Hi all,
does the shrinking of the database affect the data in the database, such as will it change the data, or deleting the data away?
Thanx
Affect Of Deleting Error Messages
Jun 10, 2004I'm using a unique index on a table with ignore_dup_key to get around using distinct in a stored procedure. In Query analyser, when I issue the stored procedure, you get a message and also the data in the Grid. In a stored procedure call, initiated from MS Access, the message means I have to put additional client side programming to work around it. I know that duplicate keys are ignored; that is why I've created the table with this feature (unique index in combination with ignore_dup_key). So what's with the message. If I delete the Error message 3604 will I still get an error message?
View 6 Replies View RelatedWill Multiple Instances Affect Performance?
Mar 27, 2006I have two instances installed on my server. I have one database on the default instance and two very large reporting databases on the other instance.
Will having the default instance running affect the performance of the non-default instance where my reporting databases are?
Is Order By Affect The Query Speed
Aug 7, 2007hello,
I have a query that insert insert into new table , and then i select from this table,
if i add ORDER BY in the INSERT INTO script , does it affect the speed of the SELECT
i have big table that take about 70 sec
How Does Adding Constraints Affect Performance?
Jul 23, 2005I'm considering adding domain integrity checks to some of my database tableitems. How does adding such constraints affect SQL Server performance? Forexample, I have a simple constraint that restricts a couple of columns tohaving values within the values assigned in my application by anenumeration:(([Condition] >= 0 and [Condition] <=3) and ([Type] >= 0 and [Type] <=2))This enforces domain integrity for two enumerations having values 0, 1, 2, 3and 0, 1, 2 in the application. Is this an efficient way of performing suchchecks? What are the pitfalls of domain integrity checking?ThanksRobin
View 1 Replies View RelatedDoes 'Group By' Affect The Query Speed?
Nov 16, 2006i have a table such sa below:
Name1, Name2, Name3, Nam4, C1, C2,.., C100
and in this table, i have found index for Name1-Nam4,
i don't why sql below is very slow?
select
Name1, sum(C1), ...., Sum(C100)
from
(
select
Name1, Name2, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, Name4, C1, ...., C100
from
My_Table
group by Name1, Name2, Name3, Name4
) as T
group by Name1, Name2, Nam3
) as T
group by Name1, Name2
) as T
group by Name1
Does 'Group By' affect the speed of query?
Can Too Many Tables In The Database Affect Performance?
May 23, 2008We have more than 2000 tables in the database. Can existence of so many tables affect the performance on SQL SERVER 2005?
View 1 Replies View RelatedSkip Rows Affect The Results
Nov 28, 2007Dear all,
In Flat File Source properties windows there's Preview node, when we check that node there's an option to skip the data in how many rows. Is it affect the result ?
Best regards,
Hery
How To Test Column Value To Affect Calculation Of Another
Jan 30, 2008Hi,
I have a column in table which tells me whether Tax should be calculated on a price. This is stored in a column called taxIncluded. I have a 'price before tax' column e.g. grossPrice. I want to calculate the price after tax e.g. netPrice if the taxIncluded column is set to 1. How do I form my sql statement to test whether the taxIncluded is set to 1 & therefore add the tax at say 20%.
e.g.
id name grossPrice taxIncluded netPrice <--- calculated within SQL
-- ------------- ------------------- ---------------- -------------
1 Product1 10.00 1 12.00
2 Product2 20.00 0 20.00
Any Affect With Database If I Upgrade MS SQL7 To SQL2000 ?
Mar 17, 2001Im using MS SQL7 as database software ,but now I plan to upgrate SQL7 to SQL2000 . I would like to know that are there any affect on the old database that already use with sql7 after I upgrade to MSSQL2000 ?
Thanks
Sql Server 2005 SP2 Affect SSRS Reports.
Mar 19, 2008Hi,
I had a report (.rdl) using sql reporting services in sql server 2005, where it was running quite good.
I have just installed sql server 2005 sp2. After that, when I run the report through report viewer, the result of the report contains the first record of the dataset keep on repeating for all other records. If I run the dataset the results are correct,but if I preview it, then the first record alone repeating for all other records.
I feel that sp2 might cause this rendering problem. Any suggestion please?
Murali.
Direction Property Have No Affect On Table - Microsoft Bug!?
Apr 16, 2008Hi,
I am developing a report that should be localized dynamically. The report will have both arabic and western users. Therefore I need to use the direction property of some single text boxes, which works fine. But when setting the direction property to RTL for a table it has no affect!
You would assume that the whole tables content should be "reversed" and also the text in the table went from right to left. But no.
When viewing the report in IE and right clicked and click "View Source" you can see that the table do not have a direction style set, even though I set the property on the table in design mode.
I am going crazy about this issue! Could this be a MS bug?
I really need some help here...
PS. I have tried numerous of combinations of the different International properties to get the direction property to work - with no luck.
DB Engine :: Update Statement Affect TempDB?
Aug 3, 2015Could a simple update statement on a user database ever caused space usage in tempdb? Â Assuming the update statement fires no triggers and not using any temp tables?
IE:
User DatabaseA
Update TableX
Set col1 = X
Reason I ask is tempdb filled up and the only thing I could see running at that time was the update statement.
Client With Tcp/ip Produce "time Out Error" While Same Client While Switced To Named Pipes
May 28, 2001We have 15 clients running our applicaton
14 of then conected to SQL server using TCP/IP and it runs fine
1 of 15 when connected using TCP/IP produce "..Time out error "
but runs fine when swiched from TCP/IP to Named pipes
1.What area should we look to correct problem with Time out using TCP/IP ?
2. Where to get information about using TCP/IP via Named pipes ?
Install SQL Server2005 Error:[Native Client]Encryption Not Supported On The Client
May 10, 2006Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
View 78 Replies View RelatedClient Unable To Establish Connection Encryption Not Supported On SQL Server. (Microsoft SQL Native Client)
May 2, 2006On Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?
Setup Error : [SQL Native Client] Encryption Not Supported On The Client
Nov 1, 2006Hi,
I have SQL2000 installed as the default instance, and now I'm trying to install SQL 2005 standard edition as a named instance.
I receive this error :
SQL Server could not connect to database service for server configuration.. [SQL Native client] Encryption not supported on the client. However I'm able to install client tools
The setup works fine on other box with the same config : SQL 2000/Windows XP, is there any work around for this issue ?
In my SQL 2000 client network utilty "Force proctocol encryption " is desabled and did not find the setting for SQL 2005 !
Thank you