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 |
|
eatkinson
Starting Member
3 Posts |
 Posted - 2009-02-16 : 13:04:47
|
| Good afternoon,We have had a couple of instances of our tempdb growing so fast that it fills the disk in an exteremly rapid maner - we are having trouble trying to identify the trigger event, before the last time there was errors in the backup.I would be exteremly grateful if anyone could help identify the problem, a reboot of the server on both occassions has settled this down - but this is not something we can do lightly and we really need to figure out the issue at the root.Please see some of the logs before2009-02-12 04:14:18.72 spid66 BackupMedium::ReportIoError: write failure on backup device '2026963d-4971-47c0-b16c-f89fdbdf46a0'. Operating system error 995(error not found).2009-02-12 04:14:18.72 spid66 Internal I/O request 0x369B6660: Op: Write, pBuffer: 0x05320000, Size: 65536, Position: 1909850112, UMS: Internal: 0x0, InternalHigh: 0x10000, Offset: 0x797D0000, OffsetHigh: 0x0, m_buf: 0x05320000, m_len: 65536, m_actualBytes: 0, m_errcode: 995, BackupFile: 2026963d-4971-47c0-b16c-f89fdbdf46a02009-02-12 04:14:18.72 backup BACKUP failed to complete the command BACKUP DATABASE [webapp_b] TO VIRTUAL_DEVICE = '2026963d-4971-47c0-b16c-f89fdbdf46a0' WITH MAXTRANSFERSIZE=65536, BLOCKSIZE=65536, BUFFERCOUNT=1, NOINIT, name='CommVault Galaxy Backup'2009-02-12 04:14:18.73 spid66 BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device '2026963d-4971-47c0-b16c-f89fdbdf46a0'. Operating system error 995(error not found).2009-02-12 05:03:50.18 backup Database backed up: Database: webapp_b, creation date(time): 2008/08/30(01:23:13), pages dumped: 1738352, first LSN: 57450:2840:1, last LSN: 57451:407:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'2f895b00-225f-4675-8a08-2435d8da4c43'}).2009-02-12 08:17:57.26 spid51 Autogrow of file 'templog' in database 'tempdb' cancelled or timed out after 44906 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.2009-02-12 08:19:25.39 spid56 Autogrow of file 'templog' in database 'tempdb' cancelled or timed out after 88047 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.2009-02-12 08:22:27.07 spid9 Autogrow of file 'templog' in database 'tempdb' took 181688 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.Kind regardsEve |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-16 : 13:12:15
|
| Here are some of issues:Long running transactions(Without using batches),less space for TempDB,Temp Table without taking advantages of indexes. |
 |
|
|
eatkinson
Starting Member
3 Posts |
Posted - 2009-02-17 : 04:08:29
|
| Thanks - do you have any suggestions to troubleshoot as this just seems to be a rouge event. I have been looking at the backup issues and it look like it is related to the the Virtual Device Interface (VDI) timeout configured for this server's MS SQL backup client. So I am not sure this is the route cause. When the autogrowth occurs it keeps growing until there is no room to grow then crashes.Bring down websites using this.Kind regardsEve |
|
|
|
eatkinson
Starting Member
3 Posts |
Posted - 2009-02-17 : 05:55:39
|
| Another thought - could really high system resource allocation cause corruption?The disk allocation is not ideal for this? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-17 : 08:24:58
|
| Well TempDB can't be backed up at all.Well if you think that TempDB is highly used then you should get some extra space for TempDB and put it in separate drives. One thing I have noticed is that developers are inserting thousands of records in temp table without index and infact in single transaction.It won't truncate the log file even in simple recovery model(Which is default).But when you are doing in batches with necessary index,automatic checkpoint takes into effect. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-17 : 08:54:01
|
| Suggestion for troubleshooting is to get profiler going so you can see what's being run. Look for some of the items suggested in other posts (temp tables, long transactions etc). If I had to take a guess I'd bet you have 1 heavily used procedure that's got an issue in how it uses tempdb and as you clobber it you're seeing tempdb swell.Mike"oh, that monkey is going to pay" |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-02-18 : 13:18:16
|
| Interesting that autogrow takes double the time for each growth. Perhaps there is an endlessly looping transaction that uses its results oh each run. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-18 : 13:22:46
|
quote:
Originally posted by tripodal
Interesting that autogrow takes double the time for each growth. Perhaps there is an endlessly looping transaction that uses its results oh each run.
Or Autogrowth is set to very small Size. |
|
|
|
|
|
|
|
|
TempDB growing rapidly