Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql Profiler trace cannot trace events b/c max amount of available memory has been reached

Posted on 2004-10-04
9
Medium Priority
?
627 Views
Last Modified: 2010-08-05
I have a SQL 2000 database that seems to be running a large amount of transactions at one time to create a 10GB transaction log. I have pinpointed the time this has occurred and it happens between 1 and 2am every night. We just recently upgraded the application so I assume it could be due to that since this hasn't occurred prior to the upgrade. I attempted to do a trace using sql profiler and set the output file size to 30MB to give me a good view of when and how many transactions occur during a specified time. From 11pm-230am I have 18 - 30MB files. From 1:07am - 2:32am 15 of the 18 were created. I have been looking through the files and many of the messages I receive are "Profiler Message - Some trace events have not been reported to SQL Profiler because the server has reached its maximum amount of available memory for the process. Does this mean the processes have maxed out the available memory for SQL Server itself? We have 2GB of memory available on the server just for SQL Server. There aren't any messages in the event viewer and I can't see anything in the error logs. I'm very new to this trace thing so please help me understand where to look next??? Thanks in advance!!
0
Comment
Question by:valerian28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
9 Comments
 
LVL 1

Expert Comment

by:xabimond
ID: 12217707
Have a look at this:

http://www.winnetmag.com/SQLServer/Article/ArticleID/26046/26046.html

A good explanation of the cause of what you're seeing and how to ensure you get all events.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12220867
It just means the Server was doing too many transactions for the trace to keep up with.

Have you looked at your scheduled jobs to see if anything is running during this period?  Any backups?  Any file imports?  Maintenance plans?
0
 

Author Comment

by:valerian28
ID: 12320059
The scheduled jobs I have running on this server are the transaction log backups that run every hour on the hour and the full database backup that runs at 2am every morning. There are no imports and the activity is minimal if any.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:valerian28
ID: 12320084
One other thing I noticed, in my profiler log it appears that most of the activity is generated from sqlexec (me) or sa. To me that means it's referring to the backups but why would this cause such a large transaction log backup?
0
 

Author Comment

by:valerian28
ID: 12359467
Ok I have added a new schedule withing the db maintenance plan. From 4am-12am I have the transaction log backups running every hour. From 12am-4am I have the transaction backups running every 10min. On average they run about 5 minutes. At 1am the backup was 14KB, then the next one shows 1:48am because it took 46 minutes to run and it is 7GB in size! Then at 2:03 it is at 2.5GB and at 2:10am it is back down to 563KB. I don't know what else to do. I had spoken with the vendor prior to this and they said to increase the number of backups. What would cause this?
0
 
LVL 34

Accepted Solution

by:
arbert earned 1200 total points
ID: 12359634
What else are you doing in the maintenance plans?  Just a backup?

"then the next one shows 1:48am because it took 46 minutes to run and it is 7GB in "

With the amount of time and the size, I would almost say this is a full DB backup.

Just my personal opinion here, but I would drop the maintenance plans (they are crap) and script the backups and schedule with SQLAgent....
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question