--=_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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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--
No comments:
Post a Comment