Recovery On User Database.
Aug 19, 1999
Hi I'm a newbie with a problem.
SQL Server 6.5 SP4. Have had problems today with SQL Server running out of LOCKS. We increased these & all seemed fine. LOCKS ranout again & we brought SQL Server back up our major database started a recovery. I know the spid of the recovery from the SQL Server errorlog but when I select the sysprocesses table I don't find an entry for this spid. The recovery has been running for about 2 hours. My question is should I see the spid of the recovery process in the sysprocesses table & is 2 hours long for a recovery of a 5 GB database?
Thanks for any help in advance.
Cheers,
Phil
View 1 Replies
ADVERTISEMENT
Apr 17, 2001
Hi
Yesterday a user has run a storedprocedure which
updates a very transaction table having million of
records , as usual the user killed the process in the
middle of the execution.(which has resulted in rollback
action).
this rollback process blocked other scheduled jobs and
its was running for more than 8 hours without completing
as usual decided to shutdown and restart the sqlserver
while restarting the sql server went into automatic recovery
the following are the log recording
2001-04-17 12:00:01.86 spid9 Recovery progress on database 'ISH' (7): 48 percent.
2001-04-17 12:00:37.89 spid6 Using 'xpstar.dll' version '1998.11.13' to execute extended stored procedure 'sp_MSgetversion'.
2001-04-17 12:02:36.60 spid9 Recovery progress on database 'ISH' (7): 49 percent.
2001-04-17 12:08:12.79 spid9 Recovery progress on database 'ISH' (7): 49 percent.
2001-04-17 12:11:49.90 spid9 Recovery progress on database 'ISH' (7): 50 percent.
2001-04-17 12:11:50.32 spid9 Recovery progress on database 'ISH' (7): 92 percent.
if you see the log reading from 50% jumps to 92 % and the automatic recovery
process is running from another 5 hours (now not able to open the database)
in order to avoid the sql server going into automatic recovery mode (usually the dba use to update the master.sysdatabase table the status column with some
magic number to set the database into emergency mode) but i followed my own style (deleting the reocord for the user database from sysdatabases)
and restarted the sql server , and used my monday tape backup (complete bkup) to recover the database. In the recovery option i used force restore over existing database (hoping that this will reduce the database recovery time ,bcaus the data file (mdf and ldf ) need not to be created.
but the result :- after running six hours of loading data from tape ( i am getting the following reading in the log file)
2001-04-17 16:15:58.40 spid7 Starting up database 'ISH'.
2001-04-17 16:15:58.40 spid7 Opening file F:DATABASEISH.mdf.
2001-04-17 16:15:58.46 spid7 Opening file F:DatabaseISH_1.mdf.
2001-04-17 16:15:58.48 spid7 Opening file G:Databaseish_5_data_Data.NDF.
2001-04-17 16:15:58.49 spid7 Opening file G:Databasesrv_fg_data1.NDF.
2001-04-17 16:15:58.49 spid7 Opening file G:Databasesrv_fg_data3.NDF.
2001-04-17 16:15:58.53 spid7 Opening file G:Databasesrvindex_fg_data1.NDF.
2001-04-17 16:15:58.54 spid7 Opening file F:DATABASEISH_3.mdf.
2001-04-17 16:15:58.57 spid7 Opening file F:databaseISH_4.mdf.
2001-04-17 16:15:58.59 spid7 Opening file F:databaseISH_2.mdf.
2001-04-17 16:15:58.60 spid7 Opening file G:Databasesrv_fg_data2.NDF.
2001-04-17 16:15:58.62 spid7 Opening file G:DATABASEISH_log.ldf.
2001-04-17 16:15:58.64 spid7 Opening file G:databaseISH_log_1.ldf.
2001-04-17 16:15:58.65 spid7 Bypassing recovery for database 'ISH' because it is marked IN LOAD.
realy do not what is happening in the background.(if its oracle the dba has
the control over the database . In sql server has the control over the dba)
any one who has any idea on sql server recovery (the size of the database is
22 gb which is recovered and tape device is hp sure store DAT 40 E (DSS-4,4MM TAPE). Thanks in advance
Have a good day (not for me laaa)
Sethu
View 1 Replies
View Related
Sep 17, 2015
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies
View Related
Nov 1, 2015
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
View 2 Replies
View Related
May 12, 2015
I'm trying to execute backup job on secondary replica on clustered server(with HA) and I getting the error:execution failed see the maintenance plan and sql server agent job history logs for details.When I going to see the log, it says :configuration option 'user options' changed from 0 to 0.
View 3 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Jun 22, 2015
How to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution
        2. Audit logins off/on is also not good solution.
       3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
View 5 Replies
View Related
Jul 9, 2014
Last week I backed up my SQL Server by using BE 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.what causes this?
View 7 Replies
View Related
Nov 30, 2000
Running SQL version 7 on "Terminal" Server I have a database marked in Load Receovery and we are unable to get to it. We tried MS SQL document on how to but we are still unable to recover it!
View 4 Replies
View Related
Oct 4, 2007
hai!
i have a database which crashed recently(26th sep 2007).my last backup is on 14 sep 2007.i have restored database after two days.Now i have a database (old) with data upto 14sep 2007 and from 26sep to till date.(data from 14sep to 26sep missing).I restored database on 28th with a new database name which has data upto 26th sep 2007.how to attach these two databases to a single database to have data from beginning to tilldate.
please help in this regard.
View 1 Replies
View Related
Jan 22, 2008
Hi,
I made an UPDATE query without specifying any criteria so I had more than 1500 records have been changed and I cannot undo the UPDATE.
Is there any way I can recover the data UPDATE from the Transactions Log.
I did not back up the database lately so what is the appropriate solutions for this?
Any thoughts… :S
View 10 Replies
View Related
Mar 10, 2004
Hi,
Is there a way to Revover a Sql Server 2000 database in the absence of the backup file(the backup file got overwritten). Does Sql Server do any automatic Transaction logging?
Thanks for any input
View 4 Replies
View Related
May 23, 2008
Hai friends....
SQL Server 2005 Database, So I want to know about Database Backup and Pointing Recovery concepts..so can u please help me and any send me related documents...
---
Thanks,
Nageswar.V
New Horizons Cybersoft Ltd
+919848854114 : nageswar@nhclindia.com
View 2 Replies
View Related
Jul 23, 2005
Hi,I'm no where close to a SQL Server expert but we're using it as a back endto another application.I lost a PC that had MS SQL Server 2000. I was able to recover the .MDF and..LDF files but nothing else. How can I restore these files to another SQLServer installation on another PC. When I tried to attach them they are notrecognized as valid files (I get the red X instead of the green CHECK).Is this a basic security function that I'm not going to get around or shouldit be possible? Do the .mdf and .ldf files contain specific info about whatPC they were created on?Are there third party apps that might be able to at least extract thetables?Thanks,WH
View 1 Replies
View Related
Jul 15, 2007
Hi all
When I restart server my database apear as database(In recovery )
what's ???
thanks
View 3 Replies
View Related
Jul 30, 2007
Hello all,
Is there a way to check the status of a database in recovery. Like how far along it may be. If not is there a way to stop a database in recovery and just drop it.
Thanks in advance,
Mike
View 7 Replies
View Related
Mar 30, 2007
Our server ran out of disk space during a stored procedure process. We were able to free up some space, stopped all SQL services, restarted all SQL services and now SQL Management Studio shows the database to be in recovery. Is there a way to monitor this to ensure progress is being made and to estimate when it might complete?
View 12 Replies
View Related
Aug 9, 2007
What does it actually mean when the database is in recovery? My database has been in recovery overnight. What is normally going on in this situation. Should I wait or take more drastic action?
View 10 Replies
View Related
Aug 28, 2007
Hi all,
I had to change the path of .mdf and .ldf files, so I decided to:
1) Take offline the database
2) run the quey ALTER DATABASE... MODIFY to change the path
3) Bring online the database.
The last step hung up (with no errors) and left the database In Recovery.
When I tried to stop and restart sql server other databases changed their status In Recovery...
Here is a dump of Errorlog files
2007-08-26 18:13:29.28 spid24s Starting up database 'DbOrdini'.
...
...
2007-08-26 18:13:30.09 spid24s * BEGIN STACK DUMP:
2007-08-26 18:13:30.09 spid24s * 08/26/07 18:13:30 spid 24
2007-08-26 18:13:30.09 spid24s *
2007-08-26 18:13:30.09 spid24s * Location: "logmgr.cpp":5334
2007-08-26 18:13:30.09 spid24s * Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
2007-08-26 18:13:30.09 spid24s * SPID: 24
2007-08-26 18:13:30.09 spid24s * Process ID: 1380
.....
.....
2007-08-26 18:13:30.40 spid24s Error: 17066, Severity: 16, State: 1.
2007-08-26 18:13:30.40 spid24s SQL Server Assertion: File: <"logmgr.cpp">, line=5334 Failed Assertion = '!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2007-08-26 18:13:30.40 spid24s Error: 3624, Severity: 20, State: 1.
2007-08-26 18:13:30.40 spid24s A system assertion check has failed. Check the SQL Server error log for details
Could it be dangerous trying to kill this process ? If not, what is the best way do to it ? From Sql Server Activity Monitor (spid 24) or from Task Manager ?
Thanks in advance
View 9 Replies
View Related
Nov 30, 2007
Hi there,
I want to create database login and set that login to owner of the database? Can anyone help me?
thanks
View 3 Replies
View Related
Aug 17, 2015
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies
View Related
Jan 24, 2001
Hi everyone,
I am trying to implement a plan for a SQL7 database that is used by the company I work for. Having read the SQL manuals I have come to the following conclusion:
I want to do a complete backup of the database at midnight and do a transaction log backup every 30 minutes between 7am and 7pm. I am hoping this will allow me to 'undo' any user mistakes and roll back the database changes to the last transaction log backup.
The questions I have are:
1) By backing up the transaction log every 30 minutes, will this then give me the ability to 'roll back' the database to the last time the transaction log was backed up (i.e. 30 minutes max) ?
2) How do I load the transaction log back in ? (i.e roll the database changes back). A simple Restore operation (with truncate ???)
3)Am I totally confused or on the right track ?
Thanks,
Tim
View 1 Replies
View Related
Mar 12, 1999
Just curious, Has anyone had to do a full recovery and have it take 20+ hours?? If anyone can give me their average times for full recovery and database size, I would appreciate it. I'm researching this topic. Thanks.
View 3 Replies
View Related
Jun 7, 2004
I cancelled an INSERT operation on a database. Now I get:
"Recovery of database 'BounceRepository' (6) is 0% complete (approximately 2024654 more seconds)"
That is another 23 days of straight recover time!!! The original INSERT only ran for about 30 hours.
In Windows Performance Monitor, I can see that the system isn't disk or CPU limited. What is the server doing? How can I make it go faster?
View 14 Replies
View Related
Feb 8, 2008
Hi All,
I've recently restored one of my development databases from it's production backup. The restore completed successfully, users were able to connect to the database without any problems. However, when I checked the error log I found the following message:
Bypassing recovery for database 'Test' because it is marked IN LOAD.
I could not find anything on the web or in BOL. Any ideas?
Thanks.
View 3 Replies
View Related
Oct 3, 2007
hi
i was deleted a record from my database, but i want to recover my deleted record via log file, how to do ?
thanks
View 6 Replies
View Related
Jul 20, 2005
MS SQL-Server 7.0Bypassing recovery for database 'EfW_765' because it is marked IN LOAD.What does this mean?Our customer is backing up is maindatabase and is recovering it to thisdatabase for testing.Our custumer tries it serveral times and then the recovery works and thedata are corrupt.I have written a little programm which does some selects to this database.The program is stopped during recovery but our customer beleves that thishas something to do with our program.http://support.microsoft.com/defaul...kb;en-us;272683This article doesnt halp me much. So hwat is the meaning of IN LOAD and whatdoes the server do if it bypasses the recovery.THXJens
View 2 Replies
View Related
Aug 6, 2015
i've a task to creating ER diagram to stream database.can i have the sample diagram. should i provide you the structure of database.
View 2 Replies
View Related
Dec 19, 2006
Hi,
If we delete a Database accidentally (no backup exists), Is it possible to recover. How?
Thanks
ven--
View 4 Replies
View Related
Mar 3, 2008
Cannot open user default database. Login failed.Login failed for user
'NT AUTHORITYNETWORK SERVICE'. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Cannot open user default database. Login
failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
View 3 Replies
View Related
Apr 25, 2007
Hi, I'm new to SQL Express 2005.
I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue.
I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....".
Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively.
My connection string is:
"Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"
I would be grateful if you can help me
View 1 Replies
View Related
Dec 23, 2014
is bulk logged recovery model support point in time recovery
View 9 Replies
View Related
Jul 21, 2015
Running a report on sqlserver RS 2008 R2 that uses a data source that looks at a database on a sql express 2012 server.
I can run the report in preview mode from bits on the sql 2008 r2 server.
I have tested the connection of the  deployed data source on the sql 2008 R2 reporting services web page and connection has been successful.
I can logon to sql express 2012 using  management studio and logon as the user and access the database run stored procedures etc.
windows server 2003, sql server 2008 R2 reporting services server.
windows server 2012 sql express 2012 data source database location.
View 13 Replies
View Related
