1

I currently have replication setup across 2 MS SQL Servers (both running 2008 r2).

On the subscriber, my tempdb is filling to a ridiculous rate quickly, circa 28gb within an hour. I understand that tempdb needs to be used in order to store transactions in progress but surely this should shrink back down after?

I have also got a 35Gb Log file for the replication, which I dont seem to be able to shrink either.

Has anyone got any ideas?

Thanks

LaLa
  • 301
  • 2
  • 17

1 Answers1

0

This might help, right click on TempDb database and choose Properties in Options tab check the RecoveryModel and change it to Simple if it is something else. If you change that then Shrink your db.

TempDb will get back to it's original size when you restart SQL Server, but there are methods to shrink it without restarting.

CHECKPOINT;
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV,1024) --- New file size in MB
GO

So you can define a scheduled job to shrink it on regular basis

Reza
  • 18,865
  • 13
  • 88
  • 163