Recovering unused space on Sql 7.0 tables

Hi List,
I have 4 tables with 20+million rows (3rd party app) anyway these tables are
reporting their space used in the 50+gigabytes but the space they are
claiming is about 70-90 gb is there anyway to force them to shrink to
recover this space.
thanks
evanDepends on how the database files were created. SS 7.0 is kind of PITA
regarding file management... It's one of the main reasons to upgrade to
SS2K.
Anyway, execute the following from within the database you are discribing to
get an accurate representation:
EXECUTE sp_spaceused @.updateusage = 'true'
EXECUTE sp_helpdb 'MyDatabase'
Now, use the DBCC SHRINKFILE command to reclaim the space you desire on the
data files. SS 7.0 space reclaimation on the transaction logs is lousy.
More often than not, you have to detach the database, move the transaction
log somewhere else, then use the sp_attach_single_file_db to have the
database brought back online with a newly created, and hopefully smaller,
transaction log.
Hope this helps.
Sincerely,
Anthony Thomas
"evan b" <evan_at_cheapaschips.com.au_removethis> wrote in message
news:OGlnIj52EHA.1404@.TK2MSFTNGP11.phx.gbl...
Hi List,
I have 4 tables with 20+million rows (3rd party app) anyway these tables are
reporting their space used in the 50+gigabytes but the space they are
claiming is about 70-90 gb is there anyway to force them to shrink to
recover this space.
thanks
evan

Recovering unused space on Sql 7.0 tables

Hi List,
I have 4 tables with 20+million rows (3rd party app) anyway these tables are
reporting their space used in the 50+gigabytes but the space they are
claiming is about 70-90 gb is there anyway to force them to shrink to
recover this space.
thanks
evan
Depends on how the database files were created. SS 7.0 is kind of PITA
regarding file management... It's one of the main reasons to upgrade to
SS2K.
Anyway, execute the following from within the database you are discribing to
get an accurate representation:
EXECUTE sp_spaceused @.updateusage = 'true'
EXECUTE sp_helpdb 'MyDatabase'
Now, use the DBCC SHRINKFILE command to reclaim the space you desire on the
data files. SS 7.0 space reclaimation on the transaction logs is lousy.
More often than not, you have to detach the database, move the transaction
log somewhere else, then use the sp_attach_single_file_db to have the
database brought back online with a newly created, and hopefully smaller,
transaction log.
Hope this helps.
Sincerely,
Anthony Thomas

"evan b" <evan_at_cheapaschips.com.au_removethis> wrote in message
news:OGlnIj52EHA.1404@.TK2MSFTNGP11.phx.gbl...
Hi List,
I have 4 tables with 20+million rows (3rd party app) anyway these tables are
reporting their space used in the 50+gigabytes but the space they are
claiming is about 70-90 gb is there anyway to force them to shrink to
recover this space.
thanks
evan

Recordset is read-only

Hey,

I'm using CRecordSet to add new rows to some table.

The Sequence of operations is as following:

m_recSet->Open();

m_recSet->AddNew();

m_recSet->Update();

In the AddNew() function i'm getting an Exception Saying "Recordset is read-only".

I saw in previous posts that this problem is caused when there is no Primary Key, but, my table

does not have a Primary Key and i don't want to set one.

How can i add rows to a table that doesn't have Primary Key ?

Thank

Shahar

On http://www.thescripts.com/forum/thread82452.html I see the same problem being discussed. Is there a reason why you don't want a primary key on the table?

Thanks

Waseem

|||

The reason is as simple as there is no single primary key, i don't want to disable duplicate rows, and even if i'll compromise on that

i'll need to create Primary Key that will include 5 Fields, which as i see it won't be very efficient.

Waseem Basheer - MSFT wrote:

On http://www.thescripts.com/forum/thread82452.html I see the same problem being discussed. Is there a reason why you don't want a primary key on the table?

Thanks

Waseem

|||Perhaps you 'could' add an IDENTITY column to the table.

recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1

Record set to file

Any tips on how to get a recordset into a file. I want the result of a stored procedure (multiple rows) written to a flat file. I've tried a data flow task with an OLE DB Source linked to Raw File destination but that produces gibberish in the file - I need the raw rows returned from the s/p

Any tips much appreciated.

Greg.You can use DTS (Data Transformation Service) to accomplish this.|||I understood SSIS was the new DTS. Any pointers on how I can go about this Mike?

Greg.
|||Yes, in v2005. I haven't used it extensively, but you can get the information on how to here: http://msdn2.microsoft.com/ms141823(en-US,SQL.90).aspx|||

What do you mean by raw rows? The raw dest produces a binary file that is for use by the dataflow. If you want a readable file then use the flat file destination, which will create a text file.

HTH,

Matt

|||Thanks guys. Got it figured out using the flat file destination.

Greg.