Compressing SQL Data Base

Compressing SQL Data Base

Post by Gary C. Ne » Wed, 07 Jul 1999 04:00:00



SQL 6.5 | NT 4 (SP3)

I am currently maintaining a SQL Server Data Base which has two tables.
At the first of each month, in one of the tables, records older than 3
months are deleted by a scheduled TSQL statement.  After several months
of this activity, it has come to my attention that though the records
are deleted the space still seems to be in use (the max alloted size of
the db is used up).  However, new records are still being written to the
db without error.  It is my guess that the record data is being deleted
on schedule, but the meta data for the data isn't.

I know in MS Access there is an option to compact or compress a db and
I'm wondering if there is an option like this for MS SQL Server or will
this even solve the problem at all?

Gary
--
Gary C. New "Web Development, SMIP Interactive"
___________________________________________________
Smip Interactive, Inc.          v 703.442.5210 x233
http://www.smip.com             f 703.442.5205  
___________________________________________________
http://www.creators.com  
Comics, advice, horoscopes and opinion - all the best in online
entertainment.

 
 
 

Compressing SQL Data Base

Post by JDulber » Wed, 07 Jul 1999 04:00:00


Look at the result of sp_spaceused carefully. Also run sp_spaceused on
the table. Also check the size of the log via sp_spaceused syslogs. And
run dbcc checktable(syslogs) to reset the space used calculations.
Double check that the rows have been deleted as you guess by doing a
select.
There is no compression available.
jd
-----Original Message-----

Posted At: Tuesday, July 06, 1999 6:24 PM
Posted To: datawarehouse
Conversation: Compressing SQL Data Base
Subject: Compressing SQL Data Base

SQL 6.5 | NT 4 (SP3)

I am currently maintaining a SQL Server Data Base which has two tables.
At the first of each month, in one of the tables, records older than 3
months are deleted by a scheduled TSQL statement.  After several months
of this activity, it has come to my attention that though the records
are deleted the space still seems to be in use (the max alloted size of
the db is used up).  However, new records are still being written to the
db without error.  It is my guess that the record data is being deleted
on schedule, but the meta data for the data isn't.

I know in MS Access there is an option to compact or compress a db and
I'm wondering if there is an option like this for MS SQL Server or will
this even solve the problem at all?

Gary
--
Gary C. New "Web Development, SMIP Interactive"
___________________________________________________
Smip Interactive, Inc.          v 703.442.5210 x233
http://www.smip.com             f 703.442.5205  
___________________________________________________
http://www.creators.com  
Comics, advice, horoscopes and opinion - all the best in online
entertainment.


 
 
 

Compressing SQL Data Base

Post by Tibor Karasz » Thu, 08 Jul 1999 04:00:00


Gary,

This really would take a whiteboard and a few hours to fully explain. Space
usage reporting is refreshed with DBCC UPDATEUSAGE, DBCC CHECKDB etc.
You can reorganize data by rebuilding indexes (DBCC DBREINDEX).

--
Tibor Karaszi
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


Quote:> SQL 6.5 | NT 4 (SP3)

> I am currently maintaining a SQL Server Data Base which has two tables.
> At the first of each month, in one of the tables, records older than 3
> months are deleted by a scheduled TSQL statement.  After several months
> of this activity, it has come to my attention that though the records
> are deleted the space still seems to be in use (the max alloted size of
> the db is used up).  However, new records are still being written to the
> db without error.  It is my guess that the record data is being deleted
> on schedule, but the meta data for the data isn't.

> I know in MS Access there is an option to compact or compress a db and
> I'm wondering if there is an option like this for MS SQL Server or will
> this even solve the problem at all?

> Gary
> --
> Gary C. New "Web Development, SMIP Interactive"
> ___________________________________________________
> Smip Interactive, Inc.          v 703.442.5210 x233
> http://www.smip.com             f 703.442.5205
> ___________________________________________________
> http://www.creators.com
> Comics, advice, horoscopes and opinion - all the best in online
> entertainment.

 
 
 

1. SQL 2000 and Compressed Drives or Compressed Data ???

I'm working on designing our new DW project.  We are looking to start
with 10 TB of data.  The question of the day is

Will SQL 2000 support NT compressed drives or provide a data
compression function?

Any help would be greatly appreciated.

Thanks
jonathan

PS
Also looking for the same info on Oracle 8 - all flavors.

2. Connects once, then fails

3. Communicating between data base and applications on a central data base or distr

4. Backups

5. Another Data Base is Accessing your Data base

6. Oracle 7.3 Long Fields

7. SQL*Loader reading compressed data files

8. Visual C++ or Delphi & btrieve for startup

9. HELP: A DLL that can compress like COMPRESS.EXE

10. Transferring SQL 6.5 data base to another SQL 6.5 Server

11. Web based form feeding a vb data base

12. Data...data....data....base

13. Build a recordset based on a dynamic list instead of a data base table