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
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?
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?
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 ?
"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.
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:
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?
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;
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.
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.
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?
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
hello 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?
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.
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
i have 50 tables. M trying to fill the table in dataset using a loop. no roblem for first 25 tables , but at 26th table it gives the error "Input string was not in a correct format.Couldn't store <value> in "column_name" Column. Expected type is UInt32.
column type is varchar(50) changed it to TEXT ....but nothing happened.
its taking numericals but no characters.
Debugged one by one... at 26th table.... adapter.fill(dataset) throws exception
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?
hi friends, i look forward an answer that solves my problem. iam trying too populate a DropDown list . here is the codings. Previously it was working. suddenly, it s generating error. strConnectionString = "Provider = SQLOLEDB;Integrated Security=False; User ID=sa;Password=;Data Source=GIREESH-AC720F7;Initial Catalog=NorthWind"
in page_load event dim sql as string sql = "select AthleteNameKey from athletes" result_adap = DbAccess.ExecuteAdaP(sql) result_adap.Fill(result_ds, "athletes") cboAthleteName.DataSource = "athletes" cboAthleteName.DataTextField = "AthleteNameKey" cboAthleteName.DataValueField = "AthleteNameKey" cboAthleteName.DataBind()
Public Function ExecuteAdaP(ByVal sqls As String) As OleDbDataAdapter 'Dim ds As New OleDbDataAdapter Dim da As New OleDbDataAdapter(sqls, strConnectionString) 'da.Fill(ds) Return da End Function
Hello all I have shifted my vb/access database to vb/mysql and i have one form in my project in which i want to display all the records in the database in the list box . But while doing this i m getting the error " variable uses an automation type not supported in visual basic " . Moreover, it was working in Vb/access .
here is the code
Dim sql As String intCountSW_ID = 0 sql = "select SW_IDEN, SW_NAME, SW_DELETE,SW_LEFTDATE from SV_SOCIALWORKER order by SW_NAME" If rs.State = 1 Then rs.Close
Hi there I need to fill database table randomly with 5 ordered numbers such as 43566 , 78578 , 92565 , .. to gain approximately 100000 row of a table . Is there a query ?
I have a table that has a int field that contains unique nubers.if I need to insert 4000 or so new records into this table using a SQLcommand how would I be able to fill this field with the next availablenumber?This will be a manual procedure done maybe once a year so the nextnumber will be known ahaed of time. but the insert command need toincrement this number by one everytime it puts a new row in.
I always get a "ConstraintException" error when trying, at beginning of application, to run following statement (within "Form1_Load" routine called by "this->Load" EventHandler):
Also curiously if, while application is still running, I invoke again that same statement by means of a pushbutton clickevent, everything is running smootly without error... Looks to me that it is bugging only when running the first time...
Within Dataset, I tried to see what constraint could give me such trouble. Here is the only constraint I could find:
I then tried to find within "TauxTaxe" table if there could be any trace of records where "Pays" and "Province_Etat" columns would show any null value as well as any duplicate key values but there wasn't any...
Any place I should start to look for? BTW, I'm using a SQL Express database.
Finally, SQL Statement for "TA_TauxTaxe::FillByPays" is the following:
SELECT Pays, Province_Etat, Taxe1_Appl, Taxe1_Dsc, Taxe1_Taux, Taxe2_Appl, Taxe2_Dsc, Taxe2_Taux FROM TauxTaxe WHERE (Pays = @Pays)
In debug mode, I double-checked and could verify that @Pays didn't have any null value but a valid string value at time "Fill" routine was invoked. Any clue?
Insert into #Customers values(101,'Aron',23,1,1,12,1,0); Insert into #Customers values(102,'Cathy',28,1,1,13,1,0); Insert into #Customers values(103,'Zarog',33,1,1,14,1,0); Insert into #Customers values(104,'Michale',25,1,2,12,1,0); Insert into #Customers values(105,'Linda',43,1,2,13,1,0);
Insert into #Customers values(106,'Burt',53,1,2,14,1,0);
If you observe, the rows are unique based on the internalid per st_code,per city_code
Problem : Now the user inserts another row but this time he passes only the following :
Insert into #Customer values(120,'AronNew',null,1,1,12,null,null) - Note he doesnt pass the age or the type
I want that when he passes this row, i match up this row with the existing row based on st_code,citycode and internalid and then update the new row with the missing values(only columns with null) that were there in the existing row
When I run the code below, I get a table with "In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user." as the contents... Public Function ReturnTable(ByVal strName As String, ByVal alParameters As ArrayList, ByVal strTable As String) As DataSet Dim sqlSP As New SqlCommand sqlSP.CommandTimeout = 120 sqlSP.Connection = sqlConn sqlSP.CommandType = CommandType.StoredProcedure sqlSP.CommandText = strName
AddParameters(sqlSP.Parameters, alParameters)
Dim dsDataSet As New DataSet()
sqlConn.Open()
Dim sqlDataAdapter As New SqlDataAdapter(sqlSP) sqlDataAdapter.Fill(dsDataSet, strTable) sqlConn.Close()
Return dsDataSet
End Function
AddParameters is a function which adds each of the parameters in the array list to the sqlSP parameter collection... In SQL Profiler, the sp is called and runs and returns results... But a dataset with one table and the above msg is returned...
Hi All, I'm hoping somebody can help me with this as it is driving me mad. I've created a stored procedure which returns Employee information recordset when the windows username is passed to it as a parameter. I want to then store this information in Session variables so content can be filtered depending on employee status, but when I execute my code no records are returned. I know for a fact that the stored procedure works because I bound it sqldatasource and displayed results from it in a Datalist and tested it in sql server. My code is as follows can anybody see any problems with it, in runs through fine with but when I try a read a field from the datareader in says there is no data to read. Dim CurrentUser As String, Pos1 As Int16, EmployeeId As Int32 Dim cn As New System.Data.SqlClient.SqlConnection Dim param As New System.Data.SqlClient.SqlParameter Dim reader As System.Data.SqlClient.SqlDataReader Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Parameters.Add(param) reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) EmployeeId = reader.Item("EmployeeID") reader.Close() Any help would be much appricated this is driving me mad. Thank You Shaft
Hi I am currently working on an application that uses a stored procedure to retrieve data from a database and then display it in a web page. My problem is that some of the data in the database will be images, I am currently putting in test data to test my code/procedures my problem is how do I put in test data for images, when I am finished I am going to add an admin section that will allow me to add images that way but how do I go about adding them to the database until then? I have set the field to the image data type but have no idea how to relate this to an image on my server? Thanks, Adam
Does anyone know what would cause SQL MAil or Exchange to save all messages and some 1K att*.txt files to the WINNT directory? This is filling up the hard drive on a production system so it's kind of urgent.
I have phrased this question here once before but no one could answer it, so please let me know if you think you know what the problem is.
I have a Test Environment on Server2 restored from the production environment on Server1. Production is publishing to a subscriber on Server3. The problem is that even though I have truncate on checkpoint = TRUE set for the Test environment, the log fills up (all the way, not even truncate transaction with no_log fixes it).
The log size is 750MB, with a data portion of about 3GB. Is this somehow related to the fact that SQL Server still thinks the tables in the test environment are marked for replication? I have noticed it believes this becuase I am not allowed to drop tables on Test, even though Test is not even set up as a publishing database.
Other information: 1) The recovery interval on Server2 is 3 minutes. 2) I am frustrated
I have a database [CarlosDB] that currently has it's .MDF on E: and I need to move the x2 .NDF data files off C: to E:data using a single T-SQL statement:
Looking at the file configuration above, what would be the most logical way as a DBA / SQL Server 2014 Std to move the NDF files to live w/ the MDF file using:
