What can I do...I have a customer that's C: is now full because it has a 270gb Transaction Log. I'm not a SQL guy...so I'm not sure what options I have to help this customer?
Thanks!
What is their backup plan for this database? See e.g. this article or this one.
Quoting from the second link: If your log file expands and fills up your disk, you are doing at least one thing wrong:
This what I generally do in a SQL query, might be a consideration. (Where ScanRep1 is the database). Make sure you have good backups just in case.
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ScanRep1
SET RECOVERY SIMPLE;
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ScanRep1_log, 1);
-- Reset the database recovery model.
ALTER DATABASE ScanRep1
SET RECOVERY FULL;
To respond to Brian's comments, the few times I have had to do this were exceptions to the rule, running massive SDK imports overnight or something with limited drive space, (#3 above) . I have had rare occasions in past versions where the transaction log will grow for no apparent reason, you do the above and it's a one-off, never rears it's ugly head again; I assume some sort of SQL hiccup or something. I am by no means a DBA, I was just applying a quick fix to an unusual situation...
This is possibility #6 in the first article I linked. It is not recommended. If you're not going to do proper backups, why return the recovery model to full?
After reading the Article's above, I'm reluctant to do a Shrink...although I'm not certain what else I can do (as I mentioned...I'm not a SQL guy) as the disk space is full and that is preventing them from being able to use Laserfiche at all. I inherited this account from previous support tech that was here, they clearly are looking to me to help them. Brian...in the articles...I do believe it wasn't setup properly to begin with as it's a Production DB and should be in Full Backup Mode, however, is that something that has to be done initally...what can I do now that it has grown to resolve this issue?
Thanks!
The best thing to do would be to take a log backup, which should shrink the transaction log while also backing up the data. But if it's a production database they should already be taking automatic regular backups, so the fact that the log is so large is possibly a symptom of something wrong in the backup process. I'm sure the users are frustrated about not being able to get into their system, but I hesitate to recommend any action until you understand the situation better.
John's solution will free up some space, but he does warn that you should have a good backup already, and what you've reported makes me think that's not the case. Also it's only a temporary measure until the disk fills up again.
Brian,
They are using a Datto SIRIS backup solution that backs up the server disks at the block level. The backup is updated (ran) every hour. What they have found is that the transaction logs are not being truncated during these backups. Setting the SQL recovery mode to Simple on all databases is the published solution. In Simple mode, the dbs are truncated. With their backup method, they don’t see a need for the Full recovery mode. They have set the mode on the Laserfiche dbs to Simple. Here is the current condition of the log file in question:
BCHS 263625.4 MB in size - Log space used is 0.09872612 percent
The log file is huge but empty. Shrinking it down would seem to be the solution, but I’m not a DBA.
Note: The last successful backup update was at 1:08 PM today.
The backup frequency is more guided by the business decision of how much data loss is acceptable in the event of hardware failure. It would be pretty unusual for the transaction log to grow problematically large in just 4 hours, so this shouldn't be an important factor. Once you've done a log backup I think you'll need to manually shrink the transaction log (SQL just frees the space internally), but this should just be a one-time thing to get it down to a normal range for the amount of transaction data between backups.
Brian,
Great...that's what we thought, but I wanted your insight first. Thanks for all your help on this!!