| Author |
Can you shrink a SQL 6.5 log file?
|
|
| Dave Swift 2002-11-29, 6:23 am |
| I have a SQL 6.5 database consisting of a data file 1200Mb
and a log file of 600Mb. Only about 70Mb of the log is
ever used. Is it possible to shrink the log file down to
say 200Mb and if so how?
The Enterprise Manager will only allow me to increase the
Log device size not decrease it.
Any pointers gratefully received.
Thanks
Dave Swift
| |
| Steve Thompson 2002-11-29, 8:23 am |
| "Dave Swift" <dswift@ntlworld.com> wrote in message
news:1eb8701c297a1$99f515e0$8a
f82ecf@TK2MSFTNGXA03...
> I have a SQL 6.5 database consisting of a data file 1200Mb
> and a log file of 600Mb. Only about 70Mb of the log is
> ever used. Is it possible to shrink the log file down to
> say 200Mb and if so how?
> The Enterprise Manager will only allow me to increase the
> Log device size not decrease it.
As I recall, shrinking a database or a log file was not supported until SQL
Server 7.0.
You'd have to drop your database and restore from backup. Be sure to TEST
this first, as I'm not sure if the SQL Server 6.5 restore will complain when
restored to different sized devices (it's been too long since I worked with
6.5).
Steve
| |
| Catelin Wang 2002-11-29, 9:23 am |
| No way to fiscally do it.
"Dave Swift" <dswift@ntlworld.com> wrote in message
news:1eb8701c297a1$99f515e0$8a
f82ecf@TK2MSFTNGXA03...
> I have a SQL 6.5 database consisting of a data file 1200Mb
> and a log file of 600Mb. Only about 70Mb of the log is
> ever used. Is it possible to shrink the log file down to
> say 200Mb and if so how?
> The Enterprise Manager will only allow me to increase the
> Log device size not decrease it.
> Any pointers gratefully received.
> Thanks
> Dave Swift
| |
| Tibor Karaszi 2002-11-29, 3:33 pm |
| Dave,
You would have to run DBCC SHRINKDB to get rid of some database fragments. If you are lucky.
Then run sp_help_revdatabse so you know how to create the database that you will restore later.
Then backup the database. Drop the database. And get rid of the devices (sp_dropdevice...,
delfile).
Then create the devices with structure according to output from sp_help_revdatabase. Then create
the database according to the structure from sp_help_rev_device. Then restore the backup.
My suggestion is that if above isn't entirely clear to you (if you don't fully understand 6.x
architecture regarding database fragments, the sysusages table etc), then hire someone
proficient with 6.5 to do this for you. Or spend quite some time practicing on a test server
until you get the database and backup/restore architecture in 6.5.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Dave Swift" <dswift@ntlworld.com> wrote in message
news:1eb8701c297a1$99f515e0$8a
f82ecf@TK2MSFTNGXA03...
> I have a SQL 6.5 database consisting of a data file 1200Mb
> and a log file of 600Mb. Only about 70Mb of the log is
> ever used. Is it possible to shrink the log file down to
> say 200Mb and if so how?
> The Enterprise Manager will only allow me to increase the
> Log device size not decrease it.
> Any pointers gratefully received.
> Thanks
> Dave Swift
|
|
|
|