I am getting tempdb full error again

This is the error that I am getting in the error log
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
This is the information of my database server.
exec sp_helpdb tempdb
name,db_size,owner,dbid,created,status,compatibili ty_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxsize,growth ,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data only
templog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log only
database_name,database_size,unallocated space
tempdb,89.06 MB,87.66 MB
reserved,data,index_size,unused
672 KB,184 KB,400 KB,88 KB
exec master..xp_fixeddrives
drive,MB free
C,36715
D,371955
Please help. Where is the issue. I can manually increase the size of the tempdb and so there is no permission issue.
-Nags
Why don't you just make tempdb larger than you need and forget about this issue. You only have it at 89MB and the log at less than a MB. Disk space is way too cheap these days to deal with issues like this. Make it bigger and move on.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTNGP09.phx.gbl...
This is the error that I am getting in the error log
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
This is the information of my database server.
exec sp_helpdb tempdb
name,db_size,owner,dbid,created,status,compatibili ty_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxsize,growth ,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data only
templog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log only
database_name,database_size,unallocated space
tempdb,89.06 MB,87.66 MB
reserved,data,index_size,unused
672 KB,184 KB,400 KB,88 KB
exec master..xp_fixeddrives
drive,MB free
C,36715
D,371955
Please help. Where is the issue. I can manually increase the size of the tempdb and so there is no permission issue.
-Nags
|||Nags,
This probably means that you are holding an open transaction across tempdb and the log is growing and growing and growing. Even though the recovery mode is SIMPLE there is a need for log space during a transaction.
First: It looks like you have plenty of disk space, but have you verified that when the log file is 'full' that there is still free space on the drive? Or is the space indeed used up? If it is, then you have some large transaction running. (You can use DBCC OPENTRAN to report on the oldest open transaction in a database.)
Next: Your tempdb log is 796 KB and growing at 10%. Possibly your log usage is simply increasing faster than the 10% increments can be made and you get a false "log full". Try setting the tempdb log to be 10 MB with a 5MB increment and see if that helps.
Both of these and other possibities are described in:
http://support.microsoft.com/default...b;EN-US;317375
Russell Fields
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTNGP09.phx.gbl...
This is the error that I am getting in the error log
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
This is the information of my database server.
exec sp_helpdb tempdb
name,db_size,owner,dbid,created,status,compatibili ty_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxsize,growth ,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data only
templog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log only
database_name,database_size,unallocated space
tempdb,89.06 MB,87.66 MB
reserved,data,index_size,unused
672 KB,184 KB,400 KB,88 KB
exec master..xp_fixeddrives
drive,MB free
C,36715
D,371955
Please help. Where is the issue. I can manually increase the size of the tempdb and so there is no permission issue.
-Nags
|||Yes, I have verified, and there is about 33 Gig free. DBCC OPENTRAN shows that there are no open transactions.
I did follow your suggestion of having the log for the tempdb to be 10 MB and with a 5 MB increment. Let me see if I am going to get the same error.
More Info : All the databases were moved from an old server to this new server. We have about 20 such servers and almost similar processing being done on all the servers. We never saw such an error. We are getting this only on the new server that we built. That is why I am trying to do further research as we are going to upgrade our production server and I want to be sure that we do not run into such issues there. I am more worried and have a feeling that it might be a configuration issue and I am not sure where to look into.
-Nags
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:uXE7O99eEHA.3428@.TK2MSFTNGP11.phx.gbl...
Nags,
This probably means that you are holding an open transaction across tempdb and the log is growing and growing and growing. Even though the recovery mode is SIMPLE there is a need for log space during a transaction.
First: It looks like you have plenty of disk space, but have you verified that when the log file is 'full' that there is still free space on the drive? Or is the space indeed used up? If it is, then you have some large transaction running. (You can use DBCC OPENTRAN to report on the oldest open transaction in a database.)
Next: Your tempdb log is 796 KB and growing at 10%. Possibly your log usage is simply increasing faster than the 10% increments can be made and you get a false "log full". Try setting the tempdb log to be 10 MB with a 5MB increment and see if that helps.
Both of these and other possibities are described in:
http://support.microsoft.com/default...b;EN-US;317375
Russell Fields
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTNGP09.phx.gbl...
This is the error that I am getting in the error log
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
This is the information of my database server.
exec sp_helpdb tempdb
name,db_size,owner,dbid,created,status,compatibili ty_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxsize,growth ,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data only
templog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log only
database_name,database_size,unallocated space
tempdb,89.06 MB,87.66 MB
reserved,data,index_size,unused
672 KB,184 KB,400 KB,88 KB
exec master..xp_fixeddrives
drive,MB free
C,36715
D,371955
Please help. Where is the issue. I can manually increase the size of the tempdb and so there is no permission issue.
-Nags
|||I cannot do that. This is a new server that we built and this could be a configuration issue. We are in the process of upgrading our production server and what if similar problem occurs on production. I can allocate about 2 Gig for temp db and 2 gig for temp log.. and one day a huge load comes on the server (which we are expecting in next few months).. we will get the same error. I cannot afford this on production.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:eD$X289eEHA.2028@.tk2msftngp13.phx.gbl...
Why don't you just make tempdb larger than you need and forget about this issue. You only have it at 89MB and the log at less than a MB. Disk space is way too cheap these days to deal with issues like this. Make it bigger and move on.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTNGP09.phx.gbl...
This is the error that I am getting in the error log
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
This is the information of my database server.
exec sp_helpdb tempdb
name,db_size,owner,dbid,created,status,compatibili ty_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxsize,growth ,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data only
templog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log only
database_name,database_size,unallocated space
tempdb,89.06 MB,87.66 MB
reserved,data,index_size,unused
672 KB,184 KB,400 KB,88 KB
exec master..xp_fixeddrives
drive,MB free
C,36715
D,371955
Please help. Where is the issue. I can manually increase the size of the tempdb and so there is no permission issue.
-Nags
|||This error basically comes about when the log needs to autogrow and it can't do it fast enough. There still is no excuse for having the tempdb files that small. Sure this situation may come up at any time regardless of the size if the conditions are wrong but you are putting yourself in a position for this to happen right away. Any time you can do something proactively to avoid an issue you should do it. The other thing is that it sounds like your hardware is not able to keep up with the autogrow request. Growing is a very resource intensive process and if the hardware (CPU, Disks etc) are busy or inadequate you can get this condition. Make sure you don't have high disk or cpu queues. Also make sure the autogrowth size is only at a point where it can keep up with the hardware. By this I mean you don't want to autogrow at 10% if you have a 10GB file and slow disks. Make it a size in MB that it can easily grow with little effort.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:eAH$0G%23eEHA.3520@.TK2MSFTNGP10.phx.gbl...
I cannot do that. This is a new server that we built and this could be a configuration issue. We are in the process of upgrading our production server and what if similar problem occurs on production. I can allocate about 2 Gig for temp db and 2 gig for temp log.. and one day a huge load comes on the server (which we are expecting in next few months).. we will get the same error. I cannot afford this on production.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:eD$X289eEHA.2028@.tk2msftngp13.phx.gbl...
Why don't you just make tempdb larger than you need and forget about this issue. You only have it at 89MB and the log at less than a MB. Disk space is way too cheap these days to deal with issues like this. Make it bigger and move on.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTNGP09.phx.gbl...
This is the error that I am getting in the error log
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
This is the information of my database server.
exec sp_helpdb tempdb
name,db_size,owner,dbid,created,status,compatibili ty_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxsize,growth ,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data only
templog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log only
database_name,database_size,unallocated space
tempdb,89.06 MB,87.66 MB
reserved,data,index_size,unused
672 KB,184 KB,400 KB,88 KB
exec master..xp_fixeddrives
drive,MB free
C,36715
D,371955
Please help. Where is the issue. I can manually increase the size of the tempdb and so there is no permission issue.
-Nags
|||Please understand the situation.. the size of the tempdb is as I gave below when it gave an error ie. just 768KB . I would assume that the log file should be huge enough for the error to occur.
The log file for the tempdb now is just 20 MB. And it is the brand new server with the latest hardware and latest disks and latest bus speed. IO for a 20 MB file cannot be a bottleneck. It is giving an error for the log file, and it is so small that even if it has to grow 10% it would be only 2 mb. This should not give an error. That's was I am concerned about.
If it is production, we allocate about 2 Gig temp db and let it autogrow by 10%. With your recommendation, we will let it autogrow by 5 MB.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:#H4stR#eEHA.2812@.tk2msftngp13.phx.gbl...
This error basically comes about when the log needs to autogrow and it can't do it fast enough. There still is no excuse for having the tempdb files that small. Sure this situation may come up at any time regardless of the size if the conditions are wrong but you are putting yourself in a position for this to happen right away. Any time you can do something proactively to avoid an issue you should do it. The other thing is that it sounds like your hardware is not able to keep up with the autogrow request. Growing is a very resource intensive process and if the hardware (CPU, Disks etc) are busy or inadequate you can get this condition. Make sure you don't have high disk or cpu queues. Also make sure the autogrowth size is only at a point where it can keep up with the hardware. By this I mean you don't want to autogrow at 10% if you have a 10GB file and slow disks. Make it a size in MB that it can easily grow with little effort.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:eAH$0G%23eEHA.3520@.TK2MSFTNGP10.phx.gbl...
I cannot do that. This is a new server that we built and this could be a configuration issue. We are in the process of upgrading our production server and what if similar problem occurs on production. I can allocate about 2 Gig for temp db and 2 gig for temp log.. and one day a huge load comes on the server (which we are expecting in next few months).. we will get the same error. I cannot afford this on production.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:eD$X289eEHA.2028@.tk2msftngp13.phx.gbl...
Why don't you just make tempdb larger than you need and forget about this issue. You only have it at 89MB and the log at less than a MB. Disk space is way too cheap these days to deal with issues like this. Make it bigger and move on.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTNGP09.phx.gbl...
This is the error that I am getting in the error log
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
This is the information of my database server.
exec sp_helpdb tempdb
name,db_size,owner,dbid,created,status,compatibili ty_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxsize,growth ,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data only
templog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log only
database_name,database_size,unallocated space
tempdb,89.06 MB,87.66 MB
reserved,data,index_size,unused
672 KB,184 KB,400 KB,88 KB
exec master..xp_fixeddrives
drive,MB free
C,36715
D,371955
Please help. Where is the issue. I can manually increase the size of the tempdb and so there is no permission issue.
-Nags
|||Just because it is the latest Disks and hardware does not mean it is working properly. Have you monitored the system to ensure there are no Disk, memory or CPU bottlenecks? I have seen issues similar to this when the Raid array was broken and it was computing the parity and slowing everything down dramatically. From your xp_fixeddrives output it looks like you only have at most 2 drive arrays,( C: & D. Are these 2 physical arrays or 1 array with 2 logical drives? Do you have tempdb, tempdb logs on the same drive as the other databases and log files? What kind of array is it?
And by the way 5MB is probably too small. You are correct in that a few MB should not be a problem on properly operating hardware. While you want to ensure you don't grow in too large an amount you don't want it way too small either.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:uOijUC$eEHA.2896@.TK2MSFTNGP11.phx.gbl...
Please understand the situation.. the size of the tempdb is as I gave below when it gave an error ie. just 768KB . I would assume that the log file should be huge enough for the error to occur.
The log file for the tempdb now is just 20 MB. And it is the brand new server with the latest hardware and latest disks and latest bus speed. IO for a 20 MB file cannot be a bottleneck. It is giving an error for the log file, and it is so small that even if it has to grow 10% it would be only 2 mb. This should not give an error. That's was I am concerned about.
If it is production, we allocate about 2 Gig temp db and let it autogrow by 10%. With your recommendation, we will let it autogrow by 5 MB.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:#H4stR#eEHA.2812@.tk2msftngp13.phx.gbl...
This error basically comes about when the log needs to autogrow and it can't do it fast enough. There still is no excuse for having the tempdb files that small. Sure this situation may come up at any time regardless of the size if the conditions are wrong but you are putting yourself in a position for this to happen right away. Any time you can do something proactively to avoid an issue you should do it. The other thing is that it sounds like your hardware is not able to keep up with the autogrow request. Growing is a very resource intensive process and if the hardware (CPU, Disks etc) are busy or inadequate you can get this condition. Make sure you don't have high disk or cpu queues. Also make sure the autogrowth size is only at a point where it can keep up with the hardware. By this I mean you don't want to autogrow at 10% if you have a 10GB file and slow disks. Make it a size in MB that it can easily grow with little effort.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:eAH$0G%23eEHA.3520@.TK2MSFTNGP10.phx.gbl...
I cannot do that. This is a new server that we built and this could be a configuration issue. We are in the process of upgrading our production server and what if similar problem occurs on production. I can allocate about 2 Gig for temp db and 2 gig for temp log.. and one day a huge load comes on the server (which we are expecting in next few months).. we will get the same error. I cannot afford this on production.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:eD$X289eEHA.2028@.tk2msftngp13.phx.gbl...
Why don't you just make tempdb larger than you need and forget about this issue. You only have it at 89MB and the log at less than a MB. Disk space is way too cheap these days to deal with issues like this. Make it bigger and move on.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTNGP09.phx.gbl...
This is the error that I am getting in the error log
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
This is the information of my database server.
exec sp_helpdb tempdb
name,db_size,owner,dbid,created,status,compatibili ty_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxsize,growth ,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data only
templog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log only
database_name,database_size,unallocated space
tempdb,89.06 MB,87.66 MB
reserved,data,index_size,unused
672 KB,184 KB,400 KB,88 KB
exec master..xp_fixeddrives
drive,MB free
C,36715
D,371955
Please help. Where is the issue. I can manually increase the size of the tempdb and so there is no permission issue.
-Nags