I have a SQL 2000 box with latest service pack as a publisher and
distributor. The log reader agent is not able to connect and as a result the
transaction log is growing.
On the other hand, there is a connection to the distrubtion database and it
appears to be an orphan connection from the log reader agent. I have killed
the orphan connection several times but that has not resolved the issue.
Following is the error I am getting:
SPID 30: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I believe if I can kill the connection, I will be able to start the log
reader agent successfully. I had the exact same issue yesterday and rebooting
the server did the trick. It is a production box so I can't afford to reboot
everyday like this.
What happens when you do a sp_repltrans in publisher database? You need to
set up a verboselevel to 2 and see what kind of output you get in output
file. That would be the start.
"Adam" wrote:
> I have a SQL 2000 box with latest service pack as a publisher and
> distributor. The log reader agent is not able to connect and as a result the
> transaction log is growing.
> On the other hand, there is a connection to the distrubtion database and it
> appears to be an orphan connection from the log reader agent. I have killed
> the orphan connection several times but that has not resolved the issue.
> Following is the error I am getting:
> SPID 30: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I believe if I can kill the connection, I will be able to start the log
> reader agent successfully. I had the exact same issue yesterday and rebooting
> the server did the trick. It is a production box so I can't afford to reboot
> everyday like this.
>
|||it probably is a phantom. See if you can start up the log reader agent
again.
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
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:C47F8C71-4D3A-4823-84AE-1948DAFAF850@.microsoft.com...
>I have a SQL 2000 box with latest service pack as a publisher and
> distributor. The log reader agent is not able to connect and as a result
> the
> transaction log is growing.
> On the other hand, there is a connection to the distrubtion database and
> it
> appears to be an orphan connection from the log reader agent. I have
> killed
> the orphan connection several times but that has not resolved the issue.
> Following is the error I am getting:
> SPID 30: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I believe if I can kill the connection, I will be able to start the log
> reader agent successfully. I had the exact same issue yesterday and
> rebooting
> the server did the trick. It is a production box so I can't afford to
> reboot
> everyday like this.
>
Showing posts with label service. Show all posts
Showing posts with label service. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Help! SQL Service Manager is not connected
After completing a successful installing of MSDE build 2000.80.760.0
on my Windows XP Version 5.1.2600 Service Pack 1 Build 2600 I am
encountering the following issue:
The SQL Service Manager Tray icon indicator is in solid white state and when I
mouse over, it displays "Not Connected = \\".
Attempting to double click the guy results with both the Server and the
Service textboxes being empty. Same when I collapse their drop down
menus, they do not list any database instances. Refresh does not
solve the problem either.
I get the following error in the event log:
Event Type:Warning
Event Source:MSSQL$VSDOTNET
Event Category:(8)
Event ID:19011
Date:4/13/2004
Time:10:59:05 AM
User:N/A
Computer:TOOTI
Description:
The description for Event ID ( 19011 ) in Source ( MSSQL$VSDOTNET
) cannot be found. The local computer may not have the necessary
registry information or message DLL files to display messages from a
remote computer. You may be able to use the /AUXSOURCE= flag to
retrieve this description; see Help and Support for details. The
following information is part of the event: (SpnRegister) : Error 1231.
Some notes:
* I have installed MSDE using the following command-line:
setup INSTANCENAME=VSDOTNET SAPWD=<my password>
* My system has Office 2003 Pro and Visual Studio 2003.net
* I removed Business Contact Manager and attempted to install MSDE
once again, however the issue remains.
* I am running file and print sharing on my internal (disconnected) nic
adapter
My configuration is:
OS NameMicrosoft Windows XP Professional
Version5.1.2600 Service Pack 1 Build 2600
OS ManufacturerMicrosoft Corporation
System ManufacturerAcer
System ModelTravelMate 800
System TypeX86-based PC
Processorx86 Family 6 Model 9 Stepping 5 GenuineIntel
~1598 Mhz
BIOS Version/DateACER 4A14, 9/19/2003
SMBIOS Version2.31
Windows DirectoryC:\WINDOWS
System DirectoryC:\WINDOWS\System32
Boot Device\Device\HarddiskVolume1
LocaleUnited States
Hardware Abstraction LayerVersion = "5.1.2600.1106
(xpsp1.020828-1920)"
Total Physical Memory512.00 MB
Available Physical Memory265.73 MB
Total Virtual Memory1.72 GB
Available Virtual Memory1.27 GB
Page File Space1.22 GB
Page FileC:\pagefile.sys
LOG\ERROR.LOG:
2004-04-13 10:59:01.71 server Microsoft SQL Server 2000 -
8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service
Pack 1)
2004-04-13 10:59:01.76 server Copyright (C) 1988-2002 Microsoft
Corporation.
2004-04-13 10:59:01.76 server All rights reserved.
2004-04-13 10:59:01.76 server Server Process ID is 652.
2004-04-13 10:59:01.76 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL
Server\MSSQL$VSDOTNET\LOG\ERRORLOG'.
2004-04-13 10:59:01.89 server SQL Server is starting at priority
class 'normal'(1 CPU detected).
2004-04-13 10:59:03.67 server SQL Server configured for thread
mode processing.
2004-04-13 10:59:03.83 server Using dynamic lock allocation. [500]
Lock Blocks, [1000] Lock Owner Blocks.
2004-04-13 10:59:04.77 spid3 Starting up database 'master'.
2004-04-13 10:59:05.51 server Using 'SSNETLIB.DLL' version
'8.0.766'.
2004-04-13 10:59:05.51 spid5 Starting up database 'model'.
2004-04-13 10:59:05.75 spid3 Server name is 'TOOTI\VSDOTNET'.
2004-04-13 10:59:05.75 spid3 Skipping startup of clean database id
4
2004-04-13 10:59:05.90 server SQL server listening on Shared
Memory.
2004-04-13 10:59:05.92 server SQL Server is ready for client
connections
2004-04-13 10:59:07.20 spid5 Clearing tempdb database.
2004-04-13 10:59:12.75 spid5 Starting up database 'tempdb'.
2004-04-13 10:59:13.44 spid3 Recovery complete.
2004-04-13 10:59:13.44 spid3 SQL global counter collection task is
created.
I thank anyone in advance for a quick response.
-Itai
hi Itai,
"Itai" <itaindia2002@.yahoo.com> ha scritto nel messaggio
news:ab661ff4.0404130215.18d18b5c@.posting.google.c om...
> After completing a successful installing of MSDE build 2000.80.760.0
> on my Windows XP Version 5.1.2600 Service Pack 1 Build 2600 I am
> encountering the following issue:
> The SQL Service Manager Tray icon indicator is in solid white state and
when I
> mouse over, it displays "Not Connected = \\".
> Attempting to double click the guy results with both the Server and the
> Service textboxes being empty. Same when I collapse their drop down
> menus, they do not list any database instances. Refresh does not
> solve the problem either.
> I get the following error in the event log:
sometimes the service manager fails to load the combo with the available SQL
Server instances when they are installed with disabled network protocols,
due to ODBC related "features" at this regard... but your logs seems to
show your instance is running... try typing the server instance name in the
combobox like YourComputerName\VSDOTNET ...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
on my Windows XP Version 5.1.2600 Service Pack 1 Build 2600 I am
encountering the following issue:
The SQL Service Manager Tray icon indicator is in solid white state and when I
mouse over, it displays "Not Connected = \\".
Attempting to double click the guy results with both the Server and the
Service textboxes being empty. Same when I collapse their drop down
menus, they do not list any database instances. Refresh does not
solve the problem either.
I get the following error in the event log:
Event Type:Warning
Event Source:MSSQL$VSDOTNET
Event Category:(8)
Event ID:19011
Date:4/13/2004
Time:10:59:05 AM
User:N/A
Computer:TOOTI
Description:
The description for Event ID ( 19011 ) in Source ( MSSQL$VSDOTNET
) cannot be found. The local computer may not have the necessary
registry information or message DLL files to display messages from a
remote computer. You may be able to use the /AUXSOURCE= flag to
retrieve this description; see Help and Support for details. The
following information is part of the event: (SpnRegister) : Error 1231.
Some notes:
* I have installed MSDE using the following command-line:
setup INSTANCENAME=VSDOTNET SAPWD=<my password>
* My system has Office 2003 Pro and Visual Studio 2003.net
* I removed Business Contact Manager and attempted to install MSDE
once again, however the issue remains.
* I am running file and print sharing on my internal (disconnected) nic
adapter
My configuration is:
OS NameMicrosoft Windows XP Professional
Version5.1.2600 Service Pack 1 Build 2600
OS ManufacturerMicrosoft Corporation
System ManufacturerAcer
System ModelTravelMate 800
System TypeX86-based PC
Processorx86 Family 6 Model 9 Stepping 5 GenuineIntel
~1598 Mhz
BIOS Version/DateACER 4A14, 9/19/2003
SMBIOS Version2.31
Windows DirectoryC:\WINDOWS
System DirectoryC:\WINDOWS\System32
Boot Device\Device\HarddiskVolume1
LocaleUnited States
Hardware Abstraction LayerVersion = "5.1.2600.1106
(xpsp1.020828-1920)"
Total Physical Memory512.00 MB
Available Physical Memory265.73 MB
Total Virtual Memory1.72 GB
Available Virtual Memory1.27 GB
Page File Space1.22 GB
Page FileC:\pagefile.sys
LOG\ERROR.LOG:
2004-04-13 10:59:01.71 server Microsoft SQL Server 2000 -
8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service
Pack 1)
2004-04-13 10:59:01.76 server Copyright (C) 1988-2002 Microsoft
Corporation.
2004-04-13 10:59:01.76 server All rights reserved.
2004-04-13 10:59:01.76 server Server Process ID is 652.
2004-04-13 10:59:01.76 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL
Server\MSSQL$VSDOTNET\LOG\ERRORLOG'.
2004-04-13 10:59:01.89 server SQL Server is starting at priority
class 'normal'(1 CPU detected).
2004-04-13 10:59:03.67 server SQL Server configured for thread
mode processing.
2004-04-13 10:59:03.83 server Using dynamic lock allocation. [500]
Lock Blocks, [1000] Lock Owner Blocks.
2004-04-13 10:59:04.77 spid3 Starting up database 'master'.
2004-04-13 10:59:05.51 server Using 'SSNETLIB.DLL' version
'8.0.766'.
2004-04-13 10:59:05.51 spid5 Starting up database 'model'.
2004-04-13 10:59:05.75 spid3 Server name is 'TOOTI\VSDOTNET'.
2004-04-13 10:59:05.75 spid3 Skipping startup of clean database id
4
2004-04-13 10:59:05.90 server SQL server listening on Shared
Memory.
2004-04-13 10:59:05.92 server SQL Server is ready for client
connections
2004-04-13 10:59:07.20 spid5 Clearing tempdb database.
2004-04-13 10:59:12.75 spid5 Starting up database 'tempdb'.
2004-04-13 10:59:13.44 spid3 Recovery complete.
2004-04-13 10:59:13.44 spid3 SQL global counter collection task is
created.
I thank anyone in advance for a quick response.
-Itai
hi Itai,
"Itai" <itaindia2002@.yahoo.com> ha scritto nel messaggio
news:ab661ff4.0404130215.18d18b5c@.posting.google.c om...
> After completing a successful installing of MSDE build 2000.80.760.0
> on my Windows XP Version 5.1.2600 Service Pack 1 Build 2600 I am
> encountering the following issue:
> The SQL Service Manager Tray icon indicator is in solid white state and
when I
> mouse over, it displays "Not Connected = \\".
> Attempting to double click the guy results with both the Server and the
> Service textboxes being empty. Same when I collapse their drop down
> menus, they do not list any database instances. Refresh does not
> solve the problem either.
> I get the following error in the event log:
sometimes the service manager fails to load the combo with the available SQL
Server instances when they are installed with disabled network protocols,
due to ODBC related "features" at this regard... but your logs seems to
show your instance is running... try typing the server instance name in the
combobox like YourComputerName\VSDOTNET ...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Help! SQL Server services and Agent Service Keep restarting
There are no indications as to why. Every 8 minutes or so the event log
records:
Event Type:Information
Event Source:Service Control Manager
Event Category:None
Event ID:7035
Date:5/29/2007
Time:10:15:49 AM
User:MEMOPROD\cluster
Computer:MEMOSQL1
Description:
The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop
control.
THEN...
Event Type:Error
Event Source:ClusSvc
Event Category:Failover Mgr
Event ID:1069
Date:5/29/2007
Time:10:15:49 AM
User:N/A
Computer:MEMOSQL1
Description:
Cluster resource 'SQL Server' in Resource Group 'Disks' failed.
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7034
Date:5/29/2007
Time:10:16:50 AM
User:N/A
Computer:MEMOSQL1
Description:
The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly. It has
done this 5 time(s).
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7011
Date:5/29/2007
Time:10:17:46 AM
User:N/A
Computer:MEMOSQL1
Description:
Timeout (30000 milliseconds) waiting for a transaction response from the
MSSQLSERVER service.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Any Ideas?
Hello Paul is there anything in the cluster log?
John Vandervliet
"Paul Riker" wrote:
> There are no indications as to why. Every 8 minutes or so the event log
> records:
> Event Type:Information
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7035
> Date:5/29/2007
> Time:10:15:49 AM
> User:MEMOPROD\cluster
> Computer:MEMOSQL1
> Description:
> The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop
> control.
> THEN...
>
> Event Type:Error
> Event Source:ClusSvc
> Event Category:Failover Mgr
> Event ID:1069
> Date:5/29/2007
> Time:10:15:49 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> Cluster resource 'SQL Server' in Resource Group 'Disks' failed.
> Event Type:Error
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7034
> Date:5/29/2007
> Time:10:16:50 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly. It has
> done this 5 time(s).
> Event Type:Error
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7011
> Date:5/29/2007
> Time:10:17:46 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> Timeout (30000 milliseconds) waiting for a transaction response from the
> MSSQLSERVER service.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> Any Ideas?
records:
Event Type:Information
Event Source:Service Control Manager
Event Category:None
Event ID:7035
Date:5/29/2007
Time:10:15:49 AM
User:MEMOPROD\cluster
Computer:MEMOSQL1
Description:
The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop
control.
THEN...
Event Type:Error
Event Source:ClusSvc
Event Category:Failover Mgr
Event ID:1069
Date:5/29/2007
Time:10:15:49 AM
User:N/A
Computer:MEMOSQL1
Description:
Cluster resource 'SQL Server' in Resource Group 'Disks' failed.
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7034
Date:5/29/2007
Time:10:16:50 AM
User:N/A
Computer:MEMOSQL1
Description:
The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly. It has
done this 5 time(s).
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7011
Date:5/29/2007
Time:10:17:46 AM
User:N/A
Computer:MEMOSQL1
Description:
Timeout (30000 milliseconds) waiting for a transaction response from the
MSSQLSERVER service.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Any Ideas?
Hello Paul is there anything in the cluster log?
John Vandervliet
"Paul Riker" wrote:
> There are no indications as to why. Every 8 minutes or so the event log
> records:
> Event Type:Information
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7035
> Date:5/29/2007
> Time:10:15:49 AM
> User:MEMOPROD\cluster
> Computer:MEMOSQL1
> Description:
> The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop
> control.
> THEN...
>
> Event Type:Error
> Event Source:ClusSvc
> Event Category:Failover Mgr
> Event ID:1069
> Date:5/29/2007
> Time:10:15:49 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> Cluster resource 'SQL Server' in Resource Group 'Disks' failed.
> Event Type:Error
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7034
> Date:5/29/2007
> Time:10:16:50 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly. It has
> done this 5 time(s).
> Event Type:Error
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7011
> Date:5/29/2007
> Time:10:17:46 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> Timeout (30000 milliseconds) waiting for a transaction response from the
> MSSQLSERVER service.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> Any Ideas?
Friday, March 23, 2012
Help! Restarting the MSSQLSERVER service.
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?
No, restarting an SQL Server service shouldn't destroy databases. What made
you ask this?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?
|||SQL Server is designed to go through stop/start cycles without losing any
information. There may be issues within a database that a stop/start cycle
will catch, but the process itself should not cause any problems. If you
have a very long running transaction it may take a while for the recovery
process to complete. If you have underlying hardware issues, a stop/start
can fail.
Why do you find it necessary to stop and restart SQL Server so often? I
seldom restart the service, typically only when I have to reboot the host
computer for an OS patch. I suspect that with a bit of tuning, tewaking,
and monitoring, you can get much better reliability and availability than
you have right now.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi,
> if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this
> was
> the case. I restart sometimes to get the databases back online and
> running.
> Am I totally wrong here?
|||Hi
Stopping SQL Server using Control Panel or Tray Icon or EM results in an
orderly shutdown.
Starting it again then starts the process of recovery, with incomplete
transactions being rolled back. If a database is corrupt, it may detect it
during startup too and will result in the DB being marked as 'suspect'.
What do you understand as "running database". A Process runs, not a database.
If you are exeriencing intermittent problems like queries timing out, then
run sp_who2 to see what is happening, as restarting SQL Server will drop the
connections and you still do not find the casue of your blocking.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Horner" wrote:
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?
|||My CTO told me that I almost destroyed several production databases by my
actions...
I thank you for your answer and thought anyways that I hadn't lost my mind.
"Narayana Vyas Kondreddi" wrote:
> No, restarting an SQL Server service shouldn't destroy databases. What made
> you ask this?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?
>
>
|||Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
>
>
|||Restart the service shouldn't be the solution. I know you are not the DBA,
but you should let your CTO know that the database needs tuning. I don't
restart SQL Server service for months, and when I do restart that'll be for
a software update or hardware maintenance etc.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:A520E89D-833C-4E0F-BD63-663C2290755A@.microsoft.com...
Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start
cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in
message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
>
>
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?
No, restarting an SQL Server service shouldn't destroy databases. What made
you ask this?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?
|||SQL Server is designed to go through stop/start cycles without losing any
information. There may be issues within a database that a stop/start cycle
will catch, but the process itself should not cause any problems. If you
have a very long running transaction it may take a while for the recovery
process to complete. If you have underlying hardware issues, a stop/start
can fail.
Why do you find it necessary to stop and restart SQL Server so often? I
seldom restart the service, typically only when I have to reboot the host
computer for an OS patch. I suspect that with a bit of tuning, tewaking,
and monitoring, you can get much better reliability and availability than
you have right now.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi,
> if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this
> was
> the case. I restart sometimes to get the databases back online and
> running.
> Am I totally wrong here?
|||Hi
Stopping SQL Server using Control Panel or Tray Icon or EM results in an
orderly shutdown.
Starting it again then starts the process of recovery, with incomplete
transactions being rolled back. If a database is corrupt, it may detect it
during startup too and will result in the DB being marked as 'suspect'.
What do you understand as "running database". A Process runs, not a database.
If you are exeriencing intermittent problems like queries timing out, then
run sp_who2 to see what is happening, as restarting SQL Server will drop the
connections and you still do not find the casue of your blocking.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Horner" wrote:
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?
|||My CTO told me that I almost destroyed several production databases by my
actions...
I thank you for your answer and thought anyways that I hadn't lost my mind.
"Narayana Vyas Kondreddi" wrote:
> No, restarting an SQL Server service shouldn't destroy databases. What made
> you ask this?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?
>
>
|||Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
>
>
|||Restart the service shouldn't be the solution. I know you are not the DBA,
but you should let your CTO know that the database needs tuning. I don't
restart SQL Server service for months, and when I do restart that'll be for
a software update or hardware maintenance etc.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:A520E89D-833C-4E0F-BD63-663C2290755A@.microsoft.com...
Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start
cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in
message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
>
>
Help! Restarting the MSSQLSERVER service.
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?No, restarting an SQL Server service shouldn't destroy databases. What made
you ask this?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?|||SQL Server is designed to go through stop/start cycles without losing any
information. There may be issues within a database that a stop/start cycle
will catch, but the process itself should not cause any problems. If you
have a very long running transaction it may take a while for the recovery
process to complete. If you have underlying hardware issues, a stop/start
can fail.
Why do you find it necessary to stop and restart SQL Server so often? I
seldom restart the service, typically only when I have to reboot the host
computer for an OS patch. I suspect that with a bit of tuning, tewaking,
and monitoring, you can get much better reliability and availability than
you have right now.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi,
> if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this
> was
> the case. I restart sometimes to get the databases back online and
> running.
> Am I totally wrong here?|||Hi
Stopping SQL Server using Control Panel or Tray Icon or EM results in an
orderly shutdown.
Starting it again then starts the process of recovery, with incomplete
transactions being rolled back. If a database is corrupt, it may detect it
during startup too and will result in the DB being marked as 'suspect'.
What do you understand as "running database". A Process runs, not a database
.
If you are exeriencing intermittent problems like queries timing out, then
run sp_who2 to see what is happening, as restarting SQL Server will drop the
connections and you still do not find the casue of your blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Horner" wrote:
> Can someone please tell me, perferrably an official Microsoft SQL profi, i
f
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this wa
s
> the case. I restart sometimes to get the databases back online and runnin
g.
> Am I totally wrong here?|||My CTO told me that I almost destroyed several production databases by my
actions...
I thank you for your answer and thought anyways that I hadn't lost my mind.
"Narayana Vyas Kondreddi" wrote:
> No, restarting an SQL Server service shouldn't destroy databases. What mad
e
> you ask this?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in messag
e
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi, i
f
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this wa
s
> the case. I restart sometimes to get the databases back online and runnin
g.
> Am I totally wrong here?
>
>|||Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start cycl
e
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in messag
e
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
>
>|||Restart the service shouldn't be the solution. I know you are not the DBA,
but you should let your CTO know that the database needs tuning. I don't
restart SQL Server service for months, and when I do restart that'll be for
a software update or hardware maintenance etc.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:A520E89D-833C-4E0F-BD63-663C2290755A@.microsoft.com...
Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start
cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in
message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
>
>
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?No, restarting an SQL Server service shouldn't destroy databases. What made
you ask this?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?|||SQL Server is designed to go through stop/start cycles without losing any
information. There may be issues within a database that a stop/start cycle
will catch, but the process itself should not cause any problems. If you
have a very long running transaction it may take a while for the recovery
process to complete. If you have underlying hardware issues, a stop/start
can fail.
Why do you find it necessary to stop and restart SQL Server so often? I
seldom restart the service, typically only when I have to reboot the host
computer for an OS patch. I suspect that with a bit of tuning, tewaking,
and monitoring, you can get much better reliability and availability than
you have right now.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi,
> if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this
> was
> the case. I restart sometimes to get the databases back online and
> running.
> Am I totally wrong here?|||Hi
Stopping SQL Server using Control Panel or Tray Icon or EM results in an
orderly shutdown.
Starting it again then starts the process of recovery, with incomplete
transactions being rolled back. If a database is corrupt, it may detect it
during startup too and will result in the DB being marked as 'suspect'.
What do you understand as "running database". A Process runs, not a database
.
If you are exeriencing intermittent problems like queries timing out, then
run sp_who2 to see what is happening, as restarting SQL Server will drop the
connections and you still do not find the casue of your blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Horner" wrote:
> Can someone please tell me, perferrably an official Microsoft SQL profi, i
f
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this wa
s
> the case. I restart sometimes to get the databases back online and runnin
g.
> Am I totally wrong here?|||My CTO told me that I almost destroyed several production databases by my
actions...
I thank you for your answer and thought anyways that I hadn't lost my mind.
"Narayana Vyas Kondreddi" wrote:
> No, restarting an SQL Server service shouldn't destroy databases. What mad
e
> you ask this?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in messag
e
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi, i
f
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this wa
s
> the case. I restart sometimes to get the databases back online and runnin
g.
> Am I totally wrong here?
>
>|||Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start cycl
e
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in messag
e
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
>
>|||Restart the service shouldn't be the solution. I know you are not the DBA,
but you should let your CTO know that the database needs tuning. I don't
restart SQL Server service for months, and when I do restart that'll be for
a software update or hardware maintenance etc.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:A520E89D-833C-4E0F-BD63-663C2290755A@.microsoft.com...
Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start
cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in
message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
>
>
Help! Restarting the MSSQLSERVER service.
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?No, restarting an SQL Server service shouldn't destroy databases. What made
you ask this?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?|||SQL Server is designed to go through stop/start cycles without losing any
information. There may be issues within a database that a stop/start cycle
will catch, but the process itself should not cause any problems. If you
have a very long running transaction it may take a while for the recovery
process to complete. If you have underlying hardware issues, a stop/start
can fail.
Why do you find it necessary to stop and restart SQL Server so often? I
seldom restart the service, typically only when I have to reboot the host
computer for an OS patch. I suspect that with a bit of tuning, tewaking,
and monitoring, you can get much better reliability and availability than
you have right now.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi,
> if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this
> was
> the case. I restart sometimes to get the databases back online and
> running.
> Am I totally wrong here?|||Hi
Stopping SQL Server using Control Panel or Tray Icon or EM results in an
orderly shutdown.
Starting it again then starts the process of recovery, with incomplete
transactions being rolled back. If a database is corrupt, it may detect it
during startup too and will result in the DB being marked as 'suspect'.
What do you understand as "running database". A Process runs, not a database.
If you are exeriencing intermittent problems like queries timing out, then
run sp_who2 to see what is happening, as restarting SQL Server will drop the
connections and you still do not find the casue of your blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Horner" wrote:
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?|||My CTO told me that I almost destroyed several production databases by my
actions...
I thank you for your answer and thought anyways that I hadn't lost my mind.
"Narayana Vyas Kondreddi" wrote:
> No, restarting an SQL Server service shouldn't destroy databases. What made
> you ask this?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?
>
>|||Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> > Can someone please tell me, perferrably an official Microsoft SQL profi,
> > if
> > restarting the mssqlserver service could destroy any running databases?
> > I thought that logically, this wouldn't be able to be restarted if this
> > was
> > the case. I restart sometimes to get the databases back online and
> > running.
> > Am I totally wrong here?
>
>|||Restart the service shouldn't be the solution. I know you are not the DBA,
but you should let your CTO know that the database needs tuning. I don't
restart SQL Server service for months, and when I do restart that'll be for
a software update or hardware maintenance etc.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:A520E89D-833C-4E0F-BD63-663C2290755A@.microsoft.com...
Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start
cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in
message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> > Can someone please tell me, perferrably an official Microsoft SQL profi,
> > if
> > restarting the mssqlserver service could destroy any running databases?
> > I thought that logically, this wouldn't be able to be restarted if this
> > was
> > the case. I restart sometimes to get the databases back online and
> > running.
> > Am I totally wrong here?
>
>
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?No, restarting an SQL Server service shouldn't destroy databases. What made
you ask this?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
Can someone please tell me, perferrably an official Microsoft SQL profi, if
restarting the mssqlserver service could destroy any running databases?
I thought that logically, this wouldn't be able to be restarted if this was
the case. I restart sometimes to get the databases back online and running.
Am I totally wrong here?|||SQL Server is designed to go through stop/start cycles without losing any
information. There may be issues within a database that a stop/start cycle
will catch, but the process itself should not cause any problems. If you
have a very long running transaction it may take a while for the recovery
process to complete. If you have underlying hardware issues, a stop/start
can fail.
Why do you find it necessary to stop and restart SQL Server so often? I
seldom restart the service, typically only when I have to reboot the host
computer for an OS patch. I suspect that with a bit of tuning, tewaking,
and monitoring, you can get much better reliability and availability than
you have right now.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi,
> if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this
> was
> the case. I restart sometimes to get the databases back online and
> running.
> Am I totally wrong here?|||Hi
Stopping SQL Server using Control Panel or Tray Icon or EM results in an
orderly shutdown.
Starting it again then starts the process of recovery, with incomplete
transactions being rolled back. If a database is corrupt, it may detect it
during startup too and will result in the DB being marked as 'suspect'.
What do you understand as "running database". A Process runs, not a database.
If you are exeriencing intermittent problems like queries timing out, then
run sp_who2 to see what is happening, as restarting SQL Server will drop the
connections and you still do not find the casue of your blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Horner" wrote:
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?|||My CTO told me that I almost destroyed several production databases by my
actions...
I thank you for your answer and thought anyways that I hadn't lost my mind.
"Narayana Vyas Kondreddi" wrote:
> No, restarting an SQL Server service shouldn't destroy databases. What made
> you ask this?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> Can someone please tell me, perferrably an official Microsoft SQL profi, if
> restarting the mssqlserver service could destroy any running databases?
> I thought that logically, this wouldn't be able to be restarted if this was
> the case. I restart sometimes to get the databases back online and running.
> Am I totally wrong here?
>
>|||Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> > Can someone please tell me, perferrably an official Microsoft SQL profi,
> > if
> > restarting the mssqlserver service could destroy any running databases?
> > I thought that logically, this wouldn't be able to be restarted if this
> > was
> > the case. I restart sometimes to get the databases back online and
> > running.
> > Am I totally wrong here?
>
>|||Restart the service shouldn't be the solution. I know you are not the DBA,
but you should let your CTO know that the database needs tuning. I don't
restart SQL Server service for months, and when I do restart that'll be for
a software update or hardware maintenance etc.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in message
news:A520E89D-833C-4E0F-BD63-663C2290755A@.microsoft.com...
Thanks guys for all the super quick answers!
I'm "just" a systems admin and not a database admin. Therfore me,
adminstering a site office connecting to databases in the usa, I have
restarted the service a few times to get the situation working again. You
know, when the databases seem to hang on all of the users side. It's worked
in the past for my users here in Europe and a colleague of mine in Israel.
I was just so shocked about being accused of such a terrible thing. It
caused a lot of mistrust, which is still there.....
Thanks,
Anthony Horner
"Geoff N. Hiten" wrote:
> SQL Server is designed to go through stop/start cycles without losing any
> information. There may be issues within a database that a stop/start
cycle
> will catch, but the process itself should not cause any problems. If you
> have a very long running transaction it may take a while for the recovery
> process to complete. If you have underlying hardware issues, a stop/start
> can fail.
> Why do you find it necessary to stop and restart SQL Server so often? I
> seldom restart the service, typically only when I have to reboot the host
> computer for an OS patch. I suspect that with a bit of tuning, tewaking,
> and monitoring, you can get much better reliability and availability than
> you have right now.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Anthony Horner" <AnthonyHorner@.discussions.microsoft.com> wrote in
message
> news:D46EF01C-2C08-4A89-9BE1-CB0F1E529E6D@.microsoft.com...
> > Can someone please tell me, perferrably an official Microsoft SQL profi,
> > if
> > restarting the mssqlserver service could destroy any running databases?
> > I thought that logically, this wouldn't be able to be restarted if this
> > was
> > the case. I restart sometimes to get the databases back online and
> > running.
> > Am I totally wrong here?
>
>
Monday, March 12, 2012
Help! Enterprise Manager Turns SQL Server Service Manager Off
IF someone can assist me. Everytime I load up enterprise manager the service manager turns off. And the enterprise manager can't connect to the local database. But everytime i turn it back on and try to connect again it shuts it off and around and around we go. Help would be appreciated. Thanks.
What is the service manager? What do you mean it "turns off"? What do you mean you "turn it back on"? Why are you asking about this problem on this forum?
I'm confused.
-Jamie
|||Basically my local SQL disconnects automatically whenever I try to even access my local tables. I don't know what's causing that. When i'm not trying to access it, it stays on. Otherwise it just turns off.
Help! Enterprise Manager Turns SQL Server Service Manager Off
IF someone can assist me. Everytime I load up enterprise manager the service manager turns off. And the enterprise manager can't connect to the local database. But everytime i turn it back on and try to connect again it shuts it off and around and around we go. Help would be appreciated. Thanks.
What is the service manager? What do you mean it "turns off"? What do you mean you "turn it back on"? Why are you asking about this problem on this forum?
I'm confused.
-Jamie
|||Basically my local SQL disconnects automatically whenever I try to even access my local tables. I don't know what's causing that. When i'm not trying to access it, it stays on. Otherwise it just turns off.
HELP! EM wont connect after apply sp3a,reporting services and VS.net 2003
Install SQL 2000 on a completelyu up to date windows 2003 advanced server,
then applied sp3a, then a installed reporting service developer addition,
then installed VS .net 2003 the reinstalled reporting services to get the
Report Developer.
Now I can't connect to the Server using EM.
Get SQL_alloc_handle on SQL_handle_env failed.
Tried all types of registrations.
Any suggestions short of starting from scratch?Make an ODBC trace of the connection attempt & open a case with the Webdata
team. They handle the MDAC problems with SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Will do that if re-install of tools SQL fails. How do I open a case with the
WEBdata Team.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:XWmAGmZ9DHA.704@.cpmsftngxa07.phx.gbl...
> Make an ODBC trace of the connection attempt & open a case with the
Webdata
> team. They handle the MDAC problems with SQL.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
then applied sp3a, then a installed reporting service developer addition,
then installed VS .net 2003 the reinstalled reporting services to get the
Report Developer.
Now I can't connect to the Server using EM.
Get SQL_alloc_handle on SQL_handle_env failed.
Tried all types of registrations.
Any suggestions short of starting from scratch?Make an ODBC trace of the connection attempt & open a case with the Webdata
team. They handle the MDAC problems with SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Will do that if re-install of tools SQL fails. How do I open a case with the
WEBdata Team.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:XWmAGmZ9DHA.704@.cpmsftngxa07.phx.gbl...
> Make an ODBC trace of the connection attempt & open a case with the
Webdata
> team. They handle the MDAC problems with SQL.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
Help! Databases are in "Loading" state after Restore from tape!
I run MS SQL Server 2000 (8.00.194) on a Windows 2000 Server.
After having problems with extremely large logfiles, I was told to stop the SQL Service, and delete the logs, and when I restarted the SQL Service the Server would recreate the log files. That did not happend, and the databases had "Suspect" state. After I've tried a few things found on this forum among others, I ended up restoring the db's from my last tape backup.
This was successfully (with Veritas Backup Exec), but now the databases have "Loading" state. I've searched the forums, but haven't found a solution, please help!
Thank you in advance!In Query Analyzer, issue the following command:
restore database yourdatabase with recovery
Substitute your database's name with "yourdatabase".
That should clear up the loading state. I trust you have resolved to never again take advice from the guy that told you to delete the log files?|||Originally posted by MCrowley
In Query Analyzer, issue the following command:
restore database yourdatabase with recovery
Substitute your database's name with "yourdatabase".
That should clear up the loading state. I trust you have resolved to never again take advice from the guy that told you to delete the log files?
Thanks for your answer. I tried it, and got this msg back:
Server: Msg 4331, Level 16, State 1, Line 1
The database cannot be recovered because the files have been restored to inconsistent points in time.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
:(
Yes, I will not delete them again, but I seriously need to get these databases up and running.|||As you can see, I have full backup of the databases on tape.
So If anyone could give me info on how to do a restore correctly, that will also solve my problem.
Thanks, again.|||here is a link to an article at Veritas that may help you out:
http://seer.support.veritas.com/docs/258895.htm
do you only have the backups done through the Veritas agent? or do you also have files from a SQL Server initiated backup (like from a maintenance plan)? If you have backup files you can restore those to disk and then restore through SQL Server.
It might not help now, but you might want to consider scheduling a backup to disk (either standalone or through a maintenance plan) in addition to your agent backups. I personally don't trust backups that go straight to tape, they're good to have as additional insurance, but I wouldn't rely on them alone.
If you are running 8.00.194 (RTM) you might also want to look into getting that server up to SP3a or at least to a point where you're covered against the various vulnerabilities like slammer, etc.. that is of course if your application will run correctly|||Originally posted by drew
here is a link to an article at Veritas that may help you out:
http://seer.support.veritas.com/docs/258895.htm
do you only have the backups done through the Veritas agent? or do you also have files from a SQL Server initiated backup (like from a maintenance plan)? If you have backup files you can restore those to disk and then restore through SQL Server.
It might not help now, but you might want to consider scheduling a backup to disk (either standalone or through a maintenance plan) in addition to your agent backups. I personally don't trust backups that go straight to tape, they're good to have as additional insurance, but I wouldn't rely on them alone.
If you are running 8.00.194 (RTM) you might also want to look into getting that server up to SP3a or at least to a point where you're covered against the various vulnerabilities like slammer, etc.. that is of course if your application will run correctly
Thanks for your answer, I'll look into what the article describes. I've understand that this backup isn't good enought, sp it will be changed as soon as I have my data up and running again. About the upgrade, it has not been done due to compatibility problems. Hopefully it will soon be patched with latest sp.|||You can try this code to force your database out of the loading state, but because it modifies system tables use it at your own risk:
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go
update master..sysdatabases
set status = status - 32
where name = 'YourDatabaseName' and status & 32 > 0
go
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go
If this does not work, try dropping and recreating your databases prior to running MCrowley's RESTORE DATABASE suggestion.
blindman|||Seems like the veritas article was what I needed to at least get the first backup database back :D
Thanks a lot everyone...one db to go, hopefully that's goes okay as well.
This forum is awesome. Helping a n00b who started with sql a few hours ago, thanks again!
- Everything is back and up and running from the backup.
Good night ppl!
After having problems with extremely large logfiles, I was told to stop the SQL Service, and delete the logs, and when I restarted the SQL Service the Server would recreate the log files. That did not happend, and the databases had "Suspect" state. After I've tried a few things found on this forum among others, I ended up restoring the db's from my last tape backup.
This was successfully (with Veritas Backup Exec), but now the databases have "Loading" state. I've searched the forums, but haven't found a solution, please help!
Thank you in advance!In Query Analyzer, issue the following command:
restore database yourdatabase with recovery
Substitute your database's name with "yourdatabase".
That should clear up the loading state. I trust you have resolved to never again take advice from the guy that told you to delete the log files?|||Originally posted by MCrowley
In Query Analyzer, issue the following command:
restore database yourdatabase with recovery
Substitute your database's name with "yourdatabase".
That should clear up the loading state. I trust you have resolved to never again take advice from the guy that told you to delete the log files?
Thanks for your answer. I tried it, and got this msg back:
Server: Msg 4331, Level 16, State 1, Line 1
The database cannot be recovered because the files have been restored to inconsistent points in time.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
:(
Yes, I will not delete them again, but I seriously need to get these databases up and running.|||As you can see, I have full backup of the databases on tape.
So If anyone could give me info on how to do a restore correctly, that will also solve my problem.
Thanks, again.|||here is a link to an article at Veritas that may help you out:
http://seer.support.veritas.com/docs/258895.htm
do you only have the backups done through the Veritas agent? or do you also have files from a SQL Server initiated backup (like from a maintenance plan)? If you have backup files you can restore those to disk and then restore through SQL Server.
It might not help now, but you might want to consider scheduling a backup to disk (either standalone or through a maintenance plan) in addition to your agent backups. I personally don't trust backups that go straight to tape, they're good to have as additional insurance, but I wouldn't rely on them alone.
If you are running 8.00.194 (RTM) you might also want to look into getting that server up to SP3a or at least to a point where you're covered against the various vulnerabilities like slammer, etc.. that is of course if your application will run correctly|||Originally posted by drew
here is a link to an article at Veritas that may help you out:
http://seer.support.veritas.com/docs/258895.htm
do you only have the backups done through the Veritas agent? or do you also have files from a SQL Server initiated backup (like from a maintenance plan)? If you have backup files you can restore those to disk and then restore through SQL Server.
It might not help now, but you might want to consider scheduling a backup to disk (either standalone or through a maintenance plan) in addition to your agent backups. I personally don't trust backups that go straight to tape, they're good to have as additional insurance, but I wouldn't rely on them alone.
If you are running 8.00.194 (RTM) you might also want to look into getting that server up to SP3a or at least to a point where you're covered against the various vulnerabilities like slammer, etc.. that is of course if your application will run correctly
Thanks for your answer, I'll look into what the article describes. I've understand that this backup isn't good enought, sp it will be changed as soon as I have my data up and running again. About the upgrade, it has not been done due to compatibility problems. Hopefully it will soon be patched with latest sp.|||You can try this code to force your database out of the loading state, but because it modifies system tables use it at your own risk:
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go
update master..sysdatabases
set status = status - 32
where name = 'YourDatabaseName' and status & 32 > 0
go
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go
If this does not work, try dropping and recreating your databases prior to running MCrowley's RESTORE DATABASE suggestion.
blindman|||Seems like the veritas article was what I needed to at least get the first backup database back :D
Thanks a lot everyone...one db to go, hopefully that's goes okay as well.
This forum is awesome. Helping a n00b who started with sql a few hours ago, thanks again!
- Everything is back and up and running from the backup.
Good night ppl!
Subscribe to:
Posts (Atom)