December 16, 2011 at 9:10 am
I ran into this problem where the database became full and locked up.
I ended up deleting some historical data that was not needed to clear up some space. To my surprise after I executed the limited truncation on the tables, the database actually grew in size.
When I looked more closely I realized that the transaction log had grown supstantially, almost doubling the size of the DB as a whole.
I went and executed a shrink on the transaction log file. Now the DB is below 2 GB (this is MSDE, but same applies to 2005 express with a limit of 4 GB).
My question: what is the best way to clear up space in MSDE and SQL Express DB? Is shrinking the transaction a legitimate approach, or does it affect performance.
We need to mitigate this, until we upgrade to full SQL server. Your feedback is greatly appreciated.
December 16, 2011 at 9:45 am
The <n> GB limit applies only to the data files, not the logs.
This is one of the good reasons to run a tlog shrink (big delete, grows the log, won't likely happen again).
If you don't need point in time restore you can switch the db to simple recovery before the shrink. And that should mostly take care of that problem for good.
December 16, 2011 at 9:50 am
The database is already set to Simple Recovery.
December 16, 2011 at 10:07 am
Ok, so you're all set? Anything else you need hep with?
P.S. Simple mode doesn't stop the tlogs from growing, it just won't happen because someone forgets to take regular tlog backups.
If you rebuild a massive index, the whole operation is logged, and the tlog will grow as necessary. And since autoshrink is off by default (thank God), the file won't get back down to the original size.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply