Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rita Bhatnagar
Posting Yak Master
172 Posts |
 Posted - 2002-03-08 : 11:59:19
|
| I tried to open one table but it gives me error saying the tempdb is full.Backup the transaction log.I tried to backup the transaction log but this also gives me error: The statement backup is not allowed while the recovery model is simple.Use backup database or change the recovery model.Backup is terminated abnormally.What should i do?I have Trunc. log on chkpt is set to true.Any help is appreciared.I am stuck at this point.Thanks.Rita |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-08 : 12:04:00
|
| You can't perform transaction log backups in Simple mode or with trunc. log on checkpoint enabled. Since it's tempdb, you could just do this:BACKUP LOG tempdb WITH NO_LOG |
 |
|
|
Rita Bhatnagar
Posting Yak Master
172 Posts |
Posted - 2002-03-08 : 12:20:31
|
| Thanks.I have a table.This Table has 119912 records. I am running the statement from query analyzer and getting this error. Why sql server is using tempdb to run this statement?Select * from AWP Order by tradenameServer: Msg 1105, Level 17, State 2, Line 1Could not allocate space for object '(SYSTEM table id: -50444796)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-08 : 12:27:15
|
| ORDER BY sometimes requires a worktable to be created in order for SQL Server to perform the sort operation. These worktables are created in tempdb, as long as the database and log have enough room to allow the operation to proceed.Certain other operations, like JOINS, also use worktables, and all temp tables are created in tempdb. Books Online discusses some of these operations, and so does the Inside SQL Server series from Microsoft Press. If you look up "tempdb" in either of them you'll get some more information on how SQL Server uses tempdb. |
|
|
|
Rita Bhatnagar
Posting Yak Master
172 Posts |
Posted - 2002-03-08 : 12:30:56
|
| But after running BACKUP LOG tempdb WITH NO_LOGI am still getting the error.What do i need to do. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-08 : 12:42:48
|
| You may need to run it a few times...it won't hurt anything if you run it repeatedly.You may need to run DBCC OPENTRAN and see if there are any open transactions in tempdb. This statement is documented in Books Online, and will also discuss how to close out the transactions (killing the SPID of the transaction is probably the way).If you STILL get the error, you may have to increase the size of tempdb using the ALTER DATABASE command, or Enterprise Manager. This might also require stopping SQL Server and restarting it...which will also re-create tempdb in an empty form, and should alleviate the space issues. |
|
|
|
Rita Bhatnagar
Posting Yak Master
172 Posts |
Posted - 2002-03-08 : 13:28:38
|
| Thanks.I think i need to stop the server and start it again.rita |
|
|
|
|
|
|
|
|
Need Urgent Help