October 31, 2011 at 7:04 am
I have 'poke and hope' knowledge of SQL Server (look at something similar and mimic it). The nice thing about SQL is how well it
We have an ERP database and created created an additional database, ERPPlus, where we create our own tables. We use Access to develop reports and supplemental activity not in the ERP system. Our ERP vendor's database tech did include ERPPlus in the maintenance plans.
This morning we got a Disk Full situation and have diagnosed it back to the log_reuse_wait_desc.
We bought some time by rebooting SQL Server.
Now we need to figure out: Why this happened, how to truncate the file, and how to prevent this from occurring again.
Help me Obe Won, you're our only hope.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
October 31, 2011 at 7:14 am
are the databases in full recovery mode?
if so, do you need them to be in full recovery?
if you do need them in full recovery then you need to start taking transaction log backups if your not already, or do them more frequently.
if you dont need full recovery and can lose the amount of data you have done since your last FULL backup then change the recovery model to simple which will checkpoint the log instead of transactions stacking up without being backed up.
October 31, 2011 at 7:39 am
Hi Anthony,
You're making me smile.
I poked around and found the database ...Properties > Options > Recovery Model > ...
The ERPdb was set to Simple.
My ERPPlusDB was set to Full. I changed mine to Simple.
What, if any, next steps should I take? (i.e. to recover disk space)
Maintenance Plans
- There are backups at Noon and 6:00 PM and I see Backup Up Database (Full) and Type: Full.
- There is a weekend maintenance plan with full backups too.
Can I assume that during the Noon backup the 29.6 gb ERPLusDB_log file will become smaller?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
October 31, 2011 at 7:49 am
you will have to use a shrinkfile command to reduce the amount of space the transaction log is using.
so issue sp_helpfile in the db, copy the name of the ldf file, e.g running sp_helpfile in the master database you want to copy mastlog
then issue dbcc shrinkfile ('nn',##)
where nn is the name you have copied and ## is the amount of space in MB you want the file to be after the shrink
so dbcc shrinkfile('mastlog',1) to shrink the log to 1 MB
this will then give you the space back, but can lead to fragmentation so keep your eye on performance.
as a general rule of thumb, are all of the databases new and old going to be SIMPLE mode or FULL mode?
if everything is going to be SIMPLE, then change the recovery mode of the MODEL databases to SIMPLE, this way any new DB you create will be SIMPLE, saves running into this again, otherwise, when you create a new DB setup a maintenance plan to do transaction log backups should you use FULL recovery.
October 31, 2011 at 8:25 am
Thanks for the info. While I was waiting for your reply I was searching for the answer myself and came across a MSDN entry.
- It looks like a Wizard version of your steps?
- Which do you suggest I do, yours or the wizard?
Whatever steps I take - I am going to be extremely cautious before proceeding (like I am working with high voltage electrical lines).
Assuming I follow your steps: I want to review them to make sure I perform them correctly.
Your steps:
1. Open a New Query, connect to the ERPPlusDB.
2. Execute sp_helpfile (enter the text and press F5).
3. In the results I will see an entry ending in _ldf (ERPPlus_ldf?, I've seen _mdf and _ldf before)
* 'you want to copy mastlog' - you are using this as an example, correct?
4. Enter and execute: dbcc shrinkfile('mastlog',1)
* You are suggesting 1 for the size? I looked in MODEL and it's size is 6 - should I use 6?
Your questions:
Yes, all the databases will be Simple - since that is what the ERPDB is, I will mimic that one. I changed the model to Simple.
If you suggest using the following, what do you recommend I do about steps:
5 - I'm guessing check it.
6 - I'm guessing check it and use the value 1 or 6 (whichever you suggested above
7 - ?? Don't have a clue
How to: Shrink a File (SQL Server Management Studio)
SQL Server 2008 R2
Other Versions
This topic describes how to shrink a data or log file by using Object Explorer in SQL Server Management Studio. The primary data file cannot be made smaller than the size of the primary file in the model database.
To shrink a data or log file
1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Expand Databases and then right-click the database that you want to shrink.
3. Point to Tasks, point to Shrink, and then click Files.
4. Select the file type and file name.
5. Optionally, select the Release unused space check box.
Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
6. Optionally, select the Reorganize files before releasing unused space check box. If this is selected, the Shrink file to value must be specified. By default, the option is cleared.
Selecting this option causes any unused space in the file to be released to the operating system and tries to relocate rows to unallocated pages.
7. Optionally, enter the maximum percentage of free space to be left in the database file after the database has been shrunk. Permissible values are between 0 and 99. This option is only available when Reorganize files before releasing unused space is enabled.
8. Optionally, select the Empty file by migrating the data to other files in the same filegroup check box.
Selecting this option moves all data from the specified file to other files in the filegroup. The empty file can then be deleted. This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.
9. Click OK.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
October 31, 2011 at 8:49 am
its six of one, half a dozen of then other, wizard or code, entirely up to you. personally I like to do things in code, that way I kind of know what SQL is doing as the wizards might do other steps, but its up to you what you would like to do.
yes i was using mastlog as an example
if you used the default settings and didnt change anything when creating the db it should be something along the lines of ERPPlusDB_log so you would want to execute
dbcc shrinkfile ('ERPPlusDB_log',1)
as for the size, again this is your choice, you have to think about other factors, eg do you ever do index rebuilds etc, if so then the log will grow while the index is rebuilt etc if not then give it a few meg, if it does what is the size of your biggest index and times it by 1.5 and set the log to that size, so for a index of a gig shrink it to 1536MB, if your not to sure then shrink it by how much and keep your eye on it and see how the log grows
you can only select either option 5/6/7 so the one you would want to do is 5
what you can do is use the wizard if thats your prefered method and get it to the point where your about to hit OK, but choose the script option at the top of the wizard and send it to a query window, this way you can see the T-SQL which has been generated
October 31, 2011 at 9:03 am
First things first. Please read through this - Managing Transaction Logs[/url]
Please, if you do shrink the file (which will be necessary) DO NOT shrink it to 1 (DBCC SHRINKFILE(<filename>,1))
Doing that means that the first thing the file will do is grow again. Shrink it to a reasonable size for the regular usage of your database. If you have no idea how big that is, select something conservative for the size of the DB file. Also set the autogrow to something reasonable for the size of the file (it defaults to either 10% or 1MB, both of which are very silly)
You say you switched to simple recovery. Do you know what that implies?
If the DB were to fail now (hard drive failure) would the users accept losing all data back to the last full backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 9:37 am
I think the crisis is over.
Thanks so much for your help Anthony. I can't express how much I appreciate your quick help and how I liked your approach - providing steps and explanations. I manually performed the shrinkfile, like you I prefer to know what's going on. The db itself is 12,416KB so I chose size value 18 - 1.5 times db size. (I can't go wrong with oversizing at this small a size).
Gail - Thanks for the alert. I hadn't thought about this since we created the db. Our ERP vendor's tech guy suggested SIMPLE at the time. There are two daily backups, one at Noon and the other at 6:00 PM. Very little activity over night. We do realize that a failure would require a PITA (Pain In The A...) recovery of about 4 hours work, worst case, but accept it. We do have RAID servers as a further safe guard. My ERPPlusDB has less activity than the main one, so that part of the recovery is nothing compared to the main db.
A nice aspect of MS SQL Server is how well it runs without intervention. The down side is we have decided that I won't take extensive courses to learn about it. A key factor in the decision is that I would use the training so rarely I would probably have forgotten it when I needed it. We have been on SQL Server since 2004 - this is only the 2nd 'emergency' (recovery, but not a show stopper) in those 7 years. As I mentioned in my first post. We bought some time by restarting SQL Server, which cleared up disk space and employees have been merrily working along, not aware of what we've been doing to recover from the situation.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
October 31, 2011 at 9:43 am
Not a problem, always glad to help someone in need if I can.
Thanks to Gail as well, I should of mentioned that 1 is a stupid number to shrink to but was mearly saying it for explanatory reasons only.
October 31, 2011 at 9:47 am
EdA ROC (10/31/2011)
Gail - Thanks for the alert. I hadn't thought about this since we created the db. Our ERP vendor's tech guy suggested SIMPLE at the time. There are two daily backups, one at Noon and the other at 6:00 PM. Very little activity over night. We do realize that a failure would require a PITA (Pain In The A...) recovery of about 4 hours work, worst case, but accept it.
Do the users accept it? Do they know that in a disaster they could lose and have to redo half a day's work?
We do have RAID servers as a further safe guard.
RAID != backups.
A nice aspect of MS SQL Server is how well it runs without intervention. The down side is we have decided that I won't take extensive courses to learn about it. A key factor in the decision is that I would use the training so rarely I would probably have forgotten it when I needed it.
If that's the decision, then I strongly suggest this eBook: http://www.red-gate.com/products/dba/sql-monitor/entrypage/tame-unruly-sql-servers-ebook?utm_source=facebook&utm_medium=textad&utm_content=troubleshooting&utm_campaign=sqlmonitor
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 9:52 am
EdA ROC (10/31/2011)
We do have RAID servers as a further safe guard.
safeguard against what?
Presumably you mean you have RAID drive configurations not servers?
RAID is used for redundancy and performance, it does not safeguard your data, that is what backups are for. For instance, if you have a RAID 5 drive and you lose 2 disks your data is lost!
Taking 2 full backups per day could well be overkill. If you only require simple recovery consider a full weekly backup and differentials in between
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 31, 2011 at 12:06 pm
Do the users accept it? Do they know that in a disaster they could lose and have to redo half a day's work?
Well, they have to accept it. It's a decision our CFO made. I have to chuckle, I know the users didn't 'like' it, however, I don't think they realize as much as our CFO the royal PITA a recovery will be - including the consequences when some changes made won't be remembered in the recovery.
Thanks for the eBook reference!
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
October 31, 2011 at 1:29 pm
Yes, RAID Drives - What I meant by safeguard is that it's a level of protection above a single drive system. Hence, like a physical safeguard device with machinery - it doesn't stop an accident, it is a measure taken to reduce the risks.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
October 31, 2011 at 1:43 pm
EdA ROC (10/31/2011)
Yes, RAID Drives - What I meant by safeguard is that it's a level of protection above a single drive system. Hence, like a physical safeguard device with machinery - it doesn't stop an accident, it is a measure taken to reduce the risks.
It's like saying your GF is on the pill and then shoving it in the wrong hole.
Backups are the rubbers of sql server.
Tested backups is the ultimate protection. Just put those in the correct spot and you'll be fine, no matter what comes up!
:hehe:
October 31, 2011 at 1:44 pm
EdA ROC (10/31/2011)
Yes, RAID Drives - What I meant by safeguard is that it's a level of protection above a single drive system. Hence, like a physical safeguard device with machinery - it doesn't stop an accident, it is a measure taken to reduce the risks.
It's not a backup. Yes, it protects against single drive failure, but that's all. I've seen databases on RAID 10 lost due to drive problems. I've seen multiple cases where people use RAID 1 or RAID 5 thinking it'll protect them and they don't need all these backups, and end up losing huge amounts of data.
You say the CFO knows how hard and costly recovery from a disaster could be and still accepts multi-hour data loss? Wow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply