We have been testing a new vendor_purchased application and are now
running some month-end processes/jobs now. One of the processes that we
kicked off stopped when a 2gb tempdb filled up with only 5mb or so remaini
g. In reviewing what happened, I noticed that tempdb is in the process of
clearing itself out slowly when we restarted the process. The database has
80% of its disk space remaining and the transaction log has also 75%
remaining.
My questions are:
1) Although I don't think so, is there nay way of speeding up the process
of tempdb clearing out the data in it?
2) We will need to examine what sql code the vendor has used that caused
this to happen. Aside from group by and order by, if there are a lot of
'select into' code, what alternatives do we have?
Any information that can be provided will be fine. THanks in advance.
We have a production sql server V6.5 sp5a (running on NT 4.0 sp3) that has a tempdb that is fulling up about every two weeks. (The log to tempdb is okay, it is not filling up). Tempdb is defined as 400 MB. I have been reading about "The two most common db errors"; 1) The db log ran out of space and 2) The db ran out of space. We are receiving "The db ran out of space." Once tempdb gets near full is there anyway to clear tempdb without rebooting the nt server? Is there a stored procedure that can be scheduled to free up tempdb?
My books tell me the tempdb is used for creating temporary tables or storing temporary information. For example, a temporary table in a stored procedure or sql may create a temporary work table as a result of a query with a 'group by' or 'order by' clause. If these tables are temporary why don't sql release them or free up the space used by tempdb?
We have a SQL Server 6.5 with SP5a installed and are having problems with the Transaction log not clearing every night when a scheduled backup takes place. Is there anyway to debug this?
The Scheduled task that is run is "DUMP DATABASE DBSALES TO DBSALESBK with INIT DUMP TRANSACTION DBSALES WITH TRUNCATE_ONLY DUMP DATABASE DISTRIBUTION TO DSBK WITH INIT DUMP TRANSACTION DISTRIBUTION WITH TRUNCATE_ONLY"
I have also run on it DBCC Checktable(Syslogs) but it all seems fine. Any help would be appreciated.
I'd like to ask you what means the term "database clearing", if such a concept exists in the Database Theory. Maybe this is not directly related to SQL Server but any help would be very appreciated. So please anybody who have an idea of what this could mean is welcome to reply here
Thank you very much in advance. With best regards, Sorin
I have entered some temporary values in a table located in an SQL database, to test a project to see if it was working. Now I want to remove the values so that later, the program I have created will be capable of using the table for it's true purpose. However, when I try to delete the values located there, I receive an error message. How can I clear the table?
In SQL SERVER 2000, there is a command DBCC OutputBuffer which returns the current output buffer in hexadecimal and ASCII format for the specified system process ID.
Is it so that everytime we execute a stored procedure the contents of the outputbuffer are automatically cleared so that the new contents are inserted into the outputbuffer ?
If it is not cleared then how can we clear this outputbuffer.
"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
1. Is there a way to clear all records in a table from within a store procedure? (Instead of having to go into the table and manually selecting all rows and deleting them that way.)
2. I want to sort a list of records so that the only one left are the ones with just single words in. So I have used
WHERE SearchText LIKE '% %'
to remove the cells with spaces in, but I need to be able to remove the ones with punctuation too. Is there an easy way to do this?
Having issues with a table clearing out properly which is causing problems in the program.
By design there should only be one row in this table which updates with the most current record. However on this one system the table does not clear/update like it is supposed to, and instead starts adding multiple rows.
I have tried truncating the table but the problem does not resolve itself. Does anyone have any ideas as to why this might be happening?
Hi everyone, I have a question that I believe should be simple to answer yet I cannot find the answer anywhere. I am trying to make it possible for my report to clear the input box whenever the report is run or when anything in a dropdown list is selected. The reason why I want this is because my report has a dropdown list that inputs date ranges for "quick" report info. The other option is to manually type in the begin and end date. If anyone could help me out with this I would be very grateful.
I'm opening a large number of connections to a SQL server 2005 database using a SQL object based on the System.Data.SqlClient library. When I log out of the application, I call the ClearSQLConnectionPool method of the SQL object and this should clear the pool of these connections. This has worked in the past, but lately I have noticed some odd and very annoying behaviour.Very often I log out of the application and few, if any of the connections disappear. Just this morning I opened the application, logged out and all but one of the connections disappeared. Then I opened it again, logged out and none of the connections had gone. If I open the application again all of the past connections are still there plus the new ones.I know it works, its just very inconsistent. The problem may be with IIS, SQL or with library; I just can't tell which.Any ideas?Many thanks
Is there any way to clear out the exception from a previous Try/Catch block if I am nesting another Try/Catch block within it. I am executing a SQL Command and based on the Error number coming back decide whether or not to execute various other Sql commands. Now the Outer exception seems to be taking precedence over the Inner try block, and even though the code is stepped through for the inner try block it is never executed due to the Parent Exception. Is there any way to clear the exception received from the outer Try block? Here is a snippet of code:
Hi,I want to clear all data from 200 tables in a database from a sql query. Some of the tables have got relationship as well. I want to clear data from there as well.What could be the best approach? Does anyone have an idea or script from where I could do the needful?Do let me know.Thanks
May I know how to clear the cache area in the analyser as I need to know the time taken for execution of an sql statement(query) before and after creation of indexes.
I am searching for a resolution. I did a few searches on this site and could not gain the exact answer. I apologize is this thread is a duplicate.
I have written a report containing Multi-Values for Reporting Services. When a user selects the values for the report parameters, the checked values continue to be selected after the report is completed.
Is there a way to clear these parameters?
Some of our users run the same report more than once during a single web session.
I am ending conversations properly for a dialog, the end conversation at the target properly marks the conversation as closed in sys.conversation_endpoints and sends the EndDialog message to the initiator.
The initiator get the EndDialog message from the target and ends the conversation and it clears out of sys.conversation_endpoints.
I know that to avoid reply attacks that the conversation at the target will only clear out in 30 minutes.
However it has been a day now and the conversation is still sitting in sys.conversation_endpoints as CLOSED at the target.
How do i troubleshoot this ? Why is the conversation not clearing?
Hi, We have an application which fetches data from a table which has approximately 1 million records.
1. Nearly 25 users will be using this application concurrently. 2. frequent updations will be done to the records in the geographyrolecurriculum table. 3. This table has 1 clustered index and 4 nonclustered index bounded to it.
Problem Statement: 1. Application runs smoothly for 15 - 20 days and after that all the screens throws timeout errors. When i clear the sys.syscacheobjects its working fine again and screens get loaded quickly. Please tell me how clearing the syscacheobjects makes the execution fast? and is this the correct way to solve the timeout issue or is there any other alternative?
2. Will the stored procs timeout if the tempdb is full ?
I am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:
Msg 2714, Level 16, State 6, Line 6
There is already an object named '#mytemp2' in the database.
I am developing automated .Net Unit Tests, and as a prerequisite of each test, I would like to clear the service broker queues of any messages. Executing the
RECEIVE * FROM statement appears to only return a message at a time, and not all as I expected. Any ideas on how to make this happen, besides not having to delete the queues and then having to rebuild them?
We see the following message in our error log. WARNING: Clearing procedure cache to free contiguous memory. It is accomonpanied by fairly intensive CPU activity. We get this roughly once per working day.
Anyone have any idea why, and what we can do to stop this?
I am using SSRS 2008 and the reports we have use parameters of type Date/Time. Â The reports work well when the parameter values are entered correctly.
When entering an invalid date format for one of the Date/Time parameters the following error is displayed "The value provided for the report '<parameter name>' is not valid for its type. (rsReportParameterTypeMismatch). Â This seems to be working correctly as well. Â However, when the correct date format is then entered for the report parameter for which the report threw an error, the error persists and the report doesn't run again. Â Setting the parameter to "NULL" doesn't work either.
The only way to get the report to run again is to refresh the entire report. Â Of course, if at that point one has entered a bunch of other parameter values, those values all disappear.
hi all, if i have a comma delimited string and want to insert each delimited substring into a table which of the following way is faster?pass the whole string into the a stored procedure and loop through the delimited string and pick out the substring and insert into the table orloop and pass the substring into a stored procedure and insert N times?or any other better ways someone could suggest me to do thanks!
I was just wondering if this can be done any faster? code-wise that is...
Don't mind the converts, can't do without them, as the data discipline for the source table isn't always reliable, while I have to be absolutely sure the destination data ends in the required format.
I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...
I'm sonewhat new to MS SQL Server and I'm wondering about which of thefollowing two queries would be faster:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSELECT@ResidencesBuilt = SUM(CASE WHEN BuildingType = 0 THEN Built END),@BarracksBuilt = SUM(CASE WHEN BuildingType = 1 THEN Built END),@AirBaysBuilt = SUM(CASE WHEN BuildingType = 2 THEN Built END),@NuclearPlantsBuilt = SUM(CASE WHEN BuildingType = 3 THEN Built END),@FusionPlantsBuilt = SUM(CASE WHEN BuildingType = 4 THEN Built END),@StarMinesBuilt = SUM(CASE WHEN BuildingType = 5 THEN Built END),@TrainingCampsBuilt = SUM(CASE WHEN BuildingType = 6 THEN Built END),@FactoriesBuilt = SUM(CASE WHEN BuildingType = 7 THEN Built END)FROM BuildingsGROUP BY kdIDHAVING kdID = 2902Or:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSET @ResidencesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 0AND kdID = 2902)SET @BarracksBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 1 ANDkdID = 2902)SET @AirBaysBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 2 ANDkdID = 2902)SET @NuclearPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =3 AND kdID = 2902)SET @FusionPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 4AND kdID = 2902)SET @StarMinesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 5AND kdID = 2902)SET @TrainingCampsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =6 AND kdID = 2902)SET @FactoriesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 7AND kdID = 2902)The data source is:kdID BuildingType Built2902 6 02902 7 02902 4 02902 0 802902 2 02902 1 52902 3 402902 5 10Or:CREATE TABLE [dbo].[Buildings] ([kdID] [int],[BuildingType] [tinyint],[Built] [int])INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 0, 80)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 1, 5)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 2, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 3, 40)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 4, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 5, 10)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 6, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 7, 0)Analyzer says the first would be faster, but it has a lot of SUM()'s andwhatnot so I'm not too sure about this. There are also about 1000 rows inthe actual Buildings table. This will be a part of a stored procedure.
I want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds).
Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.
