Link to home
Start Free TrialLog in
Avatar of Inserachof
InserachofFlag for United States of America

asked on

SQL 2005 Error

Windows 2003 R2 Standard Dc SP2
SQL 2005 Standard edition SP3

I get this error every day at this time

Type :            Error
Date :            4/6/2010
Time :            11:02:28 AM
Event :            107
Source :            Report Server Windows Service (MSSQLSERVER)
Category :      Management
User :            N/A
Computer :      TGCS001
Description:
Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.


It is when WSUS is doing and check for updates from micrososft.

This runs at eleven am

I stopped WSUS and it did not happen


Type :            Information
Date :            4/6/2010
Time :            11:01:16 AM
Event :            17890
Source :            MSSQLSERVER
Category :      Server
User :            N/A
Computer :      TGCS001
Description:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 8608, committed (KB): 406024, memory utilization: 2%.

Type :            Information
Date :            4/6/2010
Time :            11:02:09 AM
Event :            833
Source :            MSSQLSERVER
Category :      Server
User :            N/A
Computer :      TGCS001
Description:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2).  The OS file handle is 0x0000070C.  The offset of the latest long I/O is: 0x000000004f2000



The other two messages occurred just before event 107

This is what I found

Any ideas

Thanks

Tom
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

>> A significant part of sql server process memory has been paged out. This may result in a performance degradation.

Seems like there is Memory resource contention in your machine..
Kindly let me know the following details:

* Windows OS 2003 either 32 bit or 64 bit
* Physical Memory available in your machine
* Major applications running in the machine other than SQL Server

And do configure these set of things for SQL Server:
* Enable AWE
* Configure Min and Max Server Memory
to mitigate these resource contentions.
Avatar of Inserachof

ASKER

Windows 2003 Os is 32 Bit

4GB

WSUS 3.1 SP2
SHAREPOINT 3.0 SP2
Kaspersky Anti Virus for Servers

I added to my boot.in the /3GB and /PAE switch but I have not yet restarted the server
Is the AWE needed it I have the /3gb switch on?

how do I configre the min and max? what values shoudl I use?


Thanks
>> I added to my boot.in the /3GB and /PAE switch but I have not yet restarted the server

Request to restart the server immediately ( recommended)

>> Is the AWE needed it I have the /3gb switch on?

Not required theoritically, but request you to enable that in SQL Server for performance on a longer run

>> how do I configre the min and max? what values shoudl I use?

Steps to configure:

1. Connect to your server in SSMS
2. Right Click your Server and choose Properties
3. Choose Memory in left side tab
4. Check Enable AWE
5. Set Min and Max Server Memory to 1500 and 2000 or 2500 respectively.
Thanks

I got a message after i did this but i did not get the info to early in the morning i guess

but these were in the event log

Type :            Information
Date :            4/7/2010
Time :            5:35:22 AM
Event :            15457
Source :            MSSQLSERVER
Category :      Server
User :            OUR\Administrator
Computer :      TGCS001
Description:
Configuration option 'min server memory (MB)' changed from 128 to 1500. Run the RECONFIGURE statement to install.

Type :            Information
Date :            4/7/2010
Time :            5:35:22 AM
Event :            15457
Source :            MSSQLSERVER
Category :      Server
User :            OUR\Administrator
Computer :      TGCS001
Description:
Configuration option 'max server memory (MB)' changed from 2147483647 to 2500. Run the RECONFIGURE statement to install.

Type :            Information
Date :            4/7/2010
Time :            5:35:22 AM
Event :            15457
Source :            MSSQLSERVER
Category :      Server
User :            OUR\Administrator
Computer :      TGCS001
Description:
Configuration option 'awe enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

not sure here but do i run a query next to get this to update?

what would the synctex be for the recofigure command?

Thanks

Tom
Just type RECONFIGURE in SSMS window and execute it;

RECONFIGURE;
GO
thanks

I executed the reconfigure command

this was the result

address windowing extensions (awe) requires the 'lock pages in memory' priviledge which is not currently present in the access token of the process

I tried this
http://technet.microsoft.com/en-us/library/ms190730.aspx

but the add user or group button is greyed out

Message displayed
This setting is not compatible with computers running windows 2000 sp1 or earlier Apply group policy objects containing this setting only to computers running a later version of the operating system

This is a Windows 2003 R2 Standard SP2 server

Any ideas


OK
Boot.ini now has /3gb /PAE

SQL 2005 is running with AWE enabled

Min and max memory set to 1500 to 2500

Restarted the server

Lets see if the errors continue


Will post results
rrjegan17:

I have another sql2005 server running on another server
this sql server has two instanaces

Shouild I apply the settings to each instance?


Thanks
Regret for the delay in reply..
Seems like you have configured everything including Lock Pages in Memory now and let me know the results of it..

>> I have another sql2005 server running on another server this sql server has two instanaces
Shouild I apply the settings to each instance?

Yes you need to do it in the following way
* Lock Pages in Memory and /3GB and /PAE in Boot.ini are windows level changes.
* Enabling AWE and setting Min and Max Server Memory at instance level ( hence need to do in both instances)

Note: Combined limit of Min and Max Server memory should be well under the Physical Memory available in the machine.
Eg:
Physical Memory: 8 GB
Instance1: Min and Max - 2 and 3 GB
Instance2: Min and Max - 2 and 3 GB
Which should be well under the overall limit of 8 GB (4 and 6GB )
Hope this clarifies.
Bad news guys the problem is back today I got these errors \

Type :            Information
Date :            4/11/2010
Time :            10:05:08 AM
Event :            17890
Source :            MSSQLSERVER
Category :      Server
User :            N/A
Computer :      TGCS001
Description:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 10080, committed (KB): 1365656, memory utilization: 0%.

Type :            Error
Date :            4/11/2010
Time :            10:05:36 AM
Event :            5000
Source :            SQLDUMPER
Category :      None
User :            N/A
Computer :      TGCS001
Description:
EventType sql90exception, P1 reportingservicesservice.exe, P2 9.0.4266.0, P3 4acd2bb6, P4 reportingservicesnativeserver.dll, P5 2005.90.4035.0, P6 492b188d, P7 0, P8 00012dd1, P9 00000000, P10 NIL.

Type :            Information
Date :            4/11/2010
Time :            10:05:39 AM
Event :            5001
Source :            SQLDUMPER
Category :      None
User :            N/A
Computer :      TGCS001
Description:
Bucket 852464128, bucket table 5, EventType sql90exception, P1 reportingservicesservice.exe, P2 9.0.4266.0, P3 4acd2bb6, P4 reportingservicesnativeserver.dll, P5 2005.90.4035.0, P6 492b188d, P7 0, P8 00012dd1, P9 00000000, P10 NIL.

Type :            Information
Date :            4/11/2010
Time :            10:05:42 AM
Event :            1010
Source :            SQLDUMPER
Category :      None
User :            N/A
Computer :      TGCS001
Description:
Bucket 852464128, bucket table 5.

Type :            Information
Date :            4/11/2010
Time :            10:06:10 AM
Event :            17890
Source :            MSSQLSERVER
Category :      Server
User :            N/A
Computer :      TGCS001
Description:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 305 seconds. Working set (KB): 15796, committed (KB): 1365656, memory utilization: 1%.

Type :            Information
Date :            4/11/2010
Time :            10:06:39 AM
Event :            0
Source :            SQL Server Report Service
Category :      None
User :            N/A
Computer :      TGCS001
Description:
Service started successfully.

Type :            Information
Date :            4/11/2010
Time :            10:11:41 AM
Event :            17890
Source :            MSSQLSERVER
Category :      Server
User :            N/A
Computer :      TGCS001
Description:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 635 seconds. Working set (KB): 163456, committed (KB): 1365656, memory utilization: 11%.

this one 5 more times



the these from the system log around the same time frame

Type :            Error
Date :            4/11/2010
Time :            10:05:10 AM
Event :            7011
Source :            Service Control Manager
Category :      None
User :            N/A
Computer :      TGCS001
Description:
Timeout (60000 milliseconds) waiting for a transaction response from the NtFrs service.


Type :            Error
Date :            4/11/2010
Time :            10:05:10 AM
Event :            7011
Source :            Service Control Manager
Category :      None
User :            N/A
Computer :      TGCS001
Description:
Timeout (60000 milliseconds) waiting for a transaction response from the ReportServer service.

Type :            Error
Date :            4/11/2010
Time :            10:05:35 AM
Event :            7031
Source :            Service Control Manager
Category :      None
User :            N/A
Computer :      TGCS001
Description:
The SQL Server Reporting Services (MSSQLSERVER) service terminated unexpectedly.  It has done this 1 time(s).  The following corrective action will be taken in 60000 milliseconds: Restart the service.

my ntbackup finished at 9:56 AM

and this ran at 10:00 am
Type :            Information
Date :            4/11/2010
Time :            10:00:00 AM
Event :            10000
Source :            Windows Server Update Services
Category :      Core
User :            N/A
Computer :      TGCS001
Description:
WSUS is working correctly.

but these errors started at 10:05 AM

Not sure whats up

the /3gb  and /pae is in the boot.ini server restarted

AWE is on is sql

min 1500 memory
max 2500 memory  are set in sql server


what else can i do  ???? more memory ? faster server?


Thanks
Also just checked my other windows 2003 R2 standard server and the same type of messages appeared today for the first time

I do not think the /3gb /pae

awe settings in sql

min max settings is sql

helped at all here

this all happens during NTBACKUP or shortly after

and on server1 when WSUS runs its daily check

shadowcopy freezes the database and we need a way to stop that from happening

any ideas   help

>> The SQL Server Reporting Services (MSSQLSERVER) service terminated unexpectedly.  It has done this 1 time(s).  The following corrective action will be taken in 60000 milliseconds: Restart the service.
>> this all happens during NTBACKUP or shortly after

Make sure your NTBACKUP is configured properly..
Try pathcing it with latest service packs if applicable and your Windows OS along with SQL Server too..

>> shadowcopy freezes the database and we need a way to stop that from happening

Try taking a native sql backup using maintenance plans instead of NTBACKUP for sql server databases / folders which should help..
still reviewing
Let me know if you have any doubts so that you can take clear decision..
rjegan17

My ntbackup does not backup sql databases it never did and it never will
I use sql maint plans all the time

The problem here is that shadowcopy frezzes the sql database even tho the folders in which the sql databases reside are excluded.  

shadowcopy is disabled on all volumes

Shadowcopy needs an exclusion feature
>> Shadowcopy needs an exclusion feature

Or just place all your SQL Database files into a separate disk ( Recommended) and exclude that disk if possible in Shawdocopy program( Don't have much expertise in Shawdowcopy and hence correct if I am wrong)..
rrjegan17

Sorry but moving the sql databaes to another disk volume will not solve the problem

Here it is in a nut shell.

Currently the SQL Database folder is excluded from the NTBACKUP job
SQL database files are only backed up via SQL maint plans

Now for shadowcopy issue

Shadowcopy is DISABLED from all volumes on the server

It is not shadowcopy on the volumes that is the issue

It is that when NTBACKUP runs it envokes shadowcopy and when NTBACKUP envokes shadowcopy it takes a complete image of the volume no matter what is excluded in NTBACKUP  So shadowcopy then freezes the SQL databases while taking this snapshot and that can cause and error along the way.

What we need is an EXCLUSION FEATURE FOR SHADOWCOPY  they have on on the workstation side

But the feature is not supported on Windows 2003 server

Do not understand why MS feels the need to have shadowcopy make an image of the entire volume and freeze the sql databases. Any good sql admin will use maint plans to backup sql databases and not ntbackup


So thats my issue


Thanks
>> Any good sql admin will use maint plans to backup sql databases and not ntbackup

Thanks for detailed info about Shawdowcopy..
If this server is dedicated only for SQL Server, then you can avoid using Shawdowcopy itself right..( Just a lateral thought)

do you mean freezing as shawdowcopy utilizes all memory in your system..
yes it does everytime NTBACKUP runs in the AM it freezes up the SQL databases
Kindly schedule NTBACKUP to run at off-peak hours..
Also check for any updates for NTBACKUP as well as your OS which also might help.
they do run at off peak hours
Any patches or updates for NTBACKUP and OS
thats a good question

I have WSUS running on another server and it keeps all my machines current with fixes from mirosoft

patches on the other hand not aware of any

if there are I have noticed that they are mostly for old service pack versions I have the latest sp fopr 2003 server on sp2

If any knows of ay let me know

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks  

what functions will i lose if I diable the SQL Server VSS Writer service?

I noticed it is set to automatic

If I stop it will I lose any fuction that I need to worry about?


Thanks
Nothing to worry about..
If you are storing SQL scripts directly to Visual Source safe ( VSS) then it would impact, else it will not.
I disable the service on one server as a test will see what happens in the am when the backup runs
It should most probably work fine now..
And revert whether it works fine or not.
it worked great thanks you will get the points
turning off the service stopped all the shadow copy messages during the nt backup process
Welcome..
And glad to help you out..