|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sql server hung
hi all,
Recently, our one sql server 2000 windows 2003 always hung and then we have to reboot the server. checked the sql log and the only one shown as "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.. Error: 9002, Severity: 17, State: 6" Could this cause the sever to hang? How to prevent this? the tempdb is set up to grow automatically and unrestriced file growth. This sql server is used for email achive solution. thanks for your help. Ed,
What is the default size for the TempDB files? Also what is the growth factor set to? SQL Server 2000 can't do instant file initialization, and Log files are zero initialize as the space is allocated to them, so this could easily timeout if the size for growth is a percent factor, and the log file grows large enough. It is always better to pre-size your TempDB to prevent it from having to grow. In the event that the auto-growth times out, the file could definately fill up even if there is available space on the server. Show quoteHide quote "Ed" <E*@discussions.microsoft.com> wrote in message news:942C8C6D-E2D2-4613-978A-E5DCDB937CAF@microsoft.com... > hi all, > > Recently, our one sql server 2000 windows 2003 always hung and then we > have > to reboot the server. checked the sql log and the only one shown as "The > log file for database 'tempdb' is full. Back up the transaction log for > the > database to free up some log space.. Error: 9002, Severity: 17, State: 6" > Could this cause the sever to hang? How to prevent this? the tempdb is > set > up to grow automatically and unrestriced file growth. This sql server is > used for email achive solution. > > thanks for your help. > thanks for your help.
>What is the default size for the TempDB files? If click tempdb and choose properties and in the general tab, the size is 8.75MB. >Also what is the growth factor set to? By percent 10. maxium file size: Unrestricted fie growth.Show quoteHide quote "Jonathan Kehayias" wrote: > Ed, > > What is the default size for the TempDB files? Also what is the growth > factor set to? SQL Server 2000 can't do instant file initialization, and > Log files are zero initialize as the space is allocated to them, so this > could easily timeout if the size for growth is a percent factor, and the log > file grows large enough. It is always better to pre-size your TempDB to > prevent it from having to grow. In the event that the auto-growth times > out, the file could definately fill up even if there is available space on > the server. > > -- > Jonathan Kehayias > SQL Server MVP > http://jmkehayias.blogspot.com > http://www.sqlclr.net > > "Ed" <E*@discussions.microsoft.com> wrote in message > news:942C8C6D-E2D2-4613-978A-E5DCDB937CAF@microsoft.com... > > hi all, > > > > Recently, our one sql server 2000 windows 2003 always hung and then we > > have > > to reboot the server. checked the sql log and the only one shown as "The > > log file for database 'tempdb' is full. Back up the transaction log for > > the > > database to free up some log space.. Error: 9002, Severity: 17, State: 6" > > Could this cause the sever to hang? How to prevent this? the tempdb is > > set > > up to grow automatically and unrestriced file growth. This sql server is > > used for email achive solution. > > > > thanks for your help. > > > > > How large is TempDB when the server hangs up? I wouldn't set TempDB to
anything less than 4GB on any of my servers as a base line size, and i generally use a 2GB Log file to start with. Then I monitor to determine how much tempdb is used, and how these files grow over time and then adjust up as needed. Show quoteHide quote "Ed" <E*@discussions.microsoft.com> wrote in message news:B4E105AF-A21E-46DE-855C-CE446EB140B3@microsoft.com... > thanks for your help. > >>What is the default size for the TempDB files? > If click tempdb and choose properties and in the general tab, the size is > 8.75MB. > >>Also what is the growth factor set to? > > By percent 10. maxium file size: Unrestricted fie growth. > > > "Jonathan Kehayias" wrote: > >> Ed, >> >> What is the default size for the TempDB files? Also what is the growth >> factor set to? SQL Server 2000 can't do instant file initialization, and >> Log files are zero initialize as the space is allocated to them, so this >> could easily timeout if the size for growth is a percent factor, and the >> log >> file grows large enough. It is always better to pre-size your TempDB to >> prevent it from having to grow. In the event that the auto-growth times >> out, the file could definately fill up even if there is available space >> on >> the server. >> >> -- >> Jonathan Kehayias >> SQL Server MVP >> http://jmkehayias.blogspot.com >> http://www.sqlclr.net >> >> "Ed" <E*@discussions.microsoft.com> wrote in message >> news:942C8C6D-E2D2-4613-978A-E5DCDB937CAF@microsoft.com... >> > hi all, >> > >> > Recently, our one sql server 2000 windows 2003 always hung and then we >> > have >> > to reboot the server. checked the sql log and the only one shown as >> > "The >> > log file for database 'tempdb' is full. Back up the transaction log for >> > the >> > database to free up some log space.. Error: 9002, Severity: 17, State: >> > 6" >> > Could this cause the sever to hang? How to prevent this? the tempdb >> > is >> > set >> > up to grow automatically and unrestriced file growth. This sql server >> > is >> > used for email achive solution. >> > >> > thanks for your help. >> > >> >> >> Hi Ed,
What is everything going on? I agree with Jonathan's suggestion. Beyond that, I just would like to recommend the following articles for your reference: Considerations for the "autogrow" and "autoshrink" settings in SQL Server http://support.microsoft.com/kb/315512/ How to shrink the tempdb database in SQL Server http://support.microsoft.com/kb/307487 Microsoft SQL Server I/O subsystem requirements for the tempdb database http://support.microsoft.com/kb/917047 I look forward to your response. Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msd***@microsoft.com. ========================================================= This posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Ed
http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html Show quoteHide quote "Ed" <E*@discussions.microsoft.com> wrote in message news:942C8C6D-E2D2-4613-978A-E5DCDB937CAF@microsoft.com... > hi all, > > Recently, our one sql server 2000 windows 2003 always hung and then we > have > to reboot the server. checked the sql log and the only one shown as "The > log file for database 'tempdb' is full. Back up the transaction log for > the > database to free up some log space.. Error: 9002, Severity: 17, State: 6" > Could this cause the sever to hang? How to prevent this? the tempdb is > set > up to grow automatically and unrestriced file growth. This sql server is > used for email achive solution. > > thanks for your help. >  
Other interesting topics
High CPU Use
text to number <--SQL rookie here Options dbcc shrinkfile SQL 2005: "Function argument count error." error SQL Server 2008 - remote connection problems listing sql server and instances Collation Issue Active/Active/Active Cluster question SQL 2000 and 2005 On Save Box Oracle Linked Server |
|||||||||||||||||||||||
