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 |
|
suresh.belur
Starting Member
2 Posts |
 Posted - 2008-11-19 : 01:39:45
|
| Hi,In SQL Server 2000, suddenly we started to get the msg "Transaction log full" and the database would not open. We have set a job for daily Full database backup and we also run DBCC SHRINKDB after the backup. Now the log file has grown to 20GB (backup sizes are only 17 MB). We restored the last backup and we are able to access the data in Enterprise manager and query analyser, but the log file is still around 19 GB, while the database size is only 17 MB.Another thing we observed is that it is not a sudden occurence. If we try to restore the backup of last month, the log file is about 12 GB; so the log file has been steadily increasing every day.We have already tried the following1. We restored the DB in SQL server 2005. There is no change in the log file size2. Attaching the mdf,ldf files on another server - Gives msg "Failed to recover at checkpoint ...."3. All Tasks --> Shrink files - There is no change in the log file size.Can someone please advise us as to how can we reduce the log size? |
|
|
suresh.belur
Starting Member
2 Posts |
Posted - 2008-11-19 : 03:48:20
|
| Well, we found the answer. In case somebody else has the same prob, run the following sql commands in the query analyser, in the same order specified:1. BACKUP LOG <databasename> WITH TRUNCATE ONLY2. DBCC SHRINKFILE (<logfilename>,<desiredsizeinMB>) |
 |
|
|
|
|
|
Transaction log full