shringing log file 1

[terry712]

firstly i must point out my sql knowledge sucks

have a sql 7 dbase on nt4 - runs like a pig - i beleive the guy who set it up didnt do a good job - ie when i look at it the size for the dbase is 1100 mb's and its set to autogrow - the actual dbase is 990mb

the log though is set to 1500mb and is actually a paltry 20mb's in size.

i want to get the dbase to run a bit sweeter and preferably on a different server - i feel the log size should be more like 150mb or 200 mb but how do you shrink

if i backup database and then restore onto a 2000 box with sql 2000 and reattach users then it seems to work at a sql level - still runs like a pig and file sizes obviously the same

i tried to restore to another loaction and the dts the data into a new dbase with better settings but get weird errors

so basically with a sql 2000 database how do i reduce this log file size down to 200mb - i assume it's weird boffin sql commands but what?

cheers

 

[clapag22]

backup log <dbname> with no_log
GO

--For recovery's sake, you should always do a full DB
--backup after issuing that command

dbcc shrinkfile(<fileid>)
GO

/*
you can get the fileid by querying the sysfiles table in your DB. If you have one data file and one log file for this DB, I'd bet the fileid is '2', but you may want to check just to be sure.
*/