Wednesday, June 19, 2013

[SQL Server 2008 issues] Memory Pressure Errors

[SQL Server 2008 issues] Memory Pressure Errors

Memory Pressure Errors

Posted: 18 Jun 2013 07:00 PM PDT

Hi,Our SQL server services got restarted last night due to memory pressure. Looking forward for your help in diagnosing the issue and finding a resolution.[u]About our environment:[/u]SQL 2008 SP1 on Windows 2003 R2 SP2 (VM) with 6GB RAM. Its running on a cluster (active/passive). The cluster service had restarted SQL services on the same active node after the incident.[u]Event Viewer Logs:[/u]1. There is insufficient system memory in resource pool 'internal' to run this query.2. SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. 3. [sqsrvres] ODBC sqldriverconnect failed4. [sqsrvres] CheckServiceAlive: Service is deadThe cluster service had restarted the SQL service after these errors.Attached are the SQL Server error log and the results of queries (taken from msdn blogs) related to VAS size. Upon examining the logs and query results, it seems the server experienced MTL pressure and the procedure cache is consuming more MTL memory. Is the diagnosis correct, and if yes, what would be the resolution for this issue?Thanks

ROLES

Posted: 18 Jun 2013 06:09 PM PDT

To run sp's what type of role we assign to user?

pivot query

Posted: 18 Jun 2013 12:27 PM PDT

hi all,I have a table like below:ID LOCATION STATUS MONTH MEASURE 1 PARIS OPEN JAN 25 2 PARIS CLOSED JAN 30 3 LONDON OPEN JAN 45 4 LONDON CLOSED JAN 50 5 PARIS OPEN FEB 27 6 PARIS CLOSED FEB 31 7 LONDON OPEN FEB 50 8 LONDON CLOSED FEB 61How do I pull these numbers in a data set as seen below:JAN 25 30 45 50FEB 27 31 50 61namely,month| measure for LOC1,STATUS1| measure for LOC1,STATUS2| measure for LOC2,STATUS1|measure for LOC2,STATUS2I believe I've done it before with a PIVOT clause but I am not sure if I have the PIVOT option at my disposal (not sure what version is the SQL Server in production).Thanks,kowalsky

PrimaryKey and Unique Clustered Index

Posted: 18 Jun 2013 03:00 PM PDT

Hi, Have a small doubt - what's the difference between the below 2 SQL statements.1. Create Table T1 (Col1 Int PrimaryKey, Col2 Int)2. Create Table T2 (Col1 Int, Col2 Int)- Create Unique Clustered Index T2(Col1)Question - in the above 2 created tables, what's the difference between T1-Col1 and T2-Col1. Do they both behave same when created in the db or are they different?Regards,Sai Viswanath

transport encoding

Posted: 18 Jun 2013 04:26 PM PDT

Hi,I need to implement transport encoding.if anybody worked on or working please provide me guidance.Thanks,Sreenivasa Chary.T

user self services

Posted: 18 Jun 2013 04:25 PM PDT

Hi,I need to implement user self services for administrative tasks.if anybody worked on or working please provide me guidance.Thanks,Sreenivasa Chary.T

integration of key management

Posted: 18 Jun 2013 04:23 PM PDT

Hi,i need to work on integration of key management.As per my knowledge we need use EKM and need to manage keys.for that we need additional hardware or appliance.please help me how to implement Integration of key management.Thanks,Sreenivasa Chary

merge case when

Posted: 17 Jun 2013 09:23 PM PDT

i have this syntaxcase when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'Late' else '' end remarkscase when (540- Time_Minutes) >= 120 then 'HALF DAY' else '' end HALFDAYi want to merge this two syntax and show in one column name remarks

Different plan for a query In APP and SQL

Posted: 17 Jun 2013 09:26 PM PDT

HiI execute a query from web App and I get the select statement from sql profiler.I run exactly the statement without any changes directly in sql right away at that time,but I get 2 different plan in sql and in sql profiler.SQL and App are behaving diffrent.Developer team uses linq in App and the statement in sql profiler is in sp_executesql format.Why I get 2 different plan for a query in App and SQL?

Dynamically choose source and destination

Posted: 13 Jun 2013 06:45 AM PDT

Hey Gurus, I have just started working with SSIS and have following task at hand and I feel lost. Can any of you please help me?Currently, I have a 2 different data flow tasks that do the same thing: Accept data from a SQL Store proc, put it to flat file destination and archive it. One of them is stored procedure giving us revenue data and the other gives us accounts data. Now, I need to combine both of these different data flows as a single data flow using parameters / variables for picking up appropriate stored procedure based on whether user wants to see account data or revenue data and then, out it in the correct file and file location (Files and file locations are different for revenue and accounts data).I do understand I should use a for-each loop maybe. However, I really need assistance in getting started with how to set up variables (system / user defined) and how can I go about it. I have attached a sample data flow diagram of how I need it. Can you please pass me some tips / ideas. Please note that am a novice is SSIS and just started. Thanks in advance!

Recommendation - Rebuild Index /Partition

Posted: 18 Jun 2013 06:42 AM PDT

I have SQL 2008R2( Enterprise edition). All my tables are partitioned. I need some advice and possibly a script for index maitenance. If my indices are fragmented should i:i) Just rebuild the indices or ii) Rebuild each individual partition

Will View Increase the Performance of a query?

Posted: 17 Jun 2013 08:55 PM PDT

Hi All,I just wanted to know whether retrieving data from a view will be faster than directly querying from table.Say suppose if a view is created by joining a number of fact tables(Records greater than 100 million) with dimensional tables.Will selecting the records be faster than querying from the actual tables.Request your views on the same.

SQL

Posted: 18 Jun 2013 04:43 AM PDT

how do you set something up like codered where there are numbers within a database and then it makes calles to cutomers that are with in the database what type of other software etc is needed.

Large INSERT INTO SELECT ....

Posted: 18 Jun 2013 02:30 AM PDT

Hi SSC,Question for you, when a user runs an INSERT INTO ... SELECT * FROM ... statement I've noticed that IOReads take place before really any decent amount of IOWrites take place.What is SQL Server doing behind the scene? Is it loading the entire set of data pages from source table into memory before writing? I could see that being a problem if your buffer pool isn't larger than the table itself on disk...Thanks in advance for the insight!

Permission to run only certain SQL jobs in 2008

Posted: 18 Jun 2013 01:11 AM PDT

I have an AD group Group1 that needs access to the SQL jobs ( only certain ones) so that any users within that group can run/stop/view history of the job .- The users in this group are not sysadmin- None of the jobs are owned by the users.Does SQLAgentUserRole solve this? How do I give access to specific jobs but not the all? Any help would be greatly appreciated. Thanks

Import action stopping in middle?

Posted: 18 Jun 2013 12:21 AM PDT

Hi,Import action stopping in middle due to as below errors, pl suggestion me what could be issues? it may be invalid character in oracle dataTotal - 879212 count in oracle data, during sql side imported rows count 400234 this stage automatically stopped..Source- oracle 9iDestination - SQL[code="other"]Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification". (SQL Server Import and Export Wizard) Error 0xc020901c: Data Flow Task 1: There was an error with input column "RESULT" (119) on input "Destination Input" (86). The column status returned was: "Conversion failed because the data value overflowed the specified type.". (SQL Server Import and Export Wizard) Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (86)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (86)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - SEZLIMS5" (73) failed with error code 0xC0209029 while processing input "Destination Input" (86). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)[/code]thanksananda

how to make trigger

Posted: 17 Jun 2013 10:40 PM PDT

[size="1"]CREATE TABLE [dbo].[patient]([pid] [nvarchar](50) NOT NULL,[name] [varchar](50) NOT NULL,[email] [varchar](50) NOT NULL,[password] [varchar](50) NOT NULL,[createdon] [datetime] NOT NULL,[modifiedon] [datetime] NOT NULL,[rowstate] [tinyint] NOT NULL,[Address] [varchar](100) NULL,[dob] [datetime] NULL,[phone] [varchar](12) NULL,CONSTRAINT [PK_patient] PRIMARY KEY CLUSTERED ([pid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])table2CREATE TABLE [dbo].[pstatus]([pstatus] [varchar](50) NOT NULL,[docstatus] [varchar](50) NOT NULL,[disease] [varchar](50) NOT NULL,[bedno] [varchar](3) NOT NULL,[drugs] [varchar](50) NULL) ON [PRIMARY]new table CREATE TABLE [dbo].[searchp]([pid] [nvarchar](50) NOT NULL,[name] [varchar](50) NOT NULL,[docstatus] [varchar](50) NOT NULL,[disease] [varchar](50) NOT NULL,[bedno] [varchar](3) NOT NULL,[drugs] [varchar](50) NULL,[docname] [varchar](50) NOT NULL) ON [PRIMARY][/size]i want to create a trigger when the values are inserted into the first 2 tables the new table will be automatically updatedthanks in advance

Utility that converts JET to T-SQL

Posted: 17 Jun 2013 11:38 PM PDT

I have been looking for a tool that converts JET SQL to T-SQL...I see plenty of references to SSMA for Access 2013 and Access upsizing wizard for older versions, but if i read them correctly, they are for database conversions but don't translate between the SQL dialects, and I am looking for something that will translate Access JET into 2008 T-SQL.I have been doing it manually, but it is a total mind numbing drag and was hoping someone else has crossed that river already.thanks very much for your help

.ldf deleted and now cant attach database

Posted: 10 Jan 2012 09:58 PM PST

Hi All,I had deleted my .ldf as it was getting far too large! and then when i went back to use that database i recieve the following error when trying to create a new database with the existing .mdfMsg 5173, Level 16, State 1, Line 1One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.Log file 'H:\Logs\360MetaVerse_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.Msg 1813, Level 16, State 2, Line 1Could not open new database '360MetaVerse'. CREATE DATABASE is aborted.Thanks in advance

can anyone give me tips how to use powershell with respect to sql server 2008

Posted: 17 Jun 2013 10:01 PM PDT

can anyone give me tips how to use powershell with respect to sql server 2008

Auto Populate Dimention Usage

Posted: 17 Jun 2013 09:27 PM PDT

HiI am creating a cube (first one ish) and i have messed around with it a bit and now my Dimention Usage tab is not as detailed as it should be.By that i mean prior to me messing around with the cub i had a lonbg list of dimentions and each measure that they were related to, now i just have a single row per dimention and no related measures.looking ar my data source and cub structure it looks like the links between the tables are there.Is there a way of automatically populating these dimention usage links from the links between the fact and measure tables?I may well have not explained myself very well or there might be a more visual way of explaining myself so thanks for being patient.Thanks in advance

sql service startup account

Posted: 12 Jun 2013 05:18 AM PDT

Hi Team,I have sql service account which does not have sa rights but have all required domain rights and there is seperate account for agent service and this account has sa rights.When backup job runs it does not delete the old backup files.When I grant sa rights to sql service account it does.So my question is does sql service account always need 'sa' rights?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog