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
![Crying [:'(]](http://pics.10026.com/?src=/emoticons/emotion-9.gif)
Try the link below to download the June CTP, it could make a difference. Hope this helps.
, make it like RAID-5. Only allow striping across 3 or more devices.|||