Tuesday, January 29, 2008 12:41 AM
SQL 2005 SP2. Upgraded from 2000 and migrated maintenance plan. I am now getting an error when running the maintenance plan for all System databases:
failed with the following error: "Backup and restore operations are not allowed on database tempdb.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Someone else has the same problem and reported it here:
Any thoughts on this?
MVP - Directory Services
Tuesday, January 29, 2008 10:24 AM
Do you know how TEMPDB works?
don't make sense to backup this database.
If we consider that there is a bug when the SYSTEM DATABASES is checked on maintenance plan because TEMPDB is included, so you just need to choice each system database (MSDB, MASTER, MODEL)
Tuesday, January 29, 2008 9:00 PMAnswerer
Why would anyone want to back up tempdb? It's what it says on the tin, a temporary database, used internally and for join/sort operations or by any queries specifying the use of temporary tables.
SQL Server destroys and recreates tempdb on start-up.
You could never back this db up in any version, so it's got nothing to do with the migration.
The bug report is actually complaining about the fact that tempdb is included in the system db maintenance plan, when it shouldn't be, because as well as a backup of tempdb being pointless, it's also actually prohibited by SQL Server.
Tuesday, January 29, 2008 11:40 PM
Thanks for the responses.
I now understand more about the role of the tempdb, having done some reading on it. What threw me was it's inclusion in the system database maintenance plan.
Thursday, March 01, 2012 2:02 PM
To answer "why", consider the following scenario. There is zero money in the budget to purchase more disk space and even if you could, the disk size in the server is maxed out. Given this scenario, disk space is at a premium. The tempdb raises flags every day and this is a production server where restarting the server takes it off line. It is not clustered and should not be scheduled for a restart. There is less than 20 GIG of space available on the disk and the 200 GIG database could, at any time, require an extra 200 GIG of dataspace on a transaction lock.
So the question is not "why", it is "how" can you reduce that file regardless of the consequences. Either the server goes down (not an option) or something else must take place. If he could force a backup of the tempdb, it may reduce the log file to a decent size. I'm sure there are better ways to reduce the size of the tempdb but everything I have looked at online indicates it cannot be done without a server restart.
Thursday, March 01, 2012 2:31 PM
Looks like the maintenance plan has a bug that is including the tempdb for backups. Edit the maintenance plan and remove tempdb if it is there, then save it and retry