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 |
|
rsys
Starting Member
3 Posts |
 Posted - 2007-01-23 : 15:53:12
|
| hi,I am not a database guy, but I've been kind of forced into this role now.I find that one of the database's transaction log is taking up 20 GB of disk space! It is not a constantly changing database. So database backups (full) are only performed once every week using Windows native backup utility. And transaction logs are never backed up.I googled and found two methods to reduce the size of the transaction log and free up disk space.1. By using the DBCC SHRINKFILE utility. If I use this, what should I realistically set as the target size?2. By using the sp_detach_db and sp_attach_db system stored procedures as mentioned in the following article:http://www.databasejournal.com/features/mssql/article.php/1460151this sounds much easier. What are your thoughts on this method?I anyway went ahead and did a back up of the transaction log of this particular database. But it did nothing to change the size of the transaction log. The back up file (of the transaction log) was 2GB lesser than the original log, ie it was 18 GB. Is this how it is supposed to work?I thank you in advance for all your suggestions.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-23 : 15:55:27
|
| If you don't plan on backing up the transaction log, then the first thing you should do is change the database recovery model to SIMPLE. If you can afford to take the database offline momentarily, then option 2 is easiest.Tara Kizer |
 |
|
|
rsys
Starting Member
3 Posts |
Posted - 2007-01-24 : 10:22:14
|
| Thanks for your reply.At this stage, is it OK to change the database recovery model? Also, if I am to use DBCC SHRINKFILE, is it okay to set the target size as 500 MB (the database physical file size is around 500 MB) to start with?Thanks, once again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-24 : 11:59:29
|
| 500MB sounds good. You can change the recovery model at any time. Make sure to change it prior to the shrink first.Tara Kizer |
|
|
|
rsys
Starting Member
3 Posts |
Posted - 2007-01-26 : 11:51:01
|
| Thanks, I now have a plan of action.I have a couple more basic questions..1. Right now the database is in the 'full recovery model' (default, I guess). If I do a transaction log backup, it should remove all the entries from the log that successfully made it to the database since the last backup, shouldn't it? In my case, when i backed (full) up the transaction log, it did nothing to change the file size of the log (as I mentioned in my first post). How come? Would it have made a difference if I had backed up the transaction log more frequently?2. At this stage when i change the recovery model to 'simple', will it reduce the current size of the log file or will it only affect the future log entries?Thanks a lot for all your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-26 : 12:05:20
|
| 1. Transaction log backups don't affect the LDF file size. They affect how much free space is inside the file. You can only affect the LDF file size with a shrink, which isn't recommended unless you know for sure it won't need that extra space again (which in your case is probably true since it's 20GB). This is due to the huge performance hit you'll receive if the file needs to expand because you shrunk it too small.2. No. You must perform a shrink.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
Transaction log size question