x
login about faq Site discussion (meta-askssc)

SQL Server 2008 R2 - Shrink TempDB Error

The "Shrink TempDb" commands are failed due to the "Truncate_Only is not a recognized backup option" error.

Any alternative or Is there any option for Shrink TempDB in Maintenance Plans of SQL server 2008 R2?

Please help.

more ▼

asked May 04 '12 at 10:32 AM in Default

Newbie Bala gravatar image

Newbie Bala
293 7 11 13

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The tempdb recovery is set to simple by default. You can't backup the log of a database in simple recovery. If you have out of control transactions, you'll need to deal with those transactions, then you can try using one of the shrink commands, either shrink database or shrink file. Truncate_only is part of a backup command, not a shrink.

By the way, constantly shrinking the database is a bad practice. If your database is constantly growing out to large sizes, the best practice is to identify the causes and eliminate or mitigate them, not just shrink the database on a nightly basis.

more ▼

answered May 04 '12 at 10:55 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
62.7k 12 20 66

(comments are locked)
10|1200 characters needed characters left

Looks like you have a maintenance plan which uses:

BACKUP LOG [databasename] WITH TRUNCATE_ONLY;
DBCC SHRINKFILE([filename]);

WITH TRUNCATE_ONLY was deprecated in SQL Server 2005 and removed in SQL Server 2008. The same thing can be achieved with

BACKUP LOG [databasename] TO DISK='NUL'
DBCC SHRINKFILE([filename]);

But Grant Fritchey's comment about not constantly shrinking tempdb-files is perfectly relevant.

more ▼

answered May 04 '12 at 11:12 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
13.7k 13 17 30

(comments are locked)
10|1200 characters needed characters left
Even after shrinking your tempdb, it may continue to grow if the auto growth is enabled and if you have uncontrolled transactions as said by @GrantFritchey. Rather than shrinking the tempdb, you have to work on these transactions that heavily utilize the tempdb( SQL Server profiler or the DMVs will help you to find these transactions). Get those queries analyze it and fine tune if possible. Other things to consider is the check for sort in tempdb in index rebuild process, this also contribute tempdb growth. Query to find the session tempdb usage details.

SELECT sp.session_id 
		, DB_NAME(database_id) AS DBName
		, HOST_NAME AS HostName
		, program_name AS AppName
		, login_name AS LoginName
		, status
		, cpu_time
		, total_elapsed_time AS    ElapsedTime
		, (memory_usage * 8) AS MemoryUsage
		, (user_objects_alloc_page_count * 8) AS AllocatedUserObjectsInKB
		, (user_objects_dealloc_page_count * 8) AS DeallocatedUserObjectsInKB
		, (internal_objects_alloc_page_count * 8) AS InternalObjectAllocObjInKB
		, (internal_objects_dealloc_page_count * 8) AS InternalObjectDeallocInKB
		,CASE is_user_process WHEN 1 THEN 'user session'
				      WHEN 0 THEN 'system session'
				      END SessionType
		, row_count
FROM sys.dm_db_session_space_usage sp
INNER join sys.dm_exec_sessions s
ON s.session_id = sp.session_id

more ▼

answered May 04 '12 at 12:35 PM

Cyborg gravatar image

Cyborg
10.2k 29 39 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x394
x42
x33

asked: May 04 '12 at 10:32 AM

Seen: 1189 times

Last Updated: May 04 '12 at 12:35 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.