Read_COMMITTED_SNAPSHOT && size tempdb

Question

Post reply

Read_COMMITTED_SNAPSHOT && size tempdb

abdalah.mehdoini

Ten Centuries

Points: 1012
More actions
April 6, 2021 at 12:49 pm

#3865946
Hello everyone
Is there a risk of tempdb saturation if I activate these fuction on my database
does the shrinkfile always remain functional with this function
```
USE [master]
GO
ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
```

Viewing 4 posts - 1 through 3 (of 3 total)

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

It depends. The favorite DBA answer.

From BOL - https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

Basically, if you have a lot of updates on the table, you will have a lot of tempdb growth. If the table is mostly SELECT and INSERT, you shouldn't notice much.

Summary - https://stackoverflow.com/questions/55679129/tempdb-is-not-growing-under-read-committed-snapshot-isolation

The important answer - This means that if you are updating one row, one row will be put in TempDB, if you are altering or updating an entire table the entire table will be put in TempDB. So it is entirely possible that your specific work load does not require large amounts of data to be versioned in TempDB. I've needed to increase the size of TempDB considerably (or turned off RSCI) during large updates to avoid this problem.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 3

Tempdb tends to grow enormously after turning on snapshot, since most shops update data fairly frequently.

Shrinkfile remains functional but it should only be used exceptionally rarely! Such as after a major purge (delete) of data.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".