We have a stored procedure that uses temp tables and must gather a lot of data. When we stress test this stored procedure, the tempdb transaction log fills up.
We tried using "Select Into" for our tables. That caused us not to write to the transaction log but it caused problems because it locked up sysobjects.
Is there some other way not to write to the transaction log?
MS SQL Enterprise Server, SP5, running under version 6.5.
I have recently been having a problem with the TempDb database
filling up. I originally started the database at 250 Mb but
recently expanded it to 500 Mb.
My last check of the activity on the server during an event
such as this produced the following information.
- Approx. 300 connections to primarily 2 databases.
- 4 active connections:
Connection 1 -SELECT on database 1 with 13,000 records
and a record size of approx. 300 bytes.
Connection 2 -SELECT on database 1 with 13,000 records
and a record size of approx. 300 bytes.
Connection 3 -SELECT on database 2 with 550 records
and a record size of approx. 100 bytes.
Connection 4 -Replication subscriber set at 100 transactions.
My questions are:
1. What processes may cause the TempDb database to fill up?
2. What processes prevent the database from purging?
Any information would be greatly appreciated.
Jim Story
Recently, we converted an Access database to SQL server 6.5. One of the processes that runs against the server is
missing a commit causing temporary stored procedures to fill up TEMPDB in the sysobjects table. The only way to
clear up TEMPDB is to stop and start SQL server when the database fills up. I wrote a quick and dirty stored
procedure to delete the affending rows out of the tempdb..sysobjects table, however, the database still registers as
full after the deletes.
Question:
does anyone know of a process/DBCC I can run against the tempdb..sysobjects table to regain the space in TEMPDB
without having to stop and restart SQL Server? I need a temporary solution while the programmer is debugging the
affending code.
Thanks!
TC
I have complex VB code that loads millions of records into a SQL 7 database. I see my tempdb slowly growing until system runs out of disk space.
What are typical programming bugs that would cause tempdb to fill up,
and how can I identify the offending statements through profiler?
Any help greatly appreciated!
What is the best way to fix issues with your log files in TempDB when you start to see them causing error msgs?
Thansk for your time.
Hi,
I've been having problems with my tempdb filling up, and causing all databases on the server to stop functioning properly. I've been removing alot of data lately (millions of rows), and I think this is the reason why my tempdb log is going thru an unusual load.
Whats the best way to make sure the tempdb doesnt fill up causing me major problems? I had temporarily turned off backups while I was having a new HD put in. Am I right in thinking that when a DB is backed up, the tempdb log is reduced in size? Should maintaining a daily backup solution help keep things under control ?
Thanks very much for any tips!
mike123
We are having continual problems with our transaction log filling up on one of our major applications.
Does anyone know of a way or tool to read the transaction log? We want to determine what is causing this problem.
Thanks
I am having an issue with the transaction growing uncontrolled and filling up the disk. I suspect that transactions are structured incorrectly between the web application that is monitoring a queue and the SQL that is executing the WAITFOR RECEIVE. This method is receiving large binary objects, so thats the reason for the arguments to the reader. Also, even though its not the suggested way, we commit everytime through to prevent the queue from disabling (which it was doing when we would ROLLBACK - we don't really care if the message is bad, we just want to log it and wait for the next one).
The basic structure is this, which is executed on a separate thread. Am I missing something that could be causing transactions to get into a state where the log grows uncontrollably? Is there a problem with the loop? Should I be doing a ROLLBACK when there is nothing to receive (this is a low volume queue, so it may not receive a message for a few minutes or more)? If so, where should I be doing this?
private void MonitorUpdateQueue()
{
const string _sqlEndDialog = @"END CONVERSATION @conversationHandle";
const string _errorMessageType = @"http://schemas.microsoft.com/SQL/ServiceBroker/Error";
const string _endDialogMessageType = @"http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog";
using (_ssbConnection)
{
if (_ssbConnection == null) _ssbConnection = new SqlConnection();
using (_monitorQueueCommand =
new SqlCommand("asp_ProcessMessage", _ssbConnection))
{
_monitorQueueCommand.CommandType = CommandType.StoredProcedure;
_monitorQueueCommand.CommandTimeout = 0;
while (true)
{
SqlDataReader reader = null;
Guid conversationHandle = Guid.Empty;
Byte[] payloadBytes = { 0 };
try
{
if (_ssbConnection.State == ConnectionState.Closed)
{
_ssbConnection.ConnectionString = _ssbConnectionString;
_ssbConnection.Open();
}
Byte[] latestFullUpdate = null;
_monitorQueueCommand.Transaction = _ssbConnection.BeginTransaction(IsolationLevel.Serializable);
if (_monitorQueueCommand.Transaction != null)
{
reader = _monitorQueueCommand.ExecuteReader(CommandBehavior.SequentialAccess);
if (reader != null)
{
XmlDocument updateMessage = new XmlDocument();
while (reader.Read())
{
conversationHandle = reader.GetGuid(0);
string messageType = reader.GetString(1);
payloadBytes = reader.GetSqlBytes(2).Value;
if (messageType == _endDialogMessageType || messageType == _errorMessageType)
{
SqlCommand cmdEndDialog = new SqlCommand(_sqlEndDialog, _ssbConnection, _monitorQueueCommand.Transaction);
cmdEndDialog.Parameters.AddWithValue("@conversationHandle",
conversationHandle);
cmdEndDialog.ExecuteNonQuery();
}
else
{
if (payloadBytes.Length > 0)
{
ProcessUpdateQueue(updateMessage);
}
}
}
reader.Close();
}
_monitorQueueCommand.Transaction.Commit();
}
}
catch (Exception ex)
{
if (_monitorQueueCommand.Transaction != null && _monitorQueueCommand.Transaction.Connection != null)
{
// Dequeue the bad message and ends the conversation
SqlCommand cmdQuarantineMessage = new SqlCommand("usp_FailMessage", _ssbConnection, _monitorQueueCommand.Transaction);
cmdQuarantineMessage.CommandType = CommandType.StoredProcedure;
cmdQuarantineMessage.ExecuteNonQuery();
if (reader != null && !reader.IsClosed) reader.Close();
if (_monitorQueueCommand.Transaction != null) _monitorQueueCommand.Transaction.Commit();
}
log.Error("Error monitoring queue.", ex);
}
finally
{
if (reader != null && !reader.IsClosed) reader.Close();
if (_monitorQueueCommand.Transaction != null)
{
_monitorQueueCommand.Transaction = null;
}
}
}
}
}
}
The stored procedure that is monitoring looks like this:
CREATE PROCEDURE [dbo].[asp_ProcessMessage]
AS
BEGIN
WAITFOR ( RECEIVE TOP ( 1 ) conversation_handle, message_type_name, message_body
FROM [MyQueue] ) ;
END
I have a database that is part of AlwaysOn that is filling up the transaction log drive even though I have a daily full backup and transaction logs set for every 2 hours. The backups are going from both the primary and secondary replica backuping up to the shared disk and I have the backup preferences set to the primary.
When I try to shrink the log I get 'The transaction log for database 'DB' is full due to 'LOG_BACKUP''. I have to manually backup the trans log and then shrink, why the maintenance plan backups aren't doing this even though they are "working".
We are having problems around TempDB as follows:
Stored Procedure with complicated Select Into's can block the TempDB's Transaction Log from being dumped (Set to Truncate Log on Checkpoint).
TempDB = 600MB (Disk)
When this happens, most of the developers get blocked until the server is rebooted.
Is there something else I can do besides Trunacte Log on Checkpoint for TempDB? I was under the impression that TempDB should share data and log, but should I have a separate log device for tempdb?
Thanks in advance. we are tired of rebooting the server.
Hello,
I am new to SQL Server 2000 and I have a specific question about the transaction log for tempdb.
Is it true that when a user disconnects from SQL Server, not only are his/her objects in tempdb dropped but
references to those objects in the transaction log are dropped too? On several occassions I rebooted my
machine but I noticed that the same amount of log space in tempdb was still used (according to dbcc
sqlperf (logspace)). Any information would be appreciated.
HelloI have questions about how works transaction log et the databasetempdb in SQL Server and I hop you could help me- Is it possible to reduce the size of the transaction log fil duringan execution ? Indeed, I have a script inserting a very large quantityof data (many Go) and during that process my transaction log file useall the space avaible on my hard drive. Is there any way to solve thatproblem ?- Is it possible to limit the size of the database tempdb ? I have ananother script inserting data using a select joinning 2 tables ofabout 20 Go with group by. If I execute that script sql server seemsto freeze and I must kill the process. What can I do ? Is the onlysolution is that I must make more avaible space on my hard drive ?Thanks in advance for your answers.K.
View 3 Replies View RelatedBesides restarting and expanding, is there a quick way to remedy the error:
"The log file for database 'tempdb' is full. Back up the transactional log for the database to free up some log space."
But, you can't back up a tempdb, so I was wondering if anyone had some thoughts on this.
Thanks
Has anyone ever run across you Tempdb Transaction Log being full and getting an error 9002 severity 17 state 2? One issue is the tempdb was created with all the defaults 1mb in size. As well as my transaction log is now at 4 GIG in size.
View 4 Replies View RelatedHi
How to clear the Transaction Log of Tempdb?(Other than Restarting the SQL server)
Thanks in Advance
Regards
Magesh
SQL2005 SP2+Cum.Patch Rrevision 4 (9.0.3175)
I always get this message, when i want to run a stonger query or a transaction that takes a longer time:
"The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
I checked the log_reuse_wait_desc column: LOG_BACKUP
I ran tr log backup...nothing...
I tried to set to simple reco mode the db...this helped... temporaly...i got again below message.
( i wouldn't like to set to simple mode the db because the size of db is 160GB now....so i don't want to eun a fullbackup)
TempDB size is 50MB now and it can grow until 7GB.
The trqansaction log size is 14GB and there are 50GB free space, so it can grow.
This symptom occurs since i installed SP2 and the CP Rev.4.0
What is the real problem ?
I have a 28Gb temp db transaction log which I want to dump halfway through a process, I have tried;
dump tran tempdb with no_log
which seems to have little effect.
Thanks for your help.
Andy
I am relatively new to SQL Server so excuse my ignorance.
I have noticed in many of my SQL Server 2005 instances that the tempdb database is very often reporting the log mode as waiting active_transaction or waiting checkpoint.
Is this of concern, how can I determine the cause of these waits and how can they be reduced or eliminated ?
Is there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.
Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.Roughly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used?
today I've put in production a big database accessed by 200 concurrent users, this database has READ_COMMITTED_SNAPHOT set to ON.I know that RCSI set to ON is very aggressive on tempDB so I'm monitoring it.I've noticed that the Transaction log space usage (%) on TempDB is slowly but ever increasing, I mean in the last 24 hours I've started from a 99% space free, now we are 37% space free...is it normal? TempDB log is 35GB in size.
View 6 Replies View RelatedHas anyone seen the SQL Server error:
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks!
- Mike
Hello All
I am wanting to fill a drop down list in ASP.NET using C# from a SQL database table using a stored procedure. I have my Sproc. But using ASP.NET C# I have no idea how to do this. Can someone give me a good example, and if not too much trouble, place comments in the code, and give an explanation. I am just learning ASP.NET after moving from Classic. Things are alot different.
Thank You in advnace for all your help
Andrew
I have a ton of data to load into a SQL 2005 database.
I just loaded a bunch of data for a number of tables using bcp, and the last table that my script loaded was an 8 million row table. The next table was a 12 million row table, and about 1 million rows into the bcp'ing a log full error was incurred. I have the batch size set to 10000 for all bcp commnads.
Here is the bcp command that failed:
"C:Program FilesMicrosoft SQL Server80ToolsBinncp" billing_data_repository..mtr_rdng_hrly_arc_t in mtr_rdng_hrly_arc_t.dat -c
-b10000 -Sxxxx -T
Here is the last part of the output from the bcp command:
...
10000 rows sent to SQL Server. Total sent: 970000
10000 rows sent to SQL Server. Total sent: 980000
10000 rows sent to SQL Server. Total sent: 990000
SQLState = 37000, NativeError = 9002
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'billing_data_repository' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
BCP copy in failed
I thought that a commit was issued after every 10000 rows and that this would keep the log from filling up.
The log_reuse_wait_desc column in sys.databases is set to 'LOG_BACKUP' for the database being used.
Does a checkpoint need to be done more often?
Besides breaking up the 12 million row data file into something more manageable, does anyone have a solution?
How can I continue to use my same loading script, and keep the log from filling up?
Thank you.
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value.
This is the code for the Method to return a datareader
publicSqlDataReader GetOrgID()
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
SqlConnection con = new SqlConnection(conString);
string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
con.Open();
SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dtr;
This is the code trying to accomplish the same thing with a Dataset instead.
public DataSet organID(DataSet dataset)
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
string queryString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand(queryString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataset);
return dataset;
}
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well.
So assume that the Guid is a valid entry..I should return a valid dataset but its null.
Additionally if I change the sql query to just be Select * From aspnet_OrgNames I still get a null value...I am assuming I am doing something wrong trying to fill the dataset.
Any help would be appreciated.
hello,
I've got replication set up as a publisher subscriber, to basically sync a primary server with a
backup server. My distribution log keeps filling up, I've got a perf alert for now to truncate it
at 75% full for now, but why does it fill up? it's size is about 1.5 gig
My tran log for my database will also not remove about 500mb of data as well, is there any way to
see what is going on?
Thanks
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
hello, i'd need a little help with filling GridViewsi browsed over like 10 search pages, but couldnt find any which would solve my problem.so in my ajax project i made a testing page pulled a gridview (GridView1) on it with a fhew buttons and textboxes.i need to fill the gv from code so my websie.asp.cs looks like this 1 protected void Page_Load(object sender, EventArgs e)2 {3 4 5 string connstr = "Data Source=.;database=teszt;user id=user;password=pass";6 SqlConnection conn = new SqlConnection(connstr);7 SqlCommand comm = new SqlCommand("select * from users", conn);8 conn.Open();9 SqlDataReader reader;10 reader = comm.ExecuteReader();11 if (reader.HasRows)12 {13 GridView1.DataSource = reader;14 GridView1.DataBind();15 }16 reader.Close();17 conn.Close();18 comm.Dispose();19 } so i load the page and there's no gridview on the page at all, nor an error msg, the connection and the database/table is fine.any suggestions on what am i doing wrong? and i also like to know if there would be any problem with using this on a tabcontrol/tabthankyou
View 3 Replies View Relatedhello everyone
i have created a table in sqlserver2005 named "Departments" - in this table different departments of a telephone ( landline ) company are to be stored,which deals with complaints registered to them by there users.
i want to know the name of these different departments which deals with complaints assigned to them like if i do have complaint from a user who has problem with his handset then that complaint will be assigned to "Maintance dept."
as i was never in indusrty , i need the help in filling the table.
just do write me name of departments and the nature of complaints wh they deal with!!!
thanks for the consideration
I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:
select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1
this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9
I have a database that I am splitting the data using odd account numbers and even account numbers. The odd acct numbers in one database and the even in the other database.
This database is very large. The problem is when I run the delete statements
it is going to fill up the log files. Can I turn on the "Simple" mode on the database while I am deleteing the data. Will this cause a problem? Then can I turn back on the 'Full' mode when I have finished?
Has anyone ever done this and so how did it work. Or better yet is it possible?
Thanks,
Dianne
Hello,
I am new to SQL Server and learning lots very quickly! I am experienced at building databases in Access and using VBA in Access and Excel.
I have a time series of 1440 records that may have some gaps in it. I need to check the time series for gaps and then fill these or reject the time series.
The criteria for accepting and rejecting is a user defined number of time steps from 1 to 10. For example, if the user sets the maximum gap as 5 time steps and a gap has 5 or less then I simply want to lineraly interpolate betwen the two timesteps bounding the gap. If the gap is 6 time steps then I will reject the timeseries.
I have searched the BOL and MSDN for SQL Server and think there must be a solution using the PredictTimeSeries in DMX, but not quite sure if I can do this. I may be better off simply passing through the time series as a recordset and processing as I would have done in Access...(I am reluctant to do this as I have of the order 100 * 5 * 365 time series and growng by 100 each day and fear it will take quite some time...)
Can anyone help me by pointing me in the right direction please?
Unless there is a way of using PredictTimeSeries on its own, I think the solution is:
Identify if a record is the a valid one or part of a gap (ie missing values).
Identify the longest gap and reject or process data on this value.
Identify if a record preceedes or succeeds a gap.
For each gap fill it using a linear interpolation.
Thanks,
Alan.
Hello,
In my application I am using Identity columns. When some rows are deleted from table, This identity values are not filling the gap. I mean My current identity is 5. That means 1 to 5 rows sequentially i inserted. If I am deleting 3rd and 4th rows, next identity will still continue with 6. So is there any method to fill the gap between rows