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 don't think the log reader is working

Simply says that there are no transactions available. But when I go to my
production app and add some things it still says the same thing.
--K
please contact me offline on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasepostreply@.here.com> wrote in message
news:e5gCnBRMFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Simply says that there are no transactions available. But when I go to my
> production app and add some things it still says the same thing.
> --K
>
|||Okay, I sent an email to your account. Thank you.
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23hsrl5SMFHA.1308@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> please contact me offline on this one.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Kristy" <pleasepostreply@.here.com> wrote in message
> news:e5gCnBRMFHA.3928@.TK2MSFTNGP09.phx.gbl...
my
>
|||I haven't got it yet, please resend - you can also try hilaryk @. att.net
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasepostreply@.here.com> wrote in message
news:ec9gRFUMFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Okay, I sent an email to your account. Thank you.
> --Kristy
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23hsrl5SMFHA.1308@.tk2msftngp13.phx.gbl...
> my
>
sql

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 Could not install SQL successfully, gave an error log

2007-03-22 09:36:52.39 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: )

2007-03-22 09:36:52.45 server Copyright (C) 1988-2000 Microsoft Corporation.
2007-03-22 09:36:52.45 server All rights reserved.
2007-03-22 09:36:52.45 server Server Process ID is 1516.
2007-03-22 09:36:52.45 server Logging SQL Server messages in file 'd:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2007-03-22 09:36:52.57 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2007-03-22 09:36:53.26 server SQL Server configured for thread mode processing.
2007-03-22 09:36:53.48 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2007-03-22 09:36:54.31 server Attempting to initialize Distributed Transaction Coordinator.
2007-03-22 09:36:56.46 spid3 Warning ******************
2007-03-22 09:36:56.46 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2007-03-22 09:36:56.60 spid3 Starting up database 'master'.
2007-03-22 09:36:57.26 spid3 Server name is 'PRECISE'.
2007-03-22 09:36:57.26 server Using 'SSNETLIB.DLL' version '8.0.194'.
2007-03-22 09:36:57.26 spid5 Starting up database 'model'.
2007-03-22 09:36:57.60 spid5 Clearing tempdb database.
2007-03-22 09:36:57.74 spid7 Starting up database 'msdb'.
2007-03-22 09:36:57.85 server SuperSocket Info: Bind failed on TCP port 1433.
2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433.
2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433.
2007-03-22 09:36:57.89 server SuperSocket Info: Bind failed on TCP port 1433.
2007-03-22 09:36:58.31 server SQL server listening on TCP, Shared Memory, Named Pipes.
2007-03-22 09:36:58.31 server SQL server listening on 195.195.195.2:1433, 127.0.0.1:1433.
2007-03-22 09:36:58.31 server SQL Server is ready for client connections
2007-03-22 09:36:58.35 spid7 Starting up database 'pubs'.
2007-03-22 09:36:58.64 spid7 Starting up database 'Northwind'.
2007-03-22 09:37:00.18 spid5 Starting up database 'tempdb'.
2007-03-22 09:37:00.32 spid3 Recovery complete.
2007-03-22 09:37:00.32 spid3 Warning: override, autoexec procedures skipped.
2007-03-22 09:37:07.95 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.

be sure to read all the requirments

hardware and software.

I cant connect to sql server from a computer on my network

I am trying to log in to sql server on another desktop on my network.
When I open sql management studio I am able to find the server however
when I try to log in I am unable to connect. I have gone into the
configuration tool and enabled tcp/ip and named pipes remote
connections on the computer that is acting as the server but I still
get an error when I try to connect. How do I enable it so that I can
log onto the server on the other computer.npbaker1@.neo.rr.com wrote:
> I am trying to log in to sql server on another desktop on my network.
> When I open sql management studio I am able to find the server however
> when I try to log in I am unable to connect. I have gone into the
> configuration tool and enabled tcp/ip and named pipes remote
> connections on the computer that is acting as the server but I still
> get an error when I try to connect. How do I enable it so that I can
> log onto the server on the other computer.
>
Hi
What is the error message you get?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

I cant connect to sql server from a computer on my network

I am trying to log in to sql server on another desktop on my network.
When I open sql management studio I am able to find the server however
when I try to log in I am unable to connect. I have gone into the
configuration tool and enabled tcp/ip and named pipes remote
connections on the computer that is acting as the server but I still
get an error when I try to connect. How do I enable it so that I can
log onto the server on the other computer.npbaker1@.neo.rr.com wrote:
> I am trying to log in to sql server on another desktop on my network.
> When I open sql management studio I am able to find the server however
> when I try to log in I am unable to connect. I have gone into the
> configuration tool and enabled tcp/ip and named pipes remote
> connections on the computer that is acting as the server but I still
> get an error when I try to connect. How do I enable it so that I can
> log onto the server on the other computer.
>
Hi
What is the error message you get?
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Try the usual suspects:
1. Check whether the instance is running,
2. Try to connect to it locally,
3. Ping the other server from this machine to make sure you can reach it,
4. Try to connect to it using the IP address followed by the port number
after a comma
The messages or error messages from these tests should give you some clue.
Linchi
"npbaker1@.neo.rr.com" wrote:

> I am trying to log in to sql server on another desktop on my network.
> When I open sql management studio I am able to find the server however
> when I try to log in I am unable to connect. I have gone into the
> configuration tool and enabled tcp/ip and named pipes remote
> connections on the computer that is acting as the server but I still
> get an error when I try to connect. How do I enable it so that I can
> log onto the server on the other computer.
>

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 cann't uninstall SQL Server , and cann't re-install

While I unistall ,it showmesage "Unable to locate
installation log file 'c:\MSSQL7\Uninst.isu'.
Uninstallation will not continue.
then I try re-install SQL Server 2K , 7.0 , or MSDE , or
personal Edition , they all cann't be intalled .
please help me , thank You.INF: How to Manually Uninstall SQL Server 7.0
http://support.microsoft.com/defaul...kb;en-us;276044
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"paul" <anonymous@.discussions.microsoft.com> wrote in message
news:034201c4ef4e$eaea55d0$a501280a@.phx.gbl...
> While I unistall ,it showmesage "Unable to locate
> installation log file 'c:\MSSQL7\Uninst.isu'.
> Uninstallation will not continue.
> then I try re-install SQL Server 2K , 7.0 , or MSDE , or
> personal Edition , they all cann't be intalled .
> please help me , thank You.

I cann't uninstall SQL Server , and cann't re-install

While I unistall ,it showmesage "Unable to locate
installation log file 'c:\MSSQL7\Uninst.isu'.
Uninstallation will not continue.
then I try re-install SQL Server 2K , 7.0 , or MSDE , or
personal Edition , they all cann't be intalled .
please help me , thank You.
INF: How to Manually Uninstall SQL Server 7.0
http://support.microsoft.com/default...b;en-us;276044
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"paul" <anonymous@.discussions.microsoft.com> wrote in message
news:034201c4ef4e$eaea55d0$a501280a@.phx.gbl...
> While I unistall ,it showmesage "Unable to locate
> installation log file 'c:\MSSQL7\Uninst.isu'.
> Uninstallation will not continue.
> then I try re-install SQL Server 2K , 7.0 , or MSDE , or
> personal Edition , they all cann't be intalled .
> please help me , thank You.
sql

I cannot log in with sa user (error 18452)

Hi to all.

I'm trying to log in on local machine to my SQL Server 2005 (in Windows Server 2003) using a user not trused, like as 'sa' user, but I receive the error Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452).

No problem to connect with trusted user (my logon user to operating system).

I set the auth mode to SQL Server & Windows but doesn't work.

What can I do?

Ciao.

Hi,

you did either not set Mixed Authentication for the instance you are connecting, or you are connecting to the wrong instance or you did not restart the service after changing the authentication mode. The is no other explanation for this error message.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I've seen people get this error when trying to work out what authentication mode they want. They switch from one to the other, but don't restart the service - only to find that the mode hasn't actually changed yet.

Rob

I AM TRYING TO FIND OUT WHAT THE ACRONYM'SQL' MEANS

I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?
Please don't shout. It stands for Structured Query Language.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:6BA76E97-5E24-44C3-B66C-DC96FE41DA57@.microsoft.com...
I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?
|||SQL = Structured Query Language.
Please note that writing your message entirely in UPPER CASE is by many
considered as shouting, and thus inappropriate.
Gert-Jan
Terry wrote:
> I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
> STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?
|||The file itself is likely from ODBC tracing - that's the
default file name. So if someone said you had some large
sql.log file on your computer, you probably want to make
sure you have ODBC tracing turned off and then just delete
the file if you aren't using it (it will be recreated if
anyone enables ODBC tracing again).
To make sure it's turned off, open Data Sources (ODBC) from
Administrative tools. Or from the start button, select Run
and run odbcad32
In the ODBC Administrator, select the Tracing tab. If the
top left command button text is Stop Tracing Now, click on
that button. That will stop the tracing.
-Sue
On Fri, 8 Jun 2007 10:01:01 -0700, Terry
<Terry@.discussions.microsoft.com> wrote:

>I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
>STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?

I AM TRYING TO FIND OUT WHAT THE ACRONYM'SQL' MEANS

I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?Please don't shout. It stands for Structured Query Language.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:6BA76E97-5E24-44C3-B66C-DC96FE41DA57@.microsoft.com...
I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?|||Structured Query Language
http://www.acronymfinder.com/af-query.asp?Acronym=SQL
--
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:6BA76E97-5E24-44C3-B66C-DC96FE41DA57@.microsoft.com...
>I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
> STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?|||SQL = Structured Query Language.
Please note that writing your message entirely in UPPER CASE is by many
considered as shouting, and thus inappropriate.
Gert-Jan
Terry wrote:
> I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
> STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?|||The file itself is likely from ODBC tracing - that's the
default file name. So if someone said you had some large
sql.log file on your computer, you probably want to make
sure you have ODBC tracing turned off and then just delete
the file if you aren't using it (it will be recreated if
anyone enables ODBC tracing again).
To make sure it's turned off, open Data Sources (ODBC) from
Administrative tools. Or from the start button, select Run
and run odbcad32
In the ODBC Administrator, select the Tracing tab. If the
top left command button text is Stop Tracing Now, click on
that button. That will stop the tracing.
-Sue
On Fri, 8 Jun 2007 10:01:01 -0700, Terry
<Terry@.discussions.microsoft.com> wrote:
>I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
>STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?

I AM TRYING TO FIND OUT WHAT THE ACRONYM'SQL' MEANS

I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?Please don't shout. It stands for Structured Query Language.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:6BA76E97-5E24-44C3-B66C-DC96FE41DA57@.microsoft.com...
I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?|||Structured Query Language
http://www.acronymfinder.com/af-query.asp?Acronym=SQL
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:6BA76E97-5E24-44C3-B66C-DC96FE41DA57@.microsoft.com...
>I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
> STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?|||SQL = Structured Query Language.
Please note that writing your message entirely in UPPER CASE is by many
considered as shouting, and thus inappropriate.
Gert-Jan
Terry wrote:
> I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
> STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?|||The file itself is likely from ODBC tracing - that's the
default file name. So if someone said you had some large
sql.log file on your computer, you probably want to make
sure you have ODBC tracing turned off and then just delete
the file if you aren't using it (it will be recreated if
anyone enables ODBC tracing again).
To make sure it's turned off, open Data Sources (ODBC) from
Administrative tools. Or from the start button, select Run
and run odbcad32
In the ODBC Administrator, select the Tracing tab. If the
top left command button text is Stop Tracing Now, click on
that button. That will stop the tracing.
-Sue
On Fri, 8 Jun 2007 10:01:01 -0700, Terry
<Terry@.discussions.microsoft.com> wrote:

>I WAS TOLD THAT I HD A SQL.LOG ON MY COMPUTER BUT I DO NOT KNOW WHAT THAT
>STANDS FOR. CAN SOMEONE TELL ME WHAT THIS ACRONYM MEANS PLEASE?

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