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 |
|
ColinD
Starting Member
40 Posts |
 Posted - 2007-04-18 : 04:17:33
|
| Hi,I have a 176MB database with a 9GB transaction log. This surely can't be right. At he moment I only have 16 users none of whom are heavy users, but within the next month this will increase to 50, most of whom will be quite heavy users.Up until yesterday I didn't have a Maintenance Paln, so the database was just growing out of control. Last night my maintenance plan ran for the first time. I set it to backup the database and the transaction log.I've found a few articles about shrinking the log, by right clicking on the database in Enterprise manager, but it's only reduced it to 8.5GB. I have been told that truncating the log at backup will not actually reduce the physical size of the file, but will just reduce the amount of data in the file. I assume therefore that it has grown and grown over the past few weeks due to the fact that I have not correctly backed it up, and that now that I have done a backup, it is using just a small fraction of the 8.5GB.So, the question is, how do I get it to dramtically shrink to around the size it actually needs rather than the size it has grown to, and what is the best way to keep it at the smaller size? I understand that if I reduce it too much there will be a hit if / when it tries to re-size, but I'm prepared to accept that at the moment.Thanks for any helpColin |
|
|
craig79
Starting Member
33 Posts |
Posted - 2007-04-18 : 05:55:56
|
| U can make use of following codes to keep track of growth of Transaction Log Space DBCC SQLPERF (LOGSPACE)It will give u the % Log Space used out of the allocated space.If it is nearing to 100% u can truncate the log and free some more space for it to grow.BACKUP LOG (dbname) with NO_LOGAnd further if u want to shrink it u can use DBCC SHRINKFILE option to reduce the physical size of Transaction LOG but only after truncating the log . |
 |
|
|
craig79
Starting Member
33 Posts |
Posted - 2007-04-18 : 06:00:15
|
| Oops!Forgot to mention about the restriction of Transaction Log size.U can restrict the Maximum File Size growth in the file properties option. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-18 : 16:05:51
|
| Should backup log periodically if the db is not in simple recovery model. |
|
|
|
|
|
|
|
|
Transaction log query