Sql Server Backup Jobs Hanging
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
ADVERTISEMENT
May 18, 2005
How to capture information of any jobs that are hanging( for example, that are running for more than 6 hours) and send an alert in the email with job name and duration it has been running?
Thanks,
View 10 Replies
View Related
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
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
Mar 21, 2008
Hi, I've just been given the task of finding out how to implement a backup procedure for our SQL server databases. Most are running 2000, some 2005.
I'm a programmer, and I'm used to having a DBA to help me! I've seen a few methods on the web involving a stored procedure and running the task from task manager.
I need to backup and restore all the databases in SQL Server 2000 and work out a way of displaying whether or not it was successful.
Can anybody please point me in the right direction as I've no idea how to do any of this really. I guess if I could setup a sproc to loop through the databases that would help, but I'm not sure where to start.
Thanks in advance.
View 9 Replies
View Related
Jan 7, 2008
I want to schedule a database backup using SQL Server Agent Jobs how can i do this?
View 1 Replies
View Related
Feb 11, 2015
we currently use Backup Scripts from Ola Hallengren, It Says Full (non copy-only) and differential backups are performed on the primary replica. Full(Copy-only) backups and transaction log backups are performed on the preferred replica.
we currently do FULL(COPY_ONLY) Backup everyday and LOG Backups for every 15 min, is there any performance benefit on running the FULL (non copy-only) on the preferred replica .
View 3 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
Feb 7, 2000
I have 3 seperate jobs to backup my database. 1 for a full backup, 1 for differentials, and the 3rd for the transaction log. Each is on its own schedule. The problem I have is that sometimes the transaction log job will try to start while one of the other two are in process. When this happens, an error is generated and I get paged (which is very anoying at midnight) Is there any way to block the transaction job until the other two are complete? It should still run, just not at the same time.
View 1 Replies
View Related
Dec 14, 2005
I have a DB Maintenance plan created that performs optimizations, integrity checks, and a database backup. The optimizations and integrity checks work fine, but the backup job fails. The backup job is to write the .bak file to a network share.
When I change the owner of the job to "sqluser", the job fails at step 0 withe the following error:
The job failed. Unable to determine if the owner (rsnsqluser) of job DB Backup Job for DB Maintenance Plan 'Online Database Server Maintenance Plan' has server access (reason: Could not obtain information about Windows NT group/user 'rsnsqluser'. [SQLSTATE 42000] (Error 8198)).
Checked with the system admin for this server and sqluser has priviledges to do everything, including write to the network share.
So, now I change the owner of the same maintenance job from sqluser to "sa". I now get this error:
Executed as user: RSNsqluser. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Checked the settings for MSSQLSERVER and SQLSERVERAGENT in Control Panel. Everything seems to be in order according to the other threads I have read.
For grins, I tried to ensure the sqluser password on the SQL Server Agent was correct. When I try to enter the new password I get this:
sql server agent startup account could not be verified
I click yes (it asks if I want to continue anyway).
Is the password not being registered properly in the agent?
One other thing, the sqluser user is listed under Security|Logins as connecting with Windows Authentication. We have several other servers that are set up the same and work fine. What am I missing? I'm pulling my hair out! :eek:
View 6 Replies
View Related
Apr 15, 2008
HI Guys.
i cannt' understand what's going on the server.. actually i have created a two jobs for full backup for my databases.. One is running perfect but one is giving me following error.
Can any one tells me where i m doing mistake.
Message
Executed as user: Domainuser. Cannot open backup device 'Database_Full(\192.0.0.1BackupsFULL_Backupsdatabase_Full.BAK)'. Operating system error 53(error not found). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
View 1 Replies
View Related
Dec 21, 2004
Our sysadmin accidentally uninstalled SQL Server started to panic and reinstalled. Thankfully the data/transaction files to our important databases were still present and I simply reattached them but our DTS packages our gone.
However, we've done weekly backups of the msdb database. How do I get the DTS jobs out of these backups?
View 1 Replies
View Related
Sep 12, 2007
Such a simple task. Not doing as it should!!
This Maintenance Cleanup Task is set to delete all BAK and TRN files it's made (in seperate maintenance plans) in given path, with the given file extension, delete files based on the age of the file at task run time. Delete files older that 4 days.
The files are now backing up for months and months. I'm not going to take care of this. I've got a computer to do this for me, every time it runs its jobs, every 4 hours and once overnight.
This is ignoring commands and refusing to do as it's told. I've checked the settings in here over and over. It's so simple - what could be wrong? I've checked the path, the file age, the extensions... The disks are getting full!
Has anyone seen anything like this?
View 17 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
Aug 21, 2000
Can anyone suggest what might be causing SQL Server 6.5 to hang?
The following messages were in the error log just before, but Books Online is not much help:
2000/08/21 10:35:20.90 ods Error : 17805, Severity: 18, State: 0
2000/08/21 10:35:20.90 ods Invalid buffer received from client.
2000/08/21 10:35:20.90 spid142 Process 142 entered sequencer without owning dataserver semaphore
there are then many messages like this one:
2000/08/21 10:36:21.06 ods Error : 17824, Severity: 10, State: 0
2000/08/21 10:36:21.06 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'E04180', hostname 'N90459'.
2000/08/21 10:36:21.06 ods OS Error : 109, The pipe has been ended.
and finally many more messages like this:
2000/08/21 10:51:20.75 ods Unable to connect. The maximum number of '750' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.
I don't think that simply increasing the number of user connections will help, but if anyone can throw some light on the cause of the first message I would be grateful.
Thanks,
John
View 4 Replies
View Related
Sep 26, 2005
Hi,Periodically I run some very complex queries or stored procedures that"hang", and the bigger problem is that it locks up all of the databaseclients ie 50 users connecting to the db via a windows application.I never know when this is going to happen, but when it does it leavesall the users completely hung up.1. Can I avoid this?2. Is there a way to "clear" what I was doing so that I don't have torestart the SQL Server serive?thanks,
View 4 Replies
View Related
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
Jul 10, 2001
I was wondering if anyone could provide me with some help, to do with a problem we are experiencing with SQL Server 7 and Windows NT 4.
We have a database that we have imported data into and are trying to build some indexes on one of the tables. THe database size is about 4GB and the table contains 21,000,000 records. When we try to build the indexes SQL server starts off OK, but after about five minutes all drive activity stops, the server stops responsing, and we need to reboot.
The machine has 2 P3 800 processers and 768Mb of RAM. NT is running on SP6 and SQL Server is running on SP3. We were initially running with 512 MB of RAM, but increased it because we thought the memory was a problem. The Server is not a dedicated SQL Server.
I have used performance monitor when running the query in SQL Analyser and have noted the following counters:
Total Server Memory
This starts off about 208000KB and creeps up to about 625000KB just before the server hangs.
Free Memory Available
This starts off high and is about 6MB just before the server hangs.
Page Reads Sec
Normally 370 and 410
Page Writes Sec
157
Buffer Cache hit Ratio
99.1
I have tried setting the SQL Server max memory option to about 600 MB, and also let SQL Server dynamically allocate it.
View 1 Replies
View Related
Aug 18, 1998
I am using SQL Server 6.5 (service pack 3) on an NT 4.0 (service pack 3). It is being used as the back-end database server for Solomon IV (accounting package from Solomon Software). The client machines eventually lock-up after limited activity. The first user to lock seems to do so when they issue a save, once they are locked all other users lock. In SQL activity log the processes has several tables locked. If this process is killed, the other users seem to free up. The only error message found in SQL errorlog is an ODS Unable to write to ListenOn "pipe.sqlquery".
This message seems to have been occuring for some time though (prior to the lock-up problems).
The only thing (I`m aware of) that changed is many of the clients where put on a 100Mb LAN and the database was expanded from 1G to 2G because it ws almost full.
Any suggestions???
Thanks,
Chris Sheehy
View 1 Replies
View Related
Feb 23, 2001
Has anybody encountered SQL SERVER 6.5 using 100% server CPU at startup with no other processes running on the NT server or accessing SQL Server?
I have tried starting up in minimal config mode to access the config values, but am having SQL Server hanging by that point as CPU usage has hit 100%
If anybody knows an answer please let me know.
Thanks
View 3 Replies
View Related
Jul 20, 2005
Hi all,Have a situation that my company has never run across before. Clientis running NT4 for the domain server, using terminal services 2000 andrunning an application with a SQL Server backend and they areexperiencing locking problems. Once one person gets locked out theneveryone trying to access that tables is also locked out as a result.It is not specific to a certain User, or module within theapplication. It's not a specific time of the day (like when a backupwould be running) and sometimes it's in the middle of the night whenthere are actually less Users on the system.We have 500 customers using this application. Most are using SQLServer backend, alot of the newer customers are using TerminalServices, and the number of Users is not accessive as compared to ourother customers. THe only difference is that I do not specificallyknow of another client with an NT4 Domain server in the mix.We actually switched to SQL Server as the recommended back end due tolocking issues using SQLBase because SQL Server is row locking andSQLBase is page locking. Since making this change we have stoppedseeing the locking for years until now. Is this a SQLServer issue orissue with the NT Domain server?Anyone have any ideas???ThanksA
View 3 Replies
View Related
Aug 21, 2006
Hi,I recently installed SQL Server 2005 Enterprise Edition (9.0.1399) andI have problems on queries concerning system file manipulation...For example, when I try to increase the Log File of MSDB database :ALTER DATABASE msdbMODIFY FILE(NAME = 'MSDBLog',SIZE = 50MB)This query never terminates...All SQL queries for database creation never terminateCREATE DATABASE EASYSHAREON PRIMARY(NAME = EASYSHAREData,FILENAME = 'D:AQSACOMDATAEasyshareEasyshareData.mdf',SIZE = 200MB,MAXSIZE = 1000MB,FILEGROWTH = 100MB)LOG ON(NAME = EASYSHARELog,FILENAME = 'E:AQSALOGEasyshareEasyshareLog.ldf',SIZE = 100MB,MAXSIZE = 300MB,FILEGROWTH = 50MB)Would someone have an idea of what happens...I'm running on Windows 2000 Server...ThanksPatrick
View 2 Replies
View Related
Dec 22, 2005
Hi,
It appears that every 5 or so days, my transactional replication is hanging. I see that I have a couple thousand undistributed commands and this number keeps growing. It forces me to generate a snapshot in order to sync up. When generating the snapshot, I will get frequent messages like "waiting for a response from server...". So I will keep stopping and starting syncronization. Eventually the snapshot will go out, with a delay after generation.
Has anyone experienced this?
Your help is much appreciated.
Thanks,
Nick
View 6 Replies
View Related
Dec 5, 2006
We have found an issue with using MSS 2005 with odbc connections, some of
our code inserts data, then reselects the data back with a select using a
different handle. This hasn't caused any issues before but in one customer
this causes a lock up. The timeout error doesn't occur as you would expect
if trying to select data that is uncommitted by another user.
Although obviously we could re-code to avoid selecting uncommitted rows, can
anyone tell me why this works sometimes but not others. Some kind of
setting in MSS that we're unaware of maybe. The code works ok on other MSS
2005 & MSS 2000 servers and oracle & sqlbase.
View 1 Replies
View Related
Jul 7, 2015
I have a weird intermittent issue with an enterprise version of SS2014. When clicking or right clicking around SSMS will lock up and display the 'SSMS is busy - waiting for an internal operation to complete'. It is only specific to the server as when I connect using my local SSMS this doesn't happen. This was happening both pre and post SP1 install.
View 4 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
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
Feb 22, 2001
We have just combined 3 sites into one server.
Two of the sites are serving fine, however one site has spids
that won't disconnect. By the end of the day there are over 600 spids
from that one site. At first we thought it was due to the ASP pages
had a db connect, but nothing that closed that db connection. But after
they modified the pages, we are still having the same problem. Checked
the webserver and they are identical to the other webservers.
Any ideas???
View 1 Replies
View Related
Feb 24, 2007
I have a scheduled job on my SQL Server 2000 that runs a DBREINDEX job on all the tables in my database. For some reason the system just hangs for hours now when it reaches one particular table. Can somebody please help me to correct the problem. Thank you all in advance.
View 2 Replies
View Related
Feb 11, 2004
I am attempting a very simple bcp out to a text file in SQL 2000 for the first time. I've tried various syntax but the command consistently hangs no matter which i use. Nothing is being directed to the output log............
"bcp rfsspd..bcp_product_view out product.out -T -c -b500 -r>>test.log"
I have no problems with this command in SQL 6.5, could it be initial setup or config of 2000 or something really daft????
Any help greatly appreciated
View 1 Replies
View Related
May 27, 2004
I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?
Thanks.
View 5 Replies
View Related
