Friday, August 23, 2013

[SQL Server 2008 issues] Mdmp files

[SQL Server 2008 issues] Mdmp files

Mdmp files

Posted: 22 Aug 2013 05:52 PM PDT

Hi All,In my production server , SQL Error log file size is growing by creating mdmp (memory dum files in c drive ).How i can stop them.

SSAS Filter field not working

Posted: 22 Aug 2013 01:59 PM PDT

In the attached image you can see the Cube structure, Item Dimension, Hierarchies used in Item Dim,attribute relationship defined in Item dim+ the data in browser.We can see that while browsing data and applying filter at the filter field we are not getting the appropriate result.Note :Amt Cat : Amt at the Item Category level (using calculated measure shown below)iif(isempty([Measures].[AMT])=true, null,([Measures].[AMT],ancestor([Item].[Items].CurrentMember,[Item].[Items].[Item Category])))Amt Grp : Amt at Item Group level (using calculated measure shown below)iif(isempty([Measures].[AMT])=true, null,([Measures].[AMT],ancestor([Item].[Items].CurrentMember,[Item].[Items].[Item Group])))But by applying the filter at filter expression we get the required result and even by modifying the attribute relationship as shown above we get the appropriate result.Can anyone explain why is this happening so ?

Failover 10,000 databases?

Posted: 22 Aug 2013 04:09 AM PDT

Hi All,It may sound like a clustering question but it's also service restart one.We have a Windows 2008 cluster with one instance of SQL Server 2008R2. There are 10,000+ databases on it. While user_db access is fast, cluster failover takes almost an hour, even with high performing hardware. We would like to drop the failover time. 1/3 of the databases are not active. Almost all of them are on SIMPLE recovery mode.I am considering;- Setting AUTO_CLOSE ON for inactive databases, or OFFLINE- Playing with recovery intervalAnything else you might suggest?Cheers,Kuzey

Backup to another network server

Posted: 16 Aug 2013 02:54 PM PDT

Hi,I am trying to backup from my sql 2005 DB which is on windows server 2003 to sql 2008R2 on windows server 2008R2 but getting access denied,I have tried using SSMS, T-Sql but still getting issue with this.I have created backup folder on my 2008R2 server and assign the full access to Everyone on security permission, I have added user and also grant the FUll Permission.1) I tried using SSMS ut didn't work2) I tried using T-sql, didn't workI have just install the sql 2008R2 on Windows 2008 R2.

query returning to many rows

Posted: 22 Aug 2013 09:08 AM PDT

Hi AllI am confused in trying to find all licensable and non licensable products that match my datalookuptable but it seems to be returning to many rowsMy queries are as follows[code]select count(*) from dbo.newtablereturns 629019 rows************************************************** select n.*,d.categoryfrom newtable n,datalookuptable dwhere n.softwaremanufacturer = d.amended_sw_manufacturer and n.productname = d.amended_product_name and n.productversion = d.amended_product_version and d.category in ('Licensable','Non Licensable') order by d.categoryreturns almost 3 million rows[/code]am i missing something

Performance issue

Posted: 21 Aug 2013 10:54 PM PDT

Hi All,I am migrating 35 million data to another new table(EVALUATION_CRITERIA) using simple joins, But it is taking to execute around 24 hrs and system ram=16 GB,Below are the [b]insert query and attached Execution plan[/b][b]Can any one suggest , is there any best ways can i get Any Time difference If run same code in SSIS Package ?[/b]Records count in user tablesEVALUATION_DETAIL 33240682EVALUATION 1297899EVALFORM 338EVALFORM_CRITERIA 8619EVALFORM_CRITERIA_DETAIL 34630 --ScriptDeclare @EvaluationId Int, @EvalformId IntDeclare CursorData Cursor ForSelect Evaluation_Id, Evalform_Id From EvaluationOpen CursorDataFetch Next From CursorData Into @EvaluationId, @EvalformIdWhile @@Fetch_Status = 0Begin Insert EVALUATION_CRITERIA (EVALUATION_ID, EVALFORM_CRITERIA_ID, TITLE_NAME, BASE_CRITERIA_ID, EVALFORM_CRITERIA_DETAIL_ID, CHILD_EXIST, PRIORITY_ORDER, WEIGHTAGE, ANSWER_TYPE, STATUS, CREATE_BY, CREATE_DATE, LEVEL, SCORE) select e.EVALUATION_ID, efcd.EVALFORM_CRITERIA_ID, efc.TITLE_NAME, efc.BASE_CRITERIA_ID, efcd.EVALFORM_CRITERIA_DETAIL_ID, efc.CHILD_EXIST, efc.PRIORITY_ORDER, efc.WEIGHTAGE, NULL, --efc.ANSWER_TYPE,-- 'A', 1, GETDATE(), efc.LEVEL, efcd.SCORE 'A', 1, GETDATE(), efc.LEVEL, (select MAX(score) from EVALFORM_CRITERIA_DETAIL where EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID and STATUS = 'A' and OMIT_FROM_SCORE = 'N') score from EVALFORM ef inner join EVALFORM_CRITERIA efc on ef.EVALFORM_ID = efc.EVALFORM_ID left join EVALFORM_CRITERIA_DETAIL efcd on efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID left join EVALUATION_DETAIL ed on efcd.EVALFORM_CRITERIA_DETAIL_ID = ed.EVALFORM_CRITERIA_DETAIL_ID left join EVALUATION e on ed.EVALUATION_ID = e.EVALUATION_ID where e.EVALUATION_ID=@EvaluationId and efc.STATUS = 'A' Union select @EvaluationId, efc.EVALFORM_CRITERIA_ID, efc.TITLE_NAME, efc.BASE_CRITERIA_ID, 0, efc.CHILD_EXIST, efc.PRIORITY_ORDER, efc.WEIGHTAGE, NULL, --efc.ANSWER_TYPE, 'A', 1, GETDATE(), efc.LEVEL, NULL --efcd.SCORE from EVALFORM ef inner join EVALFORM_CRITERIA efc on ef.EVALFORM_ID = efc.EVALFORM_ID left join EVALFORM_CRITERIA_DETAIL efcd on efc.EVALFORM_CRITERIA_ID = efcd.EVALFORM_CRITERIA_ID where efc.CHILD_EXIST = 'Y' and efc.STATUS = 'A' and ef.EVALFORM_ID = @EvalformId-- Select @EvaluationId, @EvalformIdFetch Next From CursorData Into @EvaluationId, @EvalformId EndClose CursorDataDeallocate CursorData[b]--Attached Execution plan[/b]

User redirection in DB Mirroring

Posted: 22 Aug 2013 04:53 PM PDT

I have configured DB Mirroring with automatic fail over.My users are connected to Server_A .My Partner is Server_B and witness is Server_C.As we know that whenever a fail over occurs Server_B will take the roll from Server_A and it becomes Online.But my point is how the users are redirected to Server_B.Is it the DBA who changes the connection string or the Application Developer or the Database Developer.How ? What happens behind the screen.Please explain this with details..

query to find total number of total number of reads for a stored procedure

Posted: 22 Aug 2013 03:06 AM PDT

Is there a query to find total number of logical reads/physical reads done when a given stored procedure is executed?

backing up msdb after changes

Posted: 22 Aug 2013 09:13 AM PDT

I have found some of my databases that have high vlf counts.It looks like the ones that had the high count have had either small increment growth settings or the setting was set to grow as a percentage.I have now adjusted the Log files to the correct size after following Kimberly Trips advice http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/My question is do i now need to back up the msdb databases on the servers i made the changes too, as our servers get rebooted every month, to keep the new growth settings intact ?

SSRS, cascade parameters, how to make it work for multiple choice (WHERE in ...)

Posted: 22 Aug 2013 06:14 AM PDT

Hi,I created cascade params for my report, it works fine for single selection, but when I check let say 2 params, (AAA, BBB) it doesn't work.So it's only for MULTI VALUES IN PARENT CASCADE PARAM, child is OKMy dataset query:[code="other"]select subCategory from t1where t1.Category in @Category --works OK with = for single selection or @Category = 'All'--errorAn expression of non-boolean type specified in a context where a condition is expected, near ','[/code]in SSRS/Query Designer / Define QUery Parameters window:Parameter Value = AAA ===> runs fines,returns 100 rowsParameter Value = AAA,BBB ===> nothingParameter Value = (AAA,BBB) ===> nothing, etc.. and again: WHERE t1.Category in (@Category) ...Even MSN aknowledge this problem and tricky way to solve it, very bizarre!!! Is there anything else I have to add/change? I googled and found this on http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b42af8a-d84b-4224-891e-53043b7a0614/how-to-pass-multiple-values-to-a-parameter-while-cascading... =split(Join(Parameters!XXX.value,","),",")But where to put this vbs?TxM

Performance IO issue

Posted: 21 Aug 2013 11:10 PM PDT

Hi All,We have Performance issue on of our Pord server 2005,where we are seeing I/O issues.While montring i could a high I/O values for the Database.Database i/o counter name location value ISOstall[ms]/read request xxxx(database) 25I think the database is causing performance is issue.can any one help me how to troubleshoot this issue ......what is cause for high I/o issue.Thanks.....in advance..

AD Query failed to work last night.

Posted: 22 Aug 2013 08:58 AM PDT

Hello all, I have a query to AD that worked 2 days ago, but did not work last night.SELECT sn, givenname, sAMAccountName, mail, displayNameFROM openquery(ADSI,' SELECT givenName, sn, employeeID, mail, sAMAccountName, displayName FROM ''LDAP://OU=DedicatedRemoteSites,DC=berkshire,DC=loc'' WHERE objectCategory = ''Person'' and objectClass = ''user'' and mail = ''*.org'' and givenName = ''*''');The message I get is this:Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".The only thing that I am aware of that did change is that we had to change our main Administrator Password.Is there something related to this that I need to update somewhere?Thank you in advance for your help.

VMing Instances

Posted: 22 Aug 2013 05:18 AM PDT

Hey folks.. here part of the situation. We're going to P2V some sql servers. I'd like to find the average cpu core usage and the minimum memory requirements for server/database so I know what each VM will require at a bare minimum to continue efficient operations. I realize that with our current configurations SQL Server sucks up the memory allocated to it but if I've allocated 8 gigs and it grabs all that memory, how much of it does it really use on a consistent basis? Does it need 8 gigs? Can it make do with 1? 2? Does it require more than two cores? Does it even need more than one core if it's a low usage system?Anyone have any scripts that can give me this kind of information?Thanks,Erin

Shorten query by using Top predicate

Posted: 22 Aug 2013 01:30 AM PDT

I have situations when I only want to know if I have one record, or more than one record. For instance, a query like this:[code="sql"]SELECT Top 2 AkcesAutoID FROM Podrobnosti Group By AkcesAutoID[/code]I would like to stop as soon as it sees that it has a second record. But when I look at the query plan, the first step, an Index Scan passes (in this case) 85 rows, from an estimated 32,376 rows to the Stream Aggregate function, which then passes only two to the Top function, which then passes only two to the Select function. This is a simple query, but real ones will be considerably more complex, with multiple joins and conditionals.The gist is that I want to know whether all the records matching my criteria have the same value in a particular field, in this case AkcesAutoID. If they are all the same, I can deal with the result set in a particular (simplified) way. If there are more, I don't (at this point) care how many more, I already know that the app has to do something a different way.Reading on the subject indicates that the Top predicate is always applied only to the result set. Is there any way to tell the query engine that I want it to stop working on the problem AS SOON AS it has the second record that meets my criteria, instead of gathering them all and then discarding all but two?

SSIS package error in SQL 2008 ActiveX Script but the sql agent job succeeds

Posted: 22 Aug 2013 04:27 AM PDT

Hi, I am getting this wierd scenario where SSIS SQL agent job suceeds but it actually isnt. I am getting the below error in the job history, but the job suceeds.Code: 0xC00291B1 Source: Parse XML File ActiveX Script Task Description: User script threw an exception: "Error Code: 0Error Source= Microsoft OLE DB Provider for SQL ServerError Description: Query timeout expiredError on Line 282".End ErrorDTExec: The package execution returned DTSER_SUCCESS (0).Package execution validated as success.Please share your thoughts, Thank you for spending your time.

Maintenance Solution

Posted: 22 Aug 2013 01:41 AM PDT

I came across a SQL file from Netbut did not get the information of what it do..Its a Maintenance solution.sqlPlease tell me what can i be helped with this file..I think it would of grt help.. please guide me to use it

Script to retrive Configuration files from servers 2000 to 2012

Posted: 21 Aug 2013 08:31 PM PDT

HI Everyone,can any one have idea to script that retrives configuration files from all servers.Inputs are most welcome.Cheers

Results viewer cannot execute the query error

Posted: 22 Aug 2013 12:27 AM PDT

Hello, We need to copy paste data from excel into a SQL table from time to time. We have no control over the table, it is generated and given to us periodically. We are getting an error now: "The results viewer cannot execute a query with more than 655 columns in the project list". I know that the answer is, normalize the table and reduce the number of columns. Unfortunately, we have no control over the table. So is there any easy way to get data into the table under this achitecture? Not sure I want to build SSIS for this, but might I have too? I am hoping there is a workaround someone out there might be able to hand off to me. Thanks in advance!

Possible to restrict the types of file stored in a Filestream container?

Posted: 22 Aug 2013 12:47 AM PDT

In the near future, I may be responsible for implementing a DB back-end for an application which stores documents, videos, images, and other such BLOB data. Potentially, this will be done in Filestream. My question is, is it possible to restrict the types / sizes of files on the SQL side, or would such logic have to be implemented at the application?From some quick Googleing, it looks like it would need to be on the app side of the fence, but I'd like to confirm...Thanks,Jason

Log shipping errror

Posted: 21 Aug 2013 09:52 PM PDT

Hi everyone.Hope someone can offer some guidance here.Set up log shipping. All looked ok. The noticed that I was getting the [SQLSTATE 42000] (Error 14421). The step failed. Error on the secondardy server in regards to the LSAlert job.when I run select * from msdb.dbo.log_shipping_monitor_secondaryI do intend see the last_restored_file as blank for the database.However I do see the log shipped from Primary and does exist on secondary.The copy and restore jobs say everything is ok , but as I can see from the above query it is not being restored onto the secondary Database.When looking at the restore job on the secondatry server I see these messages :2013-08-22 11:45:27.57 Skipped log backup file. Secondary DBAnything I can do to check \ resolve this ?Is the quickest way to resolve it , is to reconfigure the LS for this DB again ?many thanks all.

Replication: add a new table with out a new snapshot

Posted: 07 Aug 2013 09:40 PM PDT

Hello,i've got a database with 500 GB DATA and 200 GB INDEX. I had to add an article (table) to the replication. I want do avoid, that the replication creates a new snapshot. We need about 7 hours to create the new index in the database.Is it possible, to add article without a new snapshot. I read something, that only the difference ( in my example: 1 table) is stored in the snapshot.Publisher: SQL 2008 R2 SP2 / OS Windows Enterprise 2008 48 GB RAMSubscriber: SQL 2008 R2 SP2 / OS Windows Standards 2008 48 GB RAMDistribution-Server: SQL 2012 / OS Windows Enterprise 24 GB RAMcreate the snapshot needed about 30 Minutesdeliver the snapshot needed about 6,5 Hourscreate index neede about 7 HoursThanks for you help,Andreas

Deadlock issues

Posted: 21 Aug 2013 11:16 PM PDT

Getting Deadlock issues on regular basis..where Select insert or select update is involved in SPCan UPDLOCK & HOLDLOCK solve the issue in select query??or else suggest something you have comeacross

Google Analytics SQL Import

Posted: 08 May 2012 09:07 PM PDT

Hi AllI was just wondering if anyone has ever done a Google Analytics import to SQL via SSIS?A quick google brought up some C# scripts, a PHP web scraper and using the java client to export to CSV and then import it, but was wondering if anyone has managed to query the API and load it direct in SSIS to the DB?This was a task which was done a couple of years ago and I have been asked to re-ignite the flame to get it moving again and the previous way was to use xp_cmdshell to execute a file which loads it to CSV and imports it, which I have no trouble with, just wondering if anyone has a better solution.Thanks

How to Create configuration files for installation of SQL Server

Posted: 21 Aug 2013 07:30 PM PDT

Hi,I am Looking for procedure that create a configuration file for installation of sql server and how it works. can any one help me out?. Thanks. Replays are most welcome.Cheers

XSD to Table

Posted: 21 Aug 2013 07:39 PM PDT

Hi All,Is there any T SQL script which converts XSD to table schema?
Posted by at

1 comment:

  1. UnknownApril 16, 2018 at 12:01 AM

    Good and full article. It can help to many novice admins. In addition I want to show this site wikiext.com/mdmp here many information about the files

    ReplyDelete

Subscribe to: Post Comments (Atom)

Search This Blog