| Author |
Topic |
|
mlevier
Starting Member
33 Posts |
 Posted - 2007-11-01 : 11:48:56
|
| What can you do when your transaction log is full and you can't reduce it's file size? We are working on it's second backup and hoping that it will help. I know that is no a fix for it though. I have tried several statements and have had no luck.I tried the DBCC OPENTRAN command which said that nothing was open.I have tried USE CPADBCC SHRINKFILE(CPA_Log,TRUNCATEONLY) andDBCC SHRINKFILE ('CPA_Log',128)the second one of those commands gave me output which i dont't understand. We have even looked at the sys.database as well.We have tried these commands in both simple and full recovery modes. I'm not sure what else to look for or try to do to solve this problem. I feel that I am walking in the dark with no leads.Any imput would be great. If I'm not providing enough information please let me know.Thanks!!! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-01 : 11:50:21
|
| what is DBCC SHRINKFILE ('CPA_Log',128) saying?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
mlevier
Starting Member
33 Posts |
Posted - 2007-11-01 : 11:51:47
|
| Dbld Field CurrentSize Min.Size UsedPages Est.Pages 7 2 13820864 128 13820864 128 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-01 : 12:01:26
|
| just to make it clear:your db was in full recover mode.you changed it to simple and tried dbcc shrink file and it didn't work?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
mlevier
Starting Member
33 Posts |
Posted - 2007-11-01 : 12:02:46
|
| YES |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-01 : 12:15:32
|
| is there anything in your sql error log?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
mlevier
Starting Member
33 Posts |
Posted - 2007-11-01 : 12:51:29
|
| nope |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 13:04:37
|
| spirit:This was being discussed over at : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91338 |
|
|
|
juancabrera
Starting Member
30 Posts |
Posted - 2007-11-01 : 13:43:49
|
| I don't know if you got the problem fixed. I don't know why it's doing that, but as a work around, why can't you create a back up of the db and then restore it as a new database. Then you can destroy the old one, rename the new one, and then you are set. How many gigs is it anyway? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-01 : 22:47:28
|
| Backup log first. |
|
|
|
juancabrera
Starting Member
30 Posts |
Posted - 2007-11-02 : 15:51:53
|
| Yes you may want to back up the log first, even though you may not need it since your new db back up should have the latest transactions. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-02 : 16:24:09
|
| as he pointed out he also did back it up._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
Can't reduceTransaction Log