question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked SeeyaXi-msft commented

Session_Id cannot be killed

select * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0

kill 49

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.


How do I kill the session ID?

I have restarted the server, but it did not kill the process.

Cannot use the bellow statement on System databases
USE master;
go
ALTER DATABASE [FooData] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE FooData SET MULTI_USER;
go

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SahaSaha-5270 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @SahaSaha-5270 ,

Determining the appropriate size for tempdb in a SQL Server production environment depends on many factors. As described earlier, these factors include the existing workload and the SQL Server features that are used. We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

Set autogrow on for tempdb.
Run individual queries or workload trace files and monitor tempdb space use.
Execute index maintenance operations such as rebuilding indexes, and monitor tempdb space.
Use the space-use values from the previous steps to predict your total workload usage. Adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.

For more information, please refer to MS Docs: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15, which also provide methods about monitoring tempdb use.

Will it be a good practice to move tempdb logfile to a separate drive which has 141 GB available?

However, this size is based on your specific situation. You need estimate the size in advance through monitoring tempdb use.
Refer to this:https://logicalread.com/sql-server-tempdb-best-practices-placement-w01/#.YOf_AzPiuUk.
You can also take a look at the other two parts contained in this link.

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented

Session IDs <50 are system processes which you cannot control. Why do you feel like you need to kill 49?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The session 49 is running for last two days.
select spid, kpid, status, hostname, dbid, cmd
from master..sysprocesses

49 2580 background 1 BRKR EVENT HNDLR

Yesterday there was error The error messages are “Error in task reminder. The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'” and “Error in request approval action. The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'”.

However the transaction was not full.

0 Votes 0 ·

It is completely normal for spids < 50 to run for the entire time SQL Server has been running. This is not an issue.

The issue you have of tempdb being full has nothing to do with spids<50. Someone ran a query which filled tempdb. The reason it is not full now, is the process failed and removed all the data causing the transaction log to be full.

See:
https://www.mssqltips.com/sqlservertip/5963/monitor-sql-server-tempdb-size-and-alerting-script/

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'

The correct action of this message in most cases, is to make sure that there is enough space on the disk where you have tempdb. Not killing system processes.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

All the tempdb datafiles are in a separate drive but the tempdb logfile are bundled with other user databases. Will it be a good practice to move tempdb logfile to a separate drive which has 141 GB available?

0 Votes 0 ·

It is definitely not good practice to have any file for tempdb on a disk with not enough space required for the workload. Then how you solve it depends on your environment, which I am not acquainted with.

Moving the log file to a different disk, requires a restart of SQL Server, which may not be feasible for a production environment. But in such case, you can add a second log file on a different disk as a makeshift solution, and make a proper rearrangement when you have a maintenance window.

0 Votes 0 ·