SQL with Manoj

 
While working with huge databases with large tables there are times when you get your database and log files expand ridiculously and eating up the entire disks.
This happens when you are either on an:
– OLTP environment and doing lots of DMLs (INSERTS, UPDATES or DELETES) or
– OLAP environment when you are running huge SQL queries that goes on expanding your tempdb and finally eats up your entire drive space.

There are lot of ways to better configure your disks and databases there to avoid such situations. But these kind of situations come and you have to approach the DBA or become a DBA yourself.

 
Check the LOG Size and Space used, here:

DBCC SQLPERF (LOGSPACE)

 
Check if your database has adequate space available to free up:

SELECT 
	name, 
	size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

 
Before shrinking the log file, it better to backup your transaction logs, as shown below:

BACKUP LOG <database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(database_name_log, 2)

 
OR, you can simply change the Database recovery mode to Simple, and run the above mentioned DBCC statement.

 
tempdb can be freed up by simply restarting SQL Server. When SQL Server shuts down it deletes the tempdb and on restart it automatically creates tempdb database. Thus you get fresh tempdb with minimal space. This is not advisable on production servers and should be handled by experts.