Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2011-11-03 : 10:16:19
|
| I have a database which is in Simple recovery mode and its size is 30GB. The data file is 10GB and log file is 20GB. So my question is does Log file really have 20GB of data in it or did it just grown because of some trasaction in the past? How can i figure that out?Thanks in Advance. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-11-03 : 10:27:39
|
| If you right-click the database in Management Studio, select Reports, and select Disk Usage, you'll get a pie chart of each file showing how much is used and unused. If you find the log file is mostly unused, you can shrink it to a more acceptable size. Make sure you only shrink the log file, not the database.If you need to prevent log growth you should set a maximum file size on the log file. |
 |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-11-03 : 10:49:36
|
| Thanks!! it was almost empty. Will shrink it. |
|
|
|
vikki.seth
Yak Posting Veteran
66 Posts |
Posted - 2011-11-03 : 11:56:25
|
| Shrink the log file to a level to which it can grow during intensive log operations (eg. dbcc checkdb, reindexing etc). |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-03 : 12:32:31
|
| CheckDB doesn't use the transaction log.--Gail ShawSQL Server MVP |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-11-03 : 13:02:01
|
| Does rebuilding the indexes use the log space??? i never thought it would. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-11-03 : 13:11:21
|
| Of course it does. Operations are still logged in case they need to be rolled back. The only thing Simple recovery does is clear the transaction log after a checkpoint, and therefore prevent the log from being backed up. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-03 : 18:37:50
|
| Index rebuilds are probably the operations that use the most log space of anything.--Gail ShawSQL Server MVP |
|
|
|
Arunkumarco
Starting Member
2 Posts |
Posted - 2011-11-04 : 06:58:10
|
| Hi,Of course rebuild indexes take more log space |
|
|
|
|
|
|
Log file is more than data file