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

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

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

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

SQL Server Reports Incorrect "Data Space Available"

Mar 11, 1999

Thanks for anybody that can help me with this.

When I double click on a database in the Enterprise Manager and the "Database Space Available" (in the Database tab) always shows 0.00 MB even though there is space available. This only happens to one of our database. The data device is 1.8GB and log device is 360MB and I've allocated all the space from device to database.

We're using SQL Server 6.5 sp3 in NT 4.0 sp3.

Anybody knows why is SQL Server reporting 0.00MB when you know there is space avaialble. I need to know the data space available and log space available so that I can expand it when the space runs low. Please help.

Thank you,
Jimmy Jen

View 3 Replies View Related

Data Space Used By A Table In MSSQL Server 2005

Apr 4, 2007

How can I query (using TSQL) the data space used by a table in SQL Server 2005?

This is possible using SQL Serve Management studio. I can right click on table name and check the proeprties. But I want to write a TSQL script to check disk space used by all the individual tables in the database. How can I do that?

View 3 Replies View Related

Memory Allocation To SQL Server - AWE Under 32 GB

Apr 23, 2006

I am setting up a server under Windows Server 2003 Enterprise Edition (SP1) and SQL Server 2000 Enterprise Edition (SP4). The new server is a AMD opteron with 32 GB of memory. I noticed that PAE is enabled automatically by Windows 2003. Should I enable AWE for SQL Server 2000 and specify Max and Min amount of memory for SQL Server ?? Is there a limit on how much I could specify in MAX memory for SQL??

This is the first time I have a server with so much memory so I want to make sure that I do the right thing. In the past I only have servers with 8 GB of memory and we just enable AWE and specify Max memory to 6 GB or so.

Any advise is very much appreciated. Thanks,

View 5 Replies View Related

SQL Server Admin 2014 :: Space Did Not Gets Released After Removing Data

Nov 21, 2013

We have deleted 120GB of data but space did not released even after 2 days. Is there any reason for this? tell me how exactly it releases the space after truncating a 120GB table?

View 8 Replies View Related

SQL Server 2008 :: Deleting Data (rows) From Table To Reclaim Space?

Feb 11, 2015

I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.

If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?

View 8 Replies View Related

SSIS: Populate Data From Space Delimited File To SQL Server 2005

Aug 9, 2007

The problem is that I have (for example) following data

AA AA LH
BB BB ML
CC CC QA
DD DD PS

in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.

View 7 Replies View Related

Reporting Services :: Print Data With Blank Space And Move To Continue Data To Next Page

May 15, 2015

I am using SQL Server report 2008/2012 (SSRS) and my report viewer contains body content with 3 Row groups.
While printing the report, data print with blank space and move to continue data to next page.

Departure flight : 70 rows
First Page            : 42 rows printed
Second Page      : 23 rows printed [ Supposed to be print 28 , if the total count of records more than 23 and less than 42 then the page print only 23 records ]
Third Page           : 5 rows printed

Departure flight : 42 rows
First Page            : 42 rows printed [Report max. record allowed to print 42 rows so if total record is 42 then print perfectly ]

Departure flight : 26 rows
First Page            : 23 rows printed [Supposed to be print 26, if the total count of records more than 23 and less than 42 then the page print only 23 records ]
Second Page      : 3 rows printed

View 3 Replies View Related

SQL Server 2008 :: Log File Space Is 5 Times The Data File

Mar 16, 2015

one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.

View 9 Replies View Related

Data Space Available

Aug 20, 2001

If I right-click on a database and choose Edit, on the Database Tab Enterprise Manager displays the Data Space Available to be 0.00, even though I know there is atleast 10GB spare !. Any ideas ? Thanks.

View 1 Replies View Related

Data Space Available

Apr 26, 1999

I just ran a script that deleted over 100,000 records. I look in enterprise manager and it tells me I lost 15 megs of space. I expected I would have gained space after the deletes. Truncate log at checkpoint is selected so I am puzzled.

I went into ISQL and ran sp_spaceused and got the following results:
data: before 989126 after 822436 gain of 166690
index: before 340834 after 338108 gain of 2726

Where did the 169416 KB of space go to and how can it accurately reflect the correct sapce in enterprise manager?

Thanks,

Ivan

View 3 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

How To Know The Space Used In The Data / Log File

Apr 10, 2008

Hi,
I have 4 drives in my HDD namely C,D,E,F
On Each Drive, i have my Database Data, Log and Index File.

I wanted to know the Allocated Space / space used in the Database Data / Log or Index File.

What if i am not able to login to Database then how will i determine the used space / free space in the DB File or Log File or Index File.

Thanks in advance.
-- Chetan

View 3 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

Please Help!! How To Find Space At End Of Data In A Column

May 14, 2008

I need a query that checks each row in a column (hardness) to see if the last character is a "space". The column holds numbers (73.45) but is a varchar 50 type. It will always be the 5th character but it would be nice to not make this assumption. Please help!!
Thanks.

View 5 Replies View Related

Trans Log-&>space Allocated 27GB, Space Used 100MB

Mar 2, 2005

Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.

Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)

After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?

View 1 Replies View Related

Double Space Replaced With Single Space By Dbms ??!

Jul 20, 2005

This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale

View 2 Replies View Related

Adjusting Data Files Space Allocated On The Fly

May 2, 2005

We have a database that doesn't seem to be growing on its own correctly (or seems like it isn't). This database has been running really slow for the last week and we think this may be whu

Current size is listed at 963mb while allocated size is 959mb. Trans log size is set to auto-grow and is allocated 4mb

Can we change that number on the fly, and also change the the growth settings without affecting the system?

Being a live system we obviously do not want any downtime, but believe this will help with our slowdown.

Any other ideas would also be appreciated

Thanks!

View 4 Replies View Related

Why Data Page Save Some Free Space?

Aug 24, 2007

Hello guys.

I want to konw how many rows a data page could contains.

So i do some test to prove it.

The follow sql scripts i used:

create table table1

(

col1 char(2)

)

go

declare @a int

set @a=0

while @a<700

begin

insert into table1 values('aa')

set @a=@a+1

end

I add 700 recrods to table1,then use dbcc page command oversee the data page.

The result indicate that:the whole recrods are all in one data page,and the "m_freeCnt" flag equal 396.
(8192-96-700*9-700*2=396)So i think i can add more records to the data page.To my surprise, after i add a new record, sql server 2005 allocates a new data page and assign the recrod to the new space....

any suggestion?

View 4 Replies View Related

Memory Allocation

Feb 11, 2002

Hi,

Can someone give me some help on find out the memory limit for SQL Server 7.0 and how to limit the memory being used by the SQL Server?

Thank you very much in advance!
Denise

View 1 Replies View Related

Processor Allocation

Nov 4, 2002

I was wondering, is there a way to allocate processors to SQL2000. I have a server that has 4 processors, I would like to leave one just for the operating system and have SQL2000 use the other 3. Is this possible and do you think it would be recomended to do this? Or should i just leave the 4 processors for everything?

thanks in advance

View 1 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