Ok, so, here we go. I have an SQL database where the data is 2Gb and the log is 18Gb. The system keeps complaining about the log file being full even though I've set the mode to SIMPLE and trunc on chkpt. I'm out of disk space so I can't, and don't want to grow the log file any further. How can I tell exactly how full that log file is? We don't plan on recovering the database, so as far as I'm concerned, I can turn logging completely off.
Any ideas or suggestions?
You cant make Logging off completely . yes ofcourse you can make it minimally logged when u make it simple recovery model. Once you made it simple ,since your log is of 18 GB , now you have to truncate & shrink it to get the size reduced.
The growth of Transaction Log(TL) is depends on couple of things... (a) the Recovery Model (b) TL Backup freqency.
(a) first check whether you need Full recovery model , if not change it to Simple
(b) WHat is the Backup policy. if its in FUll recovery model and if you are taking the TL backup then increase the frequency of the TL backup
(c) If you want to shrink the file then you will have to truncate and shrink it. After truncating and shrinking the first step should be a full backup. otherwise the backup chain will break and u will not be able to restore from TL bakcup.
Refer :
http://support.microsoft.com/kb/873235.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1542414&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1630021&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1623857&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1447193&SiteID=1
madhu|||a) It's set to SIMPLE - now.b) We don't backup. We really don't have a need to.
c) the truncating seems to work, BUT in the Console if I select Tasks, Shrink, Files I see no change in the available free space. Should I?
Keep in mind that I have truncate on checkpoint set to true, and if I do a checkpoint, I get an error back saying the transaction log is full - even AFTER I've run backup with no_log.
|||
(a) Truncate the log first
BACKUP LOG yourdatbasename WITH TRUNCATE_ONLY
(b) Shrink the file after truncation
DBCC SHRINKFILE(LogicalLogfilename, Size)
read about this in BOL
Madhu
|||Many thanks for the replies.I've done the backup several times. I just tried the dbcc command and recieved the following:
Cannot shrink file '2' in database because of minimum log space required.
Current size 2277232
USed pages 2277232
I tried using a Mb size and it choked. I forget the relation between pages and Mb. It's been too long........
|||1page = 8 kb i believe..........just use as specified earlier .........
|||That did it! I was trying to give it a size and it didn't like it. I just dropped off the size parameter and she shrank. Thanks, guys!
