March 16, 2009 at 8:36 am
Hi Experts,
I got the error
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'Templog'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
when i ran
BACKUP LOG Templog With NO_LOG
go
under tempdb.
Why this error.Please Advice.
TIA
March 16, 2009 at 9:21 am
Hi,
You're getting that error because tempdb can't be backed up. If you do a search in BOL for tempdb it'll explain why.
March 16, 2009 at 9:24 am
Thanks Cath...
March 16, 2009 at 1:47 pm
Ratheesh.K.Nair (3/16/2009)
Hi Experts,I got the error
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'Templog'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
when i ran
BACKUP LOG Templog With NO_LOG
go
under tempdb.
Why this error.Please Advice.
TIA
the only system you cannot backup is the tempdb database. all others you can backup and it should be a part of your maintenance plan.
Why are you trying to backup tempdb database anway?. just a matter of curiosity:)
March 17, 2009 at 12:19 am
Before doing shrinkdb ran that
March 17, 2009 at 12:33 am
Ratheesh.K.Nair (3/16/2009)
Hi Experts,when i ran
BACKUP LOG Templog With NO_LOG
go
under tempdb.
Hi Ratheesh,
You are referring to "TEMPLOG" as the database instead of "TEMPDB". Change it to TEMPDB and everything will be fine
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 17, 2009 at 12:53 am
Thanks Pradeep..
March 17, 2009 at 5:58 am
Hi,
What are you trying to do here? You cannot backup tempdb database.
March 17, 2009 at 6:07 am
Krishna,
Just try that and see what happens
March 17, 2009 at 6:22 am
As Krishna and I have said, it's not possible to back up tempdb. If you run
BACKUP LOG TempDb (as Adiga suggested)
you receive the error
Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
BOL: ... tempdb cannot be backed up or restored. For more information, see the Restrictions described later in this section.
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ce4053fb-e37a-4851-b711-8e504059a780.htm)
March 17, 2009 at 6:26 am
Ratheesh.K.Nair (3/17/2009)
Krishna,Just try that and see what happens
Helo Ratheesh,
I would not want to try that. I want to know what you are trying to do? I did so, and I got the message saying "Commands completed successfully" .
How can you backup tempdb database? You cannot even run that command as tempdb would be in Simple recovery model and also its a bad practice to run WITH NO_LOG through which your will lose your work done in tempdb database.
March 17, 2009 at 6:27 am
Please give us some clarity.
March 17, 2009 at 6:33 am
Ratheesh.K.Nair (3/17/2009)
Krishna,Just try that and see what happens
You are trying to truncate the Transaction log. tempdb is recreated each time the sql server starts. You should always size your tempdb according to the expected needs, Don't shrink the files it will affect performance. You can run DBCC SHRIKFILE if you want to reclaim space.
WITH NO_LOG has been deprecated in SQL 2008.
March 17, 2009 at 6:59 am
I just tried to shrink the tempdb issued the DBCC SHRINK command after that...
Anyway thanks for the information 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply