Excel Export Error - Excel Can't Open File, CPU 100% Utilization

I've got an issue I cannot seem to address. We've upgraded to SP1, and I
still am not able to open Excel files exported from Reporting Services if
they contain more than a few hundred rows.
For example, I have a report which contains 1453 records. Exporting to an
Excel takes a few minutes, then generates a 7 MB file. Attempting to open
that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel steadily
consumes 8K of RAM per second, yet nothing happens. Excel will either
display as "Not Responding" in Task Manager, or will display "Running" and I
cannot switch back to Excel.
I can export to CSV and Excel will open it with no issues. However,
exporting to Excel is critical to our business' use of Reporting Services.
Please advise if anyone has a workaround or is encountering this issue.
--
Aaron Berquist
Business Systems Analyst, Information Services
Open Text CorporationCould you send me the excel file generated for a smaller number rows,
please?
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> I've got an issue I cannot seem to address. We've upgraded to SP1, and I
> still am not able to open Excel files exported from Reporting Services if
> they contain more than a few hundred rows.
> For example, I have a report which contains 1453 records. Exporting to an
> Excel takes a few minutes, then generates a 7 MB file. Attempting to open
> that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
steadily
> consumes 8K of RAM per second, yet nothing happens. Excel will either
> display as "Not Responding" in Task Manager, or will display "Running" and
I
> cannot switch back to Excel.
> I can export to CSV and Excel will open it with no issues. However,
> exporting to Excel is critical to our business' use of Reporting Services.
> Please advise if anyone has a workaround or is encountering this issue.
> --
> Aaron Berquist
> Business Systems Analyst, Information Services
> Open Text Corporation
>|||Any progress on this issue?
"Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> Could you send me the excel file generated for a smaller number rows,
> please?
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> > I've got an issue I cannot seem to address. We've upgraded to SP1, and I
> > still am not able to open Excel files exported from Reporting Services
if
> > they contain more than a few hundred rows.
> >
> > For example, I have a report which contains 1453 records. Exporting to
an
> > Excel takes a few minutes, then generates a 7 MB file. Attempting to
open
> > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
> steadily
> > consumes 8K of RAM per second, yet nothing happens. Excel will either
> > display as "Not Responding" in Task Manager, or will display "Running"
and
> I
> > cannot switch back to Excel.
> >
> > I can export to CSV and Excel will open it with no issues. However,
> > exporting to Excel is critical to our business' use of Reporting
Services.
> >
> > Please advise if anyone has a workaround or is encountering this issue.
> > --
> > Aaron Berquist
> > Business Systems Analyst, Information Services
> > Open Text Corporation
> >
> >
>|||Well, I solved the problem!
The header on the report was causing the issue, as the fields were not lined
up with the data columns on my report, so I think Excel was goign nuts
trying to parse out all the merged columns. By making sure that the
descriptions in the header matched eneded at the same point as a column, the
file now takes about 50 secs to open on a Pentium 3 1Ghz machine.
So, when designing a header, make sure your test boxes line up with your
columns!
Thanks,
Aaron
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:erwtoi$bEHA.2408@.tk2msftngp13.phx.gbl...
> Any progress on this issue?
> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
> news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> > Could you send me the excel file generated for a smaller number rows,
> > please?
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> > news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> > > I've got an issue I cannot seem to address. We've upgraded to SP1, and
I
> > > still am not able to open Excel files exported from Reporting Services
> if
> > > they contain more than a few hundred rows.
> > >
> > > For example, I have a report which contains 1453 records. Exporting to
> an
> > > Excel takes a few minutes, then generates a 7 MB file. Attempting to
> open
> > > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
> > steadily
> > > consumes 8K of RAM per second, yet nothing happens. Excel will either
> > > display as "Not Responding" in Task Manager, or will display "Running"
> and
> > I
> > > cannot switch back to Excel.
> > >
> > > I can export to CSV and Excel will open it with no issues. However,
> > > exporting to Excel is critical to our business' use of Reporting
> Services.
> > >
> > > Please advise if anyone has a workaround or is encountering this
issue.
> > > --
> > > Aaron Berquist
> > > Business Systems Analyst, Information Services
> > > Open Text Corporation
> > >
> > >
> >
> >
>|||There is also a deviceinfo setting to put the page header into the Excel
header, rather than the sheets.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:%23TxUDQLcEHA.3012@.tk2msftngp13.phx.gbl...
> Well, I solved the problem!
> The header on the report was causing the issue, as the fields were not
> lined
> up with the data columns on my report, so I think Excel was goign nuts
> trying to parse out all the merged columns. By making sure that the
> descriptions in the header matched eneded at the same point as a column,
> the
> file now takes about 50 secs to open on a Pentium 3 1Ghz machine.
> So, when designing a header, make sure your test boxes line up with your
> columns!
> Thanks,
> Aaron
> "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> news:erwtoi$bEHA.2408@.tk2msftngp13.phx.gbl...
>> Any progress on this issue?
>> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
>> news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
>> > Could you send me the excel file generated for a smaller number rows,
>> > please?
>> >
>> > --
>> > Nico Cristache [MSFT]
>> > Microsoft SQL Server Reporting Services
>> >
>> > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >
>> >
>> > "Aaron Berquist" <aberquis@.opentext.com> wrote in message
>> > news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
>> > > I've got an issue I cannot seem to address. We've upgraded to SP1,
>> > > and
> I
>> > > still am not able to open Excel files exported from Reporting
>> > > Services
>> if
>> > > they contain more than a few hundred rows.
>> > >
>> > > For example, I have a report which contains 1453 records. Exporting
>> > > to
>> an
>> > > Excel takes a few minutes, then generates a 7 MB file. Attempting to
>> open
>> > > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
>> > steadily
>> > > consumes 8K of RAM per second, yet nothing happens. Excel will either
>> > > display as "Not Responding" in Task Manager, or will display
>> > > "Running"
>> and
>> > I
>> > > cannot switch back to Excel.
>> > >
>> > > I can export to CSV and Excel will open it with no issues. However,
>> > > exporting to Excel is critical to our business' use of Reporting
>> Services.
>> > >
>> > > Please advise if anyone has a workaround or is encountering this
> issue.
>> > > --
>> > > Aaron Berquist
>> > > Business Systems Analyst, Information Services
>> > > Open Text Corporation
>> > >
>> > >
>> >
>> >
>>
>

Excel 2007 can't read data from AS 2005 cube

I have created an AS 2005 cube for my tester to access thru Excel 2007. He can connect to the cube fine, but can't read any measurement data elements from Excel. What Excel return is #N/A in the cell. I've checked many times the role that was setup to grant him permission to the cube. He should have all the rights to read data. In fact, the role setup was fine before I installed service pack 2. I wonder what's been changed in sp2. Any help would be great!

Mitch

It might be worth revisiting the "Cell Data" tab for the role, to ensure that none of the 3 "Enable .." options is checked.|||I wonder what the 3 "Enable..." options do in the "Cell Data" tab. I did unchecked the options and it works now.

Exceeding SQL 2005 Express 4GB Databse Size Limit...

Okay, I know the maximum size limit on a database in SQL 2005 Express is 4GB, but what happens when you exceed that limit? I can't seem to find much info on that topic. What kind of error return would you get from a stored procedure attempting to do an insert that will exceed the 4GB limitation? Also what factors go into that maximum size, factors that might not be completely obvious of course?

Try the link below for all the info you need, what I can tell is the read access can be scaled to very high number, if you are doing inserts and updates you should limit it to 3.5gig so SQL Server can keep .5gig for house keeping. Hope this helps.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Exam Tommorrow Help me please

Hi there I have an exam tommorrow and i am studying for my SQL which I havent done in months and cant even work out how to do my revision problems.

DELEGATE: course coude, employee num, date, attended
EMPLOYEE: employee num, forename, surname,address,DOB,joined,department
DEPARTMENT: department num, department name, manager, email
SALARY: salary grp, salary

Could someone please help me with the code for these two queries it would be greatly appreciated.

1. List the number of ppl in each department who attended a training course (attended = yes). output department name and count only.

2. Calculate a bonus for every member of staff, if they joined more than 10 years ago they get a 5% on their salary, else they get 2.5%Hi there I have an exam tommorrow and i am studying for my SQL which I havent done in months oh, man, that's too bad

my advice: get off the internet and re-read your books and course notes, then get a good night's sleep

Event Log

I'm trying to create an alert that will tell me when a database is deleted or
added.
I can't seem to find a notification i the event log when either happens.
Also I would like create an alert for my maintenance jobs when they
complete. But I see no trace of them in the event log.
Thank You
AFAIK there is no alert for dropping or adding a db within SQL Server. You
can use a third party product from www.lumigent.com called Integra that may
do what you want. As for the MP's, why would you want to know when they
complete. The whole idea of creating them is so they can run attended. In
any case you can always add a RAISERROR() to a job step that will force an
error that will be picked up by an alert.
Andrew J. Kelly SQL MVP
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:B420F0DD-4186-44CE-B031-CE5C0F9C4026@.microsoft.com...
> I'm trying to create an alert that will tell me when a database is deleted
> or
> added.
> I can't seem to find a notification i the event log when either happens.
> Also I would like create an alert for my maintenance jobs when they
> complete. But I see no trace of them in the event log.
> Thank You
|||If you use SQL Server Agent to schedule your jobs, there is an option to
have the event logged to the Windows event log. This under the
notifications tab when double clicking on the job via the EM console.
-Ben
robert_at_cbb wrote:

> I'm trying to create an alert that will tell me when a database is deleted or
> added.
> I can't seem to find a notification i the event log when either happens.
> Also I would like create an alert for my maintenance jobs when they
> complete. But I see no trace of them in the event log.
> Thank You
|||Thanks!
Too bad notification for dropping severs is not available. I have many
servers, and I want to know that my maintenance jobs finish.
"Ben" wrote:

> If you use SQL Server Agent to schedule your jobs, there is an option to
> have the event logged to the Windows event log. This under the
> notifications tab when double clicking on the job via the EM console.
> -Ben
>
>
> robert_at_cbb wrote:
>

Event ID: 17830?

All,
Can't figure out why I am getting this message in my event log...
Event Type: Error
Event Source: MSSQL$SQLSERVER2005
Event Category: (4)
Event ID: 17830
Date: 9/14/2005
Time: 7:06:03 AM
User: N/A
Computer: ADDVMFM12
Description:
A network error occurred while establishing a connection; the connection has
been closed. This may have been caused by client or server login timeout
expiration. Time spent during login: total 3 ms, enqueued 1 ms, network
writes 1 ms, network reads 3 ms, establishing SSL 3 ms, negotiating SSPI 0
ms, validating login 0 ms. [CLIENT: 10.13.0.137]
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: a6 45 00 00 14 00 00 00 E.....
0008: 18 00 00 00 41 00 44 00 ...A.D.
0010: 44 00 56 00 4d 00 46 00 D.V.M.F.
0018: 4d 00 31 00 32 00 5c 00 M.1.2.\.
0020: 53 00 51 00 4c 00 53 00 S.Q.L.S.
0028: 45 00 52 00 56 00 45 00 E.R.V.E.
0030: 52 00 32 00 30 00 30 00 R.2.0.0.
0038: 35 00 00 00 00 00 00 00 5......
Anyone have any idea why this is happening?
Thanks
Mike
You'll want to post this in the SQL Server 2005 CTP
newsgroups:
http://communities.microsoft.com/new...r2005&slcid=us
-Sue
On Wed, 14 Sep 2005 07:23:14 -0400, "Mike"
<mgasperino@.mebtel.net> wrote:

>All,
>Can't figure out why I am getting this message in my event log...
>Event Type: Error
>Event Source: MSSQL$SQLSERVER2005
>Event Category: (4)
>Event ID: 17830
>Date: 9/14/2005
>Time: 7:06:03 AM
>User: N/A
>Computer: ADDVMFM12
>Description:
>A network error occurred while establishing a connection; the connection has
>been closed. This may have been caused by client or server login timeout
>expiration. Time spent during login: total 3 ms, enqueued 1 ms, network
>writes 1 ms, network reads 3 ms, establishing SSL 3 ms, negotiating SSPI 0
>ms, validating login 0 ms. [CLIENT: 10.13.0.137]
>For more information, see Help and Support Center at
>http://go.microsoft.com/fwlink/events.asp.
>Data:
>0000: a6 45 00 00 14 00 00 00 E.....
>0008: 18 00 00 00 41 00 44 00 ...A.D.
>0010: 44 00 56 00 4d 00 46 00 D.V.M.F.
>0018: 4d 00 31 00 32 00 5c 00 M.1.2.\.
>0020: 53 00 51 00 4c 00 53 00 S.Q.L.S.
>0028: 45 00 52 00 56 00 45 00 E.R.V.E.
>0030: 52 00 32 00 30 00 30 00 R.2.0.0.
>0038: 35 00 00 00 00 00 00 00 5......
>Anyone have any idea why this is happening?
>Thanks
>Mike
>

event id 208 on sql server 7 HELP

Hi,

For some strange reason all of a sudden, my scheduled jobs are not running. I dont remember changing anything that might cause this but i cant say for sure. The other issue i have is that i cannot backup a database to the default location as shown on the second even below.

Any ideas?

Thanks for your help

Event Type: Warning
Event Source: SQLServerAgent
Event Category: Job Engine
Event ID: 208
Date: 08/09/2006
Time: 12:00:01
User: N/A
Computer: SERVER1
Description:
SQL Server Scheduled Job 'DB Backup Job for 'DBMP-EDB'' (0xC50AD7AF9993294A8134D2F7D10C0ADC) - Status: Failed - Invoked on: 08/09/2006 12:00:00 - Message: The job failed. The Job was invoked by Schedule 23 (Schedule 1). The last step to run was step 1 (Step 1).

Event Type: Error
Event Source: MSSQLServer
Event Category: Kernel
Event ID: 17055
Date: 07/09/2006
Time: 19:30:04
User: NT AUTHORITY\SYSTEM
Computer: SERVER1
Description:
18204 :
BackupDiskFile::CreateMedia: Backup device 'C:\MSSQL7\BACKUP\my_db_200609071930.BAK' failed to create. Operating system error = 5(error not found).
Data:
0000: 1c 47 00 00 10 00 00 00 .G......
0008: 0d 00 00 00 45 00 46 00 ....E.F.
0010: 41 00 43 00 53 00 53 00 A.C.S.S.
0018: 45 00 52 00 56 00 45 00 E.R.V.E.
0020: 52 00 31 00 00 00 00 00 R.1.....
0028: 00 00 ..

Moahmad,

From what I have seen this error comes up when the SQL Server Agent fails to authenticate to the OS or it cannot determine if the owner of the job has access to the OS.

Did this Backup Job work before?

|||

Hi Jeff,

yeah it all worked fine until a couple of weeks ago. The other thing i found is that the "systems managment date manager" service has not started and will not start when i attempt too. Would this have anything to do with it.

thanks for your help

Mo

|||

Here is a copy of the log files:

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SERVER1' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DBMP-EFACS' on Wed Sep 27 12:30:01 2006

Could not create a sub directory for database efacdb. The parent directory will be used instead.[1] Database efacdb: Delete Old Backup Files...
0 file(s) deleted.

Deleting old text reports... 1 file(s) deleted.

End of maintenance plan 'DBMP-EFACS' on Wed Sep 27 12:30:01 2006

SQLMAINT.EXE Process Exit Code: 0 (Success)

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SERVER1' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DBMP-All' on Wed Sep 27 20:00:01 2006

Could not create a sub directory for database model. The parent directory will be used instead.[1] Database model: Transaction Log Backup...
Destination: [C:\MSSQL7\BACKUP\model_tlog_200609272000.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\MSSQL7\BACKUP\model_tlog_200609272000.TRN'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
Deleting old text reports... 1 file(s) deleted.

End of maintenance plan 'DBMP-All' on Wed Sep 27 20:00:01 2006

SQLMAINT.EXE Process Exit Code: 1 (Failed)

event id 208 on sql server 7 HELP

Hi,

For some strange reason all of a sudden, my scheduled jobs are not running. I dont remember changing anything that might cause this but i cant say for sure. The other issue i have is that i cannot backup a database to the default location as shown on the second even below.

Any ideas?

Thanks for your help

Event Type: Warning
Event Source: SQLServerAgent
Event Category: Job Engine
Event ID: 208
Date: 08/09/2006
Time: 12:00:01
User: N/A
Computer: SERVER1
Description:
SQL Server Scheduled Job 'DB Backup Job for 'DBMP-EDB'' (0xC50AD7AF9993294A8134D2F7D10C0ADC) - Status: Failed - Invoked on: 08/09/2006 12:00:00 - Message: The job failed. The Job was invoked by Schedule 23 (Schedule 1). The last step to run was step 1 (Step 1).

Event Type: Error
Event Source: MSSQLServer
Event Category: Kernel
Event ID: 17055
Date: 07/09/2006
Time: 19:30:04
User: NT AUTHORITY\SYSTEM
Computer: SERVER1
Description:
18204 :
BackupDiskFile::CreateMedia: Backup device 'C:\MSSQL7\BACKUP\my_db_200609071930.BAK' failed to create. Operating system error = 5(error not found).
Data:
0000: 1c 47 00 00 10 00 00 00 .G......
0008: 0d 00 00 00 45 00 46 00 ....E.F.
0010: 41 00 43 00 53 00 53 00 A.C.S.S.
0018: 45 00 52 00 56 00 45 00 E.R.V.E.
0020: 52 00 31 00 00 00 00 00 R.1.....
0028: 00 00 ..

Moahmad,

From what I have seen this error comes up when the SQL Server Agent fails to authenticate to the OS or it cannot determine if the owner of the job has access to the OS.

Did this Backup Job work before?

|||

Hi Jeff,

yeah it all worked fine until a couple of weeks ago. The other thing i found is that the "systems managment date manager" service has not started and will not start when i attempt too. Would this have anything to do with it.

thanks for your help

Mo

|||

Here is a copy of the log files:

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SERVER1' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DBMP-EFACS' on Wed Sep 27 12:30:01 2006

Could not create a sub directory for database efacdb. The parent directory will be used instead.[1] Database efacdb: Delete Old Backup Files...
0 file(s) deleted.

Deleting old text reports... 1 file(s) deleted.

End of maintenance plan 'DBMP-EFACS' on Wed Sep 27 12:30:01 2006

SQLMAINT.EXE Process Exit Code: 0 (Success)

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SERVER1' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DBMP-All' on Wed Sep 27 20:00:01 2006

Could not create a sub directory for database model. The parent directory will be used instead.[1] Database model: Transaction Log Backup...
Destination: [C:\MSSQL7\BACKUP\model_tlog_200609272000.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\MSSQL7\BACKUP\model_tlog_200609272000.TRN'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
Deleting old text reports... 1 file(s) deleted.

End of maintenance plan 'DBMP-All' on Wed Sep 27 20:00:01 2006

SQLMAINT.EXE Process Exit Code: 1 (Failed)

Event 17890 in Application Event Log

Hello,
on one of my servers I'm getting Event 17890 at irregular intervals in the
event log. Since the server is running in German, I can't provide the exact
wording, but loosely translated it's more or less: "A large part of the SQL
Server process memory has been paged. This can cause performance problems."
And then there's some information on the size of the working set, the commit
and memory use in %.
I haven't seen this on any other SQL Server machine, and I can't find any
information about the event in the documentation or on the net. Is this
something to be worried about? What can I do to prevent it?
The server has 2 GB of RAM, there are a couple of SQL instances running, but
not much else. If there's any other information I can supply that would help,
I'd be glad to.
Thanks for any suggestions.
Probably 1 of youre instances needs more memory.
You can add more memory per instance in enterprisemanager.
|||hi,
You should consider configuring the memory limits to each of the sql
instances, having in consideration that you should leave memory available for
the OS.
Having that in mind, the first thing to do is to have an idea of how much
memory each of the sql instances need.
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"Tom Juergens" wrote:

> Hello,
> on one of my servers I'm getting Event 17890 at irregular intervals in the
> event log. Since the server is running in German, I can't provide the exact
> wording, but loosely translated it's more or less: "A large part of the SQL
> Server process memory has been paged. This can cause performance problems."
> And then there's some information on the size of the working set, the commit
> and memory use in %.
> I haven't seen this on any other SQL Server machine, and I can't find any
> information about the event in the documentation or on the net. Is this
> something to be worried about? What can I do to prevent it?
> The server has 2 GB of RAM, there are a couple of SQL instances running, but
> not much else. If there's any other information I can supply that would help,
> I'd be glad to.
> Thanks for any suggestions.
|||Thanks,
that sounds sensible, but I must admit I'm not sure how best to go about it.
I've checked: There are 2 instances of SQL2000 and one SQL2005. They are all
using dynamic memory allocation: One instance of 2000 is set to use between 0
and 512 MB, whereas the other two use the default values of 0 and 2147483647
MB.
I can use perfmon to check the amount of memory currently used by each
instance, but that doesn't tell me how much they might need max. Plus, the
instance which is used most and has the largest database is also using the
most memory (over 1 GB), which makes sense, but this is also the instance
that is causing event 17890. So I suppose either one of the other instances
or another process is suddenly asking for more memory.
Am I on the right track? And what do I do next? Thanks for your help!
Tom
"Paulo Ferreira" wrote:
[vbcol=seagreen]
> hi,
> You should consider configuring the memory limits to each of the sql
> instances, having in consideration that you should leave memory available for
> the OS.
> Having that in mind, the first thing to do is to have an idea of how much
> memory each of the sql instances need.
> --
> Paulo Ferreira
> http://www.info2k.pt
> SQL Server DBA Experts
>
>
> "Tom Juergens" wrote:
|||Hello Tom,
Did you use SQL 2005 x64 version? If so, please follow this KB article
How to reduce paging of buffer pool memory in the 64-bit version of SQL
Server 2005
http://support.microsoft.com/kb/918483
If not, please let me know how many physical memory your server have?
If your SQL Server configured to use all the memory, once other application
required memory, it requested it from the OS and the OS on it turn requests
all processes on the machine to trim their working set (or the virtual
memory that is in RAM). So such a scenario caused SQL Server memory to be
trimmed and paged out to page file. When SQL memory gets paged out the
performance goes to a crawl.
My suggestion is limited the Maz Server Memory for the SQL 2005 instance
which allow some RAM for other application.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Wei Lu,
no, it's not x64, and the server has 2 GB physical memory. I guess I'll
limit the memory for this SQL instance to around 1 GB and see if that helps.
And then I can play around with the memory allocation to see if more or less
gives me better results.
That leaves me with two follow-on questions:
Is there any point in setting the minimum memory?
What's the best way to determine the max memory actually used over a period
of time, so that I can decide if I need to allocate more or less?
Thank you
Tom
"Wei Lu [MSFT]" wrote:

> Hello Tom,
> Did you use SQL 2005 x64 version? If so, please follow this KB article
> How to reduce paging of buffer pool memory in the 64-bit version of SQL
> Server 2005
> http://support.microsoft.com/kb/918483
> If not, please let me know how many physical memory your server have?
> If your SQL Server configured to use all the memory, once other application
> required memory, it requested it from the OS and the OS on it turn requests
> all processes on the machine to trim their working set (or the virtual
> memory that is in RAM). So such a scenario caused SQL Server memory to be
> trimmed and paged out to page file. When SQL memory gets paged out the
> performance goes to a crawl.
> My suggestion is limited the Maz Server Memory for the SQL 2005 instance
> which allow some RAM for other application.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
|||Hello Tom,
You could use the perfmon to check the memory OS allocated to SQL Server.
I don't think the minimum memory will effect in this scenario.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.