Unable To Release Space Occupied By A Dense Populated Table
Aug 22, 2007
Hi ,
I have a table let say TableA , which have a size of 22 GB.Due to its size i down size this table by deleting 40% rows.So ideally it should size to 15 GB but space is not released by TableA. How i can do it ? I tried shrink database, shrink data file wizards and DBCC command , Reorganize the index but all is in vain.
I am using SQL2005 SP1 and I have a 4Gb Tempdb (datafile) with virtually nothing in it.
I am unable to release the free space to the operating system. I have used dbcc shrinkfile...truncateonly but this has had no effect.
There is no error message and there are no open transactions, I have attempted to drop the data file by transferring to a new file but I can't because it the primary file...
I have a table which has 6 text columns (tblA).. I no longer require 1 of those text columns and want to reclaim the space that it is currently taking up..
Is the only way to BCP out all the data (except the 1 column i no longer require) drop the column and BCP the data back into the table?
I want to know following memory space related question.1 . How can i get a Database Size by means of a SQL query?2. How can i Get a field size (not allocated space) .. ie. i amstoring some textual data to a field..after a insertion i want to know how much space has been occupied bythat particular field .ex sno product_name Description1 sample1 dfkjsdkfj kldsjfkdjksdkjdfskdjk vcmvxcvmcvnksdjfkdsn mHere i want know the space occupied by the field name "description"for the product id=1.Thanks in adavanceRegardsVisu.
The system I had developed is having a data flow as following:
Source received as file, .dat file For better performance I€™m doing little transformation between .dat file to SSIS Raw file then from Raw files doing Type2 and Type3 mappings to adhere the business rules and loading the data to destination tables.
The .dat file I receive (there are many file some where around 25 file) is dumped in a folder as €œsource€? and the Raw file are in other folder as €œSSIS files€?.
My concern is the source folder size is 6GB and the same files converted in SSIS raw files format present in SSIS FILE folder and the size of this folder is 10GB.
Why is that so? Where as there€™s no extra data and the transformations between source and SISS files are like substring for the different date format and data type conversion.
Any ideas, your help in this would be appreciated.
Hi all, I'm running SQL 7.0. Due to large tables, the space on the SQL server was getting filled up. So i truncated some tables. Now even though the SQL dbase shows 3Gb free, the HDD shows only 150 MB free. How do i get SQL 7.0 to release the sapce back to HDD?
When -all- records from a table with a varbinary(max) column are deleted (not via truncate), the table properties still show a dataspace size from before the delete operation. Inserting new blob records only leads in the growth of the allocated space withouth reusing the empty already allocated space.
Runnning commands like dbcc updatusage/checkdb/cleantable/reindex or sp_spaceused @updateusage = N'TRUE' seem to have no effect.
Does anyone know when space allocated by a varbinary col. is released?
thanks, Derk
running SQL 2005 STD ed SP2. DB is in simple recovery mode.
SQL7: After doing a full backup Enterprise Admin still shows "Transaction log space" in use. Isn't it that way that full backup releases all space formerly used by log files?
OS: Windows Server 2008 R2 Standard SQL Server: 2008 SP1 Standard
We have a database with about 500 GB of free disk space on data file and the database is being set to read only mode for the fore-see-able future. We would like to release this unused disk space. We know that we could shrink the data file and then work on re-indexing to remove fragmentation.we wanted to check if some other method like backup and restore of the database could free up unused disk space in the data file. if someone was able to release unused disk space on data file by implementing a backup and restore.
I have scenario where I have process that loades data into SQL server 2012 database by doing some manipulation on data like sorting , aggregation, etc. Once this process is completed it's not free up the Tempdb space. If I restart the database, then it does.
is there any way (apart from shirking) to release space for Tempdb, like writing some post SQL queries to delete/ truncate the data and logs from temp db?
I have a database that has grown larger than I had expected. I thinkthere is one table that is at the root of the problem. The table isdefined as follows:CREATE TABLE [dbo].[UserAudit] ([UserAudit_id] [int] IDENTITY (1, 1) NOT NULL ,[UserAuditAction_id] [int] NOT NULL ,[Dataset_id] [int] NOT NULL ,[UserName] [char] (64) NOT NULL ,[TableName] [char] (64) NOT NULL ,[Detail] [varchar] (4000) NOT NULL ,[DateRecorded] [smalldatetime] NOT NULL ,[Dsc] [char] (256) NULL)There are 14919 records in this table. When I do the calculations iapproximate that space used by this table should be in the region of10mb (4+4+4+64+64+290(average length of Detail column)+4+256) * 14919.When I execute "sp_spaceused 'UserAudit'" i see that 119MB are beingused by this table.name rows reserved data index_size unusedUserAudit 14919 119808 KB 119352 KB 400 KB 56 KBEven if I use 4000 for the Detail column in my calculations I stillcome up with about only 64mb.Any ideas on whats going on here?Thanks in advance.
I have a database with literally hundereds of tables in it. Can anyone advise me of how to tell which ones are populated with data and which ones arent?
I ran the code below in QA, and @@ERROR never gets populated with any error code, despite the fact that an error occured. This event is very misleading and errors are never caught. Any advice greatly appreciated.
-- Create the table create table test_table ( test_field int )
-- Execute the code DECLARE @err_status int, @row_count int insert into test_table ( test_field ) values ( 'TEST STRING' ) select @err_status = @@ERROR, @row_count = @@ROWCOUNT
IF @err_status <> 0 PRINT @err_status
-- Results: Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 'TEST STRING' to a column of data type int.
I currently have 8,000 rows in the orders table and it is estimated that it will have in average 50 orders daily. The orders need to be kept for 6 months, before it is archived and deleted from the database. I calculated the amount of space that needs to be reserved for the table but unsure if I am on the right track in calculating the table size.
would there be anything wrong in my calcultation that i missed?
int datetime tinyint nvarchar(15) int int int int money money money money datetime
I have a table name in SQL Server 2000 that has a space in itex: aim internationalI had trouble just in the query analyzer with this..I had to place thename in brackets [] for it to work. But now I'm in Visual Studio .Net2003 and it gives me another problem. I get the table name from a dropdown list selection and send it to a query string. But is gives me thiserror:***************Line 1: Incorrect syntax near 'AIM international'.Exception Details: System.Data.SqlClient.SqlException: Line 1:Incorrect syntax near 'AIM international'.******************Here is the string:****************Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &"' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'ORDER BY Last_Name "**********************And the variable PubName is the string AIM international .I tried placing it in brackets like in the query analyzer :****************Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &"'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'ORDER BY Last_Name "*******************and I get this:*******************Invalid object name ''AIIM international''.Exception Details: System.Data.SqlClient.SqlException: Invalid objectname ''AIIM international''.*******************Any idea what I have to do for it to work ??? Can I use table nameswith spaces or it's just not a good idea???Thanks for the help guys!!JMT
Hi, I hav eplaced an expression for the flat file connection as below
@[User::FileDirectory] + @[User::FileName]
This is supposed to be used instead of the ConnectionString property of the flat file connection.
You can see that I have created two variables.
The variable @[User::FileDirectory] is set to the directory. i.e. I have hardcoded the path to it and assigned it to this variable.
The variable @[User::FileName] is picked up automatically.
The question is:
When I go to the properties of the flat file connection, I delete the value inside the connectionstring property becuase there is now the expression which is set to the connectionstring. But when I come back to this property then I am not sure why the connectionstring property gets populated with the directory that I hardcoded to the variable.
I'm trying to determine how much space some tables use (SQL2000), and I found 2 suggestions posted earlier, but can't get them to work for me.
The first was "Right click on the DB in Enterprise Manager, select view then taskpad."
When I try that, I get some of the tables displayed with the info I want, but I can't see them all and can't scroll down beyond the first 22 tables in the database.
It says "command copleted successfully, but where does the output to this go ?? Is there something other than "print" I should use ? The grid pane is empty.
I have a stored proc that processes large amounts of data. I have used temp tables in this stored proc. After I drop these temp tables, it doesn't release the disc space and my hard drive runs out of space before the process ends. When I stop the sql service and restarts it, it releases the disc space. How can I force the release of disc space inside of my stored procedure?
I'm trying to figure out how to reclaim unused space in a huge table (tbl) after setting one of the text columns to be the empty string. Other tables have foreign key columns associated with tbl.
After doing something like this,
update tbl set col = ''
the table doesn't automatically reclaim that space. If I do a direct insert into a new table like this,
insert into tbl2 (cola, colb, colc) select * from tbl
the new table is smaller as expected (about half the size in my case). Any ideas?
The default behaviour for pressing SHIFT + SPACE when editing text inside a cell in the Table Editor view in Microsoft SQL Server Management Studio Express is to change focus to select all cells comprising the current row. This behaviour lends itself to the default behaviour for pressing SHIFT + {LEFT | RIGHT | UP | DOWN } in changing the focus to select adjacent cells.
However, I find this is quite annoying as the SHIFT + SPACE combination is quite a common miss-type when entering text in UpperCaps or when entering text with spaces while holding the SHIFT key.
Anyone else have this gripe with MSSMSE? Anyone know how to disable this behaviour?
Using lot intermediate table in SP, at end of the SP, those table are getting truncating.but those pages are still unallocated and unused , How to unallocated the space and add to OS drive.
Because of above scenario, we are wasting lot of space. i am seeing lot of unallocated space.
Hi, I am trying to do this: UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl') What would be the syntax. Any help appreciated. Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005. The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions 1) Replacing spaces with €œ €? 2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
I have a table with two columns: OwnerName Owner John;Smith Mary;Smith
OwnerName is populated. Owner is not. I want to populate the Owner column with the OwnerName in alphabetical order. I have already created a function to do this. Select fnGetOwner(OwnerName) from OwnerTable. This returns: Smith, John Smith, Mary How do I populate the blank Owner field beside the OwnerName in the OwnerTable?
Hi, I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data. I have seen the problem earlier and could not resolved it. Please let me know if any one can help.