URGENT - Backup Hanging
Jan 29, 2001
My overnight backups hang occasionally. I say this because there is no consistancy to when the routine will hang. It happens on the first step where it reindexes. The only user database on the server is about 12GB. The server is dedicated to SQL so nothing else runs on it. It has 2x 800 MHz processors with 2 G of ram of which 1.5 is allocated to SQL.
I can't trace the error because it happens before the report is written to. There are a few tables with a million plus records that take up almost a 1G per table.
It was my understanding that if users were in the database they would notice a big performance loss but that the backups would still run. Could it dying because of resource contention? I'm at a loss and really could use some input. I can't afford to go a whole weekend without a backup.
Is there a way for me to progammatically check who is the database prior to the job kicking off?
Any ideas as to what would cause an inconsistant hang of job is greatly appreicated.
Thanks,
Kelsey
View 1 Replies
ADVERTISEMENT
Apr 4, 2001
I'm running 7.0 sp2,windows 2000, 1gig of ram, and a 933mhz cpu. Server has been very stable with no problems until I moved a 2gig table into the database. Query performance is excellent even table scans take less than 2 minutes. The problem is that once a table scan is performed on the table (I can't index for every possible query) the query finishes but the enterprise manager freezes on the server and users can no longer connect. I've set SQL server to have only 650 MB of ram and the rest is free, problem also existed when the memory was controlled entirely by SQL server.
My cache Hit Ratio was 97% and Cache flushes 0.0 (unfortunately these can't be checked when the problem exist because the box is frozen). I may have a concurrency issue but I'm not sure how to be positive. I don't want to just throw memory at the problem because I'm not sure the problem will be fixed.
Thanks for any help, Dave
View 2 Replies
View Related
Aug 23, 2001
Hi,
On production server, I have two tasks in status "performing completion action" for last couple hours. Ususally these tasks take seconds to finish.
Do you know how can I kill or cancel these tasks? I tried to stop both the jobs couple times but nothing is happening.
There are no locks/blocks on current activity window.
Please give your suggestion.
Thanks.
View 2 Replies
View Related
Nov 5, 2002
Hi,
My transaction log backup task for the production database which normally takes about 10 seconds has been running for almost about 2 hours now.
Is there any way to stop it without restarting sql services? The scheduled task was stopped from the jobs but spid is still in 'runnable' status. I should not kill 'backup log' or 'xp_sqlmaint' tasks.( It doesn't solve the problem anyway. the SPID goes in rollback status and stays like that until server is rebooted). Any way to find out what causes this problem and how to prevent it?
Thanks,
Shaili
View 2 Replies
View Related
Oct 4, 2007
The backup jobs on one of our sql servers started hanging. The job appears to be complete because the backups are on the disk but the job never completes when viewed in the activity manager. sqlmaint.exe had numerous instances running all night long but the job never completes.
sql server 2000 standard edition sp3a
Windows server 2000
The jobs on this server have run successfully for well over a year and just recently they started hanging. We've done everything short of reboot the server. We've restarted the agent, shut down the sqlmaint.exe but alas nothing we do lets the job complete.
Other jobs that are not back up related are working as they should.
There is plenty of disk space.
Any ideas?
thanks in advance because we are lost as to the cause and resolution.
View 1 Replies
View Related
Dec 7, 2001
How do we know the list of all the backups that are present in a particualr backup device like file# and time of backup taken etc for the purose of restore?
When I run :
RESTORE HEADERONLY FROM BackupDeviceName
it doesn't give that info. Can anyone tell me the exact command to list all the files of a backup device so that I can restore the right one??
Thanks.
Shalini.
View 2 Replies
View Related
Aug 20, 2001
Intially my backup was working fine since couple of days it started failing, when i checked the backup through maintainance plan i saw that it does'nt recognize the mapped drive it says invalid drive.
Basically i want to take backup of the on database A from Server A to Server B which i mapped. G:oppxyz00t142linktest.
Both Sql server and Agent use the system account => kavitha is the user account through which i login hence i think this is the system user name, i have been give full access to the mapped drive.
I appreciate your help.
Kavitha
View 1 Replies
View Related
Aug 22, 2001
When i tried to take a backup on a remote server i got this error.Not only this master all the user tables and other system databases also failed,the also give the same error.
Database master: Database Backup...
Destination: [dgssmf01q001ackupmastermaster_db_200108220401.BA K]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on 'dgssmf01q001ackupmastermaster_db_200108220401.B AK' failed, status = 5. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
could any one tell me what might have caused it and how do i go about to solve this.
Thanks for your precious help.
Kavitha
View 1 Replies
View Related
Mar 27, 2001
Hi,
Schedule backup job which usually completes in 1 to 2 minutes, it's been running for over three hours. Meanwhile transact log backup job executed and it's hanging also.
I stopped the jobs, which now shows last run status 'cancelled'. But the spid still exeists in 'current activity' pane with status 'runnable'.
No locks or blocks!
Is there anyway I can get rid of this spids so that I can start backup job again?
It's very urgent..
THanks
View 4 Replies
View Related
Aug 15, 2000
My database backup which was scheduled to run every day at 2A.M is still running, I issued kill command for the process at 10.45A.M , rollback process is still running for the last two hours. Can anyone help me out how to solve this problem.
Thankx
VS
View 1 Replies
View Related
Jan 22, 2001
Can I backup a db which is on server1 by sitting at server2 by way of running a script on server2.
Thanks.
View 2 Replies
View Related
Feb 7, 2000
Hi,
What do I do if transaction log is full ?????
Can I automate the truncation of the transaction log????
Bindu
View 3 Replies
View Related
Jun 22, 2001
By mistake i restored a database to different one.
It actullay overwrote it.
Is it possible to roll it back to the state before i restored.
I need your answer as soon as possible
View 1 Replies
View Related
Apr 29, 2007
I have got a recent backup problem that just does not make sense. The system is able to perform hourly backups of my transaction logs fine but my daily backups of the entire database which occurs every night now fails. I tried recreating my daily backup job all over again from scratch and the job still fails.
When I perform a manual backup of the database however the system works fine.
View 6 Replies
View Related
Feb 18, 2004
Hi All,
Sql server 7.0
i have a db ABC and want to restore it as ABCbak, I am taking the backup of the db ABC and restoring it on ABCbak, i am also seeing that the physical names are different but logical names remains the same. Will this cause any kind of problem to db ABC.
Waiting for reply.
TIA
Adil
View 2 Replies
View Related
Jun 8, 2006
I have installed Beta 2 over SQL Express and when i wanted to install either Beta 2 or Express it dosn't allow e and i need to backup my databases urgently?
how can i do that?
is it possibe to do it with the phisyical files?
View 8 Replies
View Related
Mar 20, 2002
Hi
Is there a way to restore only one table from a DB backup.
I need restore one table not database because I have update others tables in this database.
thanks in advance
kinds regards
Abdel
View 1 Replies
View Related
Aug 28, 2001
Hi Every one,
According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
Thanks for your help.
Kavitha
View 1 Replies
View Related
Aug 29, 2001
Hi Every one,
According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup...
Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
As Said by someone i took a full backup...tooooo it does not solve the issue.
Thanks for your help.
Kavitha
View 6 Replies
View Related
Sep 25, 2001
Hi,
I have Sqlserver 7.0 cluster configured with Omniback 3.5. My backups were fine till yesterday.We moved the database to another node and bacups are failing with error in sql server error log.
backupVirtualDeviceset::Intialize:open failure on backup device 'Omniback_II_msdb' operating system error -2147024894(The system cannot find file specified)
I greatly appreciate if any one can help to solve this
thanks
Mohan
View 2 Replies
View Related
Jun 28, 2000
Hi All,
I am trying to restore transaction log using T_SQL:
restore log transfer from transfer_log with file = 2 ,norecovery
and also I was Trying--
restore log transfer from disk = 'c:mssql7ackupransfer_log.dat' with file = 2,norecovery
I am getting this error Message-
Server: Msg 4306, Level 16, State 2, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Any Ideas,Please Let me Know ASAP
Thank You ALL--
View 3 Replies
View Related
Nov 28, 2000
Hi Everybody,
Our branch office in Europe has sent full 'backup' file of capacity 25GB.
I have to restore this backup to our database. Their database is 'Candidate'
and our database is 'client'. Both these databases have different logical
names and physical names.
The following Batch script only I hv executed,
RESTORE DATABASE Client
FROM DISK = 'd:dumppaw01dump.bak'
WITH MOVE 'candidatedata' TO 'e:mssqldataclient_data.mdf',
MOVE 'candidatelog' TO 'd:mssqllogclient_log.ldf',
REPLACE
Can anybody tell me, what I have executed above is correct or not. Now
restoration is going on for more than an hr. Still it is going on. Is there
any other way to restore this backup set very fastly.
Our server has got very good hardware setup. Good amount of hard disk space.
4GB RAM Memory. Running 4 processors. No other service is running apart from
SQL Server. Right now nobody is accessing the server.
How long it will take to restore this backupset?. This is urgent please.
Any help would be appreciated.
tks in advance,
Sri
View 2 Replies
View Related
Oct 12, 2000
hi,
my Backup job, took a backup at 3.00am , backup went well, but this job is still running , now the time is 10.00am.
I tried to kill this job but it is still running.
Why my process still running after taking a backup also.
Can anyone suggest me, how i have to resolve this.
Thanks!
--Ram
View 2 Replies
View Related
Sep 18, 2001
There is a database located at remote location we wanted the same on the local server..so we backed it up then restored the same on the local server everything went successfull.
When i tried to create user it says the user is already existing, but nither can i see the user nor drop the one..(ofcourse not existing) it gives me an error 15023.
The other problem is that when i check the storedprocedures i can see and open them when i try to look for permissions or assign it by clicking permissions tab it says the specific stored procedure doesnot exist and gives me a sql-dmo error 21770.
I donno what do..help would be appreciated.
Mark
View 1 Replies
View Related
Nov 24, 2000
Hello,
I'm in trouble, I have just dropped a table from a sql dbase and now need to recreate it. I have no backup and I am panicking!!! I need to know if I can recrreate them .
If this can't be done let me know I have to bit the bullet
thanx
View 3 Replies
View Related
Jan 5, 2005
Folks, this is the error i get when backing up user db to local disk:
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent backed up.
Connection Broken
If i copy a file (2gb) to local disk from network it works. The disk have much space. Any ideas;;; :confused:
I get the same error when backuping up even MSDB to a network path using UNC [\].
View 9 Replies
View Related
Aug 8, 2007
Mrs,
I'm having a serious problem in SQL 2000 SP3 Database which is used Business Applications.
Today, I tryied to take backup in service time by the Enterprize Manager, the following
error occured and could not take backup.
--------------------------------------------------------------------------------------------------------------------------------------
Error 926 : Database '[database name]' cannot opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
--------------------------------------------------------------------------------------------------------------------------------------
After that, we tried same operation 3 times. But result is same.
After sometime, end-user report us that they can not log-in to database.
So we tried to dettach this database and attached it.
We success to dettach, but we can not attached it.
Also we tried to restore this database from Yesterday's backup file by using Enterprize Manager,
but the following error occured, and could not complete.
This backup file was generated daily backup task in Maintenance Plan in Enterprize Manager.
--------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQl-DMO(ODBC SQLState:42000)
The media family on device "E:ccd......xxx.bak" is incorrectly formed.
SQL Server cannot process this media family.
RESTORE DATABASE is terminating abnormally.
--------------------------------------------------------------------------------------------------------------------------------------
We need to recover the database immediately not to stop the end-user business process.
Please help us.
Yamagury.
View 6 Replies
View Related
Dec 26, 2002
Hi Guys,
We have a database with 20 gig and with huge transactions. The transaction log backup is scheduled every one hour
from 3.00 AM to 9.00 PM.
We take a full backup in the disk at 9.00 PM and again a full backup in the tape at 2.00 AM
It works fine in the day from 6.00 AM and complete within seconds and the size is approx. 50 to 200 MB.
But the very first transaction log backup at 3.00 AM is running like 3 hrs and the size is approx. 11 gig whick is almost equivalent to the Full backup size. There are some dts packages that are running in the night and as usual reindex, intergrity checks are running and there no large user traffic during night. But I have no idea which the very first transaction log backup in the morning takes longer time and has this bug size. Is there any work around to fix this proble.
Please advise.
Thanks,
Anu
View 7 Replies
View Related
Jul 26, 2000
Hi,
I have a dts job that imports an IIS log(a text, space delimited file) into a table. I do only one, very simple activeX transformation, and other than that, i copy the columns right in. I am loading hundreds of these logs. DTS will load most of them just fine via a batch script, but it sometimes just stops and hangs. I get no errors, but my Server's cpu is at 99%. There are no locks anywhere in the database. Any clues as to why this happens? Since i don't ever get errors, i'm really getting nervous on this one.
thanks.
View 1 Replies
View Related
Aug 3, 2000
My DTS job scheduled to run every half an hour is hanging for long.If I stop and restart it will succeed.It happened few times in the past where it ran for 3 days when nobody noticed,and still was executing!Any idea what could be the reason?
Thanks1
View 1 Replies
View Related
Mar 7, 2000
I have a problem with a process hanging my SQL server machine and the only thing that can fix it is to reboot the machine.
The environment is SQL Server 7, NT sp5, dual processor 500MHZ, 1 gig Ram.
The applications are run through MTS written in VB all executing stored procedures.
The symptoms are that the process hangs with an open transaction and it can be seen through DBCC OPENTRAN. It can also be seen in MTS on the Transaction List screen and shows as Aborting. It seems to get stuck in that state.
The Kill command does not work on the process either. I have tried one fixs that I thought may be causing the error by setting the Max Degree of Paralelism in sp_configure to 1 but that does not fix the problem.
Has anyone seen this and/or have any ideas on how to fix/repair the problem.
Thanks
Greg Flynn
View 2 Replies
View Related
Oct 18, 2000
One of my clients runs a report using Access retrieving data from SQL*server. Recently I often encounter a problem which makes the server hang.
The error messages are something like this, can somebody help?
SqlDumpExceptionHandler: Process 42 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Error: 0, Severity: 19, State: 0
CImageHelper::GetSym Error - The specified module could not be found
Stack Dump being sent to H:MSSQL7logSQL00158.dmp
Thanks for any thoughts.
Roy
View 1 Replies
View Related
Feb 12, 2001
I created a simple DTS which executes a VB standard exe that simply writes a string to an ascii file opened as append. SQL Server, the exe, and the ascii file are all on the same NT box (mine). If I execute the DTS myself the process works with no problems. When I attempt to execute the DTS via a job the job hangs with no apparent indications as to what may be the cause of the hangup. The SQL Server Agent is up and running and set to run under the system account. I have applied SQL Server SP3. The same problem was occuring prior to applying SP3.
Does anybody have any idea? All sugestions are appreciated.
Thanks in advance.
View 1 Replies
View Related