BCP And Table Space Allocation Quandary

Jul 1, 2004

Hey all, first time poster here.
Cheers!

Enterprise Edition SQL2k sp3a on Win2k3 Enterprise Edition server.

Using BCP to load ~4 million records.

Largest record width is ~350 bytes.

When finished i have over 6 million pages reserved and only about 800 thousand actually used.

Which puts me 'round about 3 rows per page and WAY too much empty space.

YIKES!

Any thoughts as to why this is happening?

Or insight into how BCP handles page allocations that may be different than the typical extent based page allocations for table data?

Thanks!

View 14 Replies

Db Space Allocation

Sep 14, 2000

Hi,

I need to create a 5GB database with 4GB for data and 1GB for log in v7.0. I know that in v6.5 I would have created five 1GB devices - to go easy on the backups.

Could someone please advise on how I should distibute allocation of space.
Should I allocate 1GB to the primary files and 1GB each to 3 secondary files?
Should I just allocate 4GB to primary?

I would really really appreciate any reponse? If there are articles I would appreciate links.

Thanks
kira

View 1 Replies View Related

Space Re-allocation 6.5

Jan 16, 2001

I have a small data warehouse which periodically has old data deleted.
However after a delete the free space within the database is not released. If I copy the tables, drop them, recreate, and copy back, the space is there.

Any ideas what is causing this anyone ?

Thanks

View 1 Replies View Related

Db Space Allocation

Dec 15, 2007

Hi,

can some one throw some light on how the DBAs calucaulate the space allocations?

For example I have 30000 records which has 30 columns each defined as varchar(100) and if the db is full and wants to increase the space. Then how much extrac space should be allocated??

View 5 Replies View Related

How To Specify Disk Space Allocation For Databases ?

Aug 13, 2002

Hello ,

I wanted to know on what basis the disk space allocation for the databases is planned . Suppose if we plan 60 GB for data files ( mdf )for a given database then what should be the space allocation for the log files ( ldf ) and the tempdb ( both mdf and ldf files ).

Is there any thumb rule or any defined ratio for the same ?

Thanks

View 1 Replies View Related

How Much Space In A Db Is Available For Allocation To Tables And Indexes?

Nov 19, 2007

I was trying to find out how much space is available in a 2000 db for allocation to tables and indexes. I am trying to find the amount of space that has to be used-up before another allocation is automatically made to the database. I looked at sp_spaceused but BOL is rather sketchy at defining what the numbers it returns really mean. Is the "unallocated space" the value I am looking for?

Thanks,

Michael

View 1 Replies View Related

Problem In Row Size Space Allocation

Feb 15, 2007

Hi

I am using DTS to transfer tables from Oracle 9i to SQL Server 2000 sitting in a shared environment and managed to migrate
a lot of tables without glitch..

When I was migration a table <XYZ> from Oracle to SQL Server..The table was created in the SQL Server whilst the DTS threw an error that read when it was copying data and 0 rows were copied with the error message being

"Cannot create a row of size 8387 which is greater than the allowed maximum of 8060"

Incidentally the have a table in the Oracle DB that has 152 Rows of Data with 94 Columns..

Does any change needs do be done on the Admin side of the SQL Server to resolve this problem and faciliate effective transfer of data from the DB's?

Thanks in Advance

KI

View 1 Replies View Related

How To Force Space Allocation To A SS2000 DB?

Sep 17, 2007

I have to force a space allocation to a DB file in order to stop a Diagnostic Manager alert which states that the database is over 80% full. Now, I don't think this alert is rational, nor do I think it is useful in any way but that's the way it goes. My only option is to allocate more space to the DB so that the space used will fall below 80% so the alert will stop being issued.
So, can you tell me how to force a space allocation on an existing db file?

Thanks,

Michael

View 1 Replies View Related

Problem In Row Size Space Allocation

Feb 15, 2007

View 1 Replies View Related

Question On Data/log Space Allocation By SQL Server

Oct 20, 1999

I have a quick question maybe someone can answer me:

Suppose I have a database with data and log on same device, by default,
3 segments are created: system,default and logsegment.

Is the allocation , or any limit, among these segments(i.e. for system table, other table and indexes , log) done by the system ? or will the space required be allocated when they are needed ?
(i.e. I can have a situation of 10% database space used for data, 90% used for logging and I can also have 90% used for data, 10% used for log ?)
Is there any control us we can make ?

Of course, for recovery and performance reason, it is always recommended to have data and log on 2 diff. device.

View 1 Replies View Related

Extent Allocation/Free Space - SQL2000??

May 6, 2004

I've faced with this problem in my DB:

There are some tables that suffer inserts and deletes daily. These tables have 3 nonclustered indexes including the pk.

I perceived that the space used for these tables are growing day after day even ocurring daily deletes.

These inserts and deletes follow the keys of the pk in ascent order.

I ran DBCC SHOWCONTIG on the tables and got results like this:

- Avg. Bytes Free per Page.....................: 7996.3

When I transform the pk to clustered this problem doesn't happen.

You realized the consequence of this: the users complains because the DB is without space, but it's not true!

Anybody could help me to understand why the extents are not being deallocated?

Thank's for help!

View 8 Replies View Related

Table Allocation

Aug 17, 2006

Dear Friends

I have one database with multipe MDF files.Normally when I am creating a new table it's going to primary MDF file.How I can allocate a new table to the MDF file which I am specifying when table is creating

Thanks in Advance
Filson Paul

View 1 Replies View Related

Dynamic Allocation Into Table Partition

Feb 14, 2008

Hi,

I am trying to partition the table i have into 10 partitions based on the last digit of the employee ID.
The following is the logic I tried to use but was not successful.

Code Snippet
CREATE PARTITION FUNCTION PartitionRange (INT)
AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8)

CREATE PARTITION SCHEME PartitionScheme AS
PARTITION PartitionRange
ALL TO ([PRIMARY])

create TABLE PartitionedTable
(
counter INT ,
string CHAR(8000) ,
EmpID INT,
UpdateDate datetime
)
ON
PartitionScheme (EmpID % 10)

Any suggestions on how I can make the logic work as required.

Thanks in advance.

With regards,

View 3 Replies View Related

OpenDataSource Quandary

Aug 17, 2007

I am trying to open and query a Microsoft Access "like" database from SQL Server 2000 using OpenDataSource. This will work for a little while, but if I am to update my ASP.NET code base I have to completely reboot the machine for these queries to work again. I also have two reboot the machine periodically because this functionality decides to crap out. I am doing this from a stored procedure. My code looks like this:DECLARE @SelectCode varchar(5000)SET @SelectCode ='SELECT CONVERT(varchar, Value) AS UnitName FROMOPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', ''Data Source = "' + @Path + @DataFile + '.fcd";User ID=Admin;Password=;'')...Properties WHERE Property LIKE ''UnitName'''EXEC(@SelectCode)Now, when I am developing on my local machine that uses Windows Authentication, I have absolutely no problems, unless the source file has a schema error.Any ideas?

View 1 Replies View Related

MSQuery Quandary

Mar 3, 2006

Ok,

I need to pull out of the database the oldest invoice for each of my clients. Yet i think either or both of myself and MSQuery criteria are limited in this capacity.

please help.

Effectively

ClientA inv00001
ClientA inv00004
ClientA inv00007
ClientA inv00008
ClientB inv00002
ClientB inv00003
ClientB inv00006
ClientC inv00005
ETC...

Where i need to pull out just the bold records... sql script syntax or MSQuery criteria advice is appreciated.

cheers,
Oosters

View 5 Replies View Related

Distinct Query Quandary

Oct 10, 2007

Here's my query:

Select top 500 email, custnumber from MailingList order by email

There may be some duplicate email addresses with different custnumbers. What would the query look like to only show records with unique email addresses, but still return the unique custnumbers in the query.

Thanks in advance.

View 9 Replies View Related

Space Used By A Table?

Jul 20, 2005

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.

View 6 Replies View Related

Calculating Table Space

Nov 16, 2007

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

4
4
2
15
4
4
4
4
4
4
4
4
4
4
= 65 bytes
+ rowoverhead = 7

total = 72

View 12 Replies View Related

Query With A Table Name That Has A Space...

Jul 23, 2005

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

View 6 Replies View Related

Table Size - Trying To Find Out Space Used

Jul 8, 2002

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.

The second suggestion:

exec sp_msforeachtable @command1="print '?'",@command2="sp_spaceused '?'"

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.

View 2 Replies View Related

Temp Table & Disc Space

Dec 4, 2007

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?

View 6 Replies View Related

Reclaiming Unused Space In A Table

Mar 13, 2008

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?

Rob

View 3 Replies View Related

SHIFT + SPACE In Table Editor

Feb 6, 2007

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?

Simon

View 2 Replies View Related

How To Unallocate Truncated Table Space

Jul 21, 2015

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.

View 5 Replies View Related

How To Replace Empty Space Or White Space In A String In A Stored Procedure

Nov 14, 2007

Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks

View 1 Replies View Related

Mutilple Space Gets Converted To Single Space In Report Viewer Control

Feb 23, 2007

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 ��&nbsp�? instead of space and ��<Pre>�? tag instead of preserving spaces.

Please provide me a solution so that spaces can be preserved in Report Viewer.

View 1 Replies View Related

Transact SQL :: How To Find Space Available Or Send Space Alerts In Percentage

Nov 26, 2015

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 %

Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList VARCHAR(4000);
CREATE TABLE #tbldiskSpace

[Code] ....

View 3 Replies View Related

Estimate Log Space For Alter Table Statement

Jan 31, 2007

My client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database.
What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns.
Any suggestions? Thanks in advance.

View 4 Replies View Related

Need Help Approximating How Much Hard Drive Space I Need For My DB Table.

May 12, 2007

In our SQL Server database we will have a table that will be populated with about 2000 recordsper day. That is 2000 records per day for 5 days per week. Currently the computer we are using has about 50 gigabytesof available hard drive space on it. We are concerned that maybe we will need a bigger hard drive,based solely on the number of records entered into this table per day. The problem is I don'tknow how to calculate how much hard drive space we need. I think I read that using varchar,sql server 2005 really optimizes a database. Here is a typical example of data in ourdatabase. I put dots on three lines between the first and last sample record to justillustrate that there are many records in between.
Basically we only need 8 months of data at a time in the table and then we can purgerecords older than 8 months.Can someone help me approximate how much hard drive space I might need for 8 months of data,given the following sample record in the database?
Sample: -->34.5               4.08        10.6       .0012
Sample Table in my DB just for illustration:
(PPsquare inch) (Diameter) (Weight gm)     (coeffOfSatFriction)
34.5                     4.08           10.6               .0012...21.7               3.54        6.22         .019

View 4 Replies View Related

How Find Space Used, Database And Table Level?

Mar 22, 2000

The sp_spaceused proc seems to have been removed in 7.0?
Dbcc sqlperf also seems to have been removed?
Surely there must be a counterpart - which is?
I like to have a script which shows the space usage
especially on a table basis for a periodic report.

View 3 Replies View Related

Release Unused Space Reserved For A Table

Sep 22, 2005

Hey guys,

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?

View 12 Replies View Related

Release Table Space After Column Is Dropped

Apr 12, 2008

I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.

thx

View 6 Replies View Related

SQL 2012 :: Reclaim The Unused Table Space?

Sep 15, 2014

currently stuck with an issue where I need to reclaim the unused table space in SQL server.

ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size.

Similarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?

View 5 Replies View Related