Question

asp.net / sql server 2005 memory leak

Asked by: newbie27

Hello Experts

When I perform a db search from one of the asp.net page of the website, w3win.exe*32 and sqlserver.exe eating up all the memory and about 80% cpu usage, crashing the website running on windows 2003 server

It was working all OK it suddenly started since Friday and every time this happens I have to physically kill these 2 process to bring the site back to normal.

Please can someone advise how can I go about fixing this problem?

thanks
s

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-08-22 at 15:33:17ID24674024
Tags

sql server

,

iis

Topics

MS SQL Server

,

SQL Server 2005

,

Microsoft IIS Web Server

Participating Experts
2
Points
500
Comments
25

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. asp.net killing asp session / cookies
    I am using a combination of asp.net and asp pages. Users originally login using an asp page. They can then at some point browse to another page that is in asp.net, once they do this is seem to kill the session/cookies that were available in asp. So once they go back the th...
  2. Killing a process from asp.net page
    I have an ASP.NET page which invokes excel, and I intend to kill this excel process once the program has completed creating the reqd excel spreadsheet I have the following code so far, when I try to kill the Excel process it states Access denied. I am running the website und...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: Mortaza_doulatyPosted on 2009-08-23 at 00:34:10ID: 25161661

Talking too general...
Have you changed your code since Friday?
Have you changed any configuration in SQL Server since Friday?

 

by: newbie27Posted on 2009-08-23 at 02:08:27ID: 25161907

Hi Mortaza_doulaty

Thanks for picking this up for me. No, I have not changed any code recently.

All I remember doing that I have stopped the attached updates half way through, I have noticed that while its doing this update all connections to the server was being cutoff..

I mean, the site went down, the smtp server stopped etc ... I have to cancel the update to make everything back again ....

Could this be the reason?

Thanks
S

 
Update to .NET Framework 3.5 Service Pack 1 for the .NET Framework Assistant 1.0 x64 (KB963707)
Download size: 741 KB , less than 1 minute
The update to .NET Framework 3.5 Service Pack 1 for the .NET Framework Assistant 1.0 for Firefox addresses several compatibility issues with version 1.0 of the extension.  Details...
 
 
Microsoft .NET Framework 3.5 Service Pack 1 and .NET Framework 3.5 Family Update for .NET versions 2.0 through 3.5 (KB951847) x64
 
please advise
thanks

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Select allOpen in new window

 

by: Mortaza_doulatyPosted on 2009-08-23 at 02:22:31ID: 25161932

>>Could this be the reason?
May be, but not sure.

Install and update all available updates and patches to your .NET Framework and Windows, then restart and see it solves or not.

 

by: newbie27Posted on 2009-08-23 at 02:28:15ID: 25161949

OK, I had 2 hidden .NET frameword updates which I am installing it now.
and 1 IE8, which I have ignored it.

I hope this may fix the issue.

Thanks
S

 

by: newbie27Posted on 2009-08-23 at 02:39:42ID: 25161978

Upates are completed, I am still having the same issue.

w3wp.exe*32  = 1,333,234 KB of memory usage ...

sqlserver = 12,990kb of memory usage

this is only when i perform the search , for the rest of the site it seems to be working ok

please advise

thanks

 

by: newbie27Posted on 2009-08-23 at 02:41:45ID: 25161980

This is my site http://www.giftrepublic.com/ProductRange.aspx?RangeID=GROW

The search function is in the Admin section of this site though....

 

by: Mortaza_doulatyPosted on 2009-08-23 at 03:02:36ID: 25162015

Try to execute the query which your web site executes when using search function in SSME (SQL Server Management Studio), see how long does it take and what are the results...

Also if possible, analyze your search function with ANTS Performance Profiler (http://www.red-gate.com/products/ants_performance_profiler/index.htm) to see it there is something wrong there...

 

by: newbie27Posted on 2009-08-23 at 03:14:10ID: 25162040

Hi,

When I run the query in SSME it works OK. Although its returning 50K records but it was quick.

Its the problem on the website somehow !

Regards
S

 

by: Mortaza_doulatyPosted on 2009-08-23 at 03:25:01ID: 25162061

Suppose running this query by 100 users, it will certainly cause further problems.

And about the current problem, have you tried ANTS?

 

by: newbie27Posted on 2009-08-23 at 03:43:30ID: 25162100

Hi

As this is only within the admin section of the website, this will only be used for the GR employees and only 2 people work on these functions, so there is no question of mulitple users using this query.

No, I have not tried with ANTS. Would you recommend to download a trial version and test it locally?

 

by: Mortaza_doulatyPosted on 2009-08-23 at 04:12:54ID: 25162166

Yes, downloading a trial version and testing locally would be enough.

 

by: newbie27Posted on 2009-08-23 at 06:15:44ID: 25162486

Hi,

On the test environment, I have got the following error....

Exception of type 'System.OutOfMemoryException' was thrown.

                                              
1:

Select allOpen in new window

 

by: acperkinsPosted on 2009-08-23 at 10:18:27ID: 25163416

You are not running SQL Server and IIS on the same box are you?  Please say no.
If not, is SQL Sever runing completely standalone with no other apps on that box?

 

by: newbie27Posted on 2009-08-23 at 13:02:03ID: 25164027

Hi acperkins

I am afraid but it is Yes, I have one dedicated Windows 2003 Server which has IIS and Sql Server.

>>SQL Sever runing completely standalone

How do you mean by this?

Please advise

 

by: Mortaza_doulatyPosted on 2009-08-23 at 13:07:19ID: 25164043

Could you describe more about the code which causes the exeception?

 

by: acperkinsPosted on 2009-08-23 at 13:10:15ID: 25164056

MS SQL Server runs best when standalone (on it's own server) without any other application (and certainly not IIS) running.  With this configuration and by default it attempts to use all memory available to improve performance.  Since this is not your case, you will have to cripple MS SQL Server by limiting the amount of memory utilized by MS SQL Server.  Let me know if you need help with that.

 

by: newbie27Posted on 2009-08-23 at 14:06:40ID: 25164247

Hi Mortaza_doulaty

Please see the code snippet attached below. The problem is when I do a search like 'star%' which returns 50K + records

Hi acperkins

Thanks for the info, please can you advise how I can go about fixing this issue my limiting the SQL server using the memory?

Thanks both for your help.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3"
            CellSpacing="3" DataKeyNames="GiftCodeID" DataSourceID="dsSearchGiftCodes">
            <Columns>
                <asp:TemplateField HeaderText="Gift Code" SortExpression="GiftCodeID">
                    <EditItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Eval("GiftCodeID") %>'></asp:Label>
                    </EditItemTemplate>
                    <ItemTemplate>
                        &nbsp;<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "SearchRegistrationResults.aspx?GiftCodeID=" & Eval("GiftCodeID") %>'
                            Text='<%# Eval("GiftCodeID") %>'></asp:HyperLink>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="CertRegFirstName" HeaderText="First Name" SortExpression="CertRegFirstName" />
                <asp:BoundField DataField="CertRegLastName" HeaderText="Last Name" SortExpression="CertRegLastName" />
            </Columns>
            <HeaderStyle BackColor="#E0E0E0" />
        </asp:GridView>
        <br />
        <br />
        <asp:SqlDataSource ID="dsSearchGiftCodes" runat="server" ConnectionString="<%$ ConnectionStrings:GiftRepublicConnectionString %>"
            SelectCommand="SELECT GiftCodeID, CertRegFirstName, CertRegLastName FROM GiftCertificate WHERE (GiftCodeID LIKE @GiftCodeID + '%')">
            <SelectParameters>
                <asp:SessionParameter Name="GiftCodeID" SessionField="SearchGiftCodeID" />
            </SelectParameters>
        </asp:SqlDataSource>

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

Select allOpen in new window

 

by: newbie27Posted on 2009-08-23 at 14:09:06ID: 25164258

Table schema

 
/****** Object:  Table [dbo].[GiftCertificate]    Script Date: 08/23/2009 22:08:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GiftCertificate](
	[GiftCodeID] [char](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CustID] [int] NULL,
	[CertRegTitle] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegFirstName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegLastName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegTelephone] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegEmail] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegAddr1] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegAddr2] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegAddr3] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegCity] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegCounty] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegStateID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegPostCode] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertRegCountryID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelTitle] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelFirstName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelLastName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelAddr1] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelAddr2] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelAddr3] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelCity] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelCounty] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelStateID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelPostCode] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CertDelCountryID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GiftCertificateTypeID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CurrentStatusID] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [GiftCertificate_PK] PRIMARY KEY CLUSTERED 
(
	[GiftCodeID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:

Select allOpen in new window

 

by: acperkinsPosted on 2009-08-23 at 14:25:40ID: 25164331

>>please can you advise how I can go about fixing this issue my limiting the SQL server using the memory?<<
You can set the maximum memory used by SQL Server by going to the SQL Server Properties (in SSMS right click on the server and select  Properties and then Memory).  See here from BOL:
<quote>
Maximum server memory (in MB)
Specifies the maximum amount of memory SQL Server can allocate when it starts and while it runs. This configuration option can be set to a specific value if you know there are multiple applications running at the same time as SQL Server and you want to guarantee that these applications have sufficient memory to run. If these other applications, such as Web or e-mail servers, request memory only as needed, then do not set the option, because SQL Server will release memory to them as needed. However, applications often use whatever memory is available when they start and do not request more if needed. If an application that behaves in this manner runs on the same computer at the same time as SQL Server, set the option to a value that guarantees that the memory required by the application is not allocated by SQL Server.
</quote>

 

by: acperkinsPosted on 2009-08-23 at 14:30:23ID: 25164350

How to adjust memory usage by using configuration options in SQL Server
http://msdn.microsoft.com/en-us/library/ms180797.aspx

This article is intended for 2008 but also applies to 2005:
Effects of min and max server memory
http://msdn.microsoft.com/en-us/library/ms180797.aspx

 

by: newbie27Posted on 2009-08-23 at 15:23:52ID: 25164525

Hi acperkins

Thanks for the information and the links.

Please can you advise what Max Value I should set in the max memory?

Aslo, what is dynamic memory here? Please see the attached.

Thanks
S

 

by: newbie27Posted on 2009-08-23 at 15:26:38ID: 25164534

Hi acperkins

We are in the process of getting a new dedicated server and setup Exchange Server on it. Can I then move Sql server database on this new server where I would not have IIS installed. Would this be any help?

Please advise

Thanks

 

by: acperkinsPosted on 2009-08-23 at 18:21:44ID: 25165084

>>Please can you advise what Max Value I should set in the max memory?<<
You will have to decide that, it depends on your system.  But see here, see if it helps:
Configuring SQL Server memory settings
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1299068,00.html

>>Aslo, what is dynamic memory here? Please see the attached.<<
I am not sure.  This is what it states in BOL:
<quote>
Specifies the amount of memory (in kilobytes) to use during index creation sorts. The default value of zero enables dynamic allocation and should work in most cases without additional adjustment; however, the user can enter a different value from 704 to 2147483647.

Note:  
Values from 1 to 703 are not allowed. If a value in this range is entered, the field overrides the entered value with 704.
</quote>

>>Can I then move Sql server database on this new server where I would not have IIS installed. Would this be any help?<<
I would not recommend that. Exchange is as bad as IIS.

 

by: Mortaza_doulatyPosted on 2009-08-23 at 22:27:43ID: 25165767

Some general recommendation about your code:
I noticed that your primary key (GiftCodeID) is [char](16), keep in mind that numeric primary keys are recommended.

Also I noticed that you are not using Stored Procedure, using SP's may increase (not always and not SHOULD increase) performance in some cases. So try to use SP's.

Also having an index for your search field is recommended.

 

by: acperkinsPosted on 2009-08-24 at 05:46:20ID: 25167716

Are we not getting a little side-tracked from the original question?  The question had to do with why MS SQL Server is using up all the memory.  The answer to that is that by default that is by design.  If contrary to MS recommendations we are running other apps (such as IIS) on the same box then we need to restrict the amount of memory used by setting the Max memory used.  MS SQL Server performance will not be great, but at least IIS or any other app will not grind to a halt requiring us to repeatedly restart the server.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...