| Author |
Topic |
|
kbk
Starting Member
34 Posts |
 Posted - 2009-01-28 : 14:12:30
|
| im very new to sql server..please give suggestions on what to do when tempdb is full..how does reindexing help and how do we do it??? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kbk
Starting Member
34 Posts |
Posted - 2009-01-28 : 14:29:41
|
| The tlog is fullkbk |
 |
|
|
kbk
Starting Member
34 Posts |
Posted - 2009-01-28 : 14:37:22
|
| sorry , the datafiles are occuping more space.so how do we resolve the issue?please tell me what happens when log file is full too.kbk |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-01-28 : 15:06:01
|
Hello;The operations that you are interested in are TRUNCATE and SHRINK.HOWEVER... You should not shrink the tempdb files. It may however be okay to truncate the log file. The tempdb data file grows as your database needs it too. If it is large then Shrinking it means your database will only grow to that size again. This can cause a performance problem because the database would have to consume extra resources in order to reallocate that memory that you just reclaimed.If the tempdb is growing to some unusually large size you may wish to examine the queries that your database runs. Tempdb is used as a scratchpad for doing sorts and other stuff. How often are you backing up the data & log files? You may wish to increase the frequency of that in order to keep the log file size more manageable.quote:
please tell me what happens when log file is full too.
If this happens you will get an ERROR 9002 (Transaction Log Full).Here is a link on how to deal with that error. I highly recommend reading it.[url]http://msdn.microsoft.com/en-us/library/ms175495.aspx[/url]r&r |
|
|
|
kbk
Starting Member
34 Posts |
Posted - 2009-01-28 : 15:16:36
|
| Thank you..All the system databases are being backed up on daily basis.bk |
|
|
|
kbk
Starting Member
34 Posts |
Posted - 2009-01-28 : 15:18:30
|
| will killing some process process that is using tempdb help?is it recommended??if not,what other suggestions?bk |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-01-28 : 17:47:24
|
quote:
Thank you..All the system databases are being backed up on daily basis.
Yes but how frequently are you backing up the transaction logs? Generally people back those up anywhere from every 15min to 1 hour.It just depends on how hard the database is being hit and the sensitivity of the data.quote:
will killing some process process that is using tempdb help?is it recommended??if not,what other suggestions?
You would need to be more specific here and elaborate on what you mean by 'process'. I don't recommend blindly stopping any process (or query) unless your absolutely sure you understand it. It is better to understand what is causing the tempdb to grow. That is the solution you should strive for..r&r |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 18:08:05
|
quote:
Originally posted by revdnrdy
quote:
Thank you..All the system databases are being backed up on daily basis.
Yes but how frequently are you backing up the transaction logs? Generally people back those up anywhere from every 15min to 1 hour.It just depends on how hard the database is being hit and the sensitivity of the data.quote:
will killing some process process that is using tempdb help?is it recommended??if not,what other suggestions?
You would need to be more specific here and elaborate on what you mean by 'process'. I don't recommend blindly stopping any process (or query) unless your absolutely sure you understand it. It is better to understand what is causing the tempdb to grow. That is the solution you should strive for..r&r
YOU CAN NOT BACKUP TEMPDB DATABASEkbk,Is your autogrow option turned on? Tempdb needs to be in single-user mode if you are planning to shrink but again it will grow. Restarting SQL Services during offline hours will create fresh TEMPDB.You need to get more spaces for it. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 18:13:19
|
quote:
Originally posted by revdnrdy
Hello;The operations that you are interested in are TRUNCATE and SHRINK.HOWEVER... You should not shrink the tempdb files. It may however be okay to truncate the log file. The tempdb data file grows as your database needs it too. If it is large then Shrinking it means your database will only grow to that size again. This can cause a performance problem because the database would have to consume extra resources in order to reallocate that memory that you just reclaimed.If the tempdb is growing to some unusually large size you may wish to examine the queries that your database runs. Tempdb is used as a scratchpad for doing sorts and other stuff. How often are you backing up the data & log files? This doesn't apply to TEMPDB and it is in Single-user modeYou may wish to increase the frequency of that in order to keep the log file size more manageable.quote:
please tell me what happens when log file is full too.
If this happens you will get an ERROR 9002 (Transaction Log Full).Here is a link on how to deal with that error. I highly recommend reading it.[url]http://msdn.microsoft.com/en-us/library/ms175495.aspx[/url]r&r
|
|
|
|
kbk
Starting Member
34 Posts |
Posted - 2009-01-29 : 07:59:14
|
| AUTOGROW IS SET ONbk |
|
|
|
kbk
Starting Member
34 Posts |
Posted - 2009-01-30 : 15:12:36
|
| should tempdb always be in single user mode?bk |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-30 : 15:21:37
|
quote:
Originally posted by kbk
should tempdb always be in single user mode?bk
NO |
|
|
|
|
tempdb