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 |
|
harshal_in
Aged Yak Warrior
633 Posts |
 Posted - 2003-02-15 : 00:57:10
|
| hi,My transaction log file size is almost 800 mb while the datafile size is about 150 MB .I have regular transaction log backups scheduled every hour and full database backup once a day.Since a couple of days the transaction backup job is failing and the size of the transaction log is increasing.To truncate the log file I have backed up the transaction ,tried to shrink the transaction log file by dbcc shrinkfile ,dbcc shrinkdatabase and from EM too but none works .I donot want to detach the database to delete the transaction log.can anyone help me out?help appreciated.Regards,Harshal.Expect the UnExpected |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-15 : 02:05:14
|
| Probably the end of the file still has an active transaction. Have you checked for a long running transaction?Try dbcc loginfo to find active entries.You may have to perform dummy updates to release the end of the file.I always use detach/attach but you don't seem happy with it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-02-17 : 05:31:46
|
| Yes I too use detach /attach on my local production server but I am afraid to use it on the remote server it hosts more than four sites .Expect the UnExpected |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-02-17 : 06:25:04
|
| I ran dbcc loginfo, but am unsure about what all the columns are. I've looked in BOL book can't find anything.What do the values in status mean, mine are filled with 0s and 2s |
|
|
|
chadmat
The Chadinator
1974 Posts |
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-02-18 : 09:40:33
|
quote:
2 Means an active transaction.Are you on 7.0 or 2000?on 2000 DBCC Shrinkfile works. You should do a backup log with truncate_only first. If you are on 7.0 See this article:http://support.microsoft.com/default.aspx?scid=kb;en-us;256650-Chad
I have sql 2k with sp3Expect the UnExpected |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-18 : 12:48:55
|
| You will need to perform dummy updates to release the end of the file like nr suggests. To do this just create a dummy table and then insert dummy data into it. |
|
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-02-19 : 04:27:47
|
| It worked .Thnxs to all.Regards, Harshal.Expect the UnExpected |
|
|
|
|
|
|
can not shrink the transaction log!