Tuesday, August 6, 2013

[SQL Server 2008 issues] What happen to the redo queue?

[SQL Server 2008 issues] What happen to the redo queue?

What happen to the redo queue?

Posted: 05 Aug 2013 06:54 PM PDT

In Mirroring ,If the principal server suddenly fails or crashes when running synchronously,what happen to the redo queue ?Are these loges hardened to the mirror db?These loges ,on principal do not be harden ,and if we have automatic failover when the session reconnects,it acts as mirror and they run synchronously.Do I understand correctly?

Database defragmentation and autogrowth settings

Posted: 19 Jul 2013 01:21 AM PDT

We do have some maintenance plan for our sql server 2008 r2 express.Every month we do defragment of the database if any table has page count more tahn 50 for any table and average fragmentation more than 20.If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL.If the Page_count>50 and avg_fragmentation_in_percent > 30 then the index is REBUILD.And if Page_count>50 and avg_fragmentation_in_percent > 5 and <30 then the index is REORGANIZE.This is what we are doing till now.But we found that autogrowth events are resource incentive and it should not happen repeatedly. Now for all database autogrowth is set to MB for mdf file and 10% for ldf file which is default value while creating new database.We are planning to increase the autogrowth values for the database depending on how much database is getting bigger every day.But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database.Also there is another problem.If i do monthly defragmentation of database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem?Please tell me a solution.

Get year in which most touchdowns scored?

Posted: 05 Aug 2013 12:44 PM PDT

NEVER MIND

Intermittent pre-login handshake error

Posted: 05 Aug 2013 03:24 PM PDT

Environment:SQL 2008 R2 ClusterRunning fine for over 6 monthsEvery once in a while I get some time-outs from our web apps with a SQLAsync Exception:Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=15003; Anyone get this before? Again the server is listening on port 1433 and the connection strings are using TCP\IP. This is a intermittent error. I remember something like this that had to do with multiple instances and Named Pipes and TCP or the client is trying to connect via TCP\IP v6 first then 4 but I have checked all that and it seems fine.thanks in advance

Log Shipping Transfer Logins

Posted: 05 Aug 2013 06:19 AM PDT

Hello,I have setup a log shipping for a couple of my databases (SQL 2008). I would like keep any additional logins created on the primary database in sync on the log shipping server. I would like to use the SSIS transfer logins task to accomplish this goal and run via a SQL Agent job. However, when I run the job I get the following error.[Transfer Logins Task] Error: Execution failed with the following error: "Database '[i]DBNAME[/i]' cannot be opened. It is in the middle of a restore.".I have the following settings on the task:AllLoginsFromSelectedDatabasesIf objects exist = SkipCopySids = TrueI understand the logins cant be added while the database is restoring. Is there anyway around this issue?Thx

Run querys on different database with Progress

Posted: 05 Aug 2013 09:43 AM PDT

Hi guys,I am using ODBC to connect Progress from Sql server 2008, But I need to connect with two differents databases on the same query, It is possible?I am using Progress OpenEdge 11.2Any help would be greatly appreciated.Thanks,Sandra

ADSI linked server and AD Description field

Posted: 05 Aug 2013 09:30 AM PDT

Hi all.We have a requirement to query our Active Directory for description fields from SQL server.We of course get the same error as a multitude of other people who have posted across the internet."Could not convert the data value due to reasons other than sign mismatch or overflow."I have read this was "fixed" from 2003 onwards.I have read there are hot fixes for Windows 2003 and 2000I cannot find any KB articles for later versions like 2008.We can happily query single valued attributes but not description.Can anyone answer definitively if this can be achieved or not?Query:[code="sql"]SELECT [Description]FROM openquery (ADSI,'SELECT description FROM ''LDAP://DC=OurDomain,DC=x,DC=y,DC=z'' WHERE objectCategory = ''Computer''' )[/code]I have domain admin rights across all servers to am able to make changes to settings if required or can escalate a change to associated admin.I notice there is a service pack 2 for 2008 R2 but can see no mention of any related fixes there.[url=http://support.microsoft.com/kb/2630458][/url]SQL Server Specks:@@versionMicrosoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Bizarre 'divide by zero' - no division happening!

Posted: 05 Aug 2013 04:52 AM PDT

I have a query that's run as part of a data import process that has suddenly broken on one server while working perfectly on another against the exact same import data. Both are running SQLServer 2008 R2.The query starts something like this: select i.ImportID, i.some_ID, @ReportDate, b.someotherID, b.yetanotherID, b.yep_an_id, b.lastID_i_promise, i.othervalue, b.I_Lied_ID, Asset, 'some static value', i.Currency, Total * fx.rate, [01D] * ISNULL(fx.rate,0), [01W] * ISNULL(fx.rate,0), [02W] * ISNULL(fx.rate,0), [01M] * ISNULL(fx.rate,0), [02M] * ISNULL(fx.rate,0), [03M] * ISNULL(fx.rate,0), [04M] * ISNULL(fx.rate,0), [05M] * ISNULL(fx.rate,0), [06M] * ISNULL(fx.rate,0), [09M] * ISNULL(fx.rate,0), [01Y] * ISNULL(fx.rate,0), [18M] * ISNULL(fx.rate,0), [02Y] * ISNULL(fx.rate,0), [03Y] * ISNULL(fx.rate,0), [04Y] * ISNULL(fx.rate,0), [05Y] * ISNULL(fx.rate,0), [06Y] * ISNULL(fx.rate,0), [07Y] * ISNULL(fx.rate,0), [08Y] * ISNULL(fx.rate,0), [09Y] * ISNULL(fx.rate,0), [10Y] * ISNULL(fx.rate,0), [11Y] * ISNULL(fx.rate,0), [12Y] * ISNULL(fx.rate,0), [15Y] * ISNULL(fx.rate,0), [20Y] * ISNULL(fx.rate,0), [25Y] * ISNULL(fx.rate,0), [30Y] * ISNULL(fx.rate,0), [40Y] * ISNULL(fx.rate,0), [50Y] * ISNULL(fx.rate,0) from... followed by a series of somewhat complex sub-queries which a) all work on one of the servers and b) all work by themselves on the "bad" server. The basic idea is to apply some criteria to move some values into time "buckets" and multiply by an exchange rate.Starting today, running this has been producing a divide by zero error. I thought it odd enough to get divide by 0 on a query that doesn't divide anything, but that's just the beginning. After much tinkering to try to find if one specific calculation was causing the problem, I discovered the following:Of the 42 columns returned by the query, commenting out any arbitrary combination of 14 of them will make the query work. Doesn't matter which ones I take out, as long as there are a maximum of 28 columns being returned. When I un-comment that 29th column, no matter which one - DIVIDE BY ZERO.Anyone have any clue as to what might actually be happening here?

SQL Query

Posted: 05 Aug 2013 07:34 AM PDT

Hi, In need of help with an SQL Query. What i need is to be able to extract specific characters from data. Eg name 1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the resultsi.e. 1.2-KPIA1-App-00001 1.2 KPIA11.2 (3 characters) will not always be the same, there will be some instances of 5 characters, e.g. 2.1.1KPIA1 (5 characters) will not always be the same, there may be some instances of 6 characters, eg KPIC10.so, examples may include;1.2-KPIA1-App-000012.1.1-KPIA2-APP-000082.4-KPIC10-App-00010

All jobs fail when manually run, scheduled OK

Posted: 05 Aug 2013 05:37 AM PDT

All jobs fail immediately when run manually with the error "Supply either @job_id or @job_name to identify the job. (Microsoft SQL Server, Error:14294). Nothing gets written to the job history. Same error if the step is running an SSIS or T-SQL. All jobs run successfully when scheduled.

Update w/ SELECT ???

Posted: 05 Aug 2013 05:39 AM PDT

Hi Trying to update a bunch of records , figured I'd use the following by using an excel SS with concatenate..I have a synax error somewhere but can't find it ??[code="sql"]UPDATE dbo.CLIENT_IDENTIFIER, (SELECT * FROM dbo.Client INNER JOIN dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION ON dbo.Client.OID = dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID INNER JOIN dbo.CLIENT_IDENTIFIER ON dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK = dbo.CLIENT_IDENTIFIER.OID WHERE(dbo.Client.ID ='123')SET dbo.CLIENT_IDENTIFIER.client_identifier = 'xx071023' where dbo.CLIENT_IDENTIFIER.client_identifier ='xx071540' ; [/code]ThanksJoe

SSIS - Exec SP in Data flow

Posted: 05 Aug 2013 02:50 AM PDT

I have a SP that does bunch of stuff. It ends with a select statement from a temp table. How do I invoke that SP and send the output to a Destination in Data Flow?

Clustering Question

Posted: 05 Aug 2013 02:48 AM PDT

Hello All,I have a question on SQL Server 2008 R2 Std Edition clustering. I am trying to setup clustering for our SQL Server staging environment. We have a lot of applications and all of them are using the name of our database server which is CPNDB01 in their app config connection strings. But if I setup clustering and bring up a new node CPNDB02 and perform a failover do you think I need to change the name of the database server in the connection string to CPNDB02 for all of our applications?I know we need to create a virtual ip address for SQLServer as well and we can use that in the connection string to prevent the name change every time a failover or a failback is performed. But what if I have to use only Server name but no IP in my app connection string?Thanks for your inputs

Delete a records in all Tables

Posted: 05 Aug 2013 01:41 AM PDT

Hi Team,I've having 120 tables in that 80+ tables have "emp_id" as a column_name, mistakenly i've inserted a record in to Employee table, there are many triggers on that table, it will automatically inserts records into many tables with column_name "emp_id"now i want to delete all the records in all tables with emp_id=136,how to delete all the records in all tables contains emp_id=136Please suggest..!

SQL Cluster Migration

Posted: 05 Aug 2013 01:59 AM PDT

Hi there,I have the task of migrating two 2-node SQL 2008 R2 clusters on to new hardware. Currently the plan is to create new clusters from scratch, script all the logins, jobs, linked servers etc. and then just restore all the databases onto it. Finally I will alter the DNS name of the cluster to be the same as the existing one so I don't have to modify all the applications.This will probably be fairly time consuming as there are a large number of databases on the servers.Is this the best way to do it or could someone suggest a easier method?! ....or do I just need to stop whinging and get on with it? ;-)Thanks,Matt

Using IF in a Stored Procedure. Syntax help neeed.

Posted: 05 Aug 2013 01:58 AM PDT

Hello all.I am trying to use my first IF statement within a SP. The concept is simple. I have a aspx web form where the user can select a CATEGORY (sends a Cat ID # which is a Int) and a "search phrase". Variables are sent to SQL Server via SP and results are spit out onto a page. The drop down list for "categories" also has a "ALL Categories" option in case the user wants to search them all. I have hard coded this "All" value to 9999. So the HTML behind the drop down list might look like this:<select name="ddlCategories" ....><option value="">**Please Select**</option><option selected="selected" value="9999"><ALL Categories></option><option value="5">Application Fees</option>...etc...My code then (asp.net) then passes the form values to the SP and gets the results back. Simple.I am trying to write the SP to use one of two SELECT Statements. This is what I have below but it seems to error on the last "END".Little help??Create Procedure [dbo].[sp_SearchDownloadsResults-Beta]@strSearchPhrase varchar(25),@intCategoryID IntASBEGIN-- User did NOT Select ALL Categories and searched by a specific category.If @intCategoryID <> 9999 Begin SELECT ....stufff.....FROM...stuff.... WHERE ((dbo.Downloads.Active = 1)) AND ( (dbo.Downloads.Title LIKE '%' + @strSearchPhrase + '%' ) OR (dbo.Downloads.Description LIKE '%' + @strSearchPhrase + '%' ) ) AND ([b]dbo.Downloads.DownloadCategoryID = @intCategoryID[/b]) ORDER BY dbo.Downloads.SortDate DESC, dbo.Downloads.Title ASCEnd--User opted to search by ALL categories.Else Begin SELECT ....etc.... WHERE ((dbo.Downloads.Active = 1)) AND ( (dbo.Downloads.Title LIKE '%' + @strSearchPhrase + '%' ) OR (dbo.Downloads.Description LIKE '%' + @strSearchPhrase + '%' ) ) ORDER BY dbo.Downloads.SortDate DESC, dbo.Downloads.Title ASC[b]End[/b] <--- This is where is seems to expect more.....

sp_spaceused columns?

Posted: 05 Aug 2013 12:55 AM PDT

Hi can someone help pleasei just did sp_spaceused '[db].[db].[demo]'where demo is my tablebut i would like to get the rows, reserverd , data , index_size and unused only for some column in my demo tablehow can i write this please

i am a newbie . help me to learn sql server

Posted: 20 Jul 2012 04:09 AM PDT

hi all, please guide me with some links to learn the basics of sql server

Convertion from Varchar to Datetime

Posted: 04 Aug 2013 09:45 PM PDT

How do I convert a string of format -> mmddyyyy into datetime in SQL Server 2008?My target column in 'DateTime'I have tried with Convert and most of the Date style values - I get a 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.' error message.

Export Contacts from Outlook to SQL Server 2008

Posted: 28 Feb 2013 06:28 AM PST

Hi All, I would like to export all the mail id's which are available in my outlook inbox and store those id's into my SQL Server 2008 table.Is there any chance do like this?If Yes can you please share your ideas

Analytical function for my Q

Posted: 29 Jul 2013 09:13 AM PDT

Hi,Is there any analytical function in sql server for achieve results like below:I did this with 2 temp tables, but might be something already there to look it nicer.I also need this group by DateIn, and display % for each delDays categoryThanks allM[code]create table #log (shipID int, datein datetime, delDays int)INSERT INTO #LOG (shipid , datein, delDays) select 1001, '2012-01-06', 9 union allselect 1002, '2012-01-06', 11 union allselect 1002 , '2012-01-06', 11 union allselect 1003 , '2012-01-06', 11 union allselect 1004 , '2012-01-06', 11 union allselect 1005 , '2012-01-06', 11 union allselect 1006 , '2012-01-06', 11 union allselect 1007 , '2012-01-06', 11 union allselect 1008 , '2012-01-06', 11 union allselect 1009 , '2012-01-06', 11 union allselect 1010 , '2012-01-06', 22 union allselect 2001 , '2012-02-06', 15 union allselect 2002 , '2012-02-06', 16 union allselect 2003 , '2012-02-06', 33 union allselect 2004 , '2012-02-06', 22 -- Need this result result:DateIn | <20days | <30days | >30days |----------|----------|-----------------------2012-01-06| 90% | 10% | | 2012-02-06| 50% | 25% | 25% | [/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog