Board index MS SQL Server

Trying to reduce size of MASSIVE log file

Trying to reduce size of MASSIVE log file

Post by LLoyd Mund » Thu, 21 Sep 2000 09:34:48



Ref SQL Server 7.0

I have created a database with a log file that I want to reduce in size. the
online help suggests
backing up the transaction log (which i've done successfully) and then
running the truncate
transaction log option to get rid of inactive transactions and thus freeing
up the space. i've
tried this many times but nothing ever seems to happen. I've unchecked the
"trunc. on checkpoint"
option when necessary but still no joy. my database file is 200mb and my
Transaction log 350mb!!!!
I don't need this file and i'm running out of space!!!. I've also tried
copying all objects
into a new database setup with a restricted sized transaction log. I get a
warning message telling
me that the log file being copied to is not big enough but the data NEVER
gets copied over. I have
ALL PERMISSIONS to all objects so that isn't the problem.

As you can probably tell I'm no expert with MS SQL SERVER 7.0 (Desktop
Implementation) but
this problem is really bugging me as the extensive on-line help seems to
suggest a work-around
but I can't find it

Any help would be greatly appreciated.

Thanks In Advance

LLOYD


 
 
 
Top

Trying to reduce size of MASSIVE log file

Post by Dan Guzma » Thu, 21 Sep 2000 11:50:50


Check out DBCC SHRINKFILE in the Books Online.

One of the gotchas regarding log files is that the file will not shrink
until a virtual log boundary has been crossed.  This will require subsequent
database updates after the shrink.

Hope this helps.


> Ref SQL Server 7.0

> I have created a database with a log file that I want to reduce in size.
the
> online help suggests
> backing up the transaction log (which i've done successfully) and then
> running the truncate
> transaction log option to get rid of inactive transactions and thus
freeing
> up the space. i've
> tried this many times but nothing ever seems to happen. I've unchecked the
> "trunc. on checkpoint"
> option when necessary but still no joy. my database file is 200mb and my
> Transaction log 350mb!!!!
> I don't need this file and i'm running out of space!!!. I've also tried
> copying all objects
> into a new database setup with a restricted sized transaction log. I get a
> warning message telling
> me that the log file being copied to is not big enough but the data NEVER
> gets copied over. I have
> ALL PERMISSIONS to all objects so that isn't the problem.

> As you can probably tell I'm no expert with MS SQL SERVER 7.0 (Desktop
> Implementation) but
> this problem is really bugging me as the extensive on-line help seems to
> suggest a work-around
> but I can't find it

> Any help would be greatly appreciated.

> Thanks In Advance

> LLOYD




 
 
 
Top

1. Reducing PDX5 table size after massive deletions?

Is it possible to reduce the file size of a table after most of
its rows are deleted? I seem to remember back with old 3.5 for
DOS that a "table/restructure" would release space in the file
back to DOS, therby reducing the filesize. This does not seem
to work in PdoxWin 5.0.

For example, I have a table that used to be vey large. After
deleting most of the rows (about 100,000 to 7,949), it seems
to be about the same size. I used a delete query. Sizes of
the various files are shown below:

 4-12-95  11:28a      1482           0  DRESULT.VAL
 3-09-95   2:44p      6144           0  DRESULT.Y05
 3-09-95   2:44p    133120           0  DRESULT.X05
11-16-95   3:17p    899072           0  DRESULT.Y03
11-20-95   9:55p   1153024           0  DRESULT.PX
11-20-95   9:11p  23463936           0  DRESULT.X03
11-20-95   9:55p  43816960           0  DRESULT.DB
        7 file(s)   69473738 bytes used
                   349814784 bytes free

This seems excessive for 7,949 rows - that comes to about
5.4 kbytes per row! There are only 18 columns in this table,
most of type "N", but a few small text fields too.

Also, this table exported to ASCII text takes less that
2.5 megabytes - a redutcion in size to about 5% of the
diskspace consumed by the DB file!

11-20-95   9:54p   2448293           0  DRESULT.TXT

Surely there must be a way to reduce file size - its
bulk is interfering with certqin query operations.

Please help! Thanks!

--

2. DMFRCP running away after Ingres upgrade.

3. Reduce the size of log file

4. MDI Child - Database tables - Stop user from selecting previously open tables?

5. SS 7.0 - Reducing size of transaction log files

6. What is the KQLS Heap?

7. How to reduce LDF log file size?

8. using "not in" in a select statement

9. How to reduce my LDF log file size?

10. Reduce log file size

11. How to reduce my LDF log file size?

12. how to reduce size of transaction log files


All times are UTC
Board index