Session_Id cannot be killed

Question

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

Answer 1

Hi @Avyayah ,

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://learn.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.

Answer 2

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

Answer 3

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.