Log file vs. Data file

[nowayout]

Hi all,

I have this question, one of my data base log file is bigger than the data file why? how can i shrink it?? i tried shrinking it with dbcc shrinkfile but did not help!!

any other way to do it?

Thanks

 

[SQLBill]

Do you do Log backups? (BACKUP LOG dbname)

If not and you dont' truncate the log, it will keep on growing.

There's lots of information in the FAQs and in this forum (do a search) on shrinking the transaction logs and backing them up.

-SQLBill

Posting advice: FAQ481-4875

 

[nowayout]

how would i set up the truncate log file while backing them up??

 

[SQLBill]

You don't need to do both, you need to either do backups of the log OR truncate the log. That's why I said: If not (you don't do log backups) and you don't truncate....

If you backup the logs on a regular basis, they will shrink down. It just doesn't happen immediately. Plus they can't shrink smaller than the size they were built as nor smaller than the active portion. If you are backing up the logs, you can 'force' a shrink by using DBCC SHRINKFILE and using the path for the log file.

If you don't care about backing up the logs, you can use BACKUP LOG <dbname> WITH TRUNCATE_ONLY

Refer to the BOL for more information about both commands.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875