Quick one today:

Sometimes, in our lab, we need to do some preparatory work before doing the actual work. This involves loading or moving large volumes of data from one table to another or something similar. While in the zone of doing such activities, the transactional log gets full and my work gets stopped in the middle. It could be tempdb or user db, but usually it is transactional log file getting full. This is always frustrating as we rarely have frequent transactional backup jobs running on lab servers.

So, right before I get into the zone of doing this preparatory work, I do this on the lab database to prevent any transactional log full errors, either from tempdb or user database.

In a way it also solves this error that we run into when doing such major data modification/transformation tasks. This topic was discussed in one of the previous posts as well.

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 140751663071232' 
in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by 
deleting unneeded files, dropping objects in the filegroup, adding additional files 
to the filegroup, or setting autogrowth on for existing files in the filegroup.

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

Hope this helps,
_Sqltimes