SQL2000 transaction log

[Guest_imported]

WE INSTALLED SQL 2000 with 2 instances. The database was wrongly set with a 'recovery model' of 'full' while recommended for our software was 'simple'.The database ran for 2 months but the transaction log merrily grew to 10 GB for a database size of 1 GB!It resulted in the hard disk filling up...we did the command for deleting the log completely. Now SQL says it's gone but the physical file of 10 GB is still there alive and well.How do I get reduce the size of this?

 

[clapag22]

try:

dbcc shrinkfile ('logical file name')

You can get the logical file name of the log file from the sysfiles table in your DB.

 

[SheetsERR]

I see that you'll be using the Simple Recovery Model. Somebody correct me if I'm wrong, but doesn't that mean that really only full backups will be utilized? Will you ever need to restore transaction logs?

If you've deleted the log in SQL Server, you should be able to delete the physical file in NT Explorer/File Manager.

Am I right?

SheetsERR

 

[tlbroadbent]

I recommend that you read faq183-1534 "Shrinking Databases and Logs - SQL 7 and Higher." It should help you understand log files, shrinking, truncating, etc.

I also recommend reading about Recovery Models in SQL BOL. That will help you determine which Recovery Model is best for your databases. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.