hi,
What is the right procedure for shutdown a production web sqlserver for maintenace purpose. Checking users by sp_who?
Any other monitoring for current connections? lockings?
thanks
ram
hi,
What are the steps to be followed to shutdown a production sqlserver?
(like checking user connections, termination processes etc)
IIS is connecting to the database.
Thanks
kumar
I have an auto exec stored procedure that needs to complete successfully or:
- the server should shutdown, or
- disable remote connections
Officially I cannot issue a Shutdown from a Stored Procedure. In addition, I can't see how to programatically disable remote connections.
I am just trying to find a good article on the process SQL goes through when shutting down and starting up, so far I have not found anything definitive on Google. I am assuming a checkpoint is invoked and committed transactions are written to disk, while uncommitted are rolled back, but I would like an official textual description of what happens.
View 0 Replies View RelatedCurrently i am using SQL server 2005. Since the begining i am facing sqlserver service shutdown problem. Normally it happens in every 2-3 days and sometimes happens twice a day. It halts all the banking operation. After starting MSSQL SERVER service, the system goes online.
Following is the error message recorded in the event viewer.
---------------
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
---------------
Please tell me what steps i should take to resolve it.
Thanks
Umesh Raghubanshi
Kathmandu,Nepal
I have a problem with an instance of SQL Server that refuses torespond to a shutdown request. I've managed to shutdown the SQLManager and DTC services but the sqlservr.exe process is permanentlyin a "Stopping" state.I cannot logon to the instance to issue a SHUTDOWN WITH NOWAITcommand. Short of rebooting the entire server, is there a way I canforce the process to end?Tony
View 3 Replies View RelatedSQL Server 2005 X64 Standard running on Windows Server 2003 X64 Htper Threaded Quad processors with 16 GB RAM.
I have had two production servers shut down this week due to Error 3449: SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
Previous to the shutdown I received multiple Error 3314 & 602: Could not find an entry for table or index with partition ID 491897996509184 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.
Also, Error 9001: During undoing of a logged operation in database 'msdb', an error occurred at log record ID (838:208:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Does anyone have an idea why I have begun receiving these error messages? I have run dbcc checkdb on Master and msdb and they do not show any problems.
I have SQL Server 2014 Enterprise Edition with a number of in-memory tables sitting in my database.When server is restarted it takes many hours to recover my database if there was data in these in-memory tables before shutdown.As a result, I need to clean up in-memory tables every time before server instance shutdown. This is really annoying and requires extra prescriptive actions for support team. Can I have DDL server/database level trigger to catch shutdown event and clean my data before instance goes down?
View 3 Replies View RelatedHi,
I'm from Argentina, I'm running SQL Server 2005 Express. I can connect to SQL Server via Management Studio and see all my db but I don't have acces to them !!
The system gives me the following error when trying to expand all folders:
Database 4 cannot be autostarted during server shutdown or startup. (Microsoft SQL Server, Error: 904)
Any ideas??
Thanks from Argentina
I have Configured always on Availability groups between Server 1 Primary Replica(Active), server 2 Secondary Replica(Passive) on top of WCFS...
Listener Name: AGListner( CLIENTS/APPLICATIONS connect using this Name)
Testing Scenario 1(on Virtual Servers):
I have turned network down on Server 1(primary) , then secondary server (Passive ) one came ONLINE and this is now the primary and i was able to connect to AGListner.now When i try to SHUTDOWN/POWER OFF PRIMARY(current Active server),failover happened to Secondary successfully but lost cluster and lost AGListner and was not able to connect ....now applications which are trying to connect using AGListenr name will loose all connections..does AlwaysOn Availability does not support Server SHUTDOWN/POWER OFF?or is there a way to resolve this? or am i doing wrong somewhere?
I create a job name test connection by SQL AGent
Background:
- Have server1 and server2
- The job has 3 steps:
+ Step1: check server1 is running, next step2
Server1 is shutdown or can not ping, next step3
+ Step2: do anything, for Example: run batch exe on server1
+ Step3: do anything, for Example: run batch exe on server2
Step1, i am using ping server1 command,
My problem is if server1 is shutdown, my command (ping server1) is also return true
and in the fact, job is run by follow Step1 -> Step2.
Expectation the job is run by follow step1 -> Step3
Hi,
Is there any shutdown command in sql that shutdowns the sql server with a single command or statement, as of now I am using the windows shutdown command.
thanks in advance.
bye,
Madhu.
hi friends,
i am new to sqlserver. can anyone tell me how to shutdown the database.
I have my suspicions, but I need some proof ...
Is restarting a Windows OS box without first stopping the MSSQL service comparable to a shutdown with nowait command? I know that MSSQL will recover, but wouldn't it recover more nicely if it was shutdown on its own terms prior to the box shutting down? OR. Since it's a Windows service does the OS *know* to stop the service gently, allow the engineto place checkpoints on every db and then shut down the OS?
I've googled plenty and cannot find an exact answer. Lots say it's fine to do - doesn't mean it's right ;)
Thanks!
hi,
for the last six months or so my pc has been shutting down all applications for no apparent reason when a 'low virtual memory' bubble appears. I have removed dozens of items, such as games, image editos; all programmes that require a lot of memory but it is no good. Every 40mins or so the pc decides to shut everything down and where it is impossible to start any further applications, unless I log off and on or shut down the pc myself. I really am fed up with this, its so annoying. Is it because of a virus or do I still have too much on my pc?
insaneolly
I have an SSIS package that looks for the existence of a file (using Konesans file watcher) on the network to begin processing. I am trying to find a way to terminate/shutdown the entire process if that file is not out there by say 7:30 AM. I think I could set a timeout on the filewatcher task, but was hoping there was another tool I could use. (If the process had to be restarted late a timeout would still countdown the same period wouldn't it? We'd still need the job to stop by 7:30.)
More background. The package, once deployed, will be called from a command line script that is started by a scheduled task normally, or manually in the event of late availability.
Thanks,
Rog
I want to install SP2 for SQL 2012, but I am not sure how my instance would be affected.
I want to know if the instance will go down for any amount of time during the update? I can schedule a server reboot after hours, but don't want to apply the service pack if it will take the instance offline at any point.
While working on my Visual C# 2005 Express programs, I will sometimes open SQL 2005 Express to look at the database. Then I will disconnect the database and exit the SQL Server Management Studio Express.
Then when I run my Visual C# 2005 program the next time, I will always get this error message.
"Cannot open user default database. Login failed...."
Here is a copy of my connection string
string cs = "Data Source=.\SQLEXPRESS;AttachDBFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\hdcTemperatureCableReadings.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True";
string strSelect = "Select * from CableReading";
SqlConnection tcrConn = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Select * from CableReading", tcrConn);
tcrConn.Open();
The error is being generated when "tcrConn.Open()" is being executed. This is line 27 of my program.
My question is: Am I shutting SQL 2005 Express down wrong when I go in and look at the database. The SQL 2005 Express is located on the same computer that I am writing my program on.
I copied the error message to the clipboard and copied into this thread.
System.Data.SqlClient.SqlException was unhandled
Message="Cannot open user default database. Login failed.
Login failed for user 'HDC\jfeeney'."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=11
LineNumber=65536
Number=4064
Procedure=""
Server="\\.\pipe\8A52A6B8-493D-45\tsql\query"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at WindowsFormsApplication1.Form1.button1_Click(Object sender, EventArgs e) in C: empTestCableReadingTestCableReadingForm1.cs:line 27
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at WindowsFormsApplication1.Program.Main() in C: empTestCableReadingTestCableReadingProgram.cs:line 18
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
Hello:I am doing this small experiment on SQL Injection, and appearantly, I was asked to do the shutdown thing, which I have read about, but don't have a single idea how how to start.Well, basically, I am still confused about a few things:1. Do I need to create a stored procedure first, before I start hacking (shutting down the SQL Server)? Or can I just use a normal User Table?2. I understand that the clause "shutdown with nowait" only allows the sysadmin and serveradmin to do so, so is there anyway a hacker makes himself a sysadmin or serveradmin?3. And I read that only with the login user: "sa", you can perform that action. But in my company, its sql login is not "sa", it's something else, so can I do anything about it?Well, that's all for now. So, actually, I have a project that first requires the user to login and must provide password. (Since I am doing experiment on SQL Injection, thus, it is vunerable). It connects to the sql server to match if the user name entered exists on the table.I hope this information is enough to help anyone to help me hahahahaha.... crossed my finger, though.Thanks in advance.
View 8 Replies View RelatedGot this situation, trying to do Use SignleMode to recover my handing db, after that lost ldf (and physically too). Tried all things thru SSMS and scripts (below) that I know with no result, is there anything else I can try to recover it, I don't need log file.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not open new database 'MyLostDB'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:xxxMyLostDB.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Server, Error: 1813)
EXEC sp_attach_single_file_db @dbname='Commissions',
@physname=N'C:SQLDataMyLostDB.mdf'
GO
CREATE DATABASE Commissions ON
(FILENAME = N'C:SQLDataMyLostDB.mdf')
FOR ATTACH_REBUILD_LOG
GO
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies View RelatedI currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
View 1 Replies View RelatedProduction and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedAny help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
View 2 Replies View Related
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
ok ok, stop laughing. for real, is there any programatic way of doing this? whom ever created this database i inherited (SQL 2000) created the LDF and DATA files on the same drive and in the same folder for that matter. just trying to do a little disaster magament.
thanks
matt
This feels like a silly question, but I`m going to ask it anyway...
I have limited SQL Server experience, but have run into a wall with a client`s Web/Access combination. I need to upgrade to SQL Server. I have Beta3 installed on a development box and am very happy with it. Is anyone running this thing in a production environment? This isn`t going to be experience huge loads, so I`m tempted. Tell me if I`m crazy for wanting to try it.
I have a brand new database server with system databases.
I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate
1.First issue
The port number already allocated was 2059. We have changed that into the default port 1433 in the node. Now we are able to connect the node from the client application. But we are not able to see the configuration manager in any of the environment now.
2. 2nd issue
Cluster Environment
Error while executing the Package The connection details are not loaded in the connection manager tag
The same error with advanced information.
Another error window
R.S.Moorthy
I have production server 2000. The server gets disconnected sometime by itself and sometime it is working fine.Sometimes it even doesn't get restarted. Is there any problem with service packs and some performance issues.
Can you SQL guru give me best suggestion and how should i proceed.
Hi,I ran test data on my development machine and it took 1 minute toinsert the data. Ran the same set of data on the server and took 5minutes.Check both database and everything is the same. I even copied theproduction DB on my machine and it was taking still about 1 minute.Look at the fragmentation, and all the numbers are better on the serverthan my development machine so it should be faster.In the application I put some timer and discover that the insert istaking 0.015 ms on the server and 0 on the development. So the problemis on the insert.It is a Web application using ASP.NET.Here are the spec of the computers:Development: P4 HT 3.2GHz 1gig memory running WIN XPServer: Xeon 2.8GHz 1.5Gig memory running WIN 2000 serverAny idea how I to pinpoint the problem? I'm not at the point ofthinking that it can be the hardware, but how to verify that?ThanksFrank
View 2 Replies View RelatedHi,
Presently our Organisation is in process of Implementing SOX.
Under Compliance all the User have to be removed from the Production Server.
1.My Question is Do a DBA Should have Admin Privilages on the Production Server.
If yes then what are the Actitvies that a DBA has to perform only if DBA have admin privilages.
Regards
Sufian
Like most enterprise there is the database administrator (dba) and there are the developers(dev). The dba are conservative while the developers are also exploring their options.
One of the current usage I'm experimenting on is to provide data visualization - image for the data. Like most I needed to "create" the System.Drawing assembly in the database, marking it unsafe.
During my testing, my code had some exception and that brought down SQL Server.
I read that the CLR is better compare to the sp_OAs as well as the extended stored procedures written in C++ because it isolates the execution in a separate app domain and termination is clean - in case of any errors, it should not bring down SQL Server.
Also I read marking assembly unsafe void these benefits of isolation.
Instead of having to manage the situation where it involves code review by the dba and asking the dba to take some risk, is there a technique where all CLR code that runs in the production server does not pose stability issues.