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 couldn't access the Secondary Database after I finished Shipping Configuration

Dear All

Please I need an urgent help

After i finished all Transaction Log Shipping Configuration.

I tried to use the database in the secondary database but i couldn't access it

i saw it in SQL Managment Studio as (Restoring......)

i tired to make a database snapshot from it , i had a message

Msg 1822, Level 16, State 1, Line 1

The database must be online to have a database snapshot.

Please urgently

the secondary database is in a "Restoring" mode in this case, so you cannot take a snapshot of it. this is by-design.

if you do want take snapshot of the secondary database , you can choose the "standby"
mode when you configure the logshipping secondary db.

HTH.

thanks

Yunwen

I concur

For your consideration and comments:
I want to consider changing the way our transaction logs grow and what the initial growth defaults are to see if I can improve processing performance for one particular process (and perhaps others as well).
I want to get away from allocating new space by percentage of current space, because as the space gets significantly larger, the 20% naturally represents lots of megabytes, resulting in some very large numbers after this update-intensive process run.
I don't know if it is better to have a fixed amount of growth versus a percentage amount, but I'm guessing that as the growth amount gets progressively larger, that the time it takes to get the larger amounts of space becomes a significant drag compared with always going after a smaller, fixed amount of growth even though we may need to get more space more often. Eventually, at the larger numbers, we probably allocate much more space than we can use, so that the time we spent going after that space is wasted. I have seen that we have gotten up to over 3Gig of txn log space in one particular process. (fyi - The is the most complex and calculation-intensive process that I've ever run across in about 30 years of development experience) This is over 2.7Gig more than any of our other processes requires.

Example:If I set up a transaction log default of 20% growth and if I currently have a huge transaction log of 3,209MB, this results in trying to acquire 642MB. That can take a significant amount of time and I may only need a small percentage of that.

Naturally, getting space in smaller chunks, goes much faster than trying to acquire large chunks and my experience is that the time to acquire multiple small chunks of space is not proportional to the equivalent time to gather fewer, much larger chunks of space. I'm not sure why, but I'd guess that since it takes less time to allocate the small chunks of space and return that space to the process, that I am not suffering all the waiting for higher priority tasks that would occur during the much longer period of time it would take to get the large chunks of space.

Does anyone out there have experience to share about this? Are there some blogs or msg threads I should read?

When SQL Server allocates additional space to a database table it takes time (as you've seen). If you allocate by a percentage then as the size of the file grows the size of the new chunks grows. If it takes to long for the transaction log to grow to fit your transaction. When this happens your transaction can time out and roll back giving you an out of space error returned to the client.

I always setup all database files to grow by a set amount, based on the load of the system and the growth requirements. I try to setup the files to grow between 100 and 500 Megs at a time. This way the system grows at a decent amount, without having to wait very long for the files to grow. This also helps keep disk fragmentation down.

|||

IMHO AUTOGROW with Fixed amount of MB is better than leaving it to %age and also it is better to set the size before by calculating the size of database for next 1 year in order avoid such intermittent process of increasing when an intensive query is processed where the performance can be degraded.

http://www.windowsitpro.com/Article/ArticleID/46220/46220.html fyi on the gotchas with similar information.

|||

I agree 100%.

IF your log or DB Grows, it's best that it grow in fixed increments.

Ideally, you'll adjust the size ahead of time so that autogrow does not have to happen at all......

I cant back up my log files

When I go into EM and choose backup, the option for
transaction logs are greyed out.
Does anyone know how to active this? Also what effect
would this change have on my DB's?
Thank you,
JoeLooks like your database is in 'Simple' recovery mode. You could verify this
in the Options tab of the 'Database Properties' dialog box in Enterprise
Manager.
If it is not in 'Simple' recovery mode, have you ever performed a full
database backup on this database?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c397f9$f6af99a0$a001280a@.phx.gbl...
When I go into EM and choose backup, the option for
transaction logs are greyed out.
Does anyone know how to active this? Also what effect
would this change have on my DB's?
Thank you,
Joe|||Should I change it to full?
Thanks,
Joe|||Yes, if you want to be able to backup transaction logs and perform
point-in-time restores. Do read up on recovery models in SQL Server Books
Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:088501c397fc$0a8336b0$a001280a@.phx.gbl...
Should I change it to full?
Thanks,
Joe|||If you need up to the minute recovery, and intend to back up the log files,
you must change to full recovery mode... Using simple recovery, SQL will
truncate the log files during each checkpoint, and you will not be able to
back up the log files.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JOE" <anonymous@.discussions.microsoft.com> wrote in message
news:088501c397fc$0a8336b0$a001280a@.phx.gbl...
> Should I change it to full?
> Thanks,
> Joe

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