4

I need to reclaim space from tempdb database which has more than 50 GB unallocated space. I have tried DBCC SHRINKDATABASE but it didn't do anything. Please note, I am aware that it has performance implications but I have no choice. I run SQL Server Express on a laptop and really need this 50 GB space. Also I have tried restarting SQL Server/laptop and have ensured that no process is running right now.

  • Which SQL Server Express version is in your environment? – Md Haidar Ali Khan Jan 1 '18 at 8:05
  • It is SQL Server 2012 - Version 11.0.2100.60 – user3467434 Jan 1 '18 at 8:07
1

I have done it using

DBCC SHRINKFILE ('tempdev',10)

Somehow DBCC SHRINKDATABASE was not working. Even DBCC SHRINKFILE ('tempdev',0) or DBCC SHRINKFILE ('tempdev') doesn't work. We have to assign some figure in MB to which it will shrink the database to.

  • From this TSQL code DBCC SHRINKFILE ('tempdev',10). What you are going to shrink (data file or log file). – Md Haidar Ali Khan Jan 1 '18 at 9:17
  • The data file was the culprit. Log file was small. – user3467434 Jan 1 '18 at 10:28
1

First of all you know the SQL Server Express database limitation.As you said tempdb which has 50 GB. As per MSDN BOL Here SQL Sever Express supports Maximum database size is 10 GB. And also you can find more SQL Server 2012 limitation details Here.

Note: Before executing the DBCC SHRINKDATABASE or DBCC SHRINKFILE . You must know their nature of execution.

To shrink all data and log files for a specific database, execute the DBCC SHRINKDATABASE command. To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command.

DBCC SHRINKDATABASE

Shrinks the size of the data and log files in the specified database

Shrinking a database and specifying a percentage of free space

The following example decreases the size of the data and log files in the UserDB user database to allow for 10 percent free space in the database

DBCC SHRINKDATABASE (UserDB, 10);  
GO  

Truncating a database

The following example shrinks the data and log files in the AdventureWorks sample database to the last allocated extent.

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY); 

DBCC SHRINKFILE

Shrinking a data file to a specified target size

The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.

USE UserDB;  
GO  
DBCC SHRINKFILE (DataFile1, 7);  
GO 

Shrinking a log file to a specified target size

The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO

Truncating a data file

The following example truncates the primary data file in the AdventureWorks database. The sys.database_files catalog view is queried to obtain the file_id of the data file.

USE AdventureWorks2012;  
GO  
SELECT file_id, name  
FROM sys.database_files;  
GO  
DBCC SHRINKFILE (1, TRUNCATEONLY); 

Note: You can reduce the default size of an empty file by using DBCC SHRINKFILE target_size. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.

Before execute the DBCC SHRINKFILE , As Mike Walsh explained very well you must know Here that, is it really necessarily to run that command.

For further your ref Here

  • Thank You. Yes. Express has limitation of 10 GB per database. Yet Tempdb was at 50 GB. May be the the limit is only for user databases and not system databases. – user3467434 Jan 1 '18 at 10:26
0

If tempdb did not shrink even after instance restart, then probably initial size is too big. Please try shrink using DBCC SHRINKFILE to get some space.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
GO 
  • Nopes, Initial size was 10 MB. It ballooned to 50 GB after I ran a bad query with incorrect join thus returning a lot more rows than I wanted. – user3467434 Jan 1 '18 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.