March 22, 2012 at 1:15 am
Dear forum member,
I have an interesting question.
The disk space where I keep my SQL data of one of my dbs is running out of space. I tried to backup the transaction log, which works fine, but the log is still 8 G big. When I check the initial space allocated for the log file, it is 8 G. That is ofcourse the reason why the log will not decrease in size.
I cannot decrease the initial space allocated since the log is at this moment 8 G, but I can also not get the log smaller because the initial space allocated prevents that. I'm running around in a circle here.
How can I solve this? The initial size of the log doesn't have to be that big, I think this is from the start of the database when a lot of data was migrated into it.
Anyone?
Thanks.
Vera
March 22, 2012 at 6:13 am
Even though initial size is 8GB, you should able to shrink the log file.
I believe there are active transactions at the end of the file. That is preventing you from shrinking the file.
Run DBCC LOGINFO(dbname). Status 2 means active transaction.
You have to get rid of that active transaction.
March 22, 2012 at 7:51 am
I tried a shrink but you cannot shrink to a size smaller than initial size.
March 22, 2012 at 8:54 am
Have you tried using DBCC SHRINKFILE? That does allow you to shrink the file smaller than its original size.
Per BOL.. "Use DBCC SHRINKFILE to shrink a file to a size that is less than the size specified when it was created."
March 23, 2012 at 1:18 am
Thanks, got it solved with DBCC SHRINKFILE (filename, size).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply