Hi
I installed " Web Wiz Forum ASP SQL 2000 DB "
it work fine but
when i added some data in the forum for example my db size is 1.45 MB
after i delete those data the db size will not decrease
is there any code that i must enter on the sql server setup file
Excuse me i asked this question in the web wix forum site but they don`t
answer me
if know what i must to do plz tell me
Thanks
Hello Guys,
Just wondering if someone can help me decrease the size of mdf and ldf files. In the past production database "NewUniverse" had been allocated space of 100 GB for mdf file and 8 GB of ldf file. However the data file has only used 30 GB of data. But now due to disk space related reason, I tried to decrease the datafile size from 100 GB to 40 GB. But I am not able to do it.
Any help in this regard would be appreciated.
Dear all,
I wanna ask you how to decrease log file size..?
For example i have database file with data file size is 122,166 Kb
and the database log file is 6,330,176 KB
Thanks,
Oko Sakti Banget
I have a database that the transaction log grows about 1 GB per day. I would like this size was decreased daily. Does anyone have any suggestions?
Some friends told me that after the Full backup that is done daily, I should perform a backup of transanction log with option to truncate and after, make a shrink in the database. That is exactly what should be done?
Thank you,
Best Regards,
Ralph Haddad
Hi all,
How to derease the database file size in Primary filegroup.
Thanks,
Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??
I have a database consisting of two main tables and 12 sub tables.
This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml  in a column of varchar(2000) type.
So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.
Surprisingly we saw that the database size increased rather than decreasing .
We are using SQL Server 2000 Standard ed, sp4 on Server 2000 Advanced.We have one table that is three times as large as the rest of the database.Most of the data is static after approximately 3-6 months, but we arerequired to keep it for 8 years. I would like to archive this table (A), butthere are complications.1. the only way to access the data is through the application (they areimages produced by the application-built on Power-Builder)2. there are multiple tables refrencing this table and vise-versa3. we restore the entire db to two other servers for testing and trainingregularly4. there might be more complications that have not been thought ofCurrently, our only plan is to setup a seperate server with a copy of this dbon it and the application. Leave only the tables necessary to access the data,and if this 'archive' works, remove from production the data from the table Aand all references to the table A from rows on the other tables.I mentioned #3 because someone mentioned a third party tool that may be ableto pull the data from the table, archive it elsewhere, and at the same time,place a 'pointer' in the table to the new storage location. The tool theymentioned only works on Oracle and we have not explored beyond that yet.I am ready to explore ideas and suggestions; I am still new to the DBA world,I am out of ideas.Thank you!--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200607/1
View 1 Replies View RelatedI have deleted nearly 30 million rows from a table. But however when I used the sp_spaceused command to calculate the data occupied by the table I don't see any difference in the data size of the table. In fact the data has increased to few MBs after the deletion, but not much.
View 8 Replies View RelatedNo transaction log involved, only the table itself.
Use sp_spaceused "table_name" to check the space used.
It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.
Recovery mode set to be simple, autoshrink turned on.
The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.
Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue
Thanks!
Hi,
I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.
When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?
I thght it might be log files..but db is set to Simple Recovery which does not utilize a Log File.
Any reasons?
In another forum post, a poster was deleting large numbers of rows from a table in batches of 50,000.
In the bad old days ('80s - '90s), I used to have to delete rows in batches of 500, then 1000, then 5000, due to the size of the transaction rollback segments (yes - Oracle).
I always found that increasing the number of deleted rows in a single statement/transaction improved overall process speed - up to some magic point, at which some overhead in the system began slowing the deletes down, so that deleting a single batch of 10,000 rows took more than twice as much time as deleting two batches of 5,000 rows each.
good rule-of-thumb numbers (or even better, some actual statistics and/or explanations) as to how many records should be deleted in a single transaction/statement for optimum speed? 50,000 - 100,000 - 1,000,000 or unlimited? Are there significant differences between 2008, 2012, 2014?
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,
Any help on this greatly appreciated...
ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
Many thanks
I want to know encrypted data's size for designing database field size.
For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.
I think the size does not depend to PassPhrase char length.
Regards,
Yoshihiro Kawabata
Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com
View 4 Replies View RelatedI am trying to delete tables from data where the ModifiedDates older than 9 years in AdventureWorks2012 database . I get console notified that foreign keys are dropped but the delete statement is throwing errors. I am sure that somewhere the key constraints are not getting altered, but i'm not able to figure it out as i'm a relative beginner to T-SQL. The error and code:
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID". The conflict
occurred in database "AdventureWorks2012", table "Sales.SalesOrderHeader
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$option_drop = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions;
$option_drop.ScriptDrops = $true;
[Code] ....
A want to user a procedure to decrease a number in a col. by 1 each day.
I need some type of operation
then a job has to run the procedure to decrease a number once a day.
I don't know how to go about the operation? I am going to the SQL Query Anylizer to help me run the script to test it. I have in a col. of 201 numbers, and I will subtract 1 from each day from all recorders. I guess you could call this a count down or timer. Does this code work for me?
Thank You,
Insert into newusers (Timer-1)
Select Timer
From newuses
GO
We were running a 4 processor server with 8 GB RAM and 800GB hard diskspace on Windows 2000 server and SQL Server 2000.Recently we have upgraded the server to 8 processor and added another800GB. The OS was upgradded to Windows 2003 enterprise edition.All the processor are 2.5GHz Xeon preocessors.After upgradation the performance of the server has gone down from whatit was giving before the upgrade.It seems that multiprocessing is not ocurring.The model of the server is HP DL740The OS is installed in a built in array(5i controller) of the serverand the SQL server is installed in a external array(6400 controller).I will really aprecite if anyone can give any clue to improve theperformance.Thanks in advance.Taw.
View 5 Replies View RelatedI don't know if the question has been nailed down. Aside from deleting tables, can we delete the *content* of data within the tables. It doesn't seem crazy that, if you can pull in data from a feed then you should be able to remove the content out again (without also destroying the user's meta-data work ). Reasons for this include:
- Security (a user may not have rights to see *my* data and should go refresh their own)
- Size (workbook doesn't need to have GB's of irrelevant data saved to disk in a workbook if it was just useful during development phase to a pre-production data feed)
- Bad data (pre-production data feed is not good data)
- User-friendliness (data feed was refreshed 2 years ago and workbook was saved to file server. Users shouldn't be presented with irrelevant data, but should get empty pivot tables until they go do their refresh)
Obviously Excel internally knows how to clear out PowerPivot data, given the prompt shown here: [URL] ....
But how does a user initiate this on their own (corruption aside)?
Previous time this question was asked, without a real resolution: [URL] ....
my friend said that to decrease quantity of item must use store procedure or trigger.. is there can use datatableadapter to decrease quantity? if yes.., so which the easy to use? procedure or trigger or tableadapter?ok, thx....
View 9 Replies View RelatedWe are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.
Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.
We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.
Any other solutions that would allow us to archive data instead of deleting it? Thanks.
I have a package which loads data from a flat file (csv) to 4 tables in a database.
Now, the load is incremental.
I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?
Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this.
Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
This is an example of my data:
Col1 Col2 Col3
1 LONGDESCRIPTION1 111
1 LONGDESCRIPTION1 222
1 LONGDESCRIPTION1 333
1 LONGDESCRIPTION1 444
2 LONGDESCRIPTION2 XXX
2 LONGDESCRIPTION2 YYY
2 LONGDESCRIPTION2 XYZ
2 LONGDESCRIPTION2 999
This is an example of what I want my report to look like (ill put row numbers to help identify)
Col1 Col2 Col3 Row Num
1 LONGDESCRIPTION1 111 1
222 2
333 3
444 4
Subtotal Col3Sum (PAGEBREAK)
2 LONGDESCRIPTION2 XXX 5
YYY 6
XYZ 7
999 8
Subtotal Col3Sum
The Settings:
I'm currently using a single TABLE DETAIL ROW with HIDE DUPLICATES in Col1 and Col2.
The LONGDESCRIPTION field can be expected to take 2 row heights worth of space because it wont fit.
I've also got CAN INCREASE TO ACCOMODATE CONTENTS and CAN DECREASE TO ACCOMODATE CONTENTS selected.
The Problem:
When Printing/PrintPreviewing Page1, the Height of Rows 2/3/4 ARE THE CORRECT SIZE (only 1 row high, meaning that can decrease to accomodate contents works).
BUT when Printing/PrintPreviewing Page2, the Height of Rows 6/7/8 FOLLOW THE HEIGHT of ROW 5 (two rows high, meaning that can decrease to accomodate contents DOES NOT WORK).
Rows 2/3/4 has the same Row Height as Row 1 ONLY ON PAGES after Page1.
Rows 6/7/8 has the same Row Height as Row 5 ONLY ON PAGES after Page1.
Can decrease to accomodate contents does not seem to be working for Pages after page 1.
I have edited the aspnet_Users_CreateUser stored procedure so that the UserId that is created when a new user is created is copied to a UserId field in another table. However, when I use the website administration tool to delete users that have been created, it gives me an error saying the delete statement conflicted with the reference constraint. I then added the following code in the aspnet_Users_DeleteUser procedure....
IF ((@TablesToDeleteFrom & 16) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_tt') AND (type = 'V'))))
BEGIN
DELETE FROM dbo.userclassset WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 hdhd
END
This code was then added to the function at the end which deletes the data from the aspnet_Users table when everything else has been removed
(@TablesToDeleteFrom & 16) <> 0 AND
Now when I delete a user in the website admin tool, it "deletes" (with no error) the user from the list but doesnt actually physically delete it from the database.
Any ideas?
Is there a way to delete all data from a database - all tables and alltables excluding system tables?sqlserver 2000?Thanks,Tmuld
View 3 Replies View Related/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
Edited by SomeNewKid. Please post code between <code> and </code> tags.
I have added a delete button to my datagrid, and put in what I think is the code to delete a member from the database at their Member ID, however when I have called a members details and the delete button is pressed, nothing happens!!??
Any ideas?
Here is the code:
<%@ Page Language="VB" %>
<script runat="server">
' Insert page code here
'
Function GetMember(ByVal iD As Integer) As System.Data.SqlClient.SqlDataReader
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [oga].* FROM [oga] WHERE ([oga].[ID] = @ID)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = iD
sqlConnection.Open
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Sub dgMember_Delete(sender as Object, e as DataGridCommandEventArgs)
dgMember.EditItemIndex = -1
dgMember.DataSource = GetMember(memberID.Text)
dgMember.DataBind()
End Sub
Sub btnView_Click(sender As Object, e As EventArgs)
dgMember.DataSource = GetMember(memberID.Text)
dgMember.DataBind()
End Sub
Sub dgMember_SelectedIndexChanged(sender As Object, e As EventArgs)
End Sub
Function DeleteMember(ByVal iD As Integer) As Integer
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "DELETE FROM [oga] WHERE ([oga].[ID] = @ID)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = iD
Dim rowsAffected As Integer = 0
sqlConnection.Open
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Finally
sqlConnection.Close
End Try
Return rowsAffected
End Function
</script>
<html>
<head>
</head>
<body id="dgMemberInfo">
<font face="arial">
<h1><img src="thlogotop.gif" align="left" /> <img src="thlogotop.gif" align="right" />
<br />
<center><font color="red">T</font>albot <font color="red">H</font>eath <font color="red">O</font>ld <font color="red">G</font>irls <font color="red">A</font>ssociation
</center>
</h1>
<h2 align="center">Delete a Member
</h2>
<asp:HyperLink id="HyperLink1" runat="server" Width="94px" NavigateUrl="Default.aspx">Main Menu</asp:HyperLink>
<hr />
<br />
<br />
<br />
<p align="center">
</p>
<form runat="server">
<br />
<div align="center">Please Enter Member ID :
<asp:TextBox id="memberID" runat="server"></asp:TextBox>
</div>
<br />
<br />
<br />
<br />
<div align="center">
<asp:Button id="btnView" onclick="btnView_Click" runat="server" Width="204px" Text="View Member Details"></asp:Button>
</div>
<br />
<br />
<p align="center">
<asp:DataGrid id="dgMember" runat="server" BorderColor="Black" OnSelectedIndexChanged="dgMember_SelectedIndexChanged" AutoGenerateColumns="False" OnDeleteCommand="dgMember_Delete">
<Columns>
<asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="Delete"></asp:ButtonColumn>
<asp:BoundColumn DataField="ID" HeaderText="ID">
<HeaderStyle font-bold="True" horizontalalign="Center" verticalalign="Middle"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Member No" HeaderText="Member No">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Surname" HeaderText="Surname">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Forenames" HeaderText="Forenames">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Known as" HeaderText="Known As">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Title" HeaderText="Title">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Address" HeaderText="Address">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Home Tel" HeaderText="Home Tel">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Email" HeaderText="Email">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="DOB" HeaderText="DOB">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="StartDate" HeaderText="StartDate">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="LeaveDate" HeaderText="LeaveDate">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="clubsoc" HeaderText="Club/Society">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
</Columns>
</asp:DataGrid>
</p>
<p align="center">
</p>
<p align="left">
<br />
<br />
</p>
<!-- Insert content here -->
</form>
</font>
</body>
</html>
my row in my data base consists of 12 coulmns, i have a duplicate values in 6 coulmns of it , i use select distinct for all it doesnt work for it it doesnot remove dupliacte , i cant make primary key or any constraint on this 6 coulmns together cause it contains duplicate values even if i choose ignore duplicate it doesnot work
example
id name adress telephone job gender deprtment
1 john dd 123 doctor m 1st deprtment
1 john dd 123 doctor m 2nd deprtment
so there is duplicate on id, name,telephone, job , gender and different in department so how i make a primary key on id , name , adress , telephone , job , gender together
also how to delete duplicate from it although i used distinct function and it doesnit remove duplicate
thanks in advance
I am looking for an opinion on the best way to delete all data from a table after it has been replicated.
The simple use case would be:
Data is replicated from production server to archive server.
Data is deleted from production serverThanks in advance...
I have another query regarding the graph/chart that i made in report builder. May i know on how to lessen the space between a column in my stacked bart chart.
View 5 Replies View RelatedI have a table where I want to delete some data from but I get this error.
You might have a record that has a foreign key value related to it, or you might have violated a check constraint.
What to do????