Hi Everyone,
I've got Windows SharePoint Services installed and it uses a database called
STS_svr-apps-1_1414639615
I'm trying to back up this database from the QSQL command line tool.
However when I try to run the backup, I get the following error:
1> BACKUP DATABASE STS_svr-apps-1_1414639615 TO DISK
'D:\ShareBack\Backup.bak'
2> GO
Msg 170, Level 15, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1
Line 1: Incorrect syntax near '-'.
1>
Thinking it might be an issue with the name I enclosed it in single quotes:
1> BACKUP DATABASE 'STS_svr-apps-1_1414639615' TO DISK
'D:\ShareBack\Backup.bak'
2> GO
Msg 170, Level 15, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1
Line 1: Incorrect syntax near 'STS_svr-apps-1_1414639615'.
1>
Yet I still get the same error!
Then I tried to just use the database:
1> use STS_svr-apps-1_1414639615
2> go
Msg 911, Level 16, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1
Could not locate entry in sysdatabases for database 'STS_svr'. No entry
found
with that name. Make sure that the name is entered correctly.
1>
Again I tried to using quotation marks, another error:
1> use 'STS_svr-apps-1_1414639615'
2> GO
Msg 170, Level 15, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1
Line 1: Incorrect syntax near 'STS_svr-apps-1_1414639615'.
1> quit
If I do:
SELECT name
FROM master..sysdatabases
ORDER BY name
It lists:
master
model
msdb
STS_Config
STS_svr-apps-1_1414639615
tempdb
From this I can only assume MSDE doesn't support having '-' in a database
name?
Can someone suggest a way around this?
Thanks
Chris Moon
hi Chris,
> From this I can only assume MSDE doesn't support having '-' in a
> database name?
>
SQL Server (and MSDE) support this kind of identifier.. executing
SET NOCOUNT ON
SELECT @.@.VERSION
GO
CREATE DATABASE [STS_svr-apps-1_1414639615];
GO
USE [STS_svr-apps-1_1414639615];
GO
USE master ;
GO
DROP DATABASE [STS_svr-apps-1_1414639615];
reports
Microsoft SQL Server 2000 - 8.00.2162 (Intel X86)
Sep 30 2005 19:59:14
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
The CREATE DATABASE process is allocating 0.75 MB on disk
'STS_svr-apps-1_1414639615'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'STS_svr-apps-1_1414639615_log'.
Deleting database file 'C:\Programmi\Microsoft SQL
Server\MSSQL$MSDE2K\Data\STS_svr-apps-1_1414639615_log.LDF'.
Deleting database file 'C:\Programmi\Microsoft SQL
Server\MSSQL$MSDE2K\Data\STS_svr-apps-1_1414639615.mdf'.
http://msdn.microsoft.com/library/de...on_03_6e9e.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Many thanks Andrea, it worked perfectly.
Thanks again.
Chris Moon
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:43ml8mF1o772rU1@.individual.net...
> hi Chris,
> SQL Server (and MSDE) support this kind of identifier.. executing
> SET NOCOUNT ON
> SELECT @.@.VERSION
> GO
> CREATE DATABASE [STS_svr-apps-1_1414639615];
> GO
> USE [STS_svr-apps-1_1414639615];
> GO
> USE master ;
> GO
> DROP DATABASE [STS_svr-apps-1_1414639615];
> reports
> ----
> Microsoft SQL Server 2000 - 8.00.2162 (Intel X86)
> Sep 30 2005 19:59:14
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
> The CREATE DATABASE process is allocating 0.75 MB on disk
> 'STS_svr-apps-1_1414639615'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'STS_svr-apps-1_1414639615_log'.
> Deleting database file 'C:\Programmi\Microsoft SQL
> Server\MSSQL$MSDE2K\Data\STS_svr-apps-1_1414639615_log.LDF'.
> Deleting database file 'C:\Programmi\Microsoft SQL
> Server\MSSQL$MSDE2K\Data\STS_svr-apps-1_1414639615.mdf'.
> http://msdn.microsoft.com/library/de...on_03_6e9e.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql
Showing posts with label windows. Show all posts
Showing posts with label windows. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Database mirroring question
I Posted this in SQL Server general and it was suggested I move it here.
Hello,
SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
In mirror monitoring witness indicates disconected from mirror.
All else is green.
Can ping witness from mirror and connect via MSSMS.
No errors in Witness SQL server errorlog.
What can I do to debug this? I have drilled into the status and I do not
see any errors. I kinda assumed I there is a connectivity problem. Any
ideas on how to debug/resolve it?
If you can't mirror the database due to network connectivity problems
mirroring will be shut down in High Safety, High Availability modes.
I would run a ping -t between your publisher and mirror to check network
stability, and then copy files back and forth to see if you get the same
latencies each way.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:538D73DD-AB07-4970-9F78-C591F6806CD7@.microsoft.com...
>I Posted this in SQL Server general and it was suggested I move it here.
> Hello,
> SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
> SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
> In mirror monitoring witness indicates disconected from mirror.
> All else is green.
> Can ping witness from mirror and connect via MSSMS.
> No errors in Witness SQL server errorlog.
> What can I do to debug this? I have drilled into the status and I do not
> see any errors. I kinda assumed I there is a connectivity problem. Any
> ideas on how to debug/resolve it?
Hello,
SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
In mirror monitoring witness indicates disconected from mirror.
All else is green.
Can ping witness from mirror and connect via MSSMS.
No errors in Witness SQL server errorlog.
What can I do to debug this? I have drilled into the status and I do not
see any errors. I kinda assumed I there is a connectivity problem. Any
ideas on how to debug/resolve it?
If you can't mirror the database due to network connectivity problems
mirroring will be shut down in High Safety, High Availability modes.
I would run a ping -t between your publisher and mirror to check network
stability, and then copy files back and forth to see if you get the same
latencies each way.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:538D73DD-AB07-4970-9F78-C591F6806CD7@.microsoft.com...
>I Posted this in SQL Server general and it was suggested I move it here.
> Hello,
> SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
> SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
> In mirror monitoring witness indicates disconected from mirror.
> All else is green.
> Can ping witness from mirror and connect via MSSMS.
> No errors in Witness SQL server errorlog.
> What can I do to debug this? I have drilled into the status and I do not
> see any errors. I kinda assumed I there is a connectivity problem. Any
> ideas on how to debug/resolve it?
Database Mirroring and Login Transfer
Hi All:
I am running SQL 2005 EE SP1 and have implemented database mirroring and
have a mix of SQL accounts and Windows accounts. . Is there a way to
automatically synchronize logins between the servers...including additions,
deletions, roles, passwords, permissions and SIDs. I know Mirroring has
nothing built in for this but am hoping someone has figured something out.For additions, I use sp_help_revlogin to synchronize logins between servers,
the procedure is described in the following article:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
"Jim Norton" wrote:
> Hi All:
> I am running SQL 2005 EE SP1 and have implemented database mirroring and
> have a mix of SQL accounts and Windows accounts. . Is there a way to
> automatically synchronize logins between the servers...including additions
,
> deletions, roles, passwords, permissions and SIDs. I know Mirroring has
> nothing built in for this but am hoping someone has figured something out.
>
>
>|||Check my post about this. I had the same problem some days ago ...
8e803" target="_blank">http://groups.google.com/group/micr...38
8e803
Let me know.
My 2 cents: do not use the script, its easier with SSIS...
Edgardo wrote:[vbcol=seagreen]
> For additions, I use sp_help_revlogin to synchronize logins between server
s,
> the procedure is described in the following article:
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133
>
> "Jim Norton" wrote:
>sql
I am running SQL 2005 EE SP1 and have implemented database mirroring and
have a mix of SQL accounts and Windows accounts. . Is there a way to
automatically synchronize logins between the servers...including additions,
deletions, roles, passwords, permissions and SIDs. I know Mirroring has
nothing built in for this but am hoping someone has figured something out.For additions, I use sp_help_revlogin to synchronize logins between servers,
the procedure is described in the following article:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
"Jim Norton" wrote:
> Hi All:
> I am running SQL 2005 EE SP1 and have implemented database mirroring and
> have a mix of SQL accounts and Windows accounts. . Is there a way to
> automatically synchronize logins between the servers...including additions
,
> deletions, roles, passwords, permissions and SIDs. I know Mirroring has
> nothing built in for this but am hoping someone has figured something out.
>
>
>|||Check my post about this. I had the same problem some days ago ...
8e803" target="_blank">http://groups.google.com/group/micr...38
8e803
Let me know.
My 2 cents: do not use the script, its easier with SSIS...
Edgardo wrote:[vbcol=seagreen]
> For additions, I use sp_help_revlogin to synchronize logins between server
s,
> the procedure is described in the following article:
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133
>
> "Jim Norton" wrote:
>sql
Database Mirroring and Login Transfer
Hi All:
I am running SQL 2005 EE SP1 and have implemented database mirroring and
have a mix of SQL accounts and Windows accounts. . Is there a way to
automatically synchronize logins between the servers...including additions,
deletions, roles, passwords, permissions and SIDs. I know Mirroring has
nothing built in for this but am hoping someone has figured something out.For additions, I use sp_help_revlogin to synchronize logins between servers,
the procedure is described in the following article:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
"Jim Norton" wrote:
> Hi All:
> I am running SQL 2005 EE SP1 and have implemented database mirroring and
> have a mix of SQL accounts and Windows accounts. . Is there a way to
> automatically synchronize logins between the servers...including additions,
> deletions, roles, passwords, permissions and SIDs. I know Mirroring has
> nothing built in for this but am hoping someone has figured something out.
>
>
>|||Check my post about this. I had the same problem some days ago ...
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/306b27dfc6f8f8ce/a691c85fe388e803?lnk=st&q=jocamp3%40gmail.com&rnum=1#a691c85fe388e803
Let me know.
My 2 cents: do not use the script, its easier with SSIS...
Edgardo wrote:
> For additions, I use sp_help_revlogin to synchronize logins between servers,
> the procedure is described in the following article:
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133
>
> "Jim Norton" wrote:
> > Hi All:
> >
> > I am running SQL 2005 EE SP1 and have implemented database mirroring and
> > have a mix of SQL accounts and Windows accounts. . Is there a way to
> > automatically synchronize logins between the servers...including additions,
> > deletions, roles, passwords, permissions and SIDs. I know Mirroring has
> > nothing built in for this but am hoping someone has figured something out.
> >
> >
> >
> >
> >
> >
I am running SQL 2005 EE SP1 and have implemented database mirroring and
have a mix of SQL accounts and Windows accounts. . Is there a way to
automatically synchronize logins between the servers...including additions,
deletions, roles, passwords, permissions and SIDs. I know Mirroring has
nothing built in for this but am hoping someone has figured something out.For additions, I use sp_help_revlogin to synchronize logins between servers,
the procedure is described in the following article:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
"Jim Norton" wrote:
> Hi All:
> I am running SQL 2005 EE SP1 and have implemented database mirroring and
> have a mix of SQL accounts and Windows accounts. . Is there a way to
> automatically synchronize logins between the servers...including additions,
> deletions, roles, passwords, permissions and SIDs. I know Mirroring has
> nothing built in for this but am hoping someone has figured something out.
>
>
>|||Check my post about this. I had the same problem some days ago ...
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/306b27dfc6f8f8ce/a691c85fe388e803?lnk=st&q=jocamp3%40gmail.com&rnum=1#a691c85fe388e803
Let me know.
My 2 cents: do not use the script, its easier with SSIS...
Edgardo wrote:
> For additions, I use sp_help_revlogin to synchronize logins between servers,
> the procedure is described in the following article:
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133
>
> "Jim Norton" wrote:
> > Hi All:
> >
> > I am running SQL 2005 EE SP1 and have implemented database mirroring and
> > have a mix of SQL accounts and Windows accounts. . Is there a way to
> > automatically synchronize logins between the servers...including additions,
> > deletions, roles, passwords, permissions and SIDs. I know Mirroring has
> > nothing built in for this but am hoping someone has figured something out.
> >
> >
> >
> >
> >
> >
Database Mirroring and Login Transfer
Hi All:
I am running SQL 2005 EE SP1 and have implemented database mirroring and
have a mix of SQL accounts and Windows accounts. . Is there a way to
automatically synchronize logins between the servers...including additions,
deletions, roles, passwords, permissions and SIDs. I know Mirroring has
nothing built in for this but am hoping someone has figured something out.
For additions, I use sp_help_revlogin to synchronize logins between servers,
the procedure is described in the following article:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
"Jim Norton" wrote:
> Hi All:
> I am running SQL 2005 EE SP1 and have implemented database mirroring and
> have a mix of SQL accounts and Windows accounts. . Is there a way to
> automatically synchronize logins between the servers...including additions,
> deletions, roles, passwords, permissions and SIDs. I know Mirroring has
> nothing built in for this but am hoping someone has figured something out.
>
>
>
|||Check my post about this. I had the same problem some days ago ...
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/306b27dfc6f8f8ce/a691c85fe388e803?lnk=st&q=jocamp3%40gmail.com&rnum =1#a691c85fe388e803[/url]
Let me know.
My 2 cents: do not use the script, its easier with SSIS...
Edgardo wrote:[vbcol=seagreen]
> For additions, I use sp_help_revlogin to synchronize logins between servers,
> the procedure is described in the following article:
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133
>
> "Jim Norton" wrote:
I am running SQL 2005 EE SP1 and have implemented database mirroring and
have a mix of SQL accounts and Windows accounts. . Is there a way to
automatically synchronize logins between the servers...including additions,
deletions, roles, passwords, permissions and SIDs. I know Mirroring has
nothing built in for this but am hoping someone has figured something out.
For additions, I use sp_help_revlogin to synchronize logins between servers,
the procedure is described in the following article:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
"Jim Norton" wrote:
> Hi All:
> I am running SQL 2005 EE SP1 and have implemented database mirroring and
> have a mix of SQL accounts and Windows accounts. . Is there a way to
> automatically synchronize logins between the servers...including additions,
> deletions, roles, passwords, permissions and SIDs. I know Mirroring has
> nothing built in for this but am hoping someone has figured something out.
>
>
>
|||Check my post about this. I had the same problem some days ago ...
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/306b27dfc6f8f8ce/a691c85fe388e803?lnk=st&q=jocamp3%40gmail.com&rnum =1#a691c85fe388e803[/url]
Let me know.
My 2 cents: do not use the script, its easier with SSIS...
Edgardo wrote:[vbcol=seagreen]
> For additions, I use sp_help_revlogin to synchronize logins between servers,
> the procedure is described in the following article:
> How to transfer logins and passwords between instances of SQL Server
> http://support.microsoft.com/kb/246133
>
> "Jim Norton" wrote:
Thursday, March 22, 2012
Database Mirroring - unable to mirror 59th database
As the subject suggests, I am unable to mirror my 59th database.
(2) database servers
HP ML370 G4
Dual Xeon 3.4Ghz, 8GB RAM
Windows 2003 R2 Enterprise
SQL Server 2005 Standard - patched to 2153
(1) watch server
HP DL 360 G4
Xeon 3Ghz, 2GB RAM
Win2k3 Standard
SQL Server Express - patched to 2153
I was able to restore the database and the transaction log to the desired
mirror. Then I indicated what the partner server should be (via T-SQL).
Then I switched to my SSMS query window that was connected to the primary
server and executed the commands to set the partner, mirror, and alter the
partner timeout.
The database was mirrored, but then I noticed a sign of trouble within the
Database Mirroring Monitor. One of the databases that was already being
mirrored switched status to "(Principal, Disconnected)"
I executed the following query to check the status:
select mirroring_state_desc, count(*)
From sys.database_mirroring
group by mirroring_state_desc
Here are the results:
mirroring_state_desc
-- --
NULL 60
DISCONNECTED 1
SYNCHRONIZED 58
I wanted to make sure that the database mirroring monitoring job (called by
SQL Server Agent) was not part of the problem, so I stopped monitoring by
calling this stored procedure: sp_dbmmonitordropmonitoring
and I waited... No change in status.
I decided to stop mirroring on the newly added database. I used the
following command to stop mirroring:
ALTER DATABASE foo SET PARTNER OFF
and the other database (the one that had a status of Principal, Disconnected
synchronized and came back online.
In researching the problem I discovered the following posts:
http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
It seems as though others are having the same issue. How can I mirror more
databases on one server? This particular server has 120 databases and I
would like to get that number to 200 before we even start thinking about a
new server.
--
KeithMicrosoft recommends you limit the number of databases you are mirroring to
10 or so.
Based on the resource (memory and threads) utilization by database
mirroring, it is a best practices recommendation to mirror not more than 10
databases in one instance. The recommended limit of 10 is only approximate
and not an exact number. The limit can vary depending upon your application
and workload.
From -
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Keith_MSDN" <Keith_MSDN@.newsgroup.nospam> wrote in message
news:71B7CAFA-EAC2-4A1E-A781-0C8D479218AC@.microsoft.com...
> As the subject suggests, I am unable to mirror my 59th database.
> (2) database servers
> HP ML370 G4
> Dual Xeon 3.4Ghz, 8GB RAM
> Windows 2003 R2 Enterprise
> SQL Server 2005 Standard - patched to 2153
> (1) watch server
> HP DL 360 G4
> Xeon 3Ghz, 2GB RAM
> Win2k3 Standard
> SQL Server Express - patched to 2153
> I was able to restore the database and the transaction log to the desired
> mirror. Then I indicated what the partner server should be (via T-SQL).
> Then I switched to my SSMS query window that was connected to the primary
> server and executed the commands to set the partner, mirror, and alter the
> partner timeout.
> The database was mirrored, but then I noticed a sign of trouble within the
> Database Mirroring Monitor. One of the databases that was already being
> mirrored switched status to "(Principal, Disconnected)"
> I executed the following query to check the status:
> select mirroring_state_desc, count(*)
> From sys.database_mirroring
> group by mirroring_state_desc
> Here are the results:
> mirroring_state_desc
> -- --
> NULL 60
> DISCONNECTED 1
> SYNCHRONIZED 58
>
> I wanted to make sure that the database mirroring monitoring job (called
> by
> SQL Server Agent) was not part of the problem, so I stopped monitoring by
> calling this stored procedure: sp_dbmmonitordropmonitoring
> and I waited... No change in status.
>
> I decided to stop mirroring on the newly added database. I used the
> following command to stop mirroring:
> ALTER DATABASE foo SET PARTNER OFF
> and the other database (the one that had a status of Principal,
> Disconnected
> synchronized and came back online.
>
> In researching the problem I discovered the following posts:
> http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
> It seems as though others are having the same issue. How can I mirror
> more
> databases on one server? This particular server has 120 databases and I
> would like to get that number to 200 before we even start thinking about a
> new server.
> --
> Keith|||Hello Keith,
I understand that when you tried to configure more than 58 databases to
mirroring, it failed with disconnected status. If I'm off-base, please
let's know.
Based on my research, this issue seems to be related to virutal memory
allocation issue. Database mirroring sessions consume much virutal memroy
per session. You may want to check if you could find "Failed Virtual
Allocate Bytes" in SQL error log. If so, you may want to increase
memtoleave area by setting -g512 as start parameter to see if this could
workarond the issue. Please see the following article for details on
memeorytoleave area.
http://msdn2.microsoft.com/en-us/library/ms190737.aspx
You may also want to consider 64-bit as well, and This would only be an
issue on x86 due to memory limitation of X86 platform.
If this workaround does not meet your requirement, or you have any
concerns, please get SQL Error log, and send it to me at
petery@.microsoft.com, we will look into this problem further. We look
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Peter. Yes, I want to mirror more than 58 databases on one server.
Upgrading to 64 Bit version of SQL (and Windows) isn't an option at this
time.
I looked within the SQL Server Log on the primary, mirror, and witness and I
did NOT find the Failed Virtual Allocate Bytes error. I even used
AgentRansack to search all the log file in the directory. I could not find
any hits for failed virtual (or virtual).
When searching the log files on the mirror I noticed that There is one SQL
Dump in the directory. Let me know if you want me to email the information.
The .mdmp file is 10MB.
I will try increasing memtoleave via the -g startup param. I assume that I
will need to modify this value on the primary and the mirror, is that
correct? This solution will require downtime, so I need to get the ball
rolling on scheduling a brief outage.
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> I understand that when you tried to configure more than 58 databases to
> mirroring, it failed with disconnected status. If I'm off-base, please
> let's know.
> Based on my research, this issue seems to be related to virutal memory
> allocation issue. Database mirroring sessions consume much virutal memroy
> per session. You may want to check if you could find "Failed Virtual
> Allocate Bytes" in SQL error log. If so, you may want to increase
> memtoleave area by setting -g512 as start parameter to see if this could
> workarond the issue. Please see the following article for details on
> memeorytoleave area.
> http://msdn2.microsoft.com/en-us/library/ms190737.aspx
> You may also want to consider 64-bit as well, and This would only be an
> issue on x86 due to memory limitation of X86 platform.
> If this workaround does not meet your requirement, or you have any
> concerns, please get SQL Error log, and send it to me at
> petery@.microsoft.com, we will look into this problem further. We look
> forward to your reply. Thank you!
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Keith,
Yes. You shall use it on both primary and the mirror. As for dump file,
will you send me an email with the latest sql error log file at
petery@.microsoft.com, and I may provide a ftp workspace via your email
address so that you could upload the dump file. I will check if the issue
is related to database mirroring,
Since dump analysis has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If we could solve the issue efficently in
newsgroup, I recommend that you open a Support incident with Microsoft
Product Support Services so that a dedicated Support Professional can
assist with this case. If you need any help in this regard, please let me
know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you have any update or questions, please feel free to let's know.
Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Peter, I modified the startup parameters on all three servers - primary,
mirror, and witness as you recommended (adding -g512 - separated from the
other params by a semicolon). I am still unable to add any more databases to
the mirroring setup on these boxes.
I just tried configuring a small database (13MB). When I issued the ALTER
DATABASE command on the mirror to specify the partner the command just ran
and ran and ran and ran. In fact, it is still running as I type this
message. It has been running for 38 minutes and counting.
I just tried calling Microsoft Support because I want to open a support case
for the problem that we are experiencing with mirroring. The response that I
received from the Microsoft employee who took my information was "We are only
offering support on database mirroring technologies to TAP customers."
I find this statement unbelievable. Database mirroring should be fully
supported by Microsoft now that SQL Server 2005 SP1 is out and the feature is
fully enabled without trace flags. I asked the support person (the one who
told me about the support for database mirroring) for their name - just in
case you need to do any followup with them.
Do you mind if I contact you at your Microsoft email address that you
provided so that we can get the ball rolling on finding a fix for this issue?
Thanks
--
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> Yes. You shall use it on both primary and the mirror. As for dump file,
> will you send me an email with the latest sql error log file at
> petery@.microsoft.com, and I may provide a ftp workspace via your email
> address so that you could upload the dump file. I will check if the issue
> is related to database mirroring,
> Since dump analysis has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. If we could solve the issue efficently in
> newsgroup, I recommend that you open a Support incident with Microsoft
> Product Support Services so that a dedicated Support Professional can
> assist with this case. If you need any help in this regard, please let me
> know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> If you have any update or questions, please feel free to let's know.
> Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hello Keith,
Thank you for your feedback. Please contact me at petery@.microsoft.com so I
could follow up the issue with you and I also think this shall be supported
by MS PSS. I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
(2) database servers
HP ML370 G4
Dual Xeon 3.4Ghz, 8GB RAM
Windows 2003 R2 Enterprise
SQL Server 2005 Standard - patched to 2153
(1) watch server
HP DL 360 G4
Xeon 3Ghz, 2GB RAM
Win2k3 Standard
SQL Server Express - patched to 2153
I was able to restore the database and the transaction log to the desired
mirror. Then I indicated what the partner server should be (via T-SQL).
Then I switched to my SSMS query window that was connected to the primary
server and executed the commands to set the partner, mirror, and alter the
partner timeout.
The database was mirrored, but then I noticed a sign of trouble within the
Database Mirroring Monitor. One of the databases that was already being
mirrored switched status to "(Principal, Disconnected)"
I executed the following query to check the status:
select mirroring_state_desc, count(*)
From sys.database_mirroring
group by mirroring_state_desc
Here are the results:
mirroring_state_desc
-- --
NULL 60
DISCONNECTED 1
SYNCHRONIZED 58
I wanted to make sure that the database mirroring monitoring job (called by
SQL Server Agent) was not part of the problem, so I stopped monitoring by
calling this stored procedure: sp_dbmmonitordropmonitoring
and I waited... No change in status.
I decided to stop mirroring on the newly added database. I used the
following command to stop mirroring:
ALTER DATABASE foo SET PARTNER OFF
and the other database (the one that had a status of Principal, Disconnected
synchronized and came back online.
In researching the problem I discovered the following posts:
http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
It seems as though others are having the same issue. How can I mirror more
databases on one server? This particular server has 120 databases and I
would like to get that number to 200 before we even start thinking about a
new server.
--
KeithMicrosoft recommends you limit the number of databases you are mirroring to
10 or so.
Based on the resource (memory and threads) utilization by database
mirroring, it is a best practices recommendation to mirror not more than 10
databases in one instance. The recommended limit of 10 is only approximate
and not an exact number. The limit can vary depending upon your application
and workload.
From -
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Keith_MSDN" <Keith_MSDN@.newsgroup.nospam> wrote in message
news:71B7CAFA-EAC2-4A1E-A781-0C8D479218AC@.microsoft.com...
> As the subject suggests, I am unable to mirror my 59th database.
> (2) database servers
> HP ML370 G4
> Dual Xeon 3.4Ghz, 8GB RAM
> Windows 2003 R2 Enterprise
> SQL Server 2005 Standard - patched to 2153
> (1) watch server
> HP DL 360 G4
> Xeon 3Ghz, 2GB RAM
> Win2k3 Standard
> SQL Server Express - patched to 2153
> I was able to restore the database and the transaction log to the desired
> mirror. Then I indicated what the partner server should be (via T-SQL).
> Then I switched to my SSMS query window that was connected to the primary
> server and executed the commands to set the partner, mirror, and alter the
> partner timeout.
> The database was mirrored, but then I noticed a sign of trouble within the
> Database Mirroring Monitor. One of the databases that was already being
> mirrored switched status to "(Principal, Disconnected)"
> I executed the following query to check the status:
> select mirroring_state_desc, count(*)
> From sys.database_mirroring
> group by mirroring_state_desc
> Here are the results:
> mirroring_state_desc
> -- --
> NULL 60
> DISCONNECTED 1
> SYNCHRONIZED 58
>
> I wanted to make sure that the database mirroring monitoring job (called
> by
> SQL Server Agent) was not part of the problem, so I stopped monitoring by
> calling this stored procedure: sp_dbmmonitordropmonitoring
> and I waited... No change in status.
>
> I decided to stop mirroring on the newly added database. I used the
> following command to stop mirroring:
> ALTER DATABASE foo SET PARTNER OFF
> and the other database (the one that had a status of Principal,
> Disconnected
> synchronized and came back online.
>
> In researching the problem I discovered the following posts:
> http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
> It seems as though others are having the same issue. How can I mirror
> more
> databases on one server? This particular server has 120 databases and I
> would like to get that number to 200 before we even start thinking about a
> new server.
> --
> Keith|||Hello Keith,
I understand that when you tried to configure more than 58 databases to
mirroring, it failed with disconnected status. If I'm off-base, please
let's know.
Based on my research, this issue seems to be related to virutal memory
allocation issue. Database mirroring sessions consume much virutal memroy
per session. You may want to check if you could find "Failed Virtual
Allocate Bytes" in SQL error log. If so, you may want to increase
memtoleave area by setting -g512 as start parameter to see if this could
workarond the issue. Please see the following article for details on
memeorytoleave area.
http://msdn2.microsoft.com/en-us/library/ms190737.aspx
You may also want to consider 64-bit as well, and This would only be an
issue on x86 due to memory limitation of X86 platform.
If this workaround does not meet your requirement, or you have any
concerns, please get SQL Error log, and send it to me at
petery@.microsoft.com, we will look into this problem further. We look
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Peter. Yes, I want to mirror more than 58 databases on one server.
Upgrading to 64 Bit version of SQL (and Windows) isn't an option at this
time.
I looked within the SQL Server Log on the primary, mirror, and witness and I
did NOT find the Failed Virtual Allocate Bytes error. I even used
AgentRansack to search all the log file in the directory. I could not find
any hits for failed virtual (or virtual).
When searching the log files on the mirror I noticed that There is one SQL
Dump in the directory. Let me know if you want me to email the information.
The .mdmp file is 10MB.
I will try increasing memtoleave via the -g startup param. I assume that I
will need to modify this value on the primary and the mirror, is that
correct? This solution will require downtime, so I need to get the ball
rolling on scheduling a brief outage.
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> I understand that when you tried to configure more than 58 databases to
> mirroring, it failed with disconnected status. If I'm off-base, please
> let's know.
> Based on my research, this issue seems to be related to virutal memory
> allocation issue. Database mirroring sessions consume much virutal memroy
> per session. You may want to check if you could find "Failed Virtual
> Allocate Bytes" in SQL error log. If so, you may want to increase
> memtoleave area by setting -g512 as start parameter to see if this could
> workarond the issue. Please see the following article for details on
> memeorytoleave area.
> http://msdn2.microsoft.com/en-us/library/ms190737.aspx
> You may also want to consider 64-bit as well, and This would only be an
> issue on x86 due to memory limitation of X86 platform.
> If this workaround does not meet your requirement, or you have any
> concerns, please get SQL Error log, and send it to me at
> petery@.microsoft.com, we will look into this problem further. We look
> forward to your reply. Thank you!
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Keith,
Yes. You shall use it on both primary and the mirror. As for dump file,
will you send me an email with the latest sql error log file at
petery@.microsoft.com, and I may provide a ftp workspace via your email
address so that you could upload the dump file. I will check if the issue
is related to database mirroring,
Since dump analysis has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If we could solve the issue efficently in
newsgroup, I recommend that you open a Support incident with Microsoft
Product Support Services so that a dedicated Support Professional can
assist with this case. If you need any help in this regard, please let me
know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you have any update or questions, please feel free to let's know.
Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Peter, I modified the startup parameters on all three servers - primary,
mirror, and witness as you recommended (adding -g512 - separated from the
other params by a semicolon). I am still unable to add any more databases to
the mirroring setup on these boxes.
I just tried configuring a small database (13MB). When I issued the ALTER
DATABASE command on the mirror to specify the partner the command just ran
and ran and ran and ran. In fact, it is still running as I type this
message. It has been running for 38 minutes and counting.
I just tried calling Microsoft Support because I want to open a support case
for the problem that we are experiencing with mirroring. The response that I
received from the Microsoft employee who took my information was "We are only
offering support on database mirroring technologies to TAP customers."
I find this statement unbelievable. Database mirroring should be fully
supported by Microsoft now that SQL Server 2005 SP1 is out and the feature is
fully enabled without trace flags. I asked the support person (the one who
told me about the support for database mirroring) for their name - just in
case you need to do any followup with them.
Do you mind if I contact you at your Microsoft email address that you
provided so that we can get the ball rolling on finding a fix for this issue?
Thanks
--
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> Yes. You shall use it on both primary and the mirror. As for dump file,
> will you send me an email with the latest sql error log file at
> petery@.microsoft.com, and I may provide a ftp workspace via your email
> address so that you could upload the dump file. I will check if the issue
> is related to database mirroring,
> Since dump analysis has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. If we could solve the issue efficently in
> newsgroup, I recommend that you open a Support incident with Microsoft
> Product Support Services so that a dedicated Support Professional can
> assist with this case. If you need any help in this regard, please let me
> know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> If you have any update or questions, please feel free to let's know.
> Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hello Keith,
Thank you for your feedback. Please contact me at petery@.microsoft.com so I
could follow up the issue with you and I also think this shall be supported
by MS PSS. I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
Database Mirroring - setting up the witness
SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
Corporate stuff runs in the parent domain (including witness)
Hosted (client) servers run in the child domain(s).
I set up mirroring between two SQL2k5 boxes in our child domain. SQL Server
runs under a network account in the child domain. We run our corparate SQL
Servers under a different account in our parent domain.
I wanted to have the witness box (located in our corporate domain) monitor
the primary and mirrors in the various child domains. I am running into the
following error message: "The ALTER DATABASE command could not be sent to
the remote server instance 'TCP://Witness-Server.TheParentDomain.com:5022'.
The database mirroring configuration was not changed. Verify that the server
is connected, and try again."
I verified that the appropriate endpoints exist within the witness via the
following query: SELECT name, role_desc, state_desc FROM
sys.database_mirroring_endpoints
What am I doing wrong?
Keith KratochvilThe service accounts are most likely different between the two domains. You
need to add the service account of the witness to the SQL Server hosting the
principal and mirror and vice versa. You then have to grant CONNECT TO
permissions to the account for the Database Mirroring endpoint.
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23T73eWR0GHA.2036@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
> Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
> Corporate stuff runs in the parent domain (including witness)
> Hosted (client) servers run in the child domain(s).
>
> I set up mirroring between two SQL2k5 boxes in our child domain. SQL
> Server runs under a network account in the child domain. We run our
> corparate SQL Servers under a different account in our parent domain.
> I wanted to have the witness box (located in our corporate domain) monitor
> the primary and mirrors in the various child domains. I am running into
> the following error message: "The ALTER DATABASE command could not be sent
> to the remote server instance
> 'TCP://Witness-Server.TheParentDomain.com:5022'. The database mirroring
> configuration was not changed. Verify that the server is connected, and
> try again."
> I verified that the appropriate endpoints exist within the witness via the
> following query: SELECT name, role_desc, state_desc FROM
> sys.database_mirroring_endpoints
> What am I doing wrong?
> --
> Keith Kratochvil
>
>
Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
Corporate stuff runs in the parent domain (including witness)
Hosted (client) servers run in the child domain(s).
I set up mirroring between two SQL2k5 boxes in our child domain. SQL Server
runs under a network account in the child domain. We run our corparate SQL
Servers under a different account in our parent domain.
I wanted to have the witness box (located in our corporate domain) monitor
the primary and mirrors in the various child domains. I am running into the
following error message: "The ALTER DATABASE command could not be sent to
the remote server instance 'TCP://Witness-Server.TheParentDomain.com:5022'.
The database mirroring configuration was not changed. Verify that the server
is connected, and try again."
I verified that the appropriate endpoints exist within the witness via the
following query: SELECT name, role_desc, state_desc FROM
sys.database_mirroring_endpoints
What am I doing wrong?
Keith KratochvilThe service accounts are most likely different between the two domains. You
need to add the service account of the witness to the SQL Server hosting the
principal and mirror and vice versa. You then have to grant CONNECT TO
permissions to the account for the Database Mirroring endpoint.
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23T73eWR0GHA.2036@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
> Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
> Corporate stuff runs in the parent domain (including witness)
> Hosted (client) servers run in the child domain(s).
>
> I set up mirroring between two SQL2k5 boxes in our child domain. SQL
> Server runs under a network account in the child domain. We run our
> corparate SQL Servers under a different account in our parent domain.
> I wanted to have the witness box (located in our corporate domain) monitor
> the primary and mirrors in the various child domains. I am running into
> the following error message: "The ALTER DATABASE command could not be sent
> to the remote server instance
> 'TCP://Witness-Server.TheParentDomain.com:5022'. The database mirroring
> configuration was not changed. Verify that the server is connected, and
> try again."
> I verified that the appropriate endpoints exist within the witness via the
> following query: SELECT name, role_desc, state_desc FROM
> sys.database_mirroring_endpoints
> What am I doing wrong?
> --
> Keith Kratochvil
>
>
Database Mirroring - setting up the witness
SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
Corporate stuff runs in the parent domain (including witness)
Hosted (client) servers run in the child domain(s).
I set up mirroring between two SQL2k5 boxes in our child domain. SQL Server
runs under a network account in the child domain. We run our corparate SQL
Servers under a different account in our parent domain.
I wanted to have the witness box (located in our corporate domain) monitor
the primary and mirrors in the various child domains. I am running into the
following error message: "The ALTER DATABASE command could not be sent to
the remote server instance 'TCP://Witness-Server.TheParentDomain.com:5022'.
The database mirroring configuration was not changed. Verify that the server
is connected, and try again."
I verified that the appropriate endpoints exist within the witness via the
following query: SELECT name, role_desc, state_desc FROM
sys.database_mirroring_endpoints
What am I doing wrong?
--
Keith KratochvilThe service accounts are most likely different between the two domains. You
need to add the service account of the witness to the SQL Server hosting the
principal and mirror and vice versa. You then have to grant CONNECT TO
permissions to the account for the Database Mirroring endpoint.
--
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23T73eWR0GHA.2036@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
> Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
> Corporate stuff runs in the parent domain (including witness)
> Hosted (client) servers run in the child domain(s).
>
> I set up mirroring between two SQL2k5 boxes in our child domain. SQL
> Server runs under a network account in the child domain. We run our
> corparate SQL Servers under a different account in our parent domain.
> I wanted to have the witness box (located in our corporate domain) monitor
> the primary and mirrors in the various child domains. I am running into
> the following error message: "The ALTER DATABASE command could not be sent
> to the remote server instance
> 'TCP://Witness-Server.TheParentDomain.com:5022'. The database mirroring
> configuration was not changed. Verify that the server is connected, and
> try again."
> I verified that the appropriate endpoints exist within the witness via the
> following query: SELECT name, role_desc, state_desc FROM
> sys.database_mirroring_endpoints
> What am I doing wrong?
> --
> Keith Kratochvil
>
>sql
Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
Corporate stuff runs in the parent domain (including witness)
Hosted (client) servers run in the child domain(s).
I set up mirroring between two SQL2k5 boxes in our child domain. SQL Server
runs under a network account in the child domain. We run our corparate SQL
Servers under a different account in our parent domain.
I wanted to have the witness box (located in our corporate domain) monitor
the primary and mirrors in the various child domains. I am running into the
following error message: "The ALTER DATABASE command could not be sent to
the remote server instance 'TCP://Witness-Server.TheParentDomain.com:5022'.
The database mirroring configuration was not changed. Verify that the server
is connected, and try again."
I verified that the appropriate endpoints exist within the witness via the
following query: SELECT name, role_desc, state_desc FROM
sys.database_mirroring_endpoints
What am I doing wrong?
--
Keith KratochvilThe service accounts are most likely different between the two domains. You
need to add the service account of the witness to the SQL Server hosting the
principal and mirror and vice versa. You then have to grant CONNECT TO
permissions to the account for the Database Mirroring endpoint.
--
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23T73eWR0GHA.2036@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
> Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
> Corporate stuff runs in the parent domain (including witness)
> Hosted (client) servers run in the child domain(s).
>
> I set up mirroring between two SQL2k5 boxes in our child domain. SQL
> Server runs under a network account in the child domain. We run our
> corparate SQL Servers under a different account in our parent domain.
> I wanted to have the witness box (located in our corporate domain) monitor
> the primary and mirrors in the various child domains. I am running into
> the following error message: "The ALTER DATABASE command could not be sent
> to the remote server instance
> 'TCP://Witness-Server.TheParentDomain.com:5022'. The database mirroring
> configuration was not changed. Verify that the server is connected, and
> try again."
> I verified that the appropriate endpoints exist within the witness via the
> following query: SELECT name, role_desc, state_desc FROM
> sys.database_mirroring_endpoints
> What am I doing wrong?
> --
> Keith Kratochvil
>
>sql
Database Mirroring
Hello,
I'm having some troubles to set up database mirroring on Itanium II based servers.
I have 3 servers registered in the same domain (windows authentication)
I have created manually the endpoint and there are all 3 started (port 5022)
When I do the alter database set partner on the mirror site it works.
When I do it on the principal instance, I have the following error: Msg 1418 the server instance "TCP:itanium8.sql.net:5022" is not running or does not exist.
If I telnet to the port 5022 I have a reply.
SAme error if I try to set up the mirroring using the GUI (wizard). I am using CTP June.
Any ideas?
Thanks
Jerome
- SQLServer is started as a service user a domain account (SQL\administrator)
- all my endpoint are started. the role is set to ALL
- I use the fully qualified name for the settings
Jerome|||Jerome,
OK, here we go:
1. Testers have confirmed that they have setup mirroring on Itanium II servers.
2. the fact that the mirror suceeds is normal. all that command does is put the server in a "wait to be contacted" mode.
3. this is different from what Pamb sees in the other thread. there, mirroring was able to log in, but the server name information didn't correctly compare. this is different from what you have. in your case, the server wasn't allowed to log in at all.
so, can you telnet from each server to the other server? you imply that it works one way, but does it work both ways? you should be able to type 16 characters and the connection should close. that tells you that you are able to connect to the port.
your error message implies that you didn't specify the '//' on the input string. it is important that you include the '//' on the server name setup.
what is in the errorlog?
check security again.
thanks,
mark|||Mark,
Ok .. it seems first of all that there is no way to do the setup using the GUI/Wizard. I always get the error described above even if the endpoints are already defined and started. I have discover that for strange reason if I create endpoint using three differents port (I have three independant server so according to the doc I should be able to use the same port) it works but only using command line:
These are the steps I have to go through to have the mirror configured:
1- backup on the principal
2- restore on the mirror with norecovery
3 - create endpoint
principal: state= started port=5022 role=partner
mirror: state=started port=5023 role=partner
witness: state=started port=5024 role=witness
4 - setup the mirror
mirror site: alter db set partner='TCP://itanium8.sql.net:5022'
principal site:
alter db set partner='TCP://itanium9.sql.net:5023'
alter db set partner='TCP://itanium10.sql.net:5024'
But again no way to set it up with the wizard....
Thanks
Jerome
|||so, you should be able to setup and manage mirroring through the GUI/UI. i watch 800 attendees do it today at a conference.
you do have to backup/restore through a different channel.
you have to setup certificates through t-sql.
glad you got it to work.
mark|||Hello Mark,
1 -You do have to backup/restore through a different channel.
What do you mean by that?
2- you have to setup certificates through t-sql.
Event if all the three instances are registered in the same domain? The wizard does not do that for you? In the BOL: "For communication accross non-trusted domain you must use certificate ....." ... in the BOL "If you are setting up database mirroring on a domain, and all SQL Server instances use the same service login and password, you do not need to create logins on the server"
I just want to understand....
Thanks
Jerome
|||1. "by a different channel" means that you have to have backed up and restored the database before you begin the wizard.
2. sorry, what i meant is that if you plan on using certificates, then you must use t-sql. using the domain account in the domain is probably the best way. but if you want to run as a non-domain account or accross non-trusted domains, then you MUST use certificates.
mark
Monday, March 19, 2012
Database marked Suspect after normabl Windows Shutdown
Hey Guys,
Looking to see if anyone has experienced this and if there is something
we are doing wrong. We have a weekly process to reboot our windows
servers (we have 100's). The process is a normal windows shutdown.
Yet weekly one of our servers will come back up and have a 'Torn Page'
error, then get marked suspect. In investigating this I notice that
the when the process works, there is a message in the SQL Server error
log that says 'SQL Server is terminating due to Server shutdown'.
Yet in the cases where it ends up suspect the message is not in the
log. So to me it looks like the SQL Server is getting shutdown hard,
but why?
I end up just restoring the 'Suspect' database, but I'm wondering why
the process sporatically fails on some servers. Is there a setting I'm
missing?
Some specifics about the environment is that these are all Windows 2003
servers running SQL Server 2000 SP3 (it also occurs on Desktop
Edition).
Thanks in advance for any assistance.
Hi
It could be that SQL Server is not responding in time to the request to
terminate.
Could you do a NET STOP on the SQL Server Services before shutting down.
Why do you need to shut down each week?
John
"paul.esposito@.comcast.net" wrote:
> Hey Guys,
> Looking to see if anyone has experienced this and if there is something
> we are doing wrong. We have a weekly process to reboot our windows
> servers (we have 100's). The process is a normal windows shutdown.
> Yet weekly one of our servers will come back up and have a 'Torn Page'
> error, then get marked suspect. In investigating this I notice that
> the when the process works, there is a message in the SQL Server error
> log that says 'SQL Server is terminating due to Server shutdown'.
> Yet in the cases where it ends up suspect the message is not in the
> log. So to me it looks like the SQL Server is getting shutdown hard,
> but why?
> I end up just restoring the 'Suspect' database, but I'm wondering why
> the process sporatically fails on some servers. Is there a setting I'm
> missing?
> Some specifics about the environment is that these are all Windows 2003
> servers running SQL Server 2000 SP3 (it also occurs on Desktop
> Edition).
> Thanks in advance for any assistance.
>
|||Well, corporate standard is one reason and other systems reside on the
server and need to be rebooted.
I'm going to incorporate a netstop into the procedures. I was just
wondering if this was a common problem.
Regardless of reason, whether I reboot the server once a week or once
every year, if the database corrupts on that shutdown there seems to be
an issue. I say this because I was told that Microsoft says W2K3 and
SQL Server are tuned not to need a reboot and that they should be left
running. Well some of our servers can't be left running all the time
Hurricanes, power outages ... Just seems like a bug to me.
Thanks for the input though. I won't fight it, I'll just do a net
stop.
John Bell wrote:[vbcol=seagreen]
> Hi
> It could be that SQL Server is not responding in time to the request to
> terminate.
> Could you do a NET STOP on the SQL Server Services before shutting down.
> Why do you need to shut down each week?
> John
> "paul.esposito@.comcast.net" wrote:
|||Tibor,
Thanks for the response. But according to the user and the event logs,
a normal shutdown was requested. i.e. The user requested Windows to
shutdown. This should have sent a message to all the services to stop.
Yet I don't see that request in the SQL Server log. But the event log
does show that a normal shutdown was requested of the OS.
Thanks again.
Tibor Karaszi wrote:[vbcol=seagreen]
> The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> a torn page (as long as the battery backup does its job).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <paul.esposito@.comcast.net> wrote in message
> news:1164056550.793217.94860@.h54g2000cwb.googlegro ups.com...
|||What sort of hardware are you using? How are your disks configured? Are
there any events or issues with your I/O controllers? How is your I/O
solution utilizing it's write cache?
I agree that you should be able to restart whenver you want. I have
several SQL installations in my environment and have never had an issue
with a normal shut down routine. Can you do a manual reboot of the
server watching it (by manual I mean not automated but going through
start/shutdown) and see what happens?
Just a point, while you should be able to reboot whenver you want
(gracefully and even safely under some not so graceful situations
should not be a problem and has not in my experience with my servers),
it is generally not always a best practice to reboot your database
servers.
For one thing there is no need to if you have a secure server and have
only SQL Server running on it. In most of the configurations out there
with only SQL running, you won't see a memory leak or any other issue
requiring reboot. Also SQL works hard to build it's caches with data
and compiled query execution plans that are optimized for your data and
workload. Everytime you reboot you clear this cache meaning that after
restart the server needs to first optimize and save all the plans again
as well as add the frequently accessed data into the data cache. Not a
huge deal but just some items to think about.
Please give more details regarding my above questions and we will see
what we can offer.
-Mike Walsh
SQL DBA
paul.esposito@.comcast.net wrote:[vbcol=seagreen]
> Tibor,
> Thanks for the response. But according to the user and the event logs,
> a normal shutdown was requested. i.e. The user requested Windows to
> shutdown. This should have sent a message to all the services to stop.
> Yet I don't see that request in the SQL Server log. But the event log
> does show that a normal shutdown was requested of the OS.
> Thanks again.
>
> Tibor Karaszi wrote:
|||Unfortunately, a Server Shutdown request does exactly this: a request for
NET STOP on every running service. The problem is that there exists a time
out value. When SQL Server receives this message, it begins its shutdown
phase, which issues stops all new connections and issues a hard CHECKPOINT
of all databases.
Typically during the maintenance windows (especially the database REORGs),
this stop request could take quite a bit of time (even more on the start up
and recovery process).
Nearly the only way you are going to get a torn page from this (as opposed
to a lot of recovery rollback operations) is that the disks were powered
down before the cache committed.
1. Plan your database maintenance around these weekly system restarts.
2. Disable controller and Windows disk caching unless you can guarantee
controller power protection.
I would also agree with most everyone else that a DBMS should be on a
dedicated system, and, which case, should be very little justification for
weekly restarts.
Now, in our Data Center, we have monthly OS updates that are deployed. For
this, we (the DBA team) have demanded subsequent system restarts (mainly
because we believe patches leave PendingFileRename operations), but that is
only on a monthly basis, and we shut down database service before the server
team is allowed to begin.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164073896.590746.191330@.h48g2000cwc.googlegr oups.com...[vbcol=seagreen]
> What sort of hardware are you using? How are your disks configured? Are
> there any events or issues with your I/O controllers? How is your I/O
> solution utilizing it's write cache?
> I agree that you should be able to restart whenver you want. I have
> several SQL installations in my environment and have never had an issue
> with a normal shut down routine. Can you do a manual reboot of the
> server watching it (by manual I mean not automated but going through
> start/shutdown) and see what happens?
> Just a point, while you should be able to reboot whenver you want
> (gracefully and even safely under some not so graceful situations
> should not be a problem and has not in my experience with my servers),
> it is generally not always a best practice to reboot your database
> servers.
> For one thing there is no need to if you have a secure server and have
> only SQL Server running on it. In most of the configurations out there
> with only SQL running, you won't see a memory leak or any other issue
> requiring reboot. Also SQL works hard to build it's caches with data
> and compiled query execution plans that are optimized for your data and
> workload. Everytime you reboot you clear this cache meaning that after
> restart the server needs to first optimize and save all the plans again
> as well as add the frequently accessed data into the data cache. Not a
> huge deal but just some items to think about.
> Please give more details regarding my above questions and we will see
> what we can offer.
> -Mike Walsh
> SQL DBA
>
> paul.esposito@.comcast.net wrote:
typically uses a sector as the[vbcol=seagreen]
typically 16 sectors. A power[vbcol=seagreen]
partially written page (a torn[vbcol=seagreen]
backup* will minimize the risk of[vbcol=seagreen]
the[vbcol=seagreen]
once[vbcol=seagreen]
be[vbcol=seagreen]
and[vbcol=seagreen]
left[vbcol=seagreen]
time[vbcol=seagreen]
request to[vbcol=seagreen]
down.[vbcol=seagreen]
something[vbcol=seagreen]
windows[vbcol=seagreen]
shutdown.[vbcol=seagreen]
Page'[vbcol=seagreen]
that[vbcol=seagreen]
error[vbcol=seagreen]
the[vbcol=seagreen]
hard,[vbcol=seagreen]
why[vbcol=seagreen]
setting I'm[vbcol=seagreen]
Windows 2003
>
|||Certainly a good place to examine. But, more often than not, especially if
this is in the evening on a weekend/Friday evening, the Index Rebuilds are
racking up large transactions. If the database(s) is(are) of any
appreciable size, then it could "hang" for awhile before letting go.
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwmyoYDHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Perhaps this SQL Server instance has been configured with a high value for
sp_configure, recovery
> interval? Less frequent checkpoint should mean more work for each
checkpoint, including the one
> submitted for a shutdown...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
for[vbcol=seagreen]
time[vbcol=seagreen]
shutdown[vbcol=seagreen]
CHECKPOINT[vbcol=seagreen]
REORGs),[vbcol=seagreen]
up[vbcol=seagreen]
opposed[vbcol=seagreen]
for[vbcol=seagreen]
For[vbcol=seagreen]
is[vbcol=seagreen]
server[vbcol=seagreen]
logs,[vbcol=seagreen]
stop.[vbcol=seagreen]
log[vbcol=seagreen]
is[vbcol=seagreen]
on[vbcol=seagreen]
just[vbcol=seagreen]
to[vbcol=seagreen]
net[vbcol=seagreen]
shutting[vbcol=seagreen]
'Torn[vbcol=seagreen]
notice[vbcol=seagreen]
Server[vbcol=seagreen]
shutdown'.[vbcol=seagreen]
in[vbcol=seagreen]
shutdown[vbcol=seagreen]
wondering
>
Looking to see if anyone has experienced this and if there is something
we are doing wrong. We have a weekly process to reboot our windows
servers (we have 100's). The process is a normal windows shutdown.
Yet weekly one of our servers will come back up and have a 'Torn Page'
error, then get marked suspect. In investigating this I notice that
the when the process works, there is a message in the SQL Server error
log that says 'SQL Server is terminating due to Server shutdown'.
Yet in the cases where it ends up suspect the message is not in the
log. So to me it looks like the SQL Server is getting shutdown hard,
but why?
I end up just restoring the 'Suspect' database, but I'm wondering why
the process sporatically fails on some servers. Is there a setting I'm
missing?
Some specifics about the environment is that these are all Windows 2003
servers running SQL Server 2000 SP3 (it also occurs on Desktop
Edition).
Thanks in advance for any assistance.
Hi
It could be that SQL Server is not responding in time to the request to
terminate.
Could you do a NET STOP on the SQL Server Services before shutting down.
Why do you need to shut down each week?
John
"paul.esposito@.comcast.net" wrote:
> Hey Guys,
> Looking to see if anyone has experienced this and if there is something
> we are doing wrong. We have a weekly process to reboot our windows
> servers (we have 100's). The process is a normal windows shutdown.
> Yet weekly one of our servers will come back up and have a 'Torn Page'
> error, then get marked suspect. In investigating this I notice that
> the when the process works, there is a message in the SQL Server error
> log that says 'SQL Server is terminating due to Server shutdown'.
> Yet in the cases where it ends up suspect the message is not in the
> log. So to me it looks like the SQL Server is getting shutdown hard,
> but why?
> I end up just restoring the 'Suspect' database, but I'm wondering why
> the process sporatically fails on some servers. Is there a setting I'm
> missing?
> Some specifics about the environment is that these are all Windows 2003
> servers running SQL Server 2000 SP3 (it also occurs on Desktop
> Edition).
> Thanks in advance for any assistance.
>
|||Well, corporate standard is one reason and other systems reside on the
server and need to be rebooted.
I'm going to incorporate a netstop into the procedures. I was just
wondering if this was a common problem.
Regardless of reason, whether I reboot the server once a week or once
every year, if the database corrupts on that shutdown there seems to be
an issue. I say this because I was told that Microsoft says W2K3 and
SQL Server are tuned not to need a reboot and that they should be left
running. Well some of our servers can't be left running all the time
Hurricanes, power outages ... Just seems like a bug to me.
Thanks for the input though. I won't fight it, I'll just do a net
stop.
John Bell wrote:[vbcol=seagreen]
> Hi
> It could be that SQL Server is not responding in time to the request to
> terminate.
> Could you do a NET STOP on the SQL Server Services before shutting down.
> Why do you need to shut down each week?
> John
> "paul.esposito@.comcast.net" wrote:
|||Tibor,
Thanks for the response. But according to the user and the event logs,
a normal shutdown was requested. i.e. The user requested Windows to
shutdown. This should have sent a message to all the services to stop.
Yet I don't see that request in the SQL Server log. But the event log
does show that a normal shutdown was requested of the OS.
Thanks again.
Tibor Karaszi wrote:[vbcol=seagreen]
> The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> a torn page (as long as the battery backup does its job).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <paul.esposito@.comcast.net> wrote in message
> news:1164056550.793217.94860@.h54g2000cwb.googlegro ups.com...
|||What sort of hardware are you using? How are your disks configured? Are
there any events or issues with your I/O controllers? How is your I/O
solution utilizing it's write cache?
I agree that you should be able to restart whenver you want. I have
several SQL installations in my environment and have never had an issue
with a normal shut down routine. Can you do a manual reboot of the
server watching it (by manual I mean not automated but going through
start/shutdown) and see what happens?
Just a point, while you should be able to reboot whenver you want
(gracefully and even safely under some not so graceful situations
should not be a problem and has not in my experience with my servers),
it is generally not always a best practice to reboot your database
servers.
For one thing there is no need to if you have a secure server and have
only SQL Server running on it. In most of the configurations out there
with only SQL running, you won't see a memory leak or any other issue
requiring reboot. Also SQL works hard to build it's caches with data
and compiled query execution plans that are optimized for your data and
workload. Everytime you reboot you clear this cache meaning that after
restart the server needs to first optimize and save all the plans again
as well as add the frequently accessed data into the data cache. Not a
huge deal but just some items to think about.
Please give more details regarding my above questions and we will see
what we can offer.
-Mike Walsh
SQL DBA
paul.esposito@.comcast.net wrote:[vbcol=seagreen]
> Tibor,
> Thanks for the response. But according to the user and the event logs,
> a normal shutdown was requested. i.e. The user requested Windows to
> shutdown. This should have sent a message to all the services to stop.
> Yet I don't see that request in the SQL Server log. But the event log
> does show that a normal shutdown was requested of the OS.
> Thanks again.
>
> Tibor Karaszi wrote:
|||Unfortunately, a Server Shutdown request does exactly this: a request for
NET STOP on every running service. The problem is that there exists a time
out value. When SQL Server receives this message, it begins its shutdown
phase, which issues stops all new connections and issues a hard CHECKPOINT
of all databases.
Typically during the maintenance windows (especially the database REORGs),
this stop request could take quite a bit of time (even more on the start up
and recovery process).
Nearly the only way you are going to get a torn page from this (as opposed
to a lot of recovery rollback operations) is that the disks were powered
down before the cache committed.
1. Plan your database maintenance around these weekly system restarts.
2. Disable controller and Windows disk caching unless you can guarantee
controller power protection.
I would also agree with most everyone else that a DBMS should be on a
dedicated system, and, which case, should be very little justification for
weekly restarts.
Now, in our Data Center, we have monthly OS updates that are deployed. For
this, we (the DBA team) have demanded subsequent system restarts (mainly
because we believe patches leave PendingFileRename operations), but that is
only on a monthly basis, and we shut down database service before the server
team is allowed to begin.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164073896.590746.191330@.h48g2000cwc.googlegr oups.com...[vbcol=seagreen]
> What sort of hardware are you using? How are your disks configured? Are
> there any events or issues with your I/O controllers? How is your I/O
> solution utilizing it's write cache?
> I agree that you should be able to restart whenver you want. I have
> several SQL installations in my environment and have never had an issue
> with a normal shut down routine. Can you do a manual reboot of the
> server watching it (by manual I mean not automated but going through
> start/shutdown) and see what happens?
> Just a point, while you should be able to reboot whenver you want
> (gracefully and even safely under some not so graceful situations
> should not be a problem and has not in my experience with my servers),
> it is generally not always a best practice to reboot your database
> servers.
> For one thing there is no need to if you have a secure server and have
> only SQL Server running on it. In most of the configurations out there
> with only SQL running, you won't see a memory leak or any other issue
> requiring reboot. Also SQL works hard to build it's caches with data
> and compiled query execution plans that are optimized for your data and
> workload. Everytime you reboot you clear this cache meaning that after
> restart the server needs to first optimize and save all the plans again
> as well as add the frequently accessed data into the data cache. Not a
> huge deal but just some items to think about.
> Please give more details regarding my above questions and we will see
> what we can offer.
> -Mike Walsh
> SQL DBA
>
> paul.esposito@.comcast.net wrote:
typically uses a sector as the[vbcol=seagreen]
typically 16 sectors. A power[vbcol=seagreen]
partially written page (a torn[vbcol=seagreen]
backup* will minimize the risk of[vbcol=seagreen]
the[vbcol=seagreen]
once[vbcol=seagreen]
be[vbcol=seagreen]
and[vbcol=seagreen]
left[vbcol=seagreen]
time[vbcol=seagreen]
request to[vbcol=seagreen]
down.[vbcol=seagreen]
something[vbcol=seagreen]
windows[vbcol=seagreen]
shutdown.[vbcol=seagreen]
Page'[vbcol=seagreen]
that[vbcol=seagreen]
error[vbcol=seagreen]
the[vbcol=seagreen]
hard,[vbcol=seagreen]
why[vbcol=seagreen]
setting I'm[vbcol=seagreen]
Windows 2003
>
|||Certainly a good place to examine. But, more often than not, especially if
this is in the evening on a weekend/Friday evening, the Index Rebuilds are
racking up large transactions. If the database(s) is(are) of any
appreciable size, then it could "hang" for awhile before letting go.
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwmyoYDHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Perhaps this SQL Server instance has been configured with a high value for
sp_configure, recovery
> interval? Less frequent checkpoint should mean more work for each
checkpoint, including the one
> submitted for a shutdown...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
for[vbcol=seagreen]
time[vbcol=seagreen]
shutdown[vbcol=seagreen]
CHECKPOINT[vbcol=seagreen]
REORGs),[vbcol=seagreen]
up[vbcol=seagreen]
opposed[vbcol=seagreen]
for[vbcol=seagreen]
For[vbcol=seagreen]
is[vbcol=seagreen]
server[vbcol=seagreen]
logs,[vbcol=seagreen]
stop.[vbcol=seagreen]
log[vbcol=seagreen]
is[vbcol=seagreen]
on[vbcol=seagreen]
just[vbcol=seagreen]
to[vbcol=seagreen]
net[vbcol=seagreen]
shutting[vbcol=seagreen]
'Torn[vbcol=seagreen]
notice[vbcol=seagreen]
Server[vbcol=seagreen]
shutdown'.[vbcol=seagreen]
in[vbcol=seagreen]
shutdown[vbcol=seagreen]
wondering
>
Database marked Suspect after normabl Windows Shutdown
Hey Guys,
Looking to see if anyone has experienced this and if there is something
we are doing wrong. We have a weekly process to reboot our windows
servers (we have 100's). The process is a normal windows shutdown.
Yet weekly one of our servers will come back up and have a 'Torn Page'
error, then get marked suspect. In investigating this I notice that
the when the process works, there is a message in the SQL Server error
log that says 'SQL Server is terminating due to Server shutdown'.
Yet in the cases where it ends up suspect the message is not in the
log. So to me it looks like the SQL Server is getting shutdown hard,
but why?
I end up just restoring the 'Suspect' database, but I'm wondering why
the process sporatically fails on some servers. Is there a setting I'm
missing?
Some specifics about the environment is that these are all Windows 2003
servers running SQL Server 2000 SP3 (it also occurs on Desktop
Edition).
Thanks in advance for any assistance.Hi
It could be that SQL Server is not responding in time to the request to
terminate.
Could you do a NET STOP on the SQL Server Services before shutting down.
Why do you need to shut down each week?
John
"paul.esposito@.comcast.net" wrote:
> Hey Guys,
> Looking to see if anyone has experienced this and if there is something
> we are doing wrong. We have a weekly process to reboot our windows
> servers (we have 100's). The process is a normal windows shutdown.
> Yet weekly one of our servers will come back up and have a 'Torn Page'
> error, then get marked suspect. In investigating this I notice that
> the when the process works, there is a message in the SQL Server error
> log that says 'SQL Server is terminating due to Server shutdown'.
> Yet in the cases where it ends up suspect the message is not in the
> log. So to me it looks like the SQL Server is getting shutdown hard,
> but why?
> I end up just restoring the 'Suspect' database, but I'm wondering why
> the process sporatically fails on some servers. Is there a setting I'm
> missing?
> Some specifics about the environment is that these are all Windows 2003
> servers running SQL Server 2000 SP3 (it also occurs on Desktop
> Edition).
> Thanks in advance for any assistance.
>|||Well, corporate standard is one reason and other systems reside on the
server and need to be rebooted.
I'm going to incorporate a netstop into the procedures. I was just
wondering if this was a common problem.
Regardless of reason, whether I reboot the server once a week or once
every year, if the database corrupts on that shutdown there seems to be
an issue. I say this because I was told that Microsoft says W2K3 and
SQL Server are tuned not to need a reboot and that they should be left
running. Well some of our servers can't be left running all the time
Hurricanes, power outages ... Just seems like a bug to me.
Thanks for the input though. I won't fight it, I'll just do a net
stop.
John Bell wrote:
> Hi
> It could be that SQL Server is not responding in time to the request to
> terminate.
> Could you do a NET STOP on the SQL Server Services before shutting down.
> Why do you need to shut down each week?
> John
> "paul.esposito@.comcast.net" wrote:
> > Hey Guys,
> >
> > Looking to see if anyone has experienced this and if there is something
> > we are doing wrong. We have a weekly process to reboot our windows
> > servers (we have 100's). The process is a normal windows shutdown.
> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> > error, then get marked suspect. In investigating this I notice that
> > the when the process works, there is a message in the SQL Server error
> > log that says 'SQL Server is terminating due to Server shutdown'.
> > Yet in the cases where it ends up suspect the message is not in the
> > log. So to me it looks like the SQL Server is getting shutdown hard,
> > but why?
> >
> > I end up just restoring the 'Suspect' database, but I'm wondering why
> > the process sporatically fails on some servers. Is there a setting I'm
> > missing?
> >
> > Some specifics about the environment is that these are all Windows 2003
> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > Edition).
> >
> > Thanks in advance for any assistance.
> >
> >|||The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
a torn page (as long as the battery backup does its job).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<paul.esposito@.comcast.net> wrote in message
news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> Well, corporate standard is one reason and other systems reside on the
> server and need to be rebooted.
> I'm going to incorporate a netstop into the procedures. I was just
> wondering if this was a common problem.
> Regardless of reason, whether I reboot the server once a week or once
> every year, if the database corrupts on that shutdown there seems to be
> an issue. I say this because I was told that Microsoft says W2K3 and
> SQL Server are tuned not to need a reboot and that they should be left
> running. Well some of our servers can't be left running all the time
> Hurricanes, power outages ... Just seems like a bug to me.
> Thanks for the input though. I won't fight it, I'll just do a net
> stop.
>
> John Bell wrote:
>> Hi
>> It could be that SQL Server is not responding in time to the request to
>> terminate.
>> Could you do a NET STOP on the SQL Server Services before shutting down.
>> Why do you need to shut down each week?
>> John
>> "paul.esposito@.comcast.net" wrote:
>> > Hey Guys,
>> >
>> > Looking to see if anyone has experienced this and if there is something
>> > we are doing wrong. We have a weekly process to reboot our windows
>> > servers (we have 100's). The process is a normal windows shutdown.
>> > Yet weekly one of our servers will come back up and have a 'Torn Page'
>> > error, then get marked suspect. In investigating this I notice that
>> > the when the process works, there is a message in the SQL Server error
>> > log that says 'SQL Server is terminating due to Server shutdown'.
>> > Yet in the cases where it ends up suspect the message is not in the
>> > log. So to me it looks like the SQL Server is getting shutdown hard,
>> > but why?
>> >
>> > I end up just restoring the 'Suspect' database, but I'm wondering why
>> > the process sporatically fails on some servers. Is there a setting I'm
>> > missing?
>> >
>> > Some specifics about the environment is that these are all Windows 2003
>> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
>> > Edition).
>> >
>> > Thanks in advance for any assistance.
>> >
>> >
>|||Tibor,
Thanks for the response. But according to the user and the event logs,
a normal shutdown was requested. i.e. The user requested Windows to
shutdown. This should have sent a message to all the services to stop.
Yet I don't see that request in the SQL Server log. But the event log
does show that a normal shutdown was requested of the OS.
Thanks again.
Tibor Karaszi wrote:
> The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> a torn page (as long as the battery backup does its job).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <paul.esposito@.comcast.net> wrote in message
> news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > Well, corporate standard is one reason and other systems reside on the
> > server and need to be rebooted.
> >
> > I'm going to incorporate a netstop into the procedures. I was just
> > wondering if this was a common problem.
> >
> > Regardless of reason, whether I reboot the server once a week or once
> > every year, if the database corrupts on that shutdown there seems to be
> > an issue. I say this because I was told that Microsoft says W2K3 and
> > SQL Server are tuned not to need a reboot and that they should be left
> > running. Well some of our servers can't be left running all the time
> > Hurricanes, power outages ... Just seems like a bug to me.
> >
> > Thanks for the input though. I won't fight it, I'll just do a net
> > stop.
> >
> >
> > John Bell wrote:
> >> Hi
> >>
> >> It could be that SQL Server is not responding in time to the request to
> >> terminate.
> >> Could you do a NET STOP on the SQL Server Services before shutting down.
> >> Why do you need to shut down each week?
> >>
> >> John
> >>
> >> "paul.esposito@.comcast.net" wrote:
> >>
> >> > Hey Guys,
> >> >
> >> > Looking to see if anyone has experienced this and if there is something
> >> > we are doing wrong. We have a weekly process to reboot our windows
> >> > servers (we have 100's). The process is a normal windows shutdown.
> >> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> >> > error, then get marked suspect. In investigating this I notice that
> >> > the when the process works, there is a message in the SQL Server error
> >> > log that says 'SQL Server is terminating due to Server shutdown'.
> >> > Yet in the cases where it ends up suspect the message is not in the
> >> > log. So to me it looks like the SQL Server is getting shutdown hard,
> >> > but why?
> >> >
> >> > I end up just restoring the 'Suspect' database, but I'm wondering why
> >> > the process sporatically fails on some servers. Is there a setting I'm
> >> > missing?
> >> >
> >> > Some specifics about the environment is that these are all Windows 2003
> >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> >> > Edition).
> >> >
> >> > Thanks in advance for any assistance.
> >> >
> >> >
> >|||What sort of hardware are you using? How are your disks configured? Are
there any events or issues with your I/O controllers? How is your I/O
solution utilizing it's write cache?
I agree that you should be able to restart whenver you want. I have
several SQL installations in my environment and have never had an issue
with a normal shut down routine. Can you do a manual reboot of the
server watching it (by manual I mean not automated but going through
start/shutdown) and see what happens?
Just a point, while you should be able to reboot whenver you want
(gracefully and even safely under some not so graceful situations
should not be a problem and has not in my experience with my servers),
it is generally not always a best practice to reboot your database
servers.
For one thing there is no need to if you have a secure server and have
only SQL Server running on it. In most of the configurations out there
with only SQL running, you won't see a memory leak or any other issue
requiring reboot. Also SQL works hard to build it's caches with data
and compiled query execution plans that are optimized for your data and
workload. Everytime you reboot you clear this cache meaning that after
restart the server needs to first optimize and save all the plans again
as well as add the frequently accessed data into the data cache. Not a
huge deal but just some items to think about.
Please give more details regarding my above questions and we will see
what we can offer.
-Mike Walsh
SQL DBA
paul.esposito@.comcast.net wrote:
> Tibor,
> Thanks for the response. But according to the user and the event logs,
> a normal shutdown was requested. i.e. The user requested Windows to
> shutdown. This should have sent a message to all the services to stop.
> Yet I don't see that request in the SQL Server log. But the event log
> does show that a normal shutdown was requested of the OS.
> Thanks again.
>
> Tibor Karaszi wrote:
> > The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> > failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> > page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> > a torn page (as long as the battery backup does its job).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <paul.esposito@.comcast.net> wrote in message
> > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > > Well, corporate standard is one reason and other systems reside on the
> > > server and need to be rebooted.
> > >
> > > I'm going to incorporate a netstop into the procedures. I was just
> > > wondering if this was a common problem.
> > >
> > > Regardless of reason, whether I reboot the server once a week or once
> > > every year, if the database corrupts on that shutdown there seems to be
> > > an issue. I say this because I was told that Microsoft says W2K3 and
> > > SQL Server are tuned not to need a reboot and that they should be left
> > > running. Well some of our servers can't be left running all the time
> > > Hurricanes, power outages ... Just seems like a bug to me.
> > >
> > > Thanks for the input though. I won't fight it, I'll just do a net
> > > stop.
> > >
> > >
> > > John Bell wrote:
> > >> Hi
> > >>
> > >> It could be that SQL Server is not responding in time to the request to
> > >> terminate.
> > >> Could you do a NET STOP on the SQL Server Services before shutting down.
> > >> Why do you need to shut down each week?
> > >>
> > >> John
> > >>
> > >> "paul.esposito@.comcast.net" wrote:
> > >>
> > >> > Hey Guys,
> > >> >
> > >> > Looking to see if anyone has experienced this and if there is something
> > >> > we are doing wrong. We have a weekly process to reboot our windows
> > >> > servers (we have 100's). The process is a normal windows shutdown.
> > >> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> > >> > error, then get marked suspect. In investigating this I notice that
> > >> > the when the process works, there is a message in the SQL Server error
> > >> > log that says 'SQL Server is terminating due to Server shutdown'.
> > >> > Yet in the cases where it ends up suspect the message is not in the
> > >> > log. So to me it looks like the SQL Server is getting shutdown hard,
> > >> > but why?
> > >> >
> > >> > I end up just restoring the 'Suspect' database, but I'm wondering why
> > >> > the process sporatically fails on some servers. Is there a setting I'm
> > >> > missing?
> > >> >
> > >> > Some specifics about the environment is that these are all Windows 2003
> > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > >> > Edition).
> > >> >
> > >> > Thanks in advance for any assistance.
> > >> >
> > >> >
> > >|||Unfortunately, a Server Shutdown request does exactly this: a request for
NET STOP on every running service. The problem is that there exists a time
out value. When SQL Server receives this message, it begins its shutdown
phase, which issues stops all new connections and issues a hard CHECKPOINT
of all databases.
Typically during the maintenance windows (especially the database REORGs),
this stop request could take quite a bit of time (even more on the start up
and recovery process).
Nearly the only way you are going to get a torn page from this (as opposed
to a lot of recovery rollback operations) is that the disks were powered
down before the cache committed.
1. Plan your database maintenance around these weekly system restarts.
2. Disable controller and Windows disk caching unless you can guarantee
controller power protection.
I would also agree with most everyone else that a DBMS should be on a
dedicated system, and, which case, should be very little justification for
weekly restarts.
Now, in our Data Center, we have monthly OS updates that are deployed. For
this, we (the DBA team) have demanded subsequent system restarts (mainly
because we believe patches leave PendingFileRename operations), but that is
only on a monthly basis, and we shut down database service before the server
team is allowed to begin.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> What sort of hardware are you using? How are your disks configured? Are
> there any events or issues with your I/O controllers? How is your I/O
> solution utilizing it's write cache?
> I agree that you should be able to restart whenver you want. I have
> several SQL installations in my environment and have never had an issue
> with a normal shut down routine. Can you do a manual reboot of the
> server watching it (by manual I mean not automated but going through
> start/shutdown) and see what happens?
> Just a point, while you should be able to reboot whenver you want
> (gracefully and even safely under some not so graceful situations
> should not be a problem and has not in my experience with my servers),
> it is generally not always a best practice to reboot your database
> servers.
> For one thing there is no need to if you have a secure server and have
> only SQL Server running on it. In most of the configurations out there
> with only SQL running, you won't see a memory leak or any other issue
> requiring reboot. Also SQL works hard to build it's caches with data
> and compiled query execution plans that are optimized for your data and
> workload. Everytime you reboot you clear this cache meaning that after
> restart the server needs to first optimize and save all the plans again
> as well as add the frequently accessed data into the data cache. Not a
> huge deal but just some items to think about.
> Please give more details regarding my above questions and we will see
> what we can offer.
> -Mike Walsh
> SQL DBA
>
> paul.esposito@.comcast.net wrote:
> > Tibor,
> >
> > Thanks for the response. But according to the user and the event logs,
> > a normal shutdown was requested. i.e. The user requested Windows to
> > shutdown. This should have sent a message to all the services to stop.
> > Yet I don't see that request in the SQL Server log. But the event log
> > does show that a normal shutdown was requested of the OS.
> >
> > Thanks again.
> >
> >
> >
> > Tibor Karaszi wrote:
> > > The problem is in the nature of hardware. A page it 8KB. The disks
typically uses a sector as the
> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is
typically 16 sectors. A power
> > > failure (or hard stop) in the middle of a page write will cause a
partially written page (a torn
> > > page). Having HW write cache *with a properly implemented battery
backup* will minimize the risk of
> > > a torn page (as long as the battery backup does its job).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > <paul.esposito@.comcast.net> wrote in message
> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > > > Well, corporate standard is one reason and other systems reside on
the
> > > > server and need to be rebooted.
> > > >
> > > > I'm going to incorporate a netstop into the procedures. I was just
> > > > wondering if this was a common problem.
> > > >
> > > > Regardless of reason, whether I reboot the server once a week or
once
> > > > every year, if the database corrupts on that shutdown there seems to
be
> > > > an issue. I say this because I was told that Microsoft says W2K3
and
> > > > SQL Server are tuned not to need a reboot and that they should be
left
> > > > running. Well some of our servers can't be left running all the
time
> > > > Hurricanes, power outages ... Just seems like a bug to me.
> > > >
> > > > Thanks for the input though. I won't fight it, I'll just do a net
> > > > stop.
> > > >
> > > >
> > > > John Bell wrote:
> > > >> Hi
> > > >>
> > > >> It could be that SQL Server is not responding in time to the
request to
> > > >> terminate.
> > > >> Could you do a NET STOP on the SQL Server Services before shutting
down.
> > > >> Why do you need to shut down each week?
> > > >>
> > > >> John
> > > >>
> > > >> "paul.esposito@.comcast.net" wrote:
> > > >>
> > > >> > Hey Guys,
> > > >> >
> > > >> > Looking to see if anyone has experienced this and if there is
something
> > > >> > we are doing wrong. We have a weekly process to reboot our
windows
> > > >> > servers (we have 100's). The process is a normal windows
shutdown.
> > > >> > Yet weekly one of our servers will come back up and have a 'Torn
Page'
> > > >> > error, then get marked suspect. In investigating this I notice
that
> > > >> > the when the process works, there is a message in the SQL Server
error
> > > >> > log that says 'SQL Server is terminating due to Server shutdown'.
> > > >> > Yet in the cases where it ends up suspect the message is not in
the
> > > >> > log. So to me it looks like the SQL Server is getting shutdown
hard,
> > > >> > but why?
> > > >> >
> > > >> > I end up just restoring the 'Suspect' database, but I'm wondering
why
> > > >> > the process sporatically fails on some servers. Is there a
setting I'm
> > > >> > missing?
> > > >> >
> > > >> > Some specifics about the environment is that these are all
Windows 2003
> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > > >> > Edition).
> > > >> >
> > > >> > Thanks in advance for any assistance.
> > > >> >
> > > >> >
> > > >
>|||Perhaps this SQL Server instance has been configured with a high value for sp_configure, recovery
interval? Less frequent checkpoint should mean more work for each checkpoint, including the one
submitted for a shutdown...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
> Unfortunately, a Server Shutdown request does exactly this: a request for
> NET STOP on every running service. The problem is that there exists a time
> out value. When SQL Server receives this message, it begins its shutdown
> phase, which issues stops all new connections and issues a hard CHECKPOINT
> of all databases.
> Typically during the maintenance windows (especially the database REORGs),
> this stop request could take quite a bit of time (even more on the start up
> and recovery process).
> Nearly the only way you are going to get a torn page from this (as opposed
> to a lot of recovery rollback operations) is that the disks were powered
> down before the cache committed.
> 1. Plan your database maintenance around these weekly system restarts.
> 2. Disable controller and Windows disk caching unless you can guarantee
> controller power protection.
> I would also agree with most everyone else that a DBMS should be on a
> dedicated system, and, which case, should be very little justification for
> weekly restarts.
> Now, in our Data Center, we have monthly OS updates that are deployed. For
> this, we (the DBA team) have demanded subsequent system restarts (mainly
> because we believe patches leave PendingFileRename operations), but that is
> only on a monthly basis, and we shut down database service before the server
> team is allowed to begin.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
>> What sort of hardware are you using? How are your disks configured? Are
>> there any events or issues with your I/O controllers? How is your I/O
>> solution utilizing it's write cache?
>> I agree that you should be able to restart whenver you want. I have
>> several SQL installations in my environment and have never had an issue
>> with a normal shut down routine. Can you do a manual reboot of the
>> server watching it (by manual I mean not automated but going through
>> start/shutdown) and see what happens?
>> Just a point, while you should be able to reboot whenver you want
>> (gracefully and even safely under some not so graceful situations
>> should not be a problem and has not in my experience with my servers),
>> it is generally not always a best practice to reboot your database
>> servers.
>> For one thing there is no need to if you have a secure server and have
>> only SQL Server running on it. In most of the configurations out there
>> with only SQL running, you won't see a memory leak or any other issue
>> requiring reboot. Also SQL works hard to build it's caches with data
>> and compiled query execution plans that are optimized for your data and
>> workload. Everytime you reboot you clear this cache meaning that after
>> restart the server needs to first optimize and save all the plans again
>> as well as add the frequently accessed data into the data cache. Not a
>> huge deal but just some items to think about.
>> Please give more details regarding my above questions and we will see
>> what we can offer.
>> -Mike Walsh
>> SQL DBA
>>
>> paul.esposito@.comcast.net wrote:
>> > Tibor,
>> >
>> > Thanks for the response. But according to the user and the event logs,
>> > a normal shutdown was requested. i.e. The user requested Windows to
>> > shutdown. This should have sent a message to all the services to stop.
>> > Yet I don't see that request in the SQL Server log. But the event log
>> > does show that a normal shutdown was requested of the OS.
>> >
>> > Thanks again.
>> >
>> >
>> >
>> > Tibor Karaszi wrote:
>> > > The problem is in the nature of hardware. A page it 8KB. The disks
> typically uses a sector as the
>> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is
> typically 16 sectors. A power
>> > > failure (or hard stop) in the middle of a page write will cause a
> partially written page (a torn
>> > > page). Having HW write cache *with a properly implemented battery
> backup* will minimize the risk of
>> > > a torn page (as long as the battery backup does its job).
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > <paul.esposito@.comcast.net> wrote in message
>> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
>> > > > Well, corporate standard is one reason and other systems reside on
> the
>> > > > server and need to be rebooted.
>> > > >
>> > > > I'm going to incorporate a netstop into the procedures. I was just
>> > > > wondering if this was a common problem.
>> > > >
>> > > > Regardless of reason, whether I reboot the server once a week or
> once
>> > > > every year, if the database corrupts on that shutdown there seems to
> be
>> > > > an issue. I say this because I was told that Microsoft says W2K3
> and
>> > > > SQL Server are tuned not to need a reboot and that they should be
> left
>> > > > running. Well some of our servers can't be left running all the
> time
>> > > > Hurricanes, power outages ... Just seems like a bug to me.
>> > > >
>> > > > Thanks for the input though. I won't fight it, I'll just do a net
>> > > > stop.
>> > > >
>> > > >
>> > > > John Bell wrote:
>> > > >> Hi
>> > > >>
>> > > >> It could be that SQL Server is not responding in time to the
> request to
>> > > >> terminate.
>> > > >> Could you do a NET STOP on the SQL Server Services before shutting
> down.
>> > > >> Why do you need to shut down each week?
>> > > >>
>> > > >> John
>> > > >>
>> > > >> "paul.esposito@.comcast.net" wrote:
>> > > >>
>> > > >> > Hey Guys,
>> > > >> >
>> > > >> > Looking to see if anyone has experienced this and if there is
> something
>> > > >> > we are doing wrong. We have a weekly process to reboot our
> windows
>> > > >> > servers (we have 100's). The process is a normal windows
> shutdown.
>> > > >> > Yet weekly one of our servers will come back up and have a 'Torn
> Page'
>> > > >> > error, then get marked suspect. In investigating this I notice
> that
>> > > >> > the when the process works, there is a message in the SQL Server
> error
>> > > >> > log that says 'SQL Server is terminating due to Server shutdown'.
>> > > >> > Yet in the cases where it ends up suspect the message is not in
> the
>> > > >> > log. So to me it looks like the SQL Server is getting shutdown
> hard,
>> > > >> > but why?
>> > > >> >
>> > > >> > I end up just restoring the 'Suspect' database, but I'm wondering
> why
>> > > >> > the process sporatically fails on some servers. Is there a
> setting I'm
>> > > >> > missing?
>> > > >> >
>> > > >> > Some specifics about the environment is that these are all
> Windows 2003
>> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
>> > > >> > Edition).
>> > > >> >
>> > > >> > Thanks in advance for any assistance.
>> > > >> >
>> > > >> >
>> > > >
>|||Certainly a good place to examine. But, more often than not, especially if
this is in the evening on a weekend/Friday evening, the Index Rebuilds are
racking up large transactions. If the database(s) is(are) of any
appreciable size, then it could "hang" for awhile before letting go.
Anthony Thomas
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwmyoYDHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Perhaps this SQL Server instance has been configured with a high value for
sp_configure, recovery
> interval? Less frequent checkpoint should mean more work for each
checkpoint, including the one
> submitted for a shutdown...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
> > Unfortunately, a Server Shutdown request does exactly this: a request
for
> > NET STOP on every running service. The problem is that there exists a
time
> > out value. When SQL Server receives this message, it begins its
shutdown
> > phase, which issues stops all new connections and issues a hard
CHECKPOINT
> > of all databases.
> >
> > Typically during the maintenance windows (especially the database
REORGs),
> > this stop request could take quite a bit of time (even more on the start
up
> > and recovery process).
> >
> > Nearly the only way you are going to get a torn page from this (as
opposed
> > to a lot of recovery rollback operations) is that the disks were powered
> > down before the cache committed.
> >
> > 1. Plan your database maintenance around these weekly system restarts.
> > 2. Disable controller and Windows disk caching unless you can guarantee
> > controller power protection.
> >
> > I would also agree with most everyone else that a DBMS should be on a
> > dedicated system, and, which case, should be very little justification
for
> > weekly restarts.
> >
> > Now, in our Data Center, we have monthly OS updates that are deployed.
For
> > this, we (the DBA team) have demanded subsequent system restarts (mainly
> > because we believe patches leave PendingFileRename operations), but that
is
> > only on a monthly basis, and we shut down database service before the
server
> > team is allowed to begin.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> > news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> >> What sort of hardware are you using? How are your disks configured? Are
> >> there any events or issues with your I/O controllers? How is your I/O
> >> solution utilizing it's write cache?
> >>
> >> I agree that you should be able to restart whenver you want. I have
> >> several SQL installations in my environment and have never had an issue
> >> with a normal shut down routine. Can you do a manual reboot of the
> >> server watching it (by manual I mean not automated but going through
> >> start/shutdown) and see what happens?
> >>
> >> Just a point, while you should be able to reboot whenver you want
> >> (gracefully and even safely under some not so graceful situations
> >> should not be a problem and has not in my experience with my servers),
> >> it is generally not always a best practice to reboot your database
> >> servers.
> >>
> >> For one thing there is no need to if you have a secure server and have
> >> only SQL Server running on it. In most of the configurations out there
> >> with only SQL running, you won't see a memory leak or any other issue
> >> requiring reboot. Also SQL works hard to build it's caches with data
> >> and compiled query execution plans that are optimized for your data and
> >> workload. Everytime you reboot you clear this cache meaning that after
> >> restart the server needs to first optimize and save all the plans again
> >> as well as add the frequently accessed data into the data cache. Not a
> >> huge deal but just some items to think about.
> >>
> >> Please give more details regarding my above questions and we will see
> >> what we can offer.
> >>
> >> -Mike Walsh
> >> SQL DBA
> >>
> >>
> >> paul.esposito@.comcast.net wrote:
> >> > Tibor,
> >> >
> >> > Thanks for the response. But according to the user and the event
logs,
> >> > a normal shutdown was requested. i.e. The user requested Windows to
> >> > shutdown. This should have sent a message to all the services to
stop.
> >> > Yet I don't see that request in the SQL Server log. But the event
log
> >> > does show that a normal shutdown was requested of the OS.
> >> >
> >> > Thanks again.
> >> >
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> > > The problem is in the nature of hardware. A page it 8KB. The disks
> > typically uses a sector as the
> >> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page
is
> > typically 16 sectors. A power
> >> > > failure (or hard stop) in the middle of a page write will cause a
> > partially written page (a torn
> >> > > page). Having HW write cache *with a properly implemented battery
> > backup* will minimize the risk of
> >> > > a torn page (as long as the battery backup does its job).
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > <paul.esposito@.comcast.net> wrote in message
> >> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> >> > > > Well, corporate standard is one reason and other systems reside
on
> > the
> >> > > > server and need to be rebooted.
> >> > > >
> >> > > > I'm going to incorporate a netstop into the procedures. I was
just
> >> > > > wondering if this was a common problem.
> >> > > >
> >> > > > Regardless of reason, whether I reboot the server once a week or
> > once
> >> > > > every year, if the database corrupts on that shutdown there seems
to
> > be
> >> > > > an issue. I say this because I was told that Microsoft says W2K3
> > and
> >> > > > SQL Server are tuned not to need a reboot and that they should be
> > left
> >> > > > running. Well some of our servers can't be left running all the
> > time
> >> > > > Hurricanes, power outages ... Just seems like a bug to me.
> >> > > >
> >> > > > Thanks for the input though. I won't fight it, I'll just do a
net
> >> > > > stop.
> >> > > >
> >> > > >
> >> > > > John Bell wrote:
> >> > > >> Hi
> >> > > >>
> >> > > >> It could be that SQL Server is not responding in time to the
> > request to
> >> > > >> terminate.
> >> > > >> Could you do a NET STOP on the SQL Server Services before
shutting
> > down.
> >> > > >> Why do you need to shut down each week?
> >> > > >>
> >> > > >> John
> >> > > >>
> >> > > >> "paul.esposito@.comcast.net" wrote:
> >> > > >>
> >> > > >> > Hey Guys,
> >> > > >> >
> >> > > >> > Looking to see if anyone has experienced this and if there is
> > something
> >> > > >> > we are doing wrong. We have a weekly process to reboot our
> > windows
> >> > > >> > servers (we have 100's). The process is a normal windows
> > shutdown.
> >> > > >> > Yet weekly one of our servers will come back up and have a
'Torn
> > Page'
> >> > > >> > error, then get marked suspect. In investigating this I
notice
> > that
> >> > > >> > the when the process works, there is a message in the SQL
Server
> > error
> >> > > >> > log that says 'SQL Server is terminating due to Server
shutdown'.
> >> > > >> > Yet in the cases where it ends up suspect the message is not
in
> > the
> >> > > >> > log. So to me it looks like the SQL Server is getting
shutdown
> > hard,
> >> > > >> > but why?
> >> > > >> >
> >> > > >> > I end up just restoring the 'Suspect' database, but I'm
wondering
> > why
> >> > > >> > the process sporatically fails on some servers. Is there a
> > setting I'm
> >> > > >> > missing?
> >> > > >> >
> >> > > >> > Some specifics about the environment is that these are all
> > Windows 2003
> >> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> >> > > >> > Edition).
> >> > > >> >
> >> > > >> > Thanks in advance for any assistance.
> >> > > >> >
> >> > > >> >
> >> > > >
> >>
> >
> >
>
Looking to see if anyone has experienced this and if there is something
we are doing wrong. We have a weekly process to reboot our windows
servers (we have 100's). The process is a normal windows shutdown.
Yet weekly one of our servers will come back up and have a 'Torn Page'
error, then get marked suspect. In investigating this I notice that
the when the process works, there is a message in the SQL Server error
log that says 'SQL Server is terminating due to Server shutdown'.
Yet in the cases where it ends up suspect the message is not in the
log. So to me it looks like the SQL Server is getting shutdown hard,
but why?
I end up just restoring the 'Suspect' database, but I'm wondering why
the process sporatically fails on some servers. Is there a setting I'm
missing?
Some specifics about the environment is that these are all Windows 2003
servers running SQL Server 2000 SP3 (it also occurs on Desktop
Edition).
Thanks in advance for any assistance.Hi
It could be that SQL Server is not responding in time to the request to
terminate.
Could you do a NET STOP on the SQL Server Services before shutting down.
Why do you need to shut down each week?
John
"paul.esposito@.comcast.net" wrote:
> Hey Guys,
> Looking to see if anyone has experienced this and if there is something
> we are doing wrong. We have a weekly process to reboot our windows
> servers (we have 100's). The process is a normal windows shutdown.
> Yet weekly one of our servers will come back up and have a 'Torn Page'
> error, then get marked suspect. In investigating this I notice that
> the when the process works, there is a message in the SQL Server error
> log that says 'SQL Server is terminating due to Server shutdown'.
> Yet in the cases where it ends up suspect the message is not in the
> log. So to me it looks like the SQL Server is getting shutdown hard,
> but why?
> I end up just restoring the 'Suspect' database, but I'm wondering why
> the process sporatically fails on some servers. Is there a setting I'm
> missing?
> Some specifics about the environment is that these are all Windows 2003
> servers running SQL Server 2000 SP3 (it also occurs on Desktop
> Edition).
> Thanks in advance for any assistance.
>|||Well, corporate standard is one reason and other systems reside on the
server and need to be rebooted.
I'm going to incorporate a netstop into the procedures. I was just
wondering if this was a common problem.
Regardless of reason, whether I reboot the server once a week or once
every year, if the database corrupts on that shutdown there seems to be
an issue. I say this because I was told that Microsoft says W2K3 and
SQL Server are tuned not to need a reboot and that they should be left
running. Well some of our servers can't be left running all the time
Hurricanes, power outages ... Just seems like a bug to me.
Thanks for the input though. I won't fight it, I'll just do a net
stop.
John Bell wrote:
> Hi
> It could be that SQL Server is not responding in time to the request to
> terminate.
> Could you do a NET STOP on the SQL Server Services before shutting down.
> Why do you need to shut down each week?
> John
> "paul.esposito@.comcast.net" wrote:
> > Hey Guys,
> >
> > Looking to see if anyone has experienced this and if there is something
> > we are doing wrong. We have a weekly process to reboot our windows
> > servers (we have 100's). The process is a normal windows shutdown.
> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> > error, then get marked suspect. In investigating this I notice that
> > the when the process works, there is a message in the SQL Server error
> > log that says 'SQL Server is terminating due to Server shutdown'.
> > Yet in the cases where it ends up suspect the message is not in the
> > log. So to me it looks like the SQL Server is getting shutdown hard,
> > but why?
> >
> > I end up just restoring the 'Suspect' database, but I'm wondering why
> > the process sporatically fails on some servers. Is there a setting I'm
> > missing?
> >
> > Some specifics about the environment is that these are all Windows 2003
> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > Edition).
> >
> > Thanks in advance for any assistance.
> >
> >|||The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
a torn page (as long as the battery backup does its job).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<paul.esposito@.comcast.net> wrote in message
news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> Well, corporate standard is one reason and other systems reside on the
> server and need to be rebooted.
> I'm going to incorporate a netstop into the procedures. I was just
> wondering if this was a common problem.
> Regardless of reason, whether I reboot the server once a week or once
> every year, if the database corrupts on that shutdown there seems to be
> an issue. I say this because I was told that Microsoft says W2K3 and
> SQL Server are tuned not to need a reboot and that they should be left
> running. Well some of our servers can't be left running all the time
> Hurricanes, power outages ... Just seems like a bug to me.
> Thanks for the input though. I won't fight it, I'll just do a net
> stop.
>
> John Bell wrote:
>> Hi
>> It could be that SQL Server is not responding in time to the request to
>> terminate.
>> Could you do a NET STOP on the SQL Server Services before shutting down.
>> Why do you need to shut down each week?
>> John
>> "paul.esposito@.comcast.net" wrote:
>> > Hey Guys,
>> >
>> > Looking to see if anyone has experienced this and if there is something
>> > we are doing wrong. We have a weekly process to reboot our windows
>> > servers (we have 100's). The process is a normal windows shutdown.
>> > Yet weekly one of our servers will come back up and have a 'Torn Page'
>> > error, then get marked suspect. In investigating this I notice that
>> > the when the process works, there is a message in the SQL Server error
>> > log that says 'SQL Server is terminating due to Server shutdown'.
>> > Yet in the cases where it ends up suspect the message is not in the
>> > log. So to me it looks like the SQL Server is getting shutdown hard,
>> > but why?
>> >
>> > I end up just restoring the 'Suspect' database, but I'm wondering why
>> > the process sporatically fails on some servers. Is there a setting I'm
>> > missing?
>> >
>> > Some specifics about the environment is that these are all Windows 2003
>> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
>> > Edition).
>> >
>> > Thanks in advance for any assistance.
>> >
>> >
>|||Tibor,
Thanks for the response. But according to the user and the event logs,
a normal shutdown was requested. i.e. The user requested Windows to
shutdown. This should have sent a message to all the services to stop.
Yet I don't see that request in the SQL Server log. But the event log
does show that a normal shutdown was requested of the OS.
Thanks again.
Tibor Karaszi wrote:
> The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> a torn page (as long as the battery backup does its job).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <paul.esposito@.comcast.net> wrote in message
> news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > Well, corporate standard is one reason and other systems reside on the
> > server and need to be rebooted.
> >
> > I'm going to incorporate a netstop into the procedures. I was just
> > wondering if this was a common problem.
> >
> > Regardless of reason, whether I reboot the server once a week or once
> > every year, if the database corrupts on that shutdown there seems to be
> > an issue. I say this because I was told that Microsoft says W2K3 and
> > SQL Server are tuned not to need a reboot and that they should be left
> > running. Well some of our servers can't be left running all the time
> > Hurricanes, power outages ... Just seems like a bug to me.
> >
> > Thanks for the input though. I won't fight it, I'll just do a net
> > stop.
> >
> >
> > John Bell wrote:
> >> Hi
> >>
> >> It could be that SQL Server is not responding in time to the request to
> >> terminate.
> >> Could you do a NET STOP on the SQL Server Services before shutting down.
> >> Why do you need to shut down each week?
> >>
> >> John
> >>
> >> "paul.esposito@.comcast.net" wrote:
> >>
> >> > Hey Guys,
> >> >
> >> > Looking to see if anyone has experienced this and if there is something
> >> > we are doing wrong. We have a weekly process to reboot our windows
> >> > servers (we have 100's). The process is a normal windows shutdown.
> >> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> >> > error, then get marked suspect. In investigating this I notice that
> >> > the when the process works, there is a message in the SQL Server error
> >> > log that says 'SQL Server is terminating due to Server shutdown'.
> >> > Yet in the cases where it ends up suspect the message is not in the
> >> > log. So to me it looks like the SQL Server is getting shutdown hard,
> >> > but why?
> >> >
> >> > I end up just restoring the 'Suspect' database, but I'm wondering why
> >> > the process sporatically fails on some servers. Is there a setting I'm
> >> > missing?
> >> >
> >> > Some specifics about the environment is that these are all Windows 2003
> >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> >> > Edition).
> >> >
> >> > Thanks in advance for any assistance.
> >> >
> >> >
> >|||What sort of hardware are you using? How are your disks configured? Are
there any events or issues with your I/O controllers? How is your I/O
solution utilizing it's write cache?
I agree that you should be able to restart whenver you want. I have
several SQL installations in my environment and have never had an issue
with a normal shut down routine. Can you do a manual reboot of the
server watching it (by manual I mean not automated but going through
start/shutdown) and see what happens?
Just a point, while you should be able to reboot whenver you want
(gracefully and even safely under some not so graceful situations
should not be a problem and has not in my experience with my servers),
it is generally not always a best practice to reboot your database
servers.
For one thing there is no need to if you have a secure server and have
only SQL Server running on it. In most of the configurations out there
with only SQL running, you won't see a memory leak or any other issue
requiring reboot. Also SQL works hard to build it's caches with data
and compiled query execution plans that are optimized for your data and
workload. Everytime you reboot you clear this cache meaning that after
restart the server needs to first optimize and save all the plans again
as well as add the frequently accessed data into the data cache. Not a
huge deal but just some items to think about.
Please give more details regarding my above questions and we will see
what we can offer.
-Mike Walsh
SQL DBA
paul.esposito@.comcast.net wrote:
> Tibor,
> Thanks for the response. But according to the user and the event logs,
> a normal shutdown was requested. i.e. The user requested Windows to
> shutdown. This should have sent a message to all the services to stop.
> Yet I don't see that request in the SQL Server log. But the event log
> does show that a normal shutdown was requested of the OS.
> Thanks again.
>
> Tibor Karaszi wrote:
> > The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> > failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> > page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> > a torn page (as long as the battery backup does its job).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <paul.esposito@.comcast.net> wrote in message
> > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > > Well, corporate standard is one reason and other systems reside on the
> > > server and need to be rebooted.
> > >
> > > I'm going to incorporate a netstop into the procedures. I was just
> > > wondering if this was a common problem.
> > >
> > > Regardless of reason, whether I reboot the server once a week or once
> > > every year, if the database corrupts on that shutdown there seems to be
> > > an issue. I say this because I was told that Microsoft says W2K3 and
> > > SQL Server are tuned not to need a reboot and that they should be left
> > > running. Well some of our servers can't be left running all the time
> > > Hurricanes, power outages ... Just seems like a bug to me.
> > >
> > > Thanks for the input though. I won't fight it, I'll just do a net
> > > stop.
> > >
> > >
> > > John Bell wrote:
> > >> Hi
> > >>
> > >> It could be that SQL Server is not responding in time to the request to
> > >> terminate.
> > >> Could you do a NET STOP on the SQL Server Services before shutting down.
> > >> Why do you need to shut down each week?
> > >>
> > >> John
> > >>
> > >> "paul.esposito@.comcast.net" wrote:
> > >>
> > >> > Hey Guys,
> > >> >
> > >> > Looking to see if anyone has experienced this and if there is something
> > >> > we are doing wrong. We have a weekly process to reboot our windows
> > >> > servers (we have 100's). The process is a normal windows shutdown.
> > >> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> > >> > error, then get marked suspect. In investigating this I notice that
> > >> > the when the process works, there is a message in the SQL Server error
> > >> > log that says 'SQL Server is terminating due to Server shutdown'.
> > >> > Yet in the cases where it ends up suspect the message is not in the
> > >> > log. So to me it looks like the SQL Server is getting shutdown hard,
> > >> > but why?
> > >> >
> > >> > I end up just restoring the 'Suspect' database, but I'm wondering why
> > >> > the process sporatically fails on some servers. Is there a setting I'm
> > >> > missing?
> > >> >
> > >> > Some specifics about the environment is that these are all Windows 2003
> > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > >> > Edition).
> > >> >
> > >> > Thanks in advance for any assistance.
> > >> >
> > >> >
> > >|||Unfortunately, a Server Shutdown request does exactly this: a request for
NET STOP on every running service. The problem is that there exists a time
out value. When SQL Server receives this message, it begins its shutdown
phase, which issues stops all new connections and issues a hard CHECKPOINT
of all databases.
Typically during the maintenance windows (especially the database REORGs),
this stop request could take quite a bit of time (even more on the start up
and recovery process).
Nearly the only way you are going to get a torn page from this (as opposed
to a lot of recovery rollback operations) is that the disks were powered
down before the cache committed.
1. Plan your database maintenance around these weekly system restarts.
2. Disable controller and Windows disk caching unless you can guarantee
controller power protection.
I would also agree with most everyone else that a DBMS should be on a
dedicated system, and, which case, should be very little justification for
weekly restarts.
Now, in our Data Center, we have monthly OS updates that are deployed. For
this, we (the DBA team) have demanded subsequent system restarts (mainly
because we believe patches leave PendingFileRename operations), but that is
only on a monthly basis, and we shut down database service before the server
team is allowed to begin.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> What sort of hardware are you using? How are your disks configured? Are
> there any events or issues with your I/O controllers? How is your I/O
> solution utilizing it's write cache?
> I agree that you should be able to restart whenver you want. I have
> several SQL installations in my environment and have never had an issue
> with a normal shut down routine. Can you do a manual reboot of the
> server watching it (by manual I mean not automated but going through
> start/shutdown) and see what happens?
> Just a point, while you should be able to reboot whenver you want
> (gracefully and even safely under some not so graceful situations
> should not be a problem and has not in my experience with my servers),
> it is generally not always a best practice to reboot your database
> servers.
> For one thing there is no need to if you have a secure server and have
> only SQL Server running on it. In most of the configurations out there
> with only SQL running, you won't see a memory leak or any other issue
> requiring reboot. Also SQL works hard to build it's caches with data
> and compiled query execution plans that are optimized for your data and
> workload. Everytime you reboot you clear this cache meaning that after
> restart the server needs to first optimize and save all the plans again
> as well as add the frequently accessed data into the data cache. Not a
> huge deal but just some items to think about.
> Please give more details regarding my above questions and we will see
> what we can offer.
> -Mike Walsh
> SQL DBA
>
> paul.esposito@.comcast.net wrote:
> > Tibor,
> >
> > Thanks for the response. But according to the user and the event logs,
> > a normal shutdown was requested. i.e. The user requested Windows to
> > shutdown. This should have sent a message to all the services to stop.
> > Yet I don't see that request in the SQL Server log. But the event log
> > does show that a normal shutdown was requested of the OS.
> >
> > Thanks again.
> >
> >
> >
> > Tibor Karaszi wrote:
> > > The problem is in the nature of hardware. A page it 8KB. The disks
typically uses a sector as the
> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is
typically 16 sectors. A power
> > > failure (or hard stop) in the middle of a page write will cause a
partially written page (a torn
> > > page). Having HW write cache *with a properly implemented battery
backup* will minimize the risk of
> > > a torn page (as long as the battery backup does its job).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > <paul.esposito@.comcast.net> wrote in message
> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > > > Well, corporate standard is one reason and other systems reside on
the
> > > > server and need to be rebooted.
> > > >
> > > > I'm going to incorporate a netstop into the procedures. I was just
> > > > wondering if this was a common problem.
> > > >
> > > > Regardless of reason, whether I reboot the server once a week or
once
> > > > every year, if the database corrupts on that shutdown there seems to
be
> > > > an issue. I say this because I was told that Microsoft says W2K3
and
> > > > SQL Server are tuned not to need a reboot and that they should be
left
> > > > running. Well some of our servers can't be left running all the
time
> > > > Hurricanes, power outages ... Just seems like a bug to me.
> > > >
> > > > Thanks for the input though. I won't fight it, I'll just do a net
> > > > stop.
> > > >
> > > >
> > > > John Bell wrote:
> > > >> Hi
> > > >>
> > > >> It could be that SQL Server is not responding in time to the
request to
> > > >> terminate.
> > > >> Could you do a NET STOP on the SQL Server Services before shutting
down.
> > > >> Why do you need to shut down each week?
> > > >>
> > > >> John
> > > >>
> > > >> "paul.esposito@.comcast.net" wrote:
> > > >>
> > > >> > Hey Guys,
> > > >> >
> > > >> > Looking to see if anyone has experienced this and if there is
something
> > > >> > we are doing wrong. We have a weekly process to reboot our
windows
> > > >> > servers (we have 100's). The process is a normal windows
shutdown.
> > > >> > Yet weekly one of our servers will come back up and have a 'Torn
Page'
> > > >> > error, then get marked suspect. In investigating this I notice
that
> > > >> > the when the process works, there is a message in the SQL Server
error
> > > >> > log that says 'SQL Server is terminating due to Server shutdown'.
> > > >> > Yet in the cases where it ends up suspect the message is not in
the
> > > >> > log. So to me it looks like the SQL Server is getting shutdown
hard,
> > > >> > but why?
> > > >> >
> > > >> > I end up just restoring the 'Suspect' database, but I'm wondering
why
> > > >> > the process sporatically fails on some servers. Is there a
setting I'm
> > > >> > missing?
> > > >> >
> > > >> > Some specifics about the environment is that these are all
Windows 2003
> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > > >> > Edition).
> > > >> >
> > > >> > Thanks in advance for any assistance.
> > > >> >
> > > >> >
> > > >
>|||Perhaps this SQL Server instance has been configured with a high value for sp_configure, recovery
interval? Less frequent checkpoint should mean more work for each checkpoint, including the one
submitted for a shutdown...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
> Unfortunately, a Server Shutdown request does exactly this: a request for
> NET STOP on every running service. The problem is that there exists a time
> out value. When SQL Server receives this message, it begins its shutdown
> phase, which issues stops all new connections and issues a hard CHECKPOINT
> of all databases.
> Typically during the maintenance windows (especially the database REORGs),
> this stop request could take quite a bit of time (even more on the start up
> and recovery process).
> Nearly the only way you are going to get a torn page from this (as opposed
> to a lot of recovery rollback operations) is that the disks were powered
> down before the cache committed.
> 1. Plan your database maintenance around these weekly system restarts.
> 2. Disable controller and Windows disk caching unless you can guarantee
> controller power protection.
> I would also agree with most everyone else that a DBMS should be on a
> dedicated system, and, which case, should be very little justification for
> weekly restarts.
> Now, in our Data Center, we have monthly OS updates that are deployed. For
> this, we (the DBA team) have demanded subsequent system restarts (mainly
> because we believe patches leave PendingFileRename operations), but that is
> only on a monthly basis, and we shut down database service before the server
> team is allowed to begin.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
>> What sort of hardware are you using? How are your disks configured? Are
>> there any events or issues with your I/O controllers? How is your I/O
>> solution utilizing it's write cache?
>> I agree that you should be able to restart whenver you want. I have
>> several SQL installations in my environment and have never had an issue
>> with a normal shut down routine. Can you do a manual reboot of the
>> server watching it (by manual I mean not automated but going through
>> start/shutdown) and see what happens?
>> Just a point, while you should be able to reboot whenver you want
>> (gracefully and even safely under some not so graceful situations
>> should not be a problem and has not in my experience with my servers),
>> it is generally not always a best practice to reboot your database
>> servers.
>> For one thing there is no need to if you have a secure server and have
>> only SQL Server running on it. In most of the configurations out there
>> with only SQL running, you won't see a memory leak or any other issue
>> requiring reboot. Also SQL works hard to build it's caches with data
>> and compiled query execution plans that are optimized for your data and
>> workload. Everytime you reboot you clear this cache meaning that after
>> restart the server needs to first optimize and save all the plans again
>> as well as add the frequently accessed data into the data cache. Not a
>> huge deal but just some items to think about.
>> Please give more details regarding my above questions and we will see
>> what we can offer.
>> -Mike Walsh
>> SQL DBA
>>
>> paul.esposito@.comcast.net wrote:
>> > Tibor,
>> >
>> > Thanks for the response. But according to the user and the event logs,
>> > a normal shutdown was requested. i.e. The user requested Windows to
>> > shutdown. This should have sent a message to all the services to stop.
>> > Yet I don't see that request in the SQL Server log. But the event log
>> > does show that a normal shutdown was requested of the OS.
>> >
>> > Thanks again.
>> >
>> >
>> >
>> > Tibor Karaszi wrote:
>> > > The problem is in the nature of hardware. A page it 8KB. The disks
> typically uses a sector as the
>> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is
> typically 16 sectors. A power
>> > > failure (or hard stop) in the middle of a page write will cause a
> partially written page (a torn
>> > > page). Having HW write cache *with a properly implemented battery
> backup* will minimize the risk of
>> > > a torn page (as long as the battery backup does its job).
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > <paul.esposito@.comcast.net> wrote in message
>> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
>> > > > Well, corporate standard is one reason and other systems reside on
> the
>> > > > server and need to be rebooted.
>> > > >
>> > > > I'm going to incorporate a netstop into the procedures. I was just
>> > > > wondering if this was a common problem.
>> > > >
>> > > > Regardless of reason, whether I reboot the server once a week or
> once
>> > > > every year, if the database corrupts on that shutdown there seems to
> be
>> > > > an issue. I say this because I was told that Microsoft says W2K3
> and
>> > > > SQL Server are tuned not to need a reboot and that they should be
> left
>> > > > running. Well some of our servers can't be left running all the
> time
>> > > > Hurricanes, power outages ... Just seems like a bug to me.
>> > > >
>> > > > Thanks for the input though. I won't fight it, I'll just do a net
>> > > > stop.
>> > > >
>> > > >
>> > > > John Bell wrote:
>> > > >> Hi
>> > > >>
>> > > >> It could be that SQL Server is not responding in time to the
> request to
>> > > >> terminate.
>> > > >> Could you do a NET STOP on the SQL Server Services before shutting
> down.
>> > > >> Why do you need to shut down each week?
>> > > >>
>> > > >> John
>> > > >>
>> > > >> "paul.esposito@.comcast.net" wrote:
>> > > >>
>> > > >> > Hey Guys,
>> > > >> >
>> > > >> > Looking to see if anyone has experienced this and if there is
> something
>> > > >> > we are doing wrong. We have a weekly process to reboot our
> windows
>> > > >> > servers (we have 100's). The process is a normal windows
> shutdown.
>> > > >> > Yet weekly one of our servers will come back up and have a 'Torn
> Page'
>> > > >> > error, then get marked suspect. In investigating this I notice
> that
>> > > >> > the when the process works, there is a message in the SQL Server
> error
>> > > >> > log that says 'SQL Server is terminating due to Server shutdown'.
>> > > >> > Yet in the cases where it ends up suspect the message is not in
> the
>> > > >> > log. So to me it looks like the SQL Server is getting shutdown
> hard,
>> > > >> > but why?
>> > > >> >
>> > > >> > I end up just restoring the 'Suspect' database, but I'm wondering
> why
>> > > >> > the process sporatically fails on some servers. Is there a
> setting I'm
>> > > >> > missing?
>> > > >> >
>> > > >> > Some specifics about the environment is that these are all
> Windows 2003
>> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
>> > > >> > Edition).
>> > > >> >
>> > > >> > Thanks in advance for any assistance.
>> > > >> >
>> > > >> >
>> > > >
>|||Certainly a good place to examine. But, more often than not, especially if
this is in the evening on a weekend/Friday evening, the Index Rebuilds are
racking up large transactions. If the database(s) is(are) of any
appreciable size, then it could "hang" for awhile before letting go.
Anthony Thomas
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwmyoYDHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Perhaps this SQL Server instance has been configured with a high value for
sp_configure, recovery
> interval? Less frequent checkpoint should mean more work for each
checkpoint, including the one
> submitted for a shutdown...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
> > Unfortunately, a Server Shutdown request does exactly this: a request
for
> > NET STOP on every running service. The problem is that there exists a
time
> > out value. When SQL Server receives this message, it begins its
shutdown
> > phase, which issues stops all new connections and issues a hard
CHECKPOINT
> > of all databases.
> >
> > Typically during the maintenance windows (especially the database
REORGs),
> > this stop request could take quite a bit of time (even more on the start
up
> > and recovery process).
> >
> > Nearly the only way you are going to get a torn page from this (as
opposed
> > to a lot of recovery rollback operations) is that the disks were powered
> > down before the cache committed.
> >
> > 1. Plan your database maintenance around these weekly system restarts.
> > 2. Disable controller and Windows disk caching unless you can guarantee
> > controller power protection.
> >
> > I would also agree with most everyone else that a DBMS should be on a
> > dedicated system, and, which case, should be very little justification
for
> > weekly restarts.
> >
> > Now, in our Data Center, we have monthly OS updates that are deployed.
For
> > this, we (the DBA team) have demanded subsequent system restarts (mainly
> > because we believe patches leave PendingFileRename operations), but that
is
> > only on a monthly basis, and we shut down database service before the
server
> > team is allowed to begin.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> > news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> >> What sort of hardware are you using? How are your disks configured? Are
> >> there any events or issues with your I/O controllers? How is your I/O
> >> solution utilizing it's write cache?
> >>
> >> I agree that you should be able to restart whenver you want. I have
> >> several SQL installations in my environment and have never had an issue
> >> with a normal shut down routine. Can you do a manual reboot of the
> >> server watching it (by manual I mean not automated but going through
> >> start/shutdown) and see what happens?
> >>
> >> Just a point, while you should be able to reboot whenver you want
> >> (gracefully and even safely under some not so graceful situations
> >> should not be a problem and has not in my experience with my servers),
> >> it is generally not always a best practice to reboot your database
> >> servers.
> >>
> >> For one thing there is no need to if you have a secure server and have
> >> only SQL Server running on it. In most of the configurations out there
> >> with only SQL running, you won't see a memory leak or any other issue
> >> requiring reboot. Also SQL works hard to build it's caches with data
> >> and compiled query execution plans that are optimized for your data and
> >> workload. Everytime you reboot you clear this cache meaning that after
> >> restart the server needs to first optimize and save all the plans again
> >> as well as add the frequently accessed data into the data cache. Not a
> >> huge deal but just some items to think about.
> >>
> >> Please give more details regarding my above questions and we will see
> >> what we can offer.
> >>
> >> -Mike Walsh
> >> SQL DBA
> >>
> >>
> >> paul.esposito@.comcast.net wrote:
> >> > Tibor,
> >> >
> >> > Thanks for the response. But according to the user and the event
logs,
> >> > a normal shutdown was requested. i.e. The user requested Windows to
> >> > shutdown. This should have sent a message to all the services to
stop.
> >> > Yet I don't see that request in the SQL Server log. But the event
log
> >> > does show that a normal shutdown was requested of the OS.
> >> >
> >> > Thanks again.
> >> >
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> > > The problem is in the nature of hardware. A page it 8KB. The disks
> > typically uses a sector as the
> >> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page
is
> > typically 16 sectors. A power
> >> > > failure (or hard stop) in the middle of a page write will cause a
> > partially written page (a torn
> >> > > page). Having HW write cache *with a properly implemented battery
> > backup* will minimize the risk of
> >> > > a torn page (as long as the battery backup does its job).
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > <paul.esposito@.comcast.net> wrote in message
> >> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> >> > > > Well, corporate standard is one reason and other systems reside
on
> > the
> >> > > > server and need to be rebooted.
> >> > > >
> >> > > > I'm going to incorporate a netstop into the procedures. I was
just
> >> > > > wondering if this was a common problem.
> >> > > >
> >> > > > Regardless of reason, whether I reboot the server once a week or
> > once
> >> > > > every year, if the database corrupts on that shutdown there seems
to
> > be
> >> > > > an issue. I say this because I was told that Microsoft says W2K3
> > and
> >> > > > SQL Server are tuned not to need a reboot and that they should be
> > left
> >> > > > running. Well some of our servers can't be left running all the
> > time
> >> > > > Hurricanes, power outages ... Just seems like a bug to me.
> >> > > >
> >> > > > Thanks for the input though. I won't fight it, I'll just do a
net
> >> > > > stop.
> >> > > >
> >> > > >
> >> > > > John Bell wrote:
> >> > > >> Hi
> >> > > >>
> >> > > >> It could be that SQL Server is not responding in time to the
> > request to
> >> > > >> terminate.
> >> > > >> Could you do a NET STOP on the SQL Server Services before
shutting
> > down.
> >> > > >> Why do you need to shut down each week?
> >> > > >>
> >> > > >> John
> >> > > >>
> >> > > >> "paul.esposito@.comcast.net" wrote:
> >> > > >>
> >> > > >> > Hey Guys,
> >> > > >> >
> >> > > >> > Looking to see if anyone has experienced this and if there is
> > something
> >> > > >> > we are doing wrong. We have a weekly process to reboot our
> > windows
> >> > > >> > servers (we have 100's). The process is a normal windows
> > shutdown.
> >> > > >> > Yet weekly one of our servers will come back up and have a
'Torn
> > Page'
> >> > > >> > error, then get marked suspect. In investigating this I
notice
> > that
> >> > > >> > the when the process works, there is a message in the SQL
Server
> > error
> >> > > >> > log that says 'SQL Server is terminating due to Server
shutdown'.
> >> > > >> > Yet in the cases where it ends up suspect the message is not
in
> > the
> >> > > >> > log. So to me it looks like the SQL Server is getting
shutdown
> > hard,
> >> > > >> > but why?
> >> > > >> >
> >> > > >> > I end up just restoring the 'Suspect' database, but I'm
wondering
> > why
> >> > > >> > the process sporatically fails on some servers. Is there a
> > setting I'm
> >> > > >> > missing?
> >> > > >> >
> >> > > >> > Some specifics about the environment is that these are all
> > Windows 2003
> >> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> >> > > >> > Edition).
> >> > > >> >
> >> > > >> > Thanks in advance for any assistance.
> >> > > >> >
> >> > > >> >
> >> > > >
> >>
> >
> >
>
Database marked Suspect after normabl Windows Shutdown
Hey Guys,
Looking to see if anyone has experienced this and if there is something
we are doing wrong. We have a weekly process to reboot our windows
servers (we have 100's). The process is a normal windows shutdown.
Yet weekly one of our servers will come back up and have a 'Torn Page'
error, then get marked suspect. In investigating this I notice that
the when the process works, there is a message in the SQL Server error
log that says 'SQL Server is terminating due to Server shutdown'.
Yet in the cases where it ends up suspect the message is not in the
log. So to me it looks like the SQL Server is getting shutdown hard,
but why?
I end up just restoring the 'Suspect' database, but I'm wondering why
the process sporatically fails on some servers. Is there a setting I'm
missing?
Some specifics about the environment is that these are all Windows 2003
servers running SQL Server 2000 SP3 (it also occurs on Desktop
Edition).
Thanks in advance for any assistance.Hi
It could be that SQL Server is not responding in time to the request to
terminate.
Could you do a NET STOP on the SQL Server Services before shutting down.
Why do you need to shut down each week?
John
"paul.esposito@.comcast.net" wrote:
> Hey Guys,
> Looking to see if anyone has experienced this and if there is something
> we are doing wrong. We have a weekly process to reboot our windows
> servers (we have 100's). The process is a normal windows shutdown.
> Yet weekly one of our servers will come back up and have a 'Torn Page'
> error, then get marked suspect. In investigating this I notice that
> the when the process works, there is a message in the SQL Server error
> log that says 'SQL Server is terminating due to Server shutdown'.
> Yet in the cases where it ends up suspect the message is not in the
> log. So to me it looks like the SQL Server is getting shutdown hard,
> but why?
> I end up just restoring the 'Suspect' database, but I'm wondering why
> the process sporatically fails on some servers. Is there a setting I'm
> missing?
> Some specifics about the environment is that these are all Windows 2003
> servers running SQL Server 2000 SP3 (it also occurs on Desktop
> Edition).
> Thanks in advance for any assistance.
>|||Well, corporate standard is one reason and other systems reside on the
server and need to be rebooted.
I'm going to incorporate a netstop into the procedures. I was just
wondering if this was a common problem.
Regardless of reason, whether I reboot the server once a week or once
every year, if the database corrupts on that shutdown there seems to be
an issue. I say this because I was told that Microsoft says W2K3 and
SQL Server are tuned not to need a reboot and that they should be left
running. Well some of our servers can't be left running all the time
Hurricanes, power outages ... Just seems like a bug to me.
Thanks for the input though. I won't fight it, I'll just do a net
stop.
John Bell wrote:[vbcol=seagreen]
> Hi
> It could be that SQL Server is not responding in time to the request to
> terminate.
> Could you do a NET STOP on the SQL Server Services before shutting down.
> Why do you need to shut down each week?
> John
> "paul.esposito@.comcast.net" wrote:
>|||The problem is in the nature of hardware. A page it 8KB. The disks typically
uses a sector as the
level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typica
lly 16 sectors. A power
failure (or hard stop) in the middle of a page write will cause a partially
written page (a torn
page). Having HW write cache *with a properly implemented battery backup* wi
ll minimize the risk of
a torn page (as long as the battery backup does its job).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<paul.esposito@.comcast.net> wrote in message
news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> Well, corporate standard is one reason and other systems reside on the
> server and need to be rebooted.
> I'm going to incorporate a netstop into the procedures. I was just
> wondering if this was a common problem.
> Regardless of reason, whether I reboot the server once a week or once
> every year, if the database corrupts on that shutdown there seems to be
> an issue. I say this because I was told that Microsoft says W2K3 and
> SQL Server are tuned not to need a reboot and that they should be left
> running. Well some of our servers can't be left running all the time
> Hurricanes, power outages ... Just seems like a bug to me.
> Thanks for the input though. I won't fight it, I'll just do a net
> stop.
>
> John Bell wrote:
>|||Tibor,
Thanks for the response. But according to the user and the event logs,
a normal shutdown was requested. i.e. The user requested Windows to
shutdown. This should have sent a message to all the services to stop.
Yet I don't see that request in the SQL Server log. But the event log
does show that a normal shutdown was requested of the OS.
Thanks again.
Tibor Karaszi wrote:[vbcol=seagreen]
> The problem is in the nature of hardware. A page it 8KB. The disks typical
ly uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typi
cally 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partiall
y written page (a torn
> page). Having HW write cache *with a properly implemented battery backup*
will minimize the risk of
> a torn page (as long as the battery backup does its job).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <paul.esposito@.comcast.net> wrote in message
> news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...|||What sort of hardware are you using? How are your disks configured? Are
there any events or issues with your I/O controllers? How is your I/O
solution utilizing it's write cache?
I agree that you should be able to restart whenver you want. I have
several SQL installations in my environment and have never had an issue
with a normal shut down routine. Can you do a manual reboot of the
server watching it (by manual I mean not automated but going through
start/shutdown) and see what happens?
Just a point, while you should be able to reboot whenver you want
(gracefully and even safely under some not so graceful situations
should not be a problem and has not in my experience with my servers),
it is generally not always a best practice to reboot your database
servers.
For one thing there is no need to if you have a secure server and have
only SQL Server running on it. In most of the configurations out there
with only SQL running, you won't see a memory leak or any other issue
requiring reboot. Also SQL works hard to build it's caches with data
and compiled query execution plans that are optimized for your data and
workload. Everytime you reboot you clear this cache meaning that after
restart the server needs to first optimize and save all the plans again
as well as add the frequently accessed data into the data cache. Not a
huge deal but just some items to think about.
Please give more details regarding my above questions and we will see
what we can offer.
-Mike Walsh
SQL DBA
paul.esposito@.comcast.net wrote:[vbcol=seagreen]
> Tibor,
> Thanks for the response. But according to the user and the event logs,
> a normal shutdown was requested. i.e. The user requested Windows to
> shutdown. This should have sent a message to all the services to stop.
> Yet I don't see that request in the SQL Server log. But the event log
> does show that a normal shutdown was requested of the OS.
> Thanks again.
>
> Tibor Karaszi wrote:|||Unfortunately, a Server Shutdown request does exactly this: a request for
NET STOP on every running service. The problem is that there exists a time
out value. When SQL Server receives this message, it begins its shutdown
phase, which issues stops all new connections and issues a hard CHECKPOINT
of all databases.
Typically during the maintenance windows (especially the database REORGs),
this stop request could take quite a bit of time (even more on the start up
and recovery process).
Nearly the only way you are going to get a torn page from this (as opposed
to a lot of recovery rollback operations) is that the disks were powered
down before the cache committed.
1. Plan your database maintenance around these weekly system restarts.
2. Disable controller and Windows disk caching unless you can guarantee
controller power protection.
I would also agree with most everyone else that a DBMS should be on a
dedicated system, and, which case, should be very little justification for
weekly restarts.
Now, in our Data Center, we have monthly OS updates that are deployed. For
this, we (the DBA team) have demanded subsequent system restarts (mainly
because we believe patches leave PendingFileRename operations), but that is
only on a monthly basis, and we shut down database service before the server
team is allowed to begin.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> What sort of hardware are you using? How are your disks configured? Are
> there any events or issues with your I/O controllers? How is your I/O
> solution utilizing it's write cache?
> I agree that you should be able to restart whenver you want. I have
> several SQL installations in my environment and have never had an issue
> with a normal shut down routine. Can you do a manual reboot of the
> server watching it (by manual I mean not automated but going through
> start/shutdown) and see what happens?
> Just a point, while you should be able to reboot whenver you want
> (gracefully and even safely under some not so graceful situations
> should not be a problem and has not in my experience with my servers),
> it is generally not always a best practice to reboot your database
> servers.
> For one thing there is no need to if you have a secure server and have
> only SQL Server running on it. In most of the configurations out there
> with only SQL running, you won't see a memory leak or any other issue
> requiring reboot. Also SQL works hard to build it's caches with data
> and compiled query execution plans that are optimized for your data and
> workload. Everytime you reboot you clear this cache meaning that after
> restart the server needs to first optimize and save all the plans again
> as well as add the frequently accessed data into the data cache. Not a
> huge deal but just some items to think about.
> Please give more details regarding my above questions and we will see
> what we can offer.
> -Mike Walsh
> SQL DBA
>
> paul.esposito@.comcast.net wrote:
typically uses a sector as the[vbcol=seagreen]
typically 16 sectors. A power[vbcol=seagreen]
partially written page (a torn[vbcol=seagreen]
backup* will minimize the risk of[vbcol=seagreen]
the[vbcol=seagreen]
once[vbcol=seagreen]
be[vbcol=seagreen]
and[vbcol=seagreen]
left[vbcol=seagreen]
time[vbcol=seagreen]
request to[vbcol=seagreen]
down.[vbcol=seagreen]
something[vbcol=seagreen]
windows[vbcol=seagreen]
shutdown.[vbcol=seagreen]
Page'[vbcol=seagreen]
that[vbcol=seagreen]
error[vbcol=seagreen]
the[vbcol=seagreen]
hard,[vbcol=seagreen]
why[vbcol=seagreen]
setting I'm[vbcol=seagreen]
Windows 2003[vbcol=seagreen]
>|||Perhaps this SQL Server instance has been configured with a high value for s
p_configure, recovery
interval? Less frequent checkpoint should mean more work for each checkpoint
, including the one
submitted for a shutdown...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx
.gbl...
> Unfortunately, a Server Shutdown request does exactly this: a request for
> NET STOP on every running service. The problem is that there exists a tim
e
> out value. When SQL Server receives this message, it begins its shutdown
> phase, which issues stops all new connections and issues a hard CHECKPOINT
> of all databases.
> Typically during the maintenance windows (especially the database REORGs),
> this stop request could take quite a bit of time (even more on the start u
p
> and recovery process).
> Nearly the only way you are going to get a torn page from this (as opposed
> to a lot of recovery rollback operations) is that the disks were powered
> down before the cache committed.
> 1. Plan your database maintenance around these weekly system restarts.
> 2. Disable controller and Windows disk caching unless you can guarantee
> controller power protection.
> I would also agree with most everyone else that a DBMS should be on a
> dedicated system, and, which case, should be very little justification for
> weekly restarts.
> Now, in our Data Center, we have monthly OS updates that are deployed. Fo
r
> this, we (the DBA team) have demanded subsequent system restarts (mainly
> because we believe patches leave PendingFileRename operations), but that i
s
> only on a monthly basis, and we shut down database service before the serv
er
> team is allowed to begin.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> typically uses a sector as the
> typically 16 sectors. A power
> partially written page (a torn
> backup* will minimize the risk of
> the
> once
> be
> and
> left
> time
> request to
> down.
> something
> windows
> shutdown.
> Page'
> that
> error
> the
> hard,
> why
> setting I'm
> Windows 2003
>|||Certainly a good place to examine. But, more often than not, especially if
this is in the evening on a weekend/Friday evening, the Index Rebuilds are
racking up large transactions. If the database(s) is(are) of any
appreciable size, then it could "hang" for awhile before letting go.
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwmyoYDHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Perhaps this SQL Server instance has been configured with a high value for
sp_configure, recovery
> interval? Less frequent checkpoint should mean more work for each
checkpoint, including the one
> submitted for a shutdown...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
for[vbcol=seagreen]
time[vbcol=seagreen]
shutdown[vbcol=seagreen]
CHECKPOINT[vbcol=seagreen]
REORGs),[vbcol=seagreen]
up[vbcol=seagreen]
opposed[vbcol=seagreen]
for[vbcol=seagreen]
For[vbcol=seagreen]
is[vbcol=seagreen]
server[vbcol=seagreen]
logs,[vbcol=seagreen]
stop.[vbcol=seagreen]
log[vbcol=seagreen]
is[vbcol=seagreen]
on[vbcol=seagreen]
just[vbcol=seagreen]
to[vbcol=seagreen]
net[vbcol=seagreen]
shutting[vbcol=seagreen]
'Torn[vbcol=seagreen]
notice[vbcol=seagreen]
Server[vbcol=seagreen]
shutdown'.[vbcol=seagreen]
in[vbcol=seagreen]
shutdown[vbcol=seagreen]
wondering[vbcol=seagreen]
>
Looking to see if anyone has experienced this and if there is something
we are doing wrong. We have a weekly process to reboot our windows
servers (we have 100's). The process is a normal windows shutdown.
Yet weekly one of our servers will come back up and have a 'Torn Page'
error, then get marked suspect. In investigating this I notice that
the when the process works, there is a message in the SQL Server error
log that says 'SQL Server is terminating due to Server shutdown'.
Yet in the cases where it ends up suspect the message is not in the
log. So to me it looks like the SQL Server is getting shutdown hard,
but why?
I end up just restoring the 'Suspect' database, but I'm wondering why
the process sporatically fails on some servers. Is there a setting I'm
missing?
Some specifics about the environment is that these are all Windows 2003
servers running SQL Server 2000 SP3 (it also occurs on Desktop
Edition).
Thanks in advance for any assistance.Hi
It could be that SQL Server is not responding in time to the request to
terminate.
Could you do a NET STOP on the SQL Server Services before shutting down.
Why do you need to shut down each week?
John
"paul.esposito@.comcast.net" wrote:
> Hey Guys,
> Looking to see if anyone has experienced this and if there is something
> we are doing wrong. We have a weekly process to reboot our windows
> servers (we have 100's). The process is a normal windows shutdown.
> Yet weekly one of our servers will come back up and have a 'Torn Page'
> error, then get marked suspect. In investigating this I notice that
> the when the process works, there is a message in the SQL Server error
> log that says 'SQL Server is terminating due to Server shutdown'.
> Yet in the cases where it ends up suspect the message is not in the
> log. So to me it looks like the SQL Server is getting shutdown hard,
> but why?
> I end up just restoring the 'Suspect' database, but I'm wondering why
> the process sporatically fails on some servers. Is there a setting I'm
> missing?
> Some specifics about the environment is that these are all Windows 2003
> servers running SQL Server 2000 SP3 (it also occurs on Desktop
> Edition).
> Thanks in advance for any assistance.
>|||Well, corporate standard is one reason and other systems reside on the
server and need to be rebooted.
I'm going to incorporate a netstop into the procedures. I was just
wondering if this was a common problem.
Regardless of reason, whether I reboot the server once a week or once
every year, if the database corrupts on that shutdown there seems to be
an issue. I say this because I was told that Microsoft says W2K3 and
SQL Server are tuned not to need a reboot and that they should be left
running. Well some of our servers can't be left running all the time
Hurricanes, power outages ... Just seems like a bug to me.
Thanks for the input though. I won't fight it, I'll just do a net
stop.
John Bell wrote:[vbcol=seagreen]
> Hi
> It could be that SQL Server is not responding in time to the request to
> terminate.
> Could you do a NET STOP on the SQL Server Services before shutting down.
> Why do you need to shut down each week?
> John
> "paul.esposito@.comcast.net" wrote:
>|||The problem is in the nature of hardware. A page it 8KB. The disks typically
uses a sector as the
level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typica
lly 16 sectors. A power
failure (or hard stop) in the middle of a page write will cause a partially
written page (a torn
page). Having HW write cache *with a properly implemented battery backup* wi
ll minimize the risk of
a torn page (as long as the battery backup does its job).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<paul.esposito@.comcast.net> wrote in message
news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> Well, corporate standard is one reason and other systems reside on the
> server and need to be rebooted.
> I'm going to incorporate a netstop into the procedures. I was just
> wondering if this was a common problem.
> Regardless of reason, whether I reboot the server once a week or once
> every year, if the database corrupts on that shutdown there seems to be
> an issue. I say this because I was told that Microsoft says W2K3 and
> SQL Server are tuned not to need a reboot and that they should be left
> running. Well some of our servers can't be left running all the time
> Hurricanes, power outages ... Just seems like a bug to me.
> Thanks for the input though. I won't fight it, I'll just do a net
> stop.
>
> John Bell wrote:
>|||Tibor,
Thanks for the response. But according to the user and the event logs,
a normal shutdown was requested. i.e. The user requested Windows to
shutdown. This should have sent a message to all the services to stop.
Yet I don't see that request in the SQL Server log. But the event log
does show that a normal shutdown was requested of the OS.
Thanks again.
Tibor Karaszi wrote:[vbcol=seagreen]
> The problem is in the nature of hardware. A page it 8KB. The disks typical
ly uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typi
cally 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partiall
y written page (a torn
> page). Having HW write cache *with a properly implemented battery backup*
will minimize the risk of
> a torn page (as long as the battery backup does its job).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <paul.esposito@.comcast.net> wrote in message
> news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...|||What sort of hardware are you using? How are your disks configured? Are
there any events or issues with your I/O controllers? How is your I/O
solution utilizing it's write cache?
I agree that you should be able to restart whenver you want. I have
several SQL installations in my environment and have never had an issue
with a normal shut down routine. Can you do a manual reboot of the
server watching it (by manual I mean not automated but going through
start/shutdown) and see what happens?
Just a point, while you should be able to reboot whenver you want
(gracefully and even safely under some not so graceful situations
should not be a problem and has not in my experience with my servers),
it is generally not always a best practice to reboot your database
servers.
For one thing there is no need to if you have a secure server and have
only SQL Server running on it. In most of the configurations out there
with only SQL running, you won't see a memory leak or any other issue
requiring reboot. Also SQL works hard to build it's caches with data
and compiled query execution plans that are optimized for your data and
workload. Everytime you reboot you clear this cache meaning that after
restart the server needs to first optimize and save all the plans again
as well as add the frequently accessed data into the data cache. Not a
huge deal but just some items to think about.
Please give more details regarding my above questions and we will see
what we can offer.
-Mike Walsh
SQL DBA
paul.esposito@.comcast.net wrote:[vbcol=seagreen]
> Tibor,
> Thanks for the response. But according to the user and the event logs,
> a normal shutdown was requested. i.e. The user requested Windows to
> shutdown. This should have sent a message to all the services to stop.
> Yet I don't see that request in the SQL Server log. But the event log
> does show that a normal shutdown was requested of the OS.
> Thanks again.
>
> Tibor Karaszi wrote:|||Unfortunately, a Server Shutdown request does exactly this: a request for
NET STOP on every running service. The problem is that there exists a time
out value. When SQL Server receives this message, it begins its shutdown
phase, which issues stops all new connections and issues a hard CHECKPOINT
of all databases.
Typically during the maintenance windows (especially the database REORGs),
this stop request could take quite a bit of time (even more on the start up
and recovery process).
Nearly the only way you are going to get a torn page from this (as opposed
to a lot of recovery rollback operations) is that the disks were powered
down before the cache committed.
1. Plan your database maintenance around these weekly system restarts.
2. Disable controller and Windows disk caching unless you can guarantee
controller power protection.
I would also agree with most everyone else that a DBMS should be on a
dedicated system, and, which case, should be very little justification for
weekly restarts.
Now, in our Data Center, we have monthly OS updates that are deployed. For
this, we (the DBA team) have demanded subsequent system restarts (mainly
because we believe patches leave PendingFileRename operations), but that is
only on a monthly basis, and we shut down database service before the server
team is allowed to begin.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> What sort of hardware are you using? How are your disks configured? Are
> there any events or issues with your I/O controllers? How is your I/O
> solution utilizing it's write cache?
> I agree that you should be able to restart whenver you want. I have
> several SQL installations in my environment and have never had an issue
> with a normal shut down routine. Can you do a manual reboot of the
> server watching it (by manual I mean not automated but going through
> start/shutdown) and see what happens?
> Just a point, while you should be able to reboot whenver you want
> (gracefully and even safely under some not so graceful situations
> should not be a problem and has not in my experience with my servers),
> it is generally not always a best practice to reboot your database
> servers.
> For one thing there is no need to if you have a secure server and have
> only SQL Server running on it. In most of the configurations out there
> with only SQL running, you won't see a memory leak or any other issue
> requiring reboot. Also SQL works hard to build it's caches with data
> and compiled query execution plans that are optimized for your data and
> workload. Everytime you reboot you clear this cache meaning that after
> restart the server needs to first optimize and save all the plans again
> as well as add the frequently accessed data into the data cache. Not a
> huge deal but just some items to think about.
> Please give more details regarding my above questions and we will see
> what we can offer.
> -Mike Walsh
> SQL DBA
>
> paul.esposito@.comcast.net wrote:
typically uses a sector as the[vbcol=seagreen]
typically 16 sectors. A power[vbcol=seagreen]
partially written page (a torn[vbcol=seagreen]
backup* will minimize the risk of[vbcol=seagreen]
the[vbcol=seagreen]
once[vbcol=seagreen]
be[vbcol=seagreen]
and[vbcol=seagreen]
left[vbcol=seagreen]
time[vbcol=seagreen]
request to[vbcol=seagreen]
down.[vbcol=seagreen]
something[vbcol=seagreen]
windows[vbcol=seagreen]
shutdown.[vbcol=seagreen]
Page'[vbcol=seagreen]
that[vbcol=seagreen]
error[vbcol=seagreen]
the[vbcol=seagreen]
hard,[vbcol=seagreen]
why[vbcol=seagreen]
setting I'm[vbcol=seagreen]
Windows 2003[vbcol=seagreen]
>|||Perhaps this SQL Server instance has been configured with a high value for s
p_configure, recovery
interval? Less frequent checkpoint should mean more work for each checkpoint
, including the one
submitted for a shutdown...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx
.gbl...
> Unfortunately, a Server Shutdown request does exactly this: a request for
> NET STOP on every running service. The problem is that there exists a tim
e
> out value. When SQL Server receives this message, it begins its shutdown
> phase, which issues stops all new connections and issues a hard CHECKPOINT
> of all databases.
> Typically during the maintenance windows (especially the database REORGs),
> this stop request could take quite a bit of time (even more on the start u
p
> and recovery process).
> Nearly the only way you are going to get a torn page from this (as opposed
> to a lot of recovery rollback operations) is that the disks were powered
> down before the cache committed.
> 1. Plan your database maintenance around these weekly system restarts.
> 2. Disable controller and Windows disk caching unless you can guarantee
> controller power protection.
> I would also agree with most everyone else that a DBMS should be on a
> dedicated system, and, which case, should be very little justification for
> weekly restarts.
> Now, in our Data Center, we have monthly OS updates that are deployed. Fo
r
> this, we (the DBA team) have demanded subsequent system restarts (mainly
> because we believe patches leave PendingFileRename operations), but that i
s
> only on a monthly basis, and we shut down database service before the serv
er
> team is allowed to begin.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> typically uses a sector as the
> typically 16 sectors. A power
> partially written page (a torn
> backup* will minimize the risk of
> the
> once
> be
> and
> left
> time
> request to
> down.
> something
> windows
> shutdown.
> Page'
> that
> error
> the
> hard,
> why
> setting I'm
> Windows 2003
>|||Certainly a good place to examine. But, more often than not, especially if
this is in the evening on a weekend/Friday evening, the Index Rebuilds are
racking up large transactions. If the database(s) is(are) of any
appreciable size, then it could "hang" for awhile before letting go.
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwmyoYDHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Perhaps this SQL Server instance has been configured with a high value for
sp_configure, recovery
> interval? Less frequent checkpoint should mean more work for each
checkpoint, including the one
> submitted for a shutdown...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
for[vbcol=seagreen]
time[vbcol=seagreen]
shutdown[vbcol=seagreen]
CHECKPOINT[vbcol=seagreen]
REORGs),[vbcol=seagreen]
up[vbcol=seagreen]
opposed[vbcol=seagreen]
for[vbcol=seagreen]
For[vbcol=seagreen]
is[vbcol=seagreen]
server[vbcol=seagreen]
logs,[vbcol=seagreen]
stop.[vbcol=seagreen]
log[vbcol=seagreen]
is[vbcol=seagreen]
on[vbcol=seagreen]
just[vbcol=seagreen]
to[vbcol=seagreen]
net[vbcol=seagreen]
shutting[vbcol=seagreen]
'Torn[vbcol=seagreen]
notice[vbcol=seagreen]
Server[vbcol=seagreen]
shutdown'.[vbcol=seagreen]
in[vbcol=seagreen]
shutdown[vbcol=seagreen]
wondering[vbcol=seagreen]
>
Subscribe to:
Posts (Atom)