Shrink logfile in sqlserver

Avyayah 1,231 Reputation points
2021-04-17T20:03:14.473+00:00

Is it possible to create a job in sqlserver to shrink the datafiles? This is the process I am following:

Step1, Identify the session ID: with this select * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0

Kill Session ID, Shrink the logfiles:

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,708 questions
0 comments
Accepted answer
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-04-19T08:17:09.347+00:00

    Hi SahaSaha-5270,
    Agree with Erland. It is better to shrink tempdb after you archiving a lot of data and need to free some disk space. You can shrink the tempdb using dbcc shrinkfile (templog, 'target size in MB') or restart SQL Server. Please refer to this doc which might help.

    Best Regards,
    Amelia

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2021-04-17T20:48:11.467+00:00

    Your question is very confused. In the title you talk about log file. But then you go asking about skrinking the data file. And then you list a DMV which relates to tempdb usage?

    I would suggest that whatever you are asking for, the answer is that you should not do it, no matter it is possible or not. Shrinking is an exceptional thing that you do after something exceptional, like moving 90% of the data to an archive database.

    But shrinking something that will start to grow again is perfectly pointless.

  2. Erland Sommarskog 101K Reputation points MVP
    2021-04-18T07:48:45.663+00:00

    Why would that be great? Archive your data. And if the tempdb log is big once you are completed, shrink the log of tempdb (or make it cheap and restart SQL Server).

    But it is completely pointless to shrink the log while you are still working with the archiving. It is only going to delay the process. When SQL Server grows the log, it must zero out the bytes on disk, and that takes time. Why would you want SQL Server to do that again and again and again?

    0 comments