SQL Db Size Will Not Decrease After Deleting Data

Jun 6, 2004


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


View 6 Replies


How To Decrease The Size Of Data File ?

Jul 30, 2007

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.

View 4 Replies View Related

How To Decrease Log File Size

Dec 7, 2005

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


Oko Sakti Banget

View 4 Replies View Related

How To Decrease The Size Of The Transaction Log Daily?

Jan 23, 2008

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

View 22 Replies View Related

How To Decrease The Database File Size

Jul 19, 2007

Hi all,

How to derease the database file size in Primary filegroup.


View 5 Replies View Related

Does DB Size Decrease When I Delete A Huge Table ??

Jan 15, 2004

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 ??

View 14 Replies View Related

Transact SQL :: Database Size Does Not Decrease After Dropping All The Tables

Oct 15, 2015

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 .

View 9 Replies View Related

Reduce Table Size Without Deleting Data

Jul 20, 2006

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 Related

SQL 2012 :: Data Size Of Table Is Not Reduced Even After Deleting Millions Of Rows

Sep 21, 2015

I 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 Related

Is It Possible For A Table To Increase Size When Deleting Records?

May 24, 2007

No 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


View 2 Replies View Related

Database Space Size Increased On Deleting Records

Feb 20, 2008


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?

View 6 Replies View Related

SQL 2012 :: Deleting Large Batches Of Rows - Optimum Batch Size?

Oct 16, 2015

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?

View 9 Replies View Related

SQL2005 Data Import Error, Unicode Data Is Odd Byte Size For Column &&<n&&>. Should Be Even Byte Size

Aug 23, 2006

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

View 5 Replies View Related

Encrypted Data Size By Original Size, Algorithm ?

Jan 24, 2006

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.

Yoshihiro Kawabata

View 3 Replies View Related

Best Practice For Data Size/Log Size Ratio

Jul 23, 2005

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 Related

Data Mining :: Deleting Old Data From Adventure Works 2012 With Powershell

May 3, 2015

I 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] ....

View 3 Replies View Related

Decrease A Number In Column By One

Mar 16, 2007

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


View 5 Replies View Related

Performance Decrease After OS Upgrade

Jul 23, 2005

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 Related

Power Pivot :: Deleting Data Content From Data Model

Sep 10, 2013

I 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] ....

View 8 Replies View Related

Use Datatableadapter To Decrease Item Quantity

Sep 3, 2007

 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 Related

Archive Data Instead Of Deleting It To Prevent 4GB Data Limit

Mar 14, 2008

We 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.

View 10 Replies View Related

Deleting Existing Data Before Loading New Data

Apr 10, 2007

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?

View 4 Replies View Related

Deleting The Master Table Withour Deleting The Child Tables

Aug 9, 2007

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.

View 9 Replies View Related

Can Decrease To Accomodate Contents Not Working For Subsequent Pages?

Feb 13, 2008

This is an example of my data:

Col1 Col2 Col3

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
222 2
333 3
444 4
Subtotal Col3Sum (PAGEBREAK)
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.

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.

View 3 Replies View Related

Deleting Data From DB

Nov 23, 2006

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'))))
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
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?

View 1 Replies View Related

Deleting All Data

Jul 26, 2007

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

T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?

Jul 18, 2014

/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
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:'.

View 9 Replies View Related

SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Sep 4, 2007

I am trying to resize a database initial log file from 500M to 2M. Im using?


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 doesnt keep the changes.

Any help with this process?

View 1 Replies View Related

Deleting Data From Database

Nov 23, 2004

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

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)

End Sub

Sub btnView_Click(sender As Object, e As EventArgs)
dgMember.DataSource = GetMember(memberID.Text)
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
rowsAffected = sqlCommand.ExecuteNonQuery
End Try

Return rowsAffected
End Function

<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
<h2 align="center">Delete a Member
<asp:HyperLink id="HyperLink1" runat="server" Width="94px" NavigateUrl="Default.aspx">Main Menu</asp:HyperLink>
<hr />
<br />
<br />
<br />
<p align="center">
<form runat="server">
<br />
<div align="center">Please Enter Member ID :
<asp:TextBox id="memberID" runat="server"></asp:TextBox>
<br />
<br />
<br />
<br />
<div align="center">
<asp:Button id="btnView" onclick="btnView_Click" runat="server" Width="204px" Text="View Member Details"></asp:Button>
<br />
<br />
<p align="center">
<asp:DataGrid id="dgMember" runat="server" BorderColor="Black" OnSelectedIndexChanged="dgMember_SelectedIndexChanged" AutoGenerateColumns="False" OnDeleteCommand="dgMember_Delete">
<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 DataField="Member No" HeaderText="Member No">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="Surname" HeaderText="Surname">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="Forenames" HeaderText="Forenames">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="Known as" HeaderText="Known As">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="Title" HeaderText="Title">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="Address" HeaderText="Address">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="Home Tel" HeaderText="Home Tel">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="Email" HeaderText="Email">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="DOB" HeaderText="DOB">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="StartDate" HeaderText="StartDate">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="LeaveDate" HeaderText="LeaveDate">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<asp:BoundColumn DataField="clubsoc" HeaderText="Club/Society">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
<p align="center">
<p align="left">
<br />
<br />
<!-- Insert content here -->

View 2 Replies View Related

I Have A Problem For Deleting Data

Oct 23, 2007

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

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

View 3 Replies View Related

Deleting Data After Replication.

Aug 8, 2007

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...

View 6 Replies View Related

Reporting Services :: Decrease Space Between Columns In Stacked Column Chart

Sep 9, 2015

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 Related

Trouble Deleting Data In Table

Mar 9, 2007

I 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????

View 1 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved