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 RepliesI 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.
Got 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
In my SQL Server Errorlog, I see the below error. The system has 8 GB of RAM with enough free RAM, something I can do to prevent this alert? (Note: I have no MIN/MAX memory set on this Instance)
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 76896, committed (KB): 167628, memory utilization: 45%.
i am setting up a process for the dataload, external windows service insert a record to dbo.dataload table with a status as 'Ready'
create table dbo.dataload
(Xmlcol xml
,Loadstatus varchar(15)
,Starttime datetime
,completedtime datetime)
now i use a SQL job to check the status column, pick xml and load to appropriate tables. instead of SQL job can i use a trigger whenever there is a insert it fires sp to load data would there be any disadvantage.
I am attempting to do a rather simple purge task on a very large table. This task will need to take place daily and delete records older than 6 months out of the database. On first pass this will delete well over 130 million rows. I thought the best way to handle this is create a proc and call the proc from a SQL Agent Job that runs nightly. Here is an example of the script:
CREATE PROCEDURE usp_Purge_WCFLogger
AS
SET NOCOUNT ON
EXEC sp_rename 'dbo.logs', 'logs_work'
GO
SELECT * INTO dbo.Logs_Backup FROM dbo.Logs_Work WHERE TIMESTAMP < DATEADD(month, -6, GETDATE())
[Code] .....
I have stored procedure .In SP i am using cursur to load data from Parent to several child table.
I have attached the script with this message.
And my problem is how to use direct select and insert or load to speedup the process instead of cursor.
USE [IconicMarketing]
GO
/****** Object: StoredProcedure [dbo].[SP_DMS_INVENTORY] Script Date: 3/6/2015 3:34:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
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
Currently 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
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
I am looking to create an upload process that will check for an Excel in a specific directly, if exists, upload the file and populate the database. I have never done this before (only data extracts). How to do the upload process, is this something that can be done with SQL Server 2012?
View 2 Replies View RelatedWe upgraded QA and production to sql server 2012 last year ( in place) leaving the user databases at sql 2005 ( 90 ). A few months ago the QA user databases were set to sql 2012 compatibility mode. Management is worried about upgrading production and wants to know if we can quickly roll back.
I want to confirm that we can roll back using the same command, and if dbcc freeproccache can be used to avoid having to update all statistics.
ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 90
This works fine in QA on my own test user database. No errors.
I am trying to use SQL Server 2012 for the first time. I am running Win 8.1. I create a local website and sample webpage using Visual Studio 2012 for Web. Running this page on my localhost, when it attempts to create a new SQL database this exception is thrown:
"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."
Our log reader process is throwing an access violation error every 10 minutes or so. It runs fine for a bit then fails. Below is, I think, the important info from a stack dump analysis. We are running SQL 2012 SP1 64 bit. I think we can either install SP2 or open a case with Microsoft.
0:000> kC 1000
Call Site
sqlmin!ReplArticleCmd::updated_column
sqlmin!ReplArticleCmd::AddSupdateParams
sqlmin!ReplArticleCmd::GenerateSUpdateProcedure
sqlmin!ResolveUpdateCommand
sqlmin!ReplTranInfo::ProcessInsert
[Code] ....
I can see that the sp_replcmds SP is running when this happens.
* BEGIN STACK DUMP:
* 07/15/15 12:37:39 spid 81
*
*
* Exception Address = 000007F988FCA480 Module(sqlmin+0000000000C4A480)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000000
[Code] ....
I have an execute process task in SSIS that executes a .Net program.When this is running as a Sql Server Agent job in Sql Server 2005, I have no issues. The operating system of the server is Windows Server 2003 R2.
Now I'm trying to get this working as a Sql Server Agent job in Sql Server 2012. The operating system of the server is Windows Server 2012 R2. And now I have issues.
If I run the SSIS package in Visual Studio debug mode, I have no issues! It's only when I try running the same package from a Sql Server Agent job that there are problems. The .Net program errors out with a System.TypeInitializationException.
I thought this might be an issue running the .Net program as 64 bit so I changed the agent setting to 32 bit. That didn't work. I thought it might be an issue with authorization (since I can run it in debug with me as the user). I put in a class in the program that changes the user through impersonation. That didn't work either.
Since I don't have a full-blown version of Visual Studio on the database server, I haven't been able to capture the error and debug the program to see where it is blowing up.
why something like this would work in Sql Server 2005/Windows 2003 but not in Sql Server 2012/Windows 2012 when run as an agent job? And on top of that, why would it work when I run it manually in debug mode but not when run as an agent job?
I need find out the number of columns in flat file before i process that particular file.
I have file name in @filename variable and file path is @filepath variable.
But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And I am using for each loop container to read the file one by one and put the file name in @filename variable.
and my file name like
Product_20120607060930.txt
Product_20130708060930.txt
My file structure is:
ID,Name,City,Country,Phone
1,Riya,Pune,India,454564
2,Jiya,New Jersey,India,454564
3,Riya,St Louis,USA,454564
4,Riya,Belleville,USA,454564
5,Riya,Miami,USA,454564
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file. if it is not there then i have to send mail to client saying that file is not valid.
Let me know how i will do it.I need to also calculate the size of flat file.
I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A.
How Can I achieve this....
A project I'm working on consists of a Main stored procedure which then runs about 30 nested procedures. The client wants to know when a certain nested SP fails, but wihtout rollbacks, as they may want to fix a data item manually (such as a missing Patient ID, that they have to call someone about). At this point, we don't want to roll back anything but halt the rest of the nested SP's and send out an email to someone that they have to check out a missing PatientID.
I'm wondering if an SSIS package would handle this better than just using a Stored Procedure. When that SP runs, it will also update a "Process tracking" table in the backend, that would update [Lastprocessran] with a number. I'm thinking that if they run the main SP again, after making a manual correction, that they could re-run the main SP, and it would bypass any step that already ran successfully based upon the [Lastprocessrun] number.
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.
Now I have a different constellation: Integration Services run on one server, in version 2014, the Analysis Services instance to process the cube database on runs on another server, version 2012.I tried several different combinations of SSIS version and Analysis Management Objects version, and got several errors while running the process package (e.g. object reference not set to an instance of an object, cannot find AnalyisServices.dll..)
Is this combination 2014/2012 possible at all?I assume the BIDS version has to be for SQL Server 2014, as I want to run SSIS packages on a 2014 server, is that correct? Does it matter at all, can I also deploy 2012 packages?Which version of Analysis Management Objects do I have to use? I assumed I have to use version 11.0 here, because I want to process a 2012 cube?If it is possible to use the "old" 11.0 version of AMO, do I have to do anything so that it can be found by the SSIS package running on the server (it was built on my local computer, there I have all SQL Server versions from 2005 to 2014 installed in parallel), or do I just have to copy it to the appropriate SQL Server folder?
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'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
this my sql script
use [master]Go
-- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO
-- Switching to our databaseuse [DatabaseName]GO
CREATE SCHEMA schemaname AUTHORIZATION usernameGO
ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO
/* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter'
-- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter]
-- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber]
-- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
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 Related