Force Kill a session?

Question

Force Kill a session?

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 1

David,

I checked the space for the backup folder, it looks fine has a lot of free space. And, I tried to backup the database manually from SSMS, I got the error that a backup operation is been running already and I need to stop that to continue with this backup.

I'm planning on restarting the SQL server services now. I see a few services running for SQL Server, do I need to restart all of them or is there a specific order that I need to restart them in??

Please help. THanks a lot

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

I did net stop mssqlserver and net start mssqlserver.

The backup works fine now.

Thanks for all your help.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

I have seen, you tried much to get out of this issue.

Check out your maintenance task that is running every night. Put more focus on your backup databases job and the steps involved in it. The backup has been waiting for either a resource or to get a lock on the target object. Also focus on other jobs that rae running simultaneously or any jobs before the backup has begun.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

Looks like its not done yet. After I restarted the services 2 days back, I was able to bounce the database and the sessions got killed.

But, today I see 2 other databases scheduled for backup jobs every night didnt run successfully since then and I see 2 sessions of these databases now for the command- BACKUP DATABASE in suspended mode. I didnt try to kill these sessions yet.

I see these two jobs in SQl server Agent -Groom SQLdmRepository and MaintenancePlan.Subplan_1. Are these like a default jobs for SQL Server? I dont think I created these jobs.. In the Error log, I see 2 errors:

[298] SQL Server Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'.[SQLSTATE 28000]

[298] SQL Server Error: 4060,Cannot open database ''SQLdmRepository'' requested by the login. The login failed.[SQLSTATE 42000]

Are these errors related to the cause of why the backups failed for a few databases?

Thanks a lot for all your help.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 5

psangeetha (5/11/2009)
Looks like its not done yet. After I restarted the services 2 days back, I was able to bounce the database and the sessions got killed.

Have you had any databases in Suspect state when you restarted the server?

But, today I see 2 other databases scheduled for backup jobs every night didnt run successfully since then and I see 2 sessions of these databases now for the command- BACKUP DATABASE in suspended mode. I didnt try to kill these sessions yet.
I see these two jobs in SQl server Agent -Groom SQLdmRepository and MaintenancePlan.Subplan_1. Are these like a default jobs for SQL Server? I dont think I created these jobs.. In the Error log, I see 2 errors:

No, SQL Server wouldn't touch your databases unless you you told it to do so.

[298] SQL Server Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'.[SQLSTATE 28000]
[298] SQL Server Error: 4060,Cannot open database ''SQLdmRepository'' requested by the login. The login failed.[SQLSTATE 42000]
Are these errors related to the cause of why the backups failed for a few databases?

Did you investigate into the steps that are involved in these jobs? and also did you look at the job woner and take it from there? Could you try and disable the jobs and create new set of jobs and see the difference?

manolomalaga SSCommitted Points: 1697 More actions · Answer 6

Hi, be sure that the user "NT AUTHORITY\SYSTEM" has database role membership: db_owner and RSExecRole over your databases with this error.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 7

Thanks for your reply. There are no databases in suspect mode..

In the log file viewer, I see 'The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) ..

The backup failed for a 7GB database, I've scheduled about 8 database backups at the same time every night. Could that be the reason for this error? Do I need to schedule every backup at different time intervals? I checked the task manager for the memory, I see Total Physical Memory is 16GB, available is 6GB, system cache is 6GB. Kernel Memory Total is 465MB, Paged is 409MB and Nonpaged is 55.3MB. Disk size where sql server is about 12GB and free space is 0.9GB. But the backup disk location has about 100Gb available. Our system admin denies its the hardware issue.

Please give me your suggestions. Thanks

David-1011936 Ten Centuries Points: 1378 More actions · Answer 8

So, some questions about your backup process.

Do you back up to tape, to a local hard drive, or to a network drive?

If you were to manually run the backup (instead of being kicked off by a job), does the job run correctly or are there issues then as well?

The system resources message is (more than likely) referring to the destination for the backup job, not the memory on that box. Assuming that there's enough drive space / tape space, and all permissions are correct, there's no reason why you can't backup those databases at the same time. In our environment, each server backs up about 100 databases at approximately the same time each night.

I would suggest running the backup manually (at an appropriate time), and see what happens then first.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 9

David,

The backup is done to the local disk on the server and then to the tape.

I tried taking backup manually, but I get an error that Backup and file manupulation operations on a database must be serialized. Reissue the statement after the current backup is completed. This is because of the backup session that is still running and I'm not able to kill the session yet. I probably need to bounce SQL server services again but I wonder if this problem might happen again.

Thanks for your support

David-1011936 Ten Centuries Points: 1378 More actions · Answer 10

psangeetha (5/13/2009)
David,
The backup is done to the local disk on the server and then to the tape.
I tried taking backup manually, but I get an error that Backup and file manupulation operations on a database must be serialized. Reissue the statement after the current backup is completed. This is because of the backup session that is still running and I'm not able to kill the session yet. I probably need to bounce SQL server services again but I wonder if this problem might happen again.
Thanks for your support

Did you have a differential or transaction log backup running at that time?

Is the process of going to disk, and then from disk to tape, all one process? You should be able to backup the database to disk as one step, see that complete correctly, and then do the copy to tape.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 11

No, we only have one type of backup -full backup every night

It is a seperate process for backup to disk at 9pm then around 12am the tape backup is scheduled..

The only change happened was, we installed SQL Server SP3 few weeks back. Could it be the reason?

David-1011936 Ten Centuries Points: 1378 More actions · Answer 12

The only change happened was, we installed SQL Server SP3 few weeks back. Could it be the reason?

I wouldn't think so. But, you should be able to do the backup manually at 9pm, and see that that part of it runs to completion without errors.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 13

Okay, I'll do that. It will work after I bounce the services. But, the next night scheduled backup fails for one or more databases with these messages and the session wouldnt go away also.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 14

psangeetha (5/13/2009)
Thanks for your reply. There are no databases in suspect mode..
In the log file viewer, I see 'The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) ..
The backup failed for a 7GB database, I've scheduled about 8 database backups at the same time every night. Could that be the reason for this error? Do I need to schedule every backup at different time intervals?

Yes that's what I would suggest you to do. Take backups at different intervals of time rather than at the same time. It's something related to the OS not the SQL server. Server couldn't able to allocate memory.

Check out this link:

http://blogs.msdn.com/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 15

Just my 10 Cents worth

4730167 seconds is about 54 Days, and even in Microsoft time that's a bit odd that a process would take that long. And I know there little to be gained from this but, killing a Rollback process is about the only way I know how to bring a SQL server to it's knees. Killing a SPID any SPID should be the last resort and you better know what the process is doing SQL won't Rollback a Rollback and killing SPID's involved with this operation may have a thread to a system SPID - 10 and this would be a bad thing especially since spid 10 is what does the CHECKPOINT operations!

rebooting the Server and restoring the databases from the last know "good" backup is all I can suggest.

CodeOn

😛