|
I am currently testing a data import program which deletes records from files in a SQL 2000 database, and then inserts new ones. The database has over 2000 tables, my program affects about 30 of them and there are over one million records that get deleted and reinserted.
During repeated testing, I noticed two events: (1) that the database would expand to fill the space allocated for it, and (2) that after several runs I would start getting SQL timeouts. I also noticed that these two events seem to be linked – when I increased the allocated space, the timeouts would disappear until such point that the database had filled the allocated space again.
When I investigated further (using ‘sp_spaceused’) I found the following:
07.80 Gb Data
01.70 Gb Index
27.10 Gb Unused
36.60 Gb Reserved
01.00 Gb Unallocated
38.60 Gb Database Size
It seems to me that the culprit is that ‘unused’ space. If I could eliminate that, I should be able to get the database down to a manageable size … about 10.5 Gb. I spent some time researching how to do that. I tried various procedures, including DBCC SHRINKFILE, SHRINKDATABASE, UPDATEUSAGE and DBREINDEX scripts, but nothing works. I ran a third-party SQL defrag utility but, although it reported that some tables/indexes could benefit by defragging, after I ran the defrag routine I noticed that nothing actually got defragged. (This could be because it is an eval version - even tho its supposed to be fully functioning, or it could be symptomatic of a larger problem with the database).
At this point I'm stymied. Can anyone help me?
Ian Dennis
Business Analyst, Assets
HANSEN
|
|
|
|
|
What recovery model (simple, full or bulk-logged) is your database using?
|
|
|
|
|
I'm using a simple recovery model (I've just discovered!) but I need to state that it is the data file (mdf) that is growing out of control, not the log file (ldf).
|
|
|
|
|
1. The sp_spaceused procedure has an @updateusage parameter - try to run EXEC sp_spaceused @updateusage = N'TRUE' and check if the results will not change.
2. If you have time and some free disk space, make a full backup of the database and check its size - is it around 10GB?
|
|
|
|
|
I've used @updateusage = 'TRUE' and it doesn't make any difference.
I've totally killed the database, reloaded it, and started testing my program again. It's growing already ...
Reload database - DbSize:06,033, Unalloc ,515, Reserved:05,341, Data:3,786, Index:1,493, Unused ,061
Run conversion. - DbSize:18,558, Unalloc:3,511, Reserved:14,869, Data:5,049, Index:1,815, Unused:8,006
(sizes in Mb)
|
|
|
|
|
When you used DBCC SHRINKFILE, did you specify target_size parameter to force pages reorganization before releasing unused space?
From BOL[^]:
A. Shrinking a data file to a specified target size
The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.
USE UserDB;<br />
GO<br />
DBCC SHRINKFILE (DataFile1, 7);<br />
GO
|
|
|
|
|
I don't remember now. I've got some more info which I'll post in a new message in this same thread so, please view that. Thanks
|
|
|
|
|
I've been running a series of further tests. My first thought is that my problem was connected with deleting records, as that is something I'm doing that is, perhaps, unusual. For instance, I delete 25,000 sewer records out of a table and insert 25,000 new sewer records, then I delete 27,000 water records out of the same table and insert 27,000 new water records, etc.
But I'm checking the progress of my program with sp_spaceused @updateusage='true' and it shows the unused area of my database growing when I am inserting records only ... i.e., no deleting is taking place!!!
Basically, what I am seeing is the unallocated size diminishing at the same speed as the unused area grows, but when the unallocated size gets to zero, it just increases the datafile size (at which point, the unallocated space gets big again). It never releases any of the unused area, nor can I claw any of that back from any of the maintenance routines I've described above.
The only thing to add is the application is written in VB.Net. I've recently learnt that a colleague wrote another VB.Net conversion program which exhibited the same symptoms, even although his was operating on an Oracle database, not a SQL Server one. Does that make any lightbulbs go off in anyone's brain?
|
|
|
|
|
|
I've finally figured it out. See my other post in this thread. Thanks for all your help!
|
|
|
|
|
The problem seems to be centered around bugs reported by Microsoft as articles 934378 and 924947.
Additionally, someone had set the database autogrowth setting to 10% and so towards the end of the conversion run the autogrow was taking longer and longer (resizing by 10% of 39Gb takes a lot longer than 10% of 6Gb) and therefore my inserts were timing out - to add misery to confusion.
My eventual solution was:
(1) Initially size the database to 40Gb. This solved the problem mentioned above
(2) Make sure each table inserted into had at least one clustered index. This worked around the bug mentioned in article 924947
(3) When the conversion was finished, run a routine to rebuild ALL the indexes for ALL the tables. This worked around the bug mentioned in article 934378.
(4) Finally, do a DBCC SHRINKDATABASE
Without these fixes, the conversion left database = 39Gb, unused = 27Gb.
With these fixes, the conversion left database = 12Gb, unused = 4Gb.
It took me a long time to find (each run of the conversion takes 10 hours) but I ended up learning a lot about SQL Server!
|
|
|
|
|
Hi!
i am facing problem when inserting very large amount of data in database. my application inserts 100,000 or more records in database but when i try to insert only 1000 records it takes too much time, approximately 2-3 minutes. and when i try to insert more than 1000 records application crashes.
I am using Microsoft Access and C# for my application. one more thing i am not using Stored procedures for this.
Can anybody tell me how can i make this insertion fast...?
Thanks in advance..
Regards,
Affan Ahmad Toor
|
|
|
|
|
My guess is that it is not really the number of records that is the issue, but rather the way in which you are doing it. Frequently queries can be written in many-many different ways, and still accomplish the same end result. However, what's going on inside the db-engine may not be at all the same.
I had a query (seemed perfectly fine to me) that was taking about 8-seconds to run. This was WAY too slow as it was going to be used several million times. Everything looked fine to me and I couldn't figure why it was running so slow. I began experimenting, pulling things out, rearranging, and suddenly the query began running in microseconds instead of seconds. Something in the design of the original query was causing the engine to do LOTS of churning internally. I never did really understand what the problem was, but didn't care since I got it fixed.
Access doesn't have a profiler (that I'm aware of). The larger systems will actually SHOW you how the query is being compiled, and the steps that will be taken to accomplish it. You can use that to rearrange things.
Without knowing the text of your query, and what you're trying to accomplish, I doubt anyone here is going to be able to give you any specific help. The only general advice I can give is to use the built-in query wizards. They will often show you how to do things you might not have thought of on your own. They write queries that Access recognizes how to optimize.
|
|
|
|
|
I have created a DTS to load the data from csv file in to a table in the database. Upto this its working fine. I did it with automapping column.
But now, I want to check for the duplicate entry of the Bank Recipt column(which is not a primary key column), if no entry found then it will upload the file data in to the table other wise it will give some error message.
And that error message I would like to store in table in the same database and show it to the user.
If anybody have idea about it, please give me advice or code snippet if possible. Thank you.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
what is SqlDataAdapter and SqlDataReader? and what is different between SqlDataAdapter and SqlDataReader?.these 2 r different from dataadapter and datareader.
|
|
|
|
|
|
Hi Friend'z...
I want to enter a row data to the sql server table, from the data entered by the user in the windows form.
I hav a query
string str="insert into books values ("+'@textBox1.Text'+","+'@textBox2.Text'+")";
what is wrong in this? pl help me in doing....
thanks in advance
 
|
|
|
|
|
Write down the query with actual values...Best way is to run in debug mode and then see wat is the actual query in str variable.
Mubashir
Every job is a self portrait of the person who did it.
|
|
|
|
|
hope the two values you are trying to insert are strings
then the statement should be ...
string str = "INSERT INTO books VALUES ('" + textBox1.text + "', '" + textBox2.text + "')"
Regards
KP
|
|
|
|
|
Try
SQLstr="insert into books ({col1}, {col2}) values ('" & textBox1.Text & "', '" & textBox2.Text & "')";
(This is VB ... I'm not sure what language you're using ... the important thing is the {col1} and {col2} which are the names of the table colums that @textBox1 and @textBox2 are going to be inserted into)
I find that a good debug tip is to use Query Analyser to test the structure of my SQL statement before I insert it into code.
So I would try something like INSERT INTO books VALUES ('SQL Programming', 'Robert Vieira') and when that didn't work I'd try INSERT INTO books (title, author) VALUES ('SQL Programming', 'Robert Vieira')
|
|
|
|
|
Hi all,
I have 4 tables where in each table 3 coulmns are the primery key(Year,Month,ProductID) and it has notherextra different columns.
Now i wanna join all four related to primery key. and combine all columns.
Table 1
Year,Month,ProductID,Location,Sales
Year,Month,ProductID,Location,Returns
Year,Month,ProductID,Location,DuePayment
Year,Month,ProductID,Location,Profit
I wanna join and get Following
Location,Sales,Returns,DuePayments,Profit
Also i wanna group the table Location
And Filter it for Month=03
Please Help me
I need the SQL Select statement
Regards
Ruwandi
rkherath
|
|
|
|
|
It should be something like:
Select t1.location, t1.sales, t2.returns, t3.duepayment, t4.profit
from table1 t1
join table2 t2 on t1.year = t2.year and t1.month = t2.month and t1.productid = t2.productid and t1.location = t2.location
join table3 t3 on t1.year = t3.year and t1.month = t3.month and t1.productid = t3.productid and t1.location = t3.location
join table4 t4 on t1.year = t4.year and t1.month = t4.month and t1.productid = t4.productid and t1.location = t4.location
where t1.month = '03'
order by t1.location
It might be a good idea for you to get a sql book so you can learn the basics of write sql queries.
Ben
|
|
|
|
|
Hi Ben,
Thx for replying. I tried on the code and it didnt work out. Actually It doesnt show error but no records shown as the resul. I checked manually and there are records that should be displayed.
I have a doubd that , is it a problem that i am using year , month, Pro ID, Location as composite key(All are primary keys in both the tables)
if the case like this is there a different way to do this.
regards
Ruwandi
rkherath
|
|
|
|
|
As per what i understood hope this is what you are looking for ....
SELECT Year, Month, ProductID, Location, SUM(Sales), SUM(Returns),
SUM(DuePayments), SUM(Profit)
FROM (
SELECT Year,Month,ProductID,Location,Sales FROM TableA
UNION
SELECT Year,Month,ProductID,Location,Returns FROM TableB
UNION
SELECT Year,Month,ProductID,Location,DuePayment FROM TableC
UNION
SELECT Year,Month,ProductID,Location,Profit FROM TableD
)a
WHERE Month = 3
GROUP BY Year, Month, ProductID, Location
Regards
KP
|
|
|
|
|
Hi all,
I have created a dts package to upload data from a csv file in to a table in SQLServer. Now I want to do 2-things.
1. I dont want the same rows to be loaded in the table more than once, means the records must be unique.
2. I would like to display an error message in to a .Net UI when DTS package fails runnig, or when the same records are tried to be loaded.
Please tell me any idea how can I achieve this, because I am new to this DTS Package services. Thank you.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
