I got 2 question, if tempdb is full, how to fix it? 2. if I have f

I got 2 questions, if tempdb is full, how to fix it? 2. if I have full backup
yesterday and no transaction log today and I delete data by accident, is
there any way to recover it?
Thanks.1)
http://www.aspfaq.com/show.asp?id=2446
2)
http://www.lumigent.com/ --explorer log for sql
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A292519F-B554-4193-9437-9B74EAE07151@.microsoft.com...
>I got 2 questions, if tempdb is full, how to fix it? 2. if I have full
>backup
> yesterday and no transaction log today and I delete data by accident, is
> there any way to recover it?
> Thanks.
>

I got 2 question, if tempdb is full, how to fix it? 2. if I have f

I got 2 questions, if tempdb is full, how to fix it? 2. if I have full backu
p
yesterday and no transaction log today and I delete data by accident, is
there any way to recover it?
Thanks.1)
http://www.aspfaq.com/show.asp?id=2446
2)
http://www.lumigent.com/ --explorer log for sql
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:A292519F-B554-4193-9437-9B74EAE07151@.microsoft.com...
>I got 2 questions, if tempdb is full, how to fix it? 2. if I have full
>backup
> yesterday and no transaction log today and I delete data by accident, is
> there any way to recover it?
> Thanks.
>

I can't create a table in TEMPDB

I have a USER wich can Create tables in the production DB, but this USER
can't create tables in TEMPDB.
? What's grong ?Does the user have access to TEMPDB database?
"Eduardo6973" <Eduardo6973@.discussions.microsoft.com> wrote in message
news:DC6F4810-17F5-4E3E-A59B-EAE30255E02F@.microsoft.com...
>I have a USER wich can Create tables in the production DB, but this USER
> can't create tables in TEMPDB.
> What's grong ?|||It's unusual to create non-temporary tables in tempdb. Can you elaborate on
why you need to do this? Perhaps there is an alternate approach.
All users have permissions to create local or global temp tables in tempdb
but CREATE TABLE permission is needed to create 'permanent' tables.
Non-sysadmin role members access tempdb under the guest user security
context so you could grant CREATE TABLE to guest so that non-sysadmin users
can create permanent tables owned by guest in tempdb. Alternatively, you
could add users to tempdb and then grant CREATE TABLE permissions to those
users. However, note that tempdb is recreated at instance startup so all
users, permissions and objects created in tempdb will lost. You'll need to
either reapply those permissions at startup or add the permissions to the
model database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eduardo6973" <Eduardo6973@.discussions.microsoft.com> wrote in message
news:DC6F4810-17F5-4E3E-A59B-EAE30255E02F@.microsoft.com...
>I have a USER wich can Create tables in the production DB, but this USER
> can't create tables in TEMPDB.
> What's grong ?

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

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,c
ompatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=53
9, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStat
istics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxs
ize,growth,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMAR
Y,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 t
empdb and so there is no permission issue.
-NagsWhy don't you just make tempdb larger than you need and forget about this is
sue. 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@.TK2MSFTN
GP09.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,c
ompatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=53
9, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStat
istics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxs
ize,growth,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMAR
Y,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 t
empdb and so there is no permission issue.
-Nags|||Nags,
This probably means that you are holding an open transaction across tempdb a
nd the log is growing and growing and growing. Even though the recovery mo
de 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 th
at 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 transac
tion running. (You can use DBCC OPENTRAN to report on the oldest open tran
saction in a database.)
Next: Your tempdb log is 796 KB and growing at 10%. Possibly your log usag
e is simply increasing faster than the 10% increments can be made and you ge
t a false "log full". Try setting the tempdb log to be 10 MB with a 5MB inc
rement and see if that helps.
Both of these and other possibities are described in:
http://support.microsoft.com/defaul...kb;EN-US;317375
Russell Fields
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTN
GP09.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,c
ompatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=53
9, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStat
istics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxs
ize,growth,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMAR
Y,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 t
empdb and so there is no permission issue.
-Nags|||Yes, I have verified, and there is about 33 Gig free. DBCC OPENTRAN shows t
hat there are no open transactions.
I did follow your suggestion of having the log for the tempdb to be 10 MB an
d 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 serv
er. 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 o
n the new server that we built. That is why I am trying to do further resea
rch as we are going to upgrade our production server and I want to be sure t
hat we do not run into such issues there. I am more worried and have a feel
ing that it might be a configuration issue and I am not sure where to look i
nto.
-Nags
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:uXE7
O99eEHA.3428@.TK2MSFTNGP11.phx.gbl...
Nags,
This probably means that you are holding an open transaction across tempdb a
nd the log is growing and growing and growing. Even though the recovery mo
de 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 th
at 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 transac
tion running. (You can use DBCC OPENTRAN to report on the oldest open tran
saction in a database.)
Next: Your tempdb log is 796 KB and growing at 10%. Possibly your log usag
e is simply increasing faster than the 10% increments can be made and you ge
t a false "log full". Try setting the tempdb log to be 10 MB with a 5MB inc
rement and see if that helps.
Both of these and other possibities are described in:
http://support.microsoft.com/defaul...kb;EN-US;317375
Russell Fields
"Nags" <nags@.DontSpamMe.com> wrote in message news:edPDW$8eEHA.1652@.TK2MSFTN
GP09.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,c
ompatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=53
9, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStat
istics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxs
ize,growth,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMAR
Y,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 t
empdb 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 co
nfiguration issue. We are in the process of upgrading our production server
and what if similar problem occurs on production. I can allocate about 2 G
ig 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 e
rror. I cannot afford this on production.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:eD$X28
9eEHA.2028@.tk2msftngp13.phx.gbl...
Why don't you just make tempdb larger than you need and forget about this is
sue. 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@.TK2MSFTN
GP09.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,c
ompatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=53
9, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStat
istics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxs
ize,growth,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMAR
Y,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 t
empdb 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 th
at small. Sure this situation may come up at any time regardless of the siz
e 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 av
oid 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 ver
y resource intensive process and if the hardware (CPU, Disks etc) are busy o
r 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 i
t can easily grow with little effort.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:eAH$0G%23eEHA.3520@.TK2MSF
TNGP10.phx.gbl...
I cannot do that. This is a new server that we built and this could be a co
nfiguration issue. We are in the process of upgrading our production server
and what if similar problem occurs on production. I can allocate about 2 G
ig 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 e
rror. I cannot afford this on production.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:eD$X28
9eEHA.2028@.tk2msftngp13.phx.gbl...
Why don't you just make tempdb larger than you need and forget about this is
sue. 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@.TK2MSFTN
GP09.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,c
ompatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=53
9, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStat
istics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxs
ize,growth,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMAR
Y,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 t
empdb 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 sho
uld 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 serv
er 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 th
at small. Sure this situation may come up at any time regardless of the siz
e 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 av
oid 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 ver
y resource intensive process and if the hardware (CPU, Disks etc) are busy o
r 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 i
t can easily grow with little effort.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:eAH$0G%23eEHA.3520@.TK2MSF
TNGP10.phx.gbl...
I cannot do that. This is a new server that we built and this could be a co
nfiguration issue. We are in the process of upgrading our production server
and what if similar problem occurs on production. I can allocate about 2 G
ig 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 e
rror. I cannot afford this on production.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:eD$X28
9eEHA.2028@.tk2msftngp13.phx.gbl...
Why don't you just make tempdb larger than you need and forget about this is
sue. 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@.TK2MSFTN
GP09.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,c
ompatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=53
9, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStat
istics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxs
ize,growth,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMAR
Y,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 t
empdb 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, memor
y or CPU bottlenecks? I have seen issues similar to this when the Raid arra
y was broken and it was computing the parity and slowing everything down dra
matically. From your xp_fixeddrives output it looks like you only have at m
ost 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 e
nsure 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@.TK2MSFTN
GP11.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 sho
uld 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 serv
er 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 th
at small. Sure this situation may come up at any time regardless of the siz
e 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 av
oid 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 ver
y resource intensive process and if the hardware (CPU, Disks etc) are busy o
r 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 i
t can easily grow with little effort.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message news:eAH$0G%23eEHA.3520@.TK2MSF
TNGP10.phx.gbl...
I cannot do that. This is a new server that we built and this could be a co
nfiguration issue. We are in the process of upgrading our production server
and what if similar problem occurs on production. I can allocate about 2 G
ig 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 e
rror. I cannot afford this on production.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:eD$X28
9eEHA.2028@.tk2msftngp13.phx.gbl...
Why don't you just make tempdb larger than you need and forget about this is
sue. 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@.TK2MSFTN
GP09.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,c
ompatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=53
9, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStat
istics, IsAutoUpdateStatistics,80
exec sp_spaceused
name,fileid,filename,filegroup,size,maxs
ize,growth,usage
tempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMAR
Y,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 t
empdb and so there is no permission issue.
-Nags

I am getting tempdb full error again

This is a multi-part message in MIME format.
--=_NextPart_000_02E8_01C47BAE.4B516D10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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,compatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, = Version=3D539, Collation=3DSQL_Latin1_General_CP1_CI_AS, = SQLSortOrder=3D52, 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
--=_NextPart_000_02E8_01C47BAE.4B516D10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 tempdbname,db_size,owner,dbid,created,status,compatibility_l= eveltempdb, 89.06 MB,sa,2,Aug 2 = 2004,Status=3DONLINE,Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, Version=3D539, Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, = IsAutoCreateStatistics, IsAutoUpdateStatistics,80exec sp_spaceusedname,fileid,filename,filegroup,size,maxsize,grow= th,usagetempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data onlytemplog,2,C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log onlydatabase_name,database_size,unallocated = spacetempdb,89.06 MB,87.66 MBreserved,data,index_size,unused672 KB,184 = KB,400 KB,88 KBexec master..xp_fixeddrivesdrive,MB = freeC,36715D,371955
Please help. Where is the = issue. I can manually increase the size of the tempdb and so there is no permission issue.

-Nags

--=_NextPart_000_02E8_01C47BAE.4B516D10--This is a multi-part message in MIME format.
--=_NextPart_000_008A_01C47BBD.A9F66870
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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,compatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, =Recovery=3DSIMPLE, Version=3D539, =Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, =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
--=_NextPart_000_008A_01C47BBD.A9F66870
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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" 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 =tempdbname,db_size,owner,dbid,created,status,compatibility_l=eveltempdb, 89.06 MB,sa,2,Aug 2 =2004,Status=3DONLINE,Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, Version=3D539, Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80exec =sp_spaceusedname,fileid,filename,filegroup,size,maxsize,grow=th,usagetempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data onlytemplog,2,C:\Program Files\Microsoft SQL = Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log onlydatabase_name,database_size,unallocated =spacetempdb,89.06 MB,87.66 MBreserved,data,index_size,unused672 KB,184 =KB,400 KB,88 KBexec =master..xp_fixeddrivesdrive,MB freeC,36715D,371955
Please help. Where is the =issue. I can manually increase the size of the tempdb and so there is no =permission issue.

-Nags

---

--=_NextPart_000_008A_01C47BBD.A9F66870--|||This is a multi-part message in MIME format.
--=_NextPart_000_003B_01C47BBD.C2ADEC30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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.aspx?scid=3Dkb;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,compatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, =Recovery=3DSIMPLE, Version=3D539, =Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, =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
--=_NextPart_000_003B_01C47BBD.C2ADEC30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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:

Russell Fields
"Nags" 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 =tempdbname,db_size,owner,dbid,created,status,compatibility_l=eveltempdb, 89.06 MB,sa,2,Aug 2 =2004,Status=3DONLINE,Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, Version=3D539, Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80exec =sp_spaceusedname,fileid,filename,filegroup,size,maxsize,grow=th,usagetempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data onlytemplog,2,C:\Program Files\Microsoft SQL = Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log onlydatabase_name,database_size,unallocated =spacetempdb,89.06 MB,87.66 MBreserved,data,index_size,unused672 KB,184 =KB,400 KB,88 KBexec =master..xp_fixeddrivesdrive,MB freeC,36715D,371955
Please help. Where is the =issue. I can manually increase the size of the tempdb and so there is no =permission issue.

-Nags

---

--=_NextPart_000_003B_01C47BBD.C2ADEC30--|||This is a multi-part message in MIME format.
--=_NextPart_000_034F_01C47BBF.C0C3BD80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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.aspx?scid=3Dkb;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,compatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, =Recovery=3DSIMPLE, Version=3D539, =Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, =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
--=_NextPart_000_034F_01C47BBF.C0C3BD80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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" 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:


Russell Fields

"Nags" 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 =tempdbname,db_size,owner,dbid,created,status,compatibility_l=eveltempdb, 89.06 MB,sa,2,Aug 2 =2004,Status=3DONLINE,Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, Version=3D539, Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, IsAutoCreateStatistics, =IsAutoUpdateStatistics,80exec =sp_spaceusedname,fileid,filename,filegroup,size,maxsize,grow=th,usagetempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data onlytemplog,2,C:\Program Files\Microsoft =SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log onlydatabase_name,database_size,unallocated =spacetempdb,89.06 MB,87.66 MBreserved,data,index_size,unused672 KB,184 =KB,400 KB,88 KBexec =master..xp_fixeddrivesdrive,MB freeC,36715D,371955
Please help. Where is the =issue. I can manually increase the size of the tempdb and so there is no =permission issue.

-Nags

---

--=_NextPart_000_034F_01C47BBF.C0C3BD80--|||This is a multi-part message in MIME format.
--=_NextPart_000_035C_01C47BC0.2C931330
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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,compatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, =Recovery=3DSIMPLE, Version=3D539, =Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, =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
--=_NextPart_000_035C_01C47BC0.2C931330
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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" 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" =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 =tempdbname,db_size,owner,dbid,created,status,compatibility_l=eveltempdb, 89.06 MB,sa,2,Aug 2 =2004,Status=3DONLINE,Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, Version=3D539, Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, IsAutoCreateStatistics, =IsAutoUpdateStatistics,80exec =sp_spaceusedname,fileid,filename,filegroup,size,maxsize,grow=th,usagetempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data onlytemplog,2,C:\Program Files\Microsoft =SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log onlydatabase_name,database_size,unallocated =spacetempdb,89.06 MB,87.66 MBreserved,data,index_size,unused672 KB,184 =KB,400 KB,88 KBexec =master..xp_fixeddrivesdrive,MB freeC,36715D,371955
Please help. Where is the =issue. I can manually increase the size of the tempdb and so there is no =permission issue.

-Nags

---

--=_NextPart_000_035C_01C47BC0.2C931330--|||This is a multi-part message in MIME format.
--=_NextPart_000_002D_01C47BC2.E1462900
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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,compatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, =Recovery=3DSIMPLE, Version=3D539, =Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, =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
--=_NextPart_000_002D_01C47BC2.E1462900
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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" 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" 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" =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 =tempdbname,db_size,owner,dbid,created,status,compatibility_l=eveltempdb, 89.06 MB,sa,2,Aug 2 =2004,Status=3DONLINE,Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, Version=3D539, Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, IsAutoCreateStatistics, =IsAutoUpdateStatistics,80exec =sp_spaceusedname,fileid,filename,filegroup,size,maxsize,grow=th,usagetempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data onlytemplog,2,C:\Program Files\Microsoft =SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log onlydatabase_name,database_size,unallocated =spacetempdb,89.06 MB,87.66 MBreserved,data,index_size,unused672 =KB,184 KB,400 KB,88 KBexec master..xp_fixeddrivesdrive,MB freeC,36715D,371955
Please help. Where is the =issue. I can manually increase the size of the tempdb and so there is no permission issue.

-Nags

---

--=_NextPart_000_002D_01C47BC2.E1462900--|||This is a multi-part message in MIME format.
--=_NextPart_000_03A7_01C47BCF.0B5D7F20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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,compatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, =Recovery=3DSIMPLE, Version=3D539, =Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, =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
--=_NextPart_000_03A7_01C47BCF.0B5D7F20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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" 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" =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" 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" =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 =tempdbname,db_size,owner,dbid,created,status,compatibility_l=eveltempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,Updateability=3DREAD_WRITE, =UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, Version=3D539, =Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, IsAutoCreateStatistics, IsAutoUpdateStatistics,80exec =sp_spaceusedname,fileid,filename,filegroup,size,maxsize,grow=th,usagetempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 KB,Unlimited,10%,data onlytemplog,2,C:\Program =Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log onlydatabase_name,database_size,unallocated spacetempdb,89.06 MB,87.66 MBreserved,data,index_size,unused672 KB,184 =KB,400 KB,88 KBexec =master..xp_fixeddrivesdrive,MB freeC,36715D,371955
Please help. Where is the = issue. I can manually increase the size of the tempdb and =so there is no permission issue.

-Nags

---

--=_NextPart_000_03A7_01C47BCF.0B5D7F20--|||This is a multi-part message in MIME format.
--=_NextPart_000_0032_01C47BD4.4B0F17A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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,compatibility_level
tempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, =Recovery=3DSIMPLE, Version=3D539, =Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, =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
--=_NextPart_000_0032_01C47BD4.4B0F17A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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" 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" 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" =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" 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" 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 =tempdbname,db_size,owner,dbid,created,status,compatibility_l=eveltempdb, 89.06 MB,sa,2,Aug 2 2004,Status=3DONLINE,Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DSIMPLE, Version=3D539, Collation=3DSQL_Latin1_General_CP1_CI_AS, SQLSortOrder=3D52, IsAutoCreateStatistics, =IsAutoUpdateStatistics,80exec =sp_spaceusedname,fileid,filename,filegroup,size,maxsize,grow=th,usagetempdev,1,C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf,PRIMARY,90432 = KB,Unlimited,10%,data onlytemplog,2,C:\Program =Files\Microsoft SQL Server\MSSQL\data\templog.ldf,,768 KB,Unlimited,10%,log onlydatabase_name,database_size,unallocated spacetempdb,89.06 MB,87.66 MBreserved,data,index_size,unused672 KB,184 =KB,400 KB,88 KBexec master..xp_fixeddrivesdrive,MB freeC,36715D,371955
Please help. Where is =the issue. I can manually increase the size of the tempdb =and so there is no permission issue.

-Nags

---

--=_NextPart_000_0032_01C47BD4.4B0F17A0--