April 12, 2012 at 11:53 am
I’m not a SQL Server guru, but I’ve inherited a SQL 2008 R2 server that is hosting several small databases for things such as Backup Exec, Symantec Endpoint Protection, WSUS, BES, etc… The server did not have any maintenance plans in place. Backup Exec is being used for backups and is only backing up the databases. The transaction logs where not being backed up or truncated. This has led to several issues now which we are trying to resolve. The main issue is that several of the data and logs are at 1% free space.
1.The first step I’ve taken is created a maintenance plan that checks database integrity, update statistics, backup database (full), maintenance cleanup tasks.
2.The second step is that I’ve changed all the databases to recovery mode: FULL.
3.The third step is I’ve created a second backup exec job using: Log – Back up Transaction Log.
Do the above steps seem correct?
I’m not sure what to do about low free space for the data and logs?
Also, the C drive is full because of a large amount of SQLDump.mdmp files and ErrorLog files located under the C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log
Please advise on steps to take to resolve.
April 12, 2012 at 12:04 pm
Please read through this - Managing Transaction Logs[/url]
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
April 12, 2012 at 12:10 pm
Thank you for the link. I've had a read through it and think I'm heading in the right direction as one of my steps was to set the DB's to "FULL".
What do you suggest I do for the DB's that have 0% free space for the logs?
April 12, 2012 at 12:12 pm
Log backups would be a good place to start for that.
But if the databases were in Simple recovery, and they're full anyway, that won't help. You'll need to expand the log files if that's the case. If they were already in Full or Bulk-Logged, then backups will probably (almost certainly) help with freeing up some space. But not if they were in Simple recovery.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2012 at 12:12 pm
If I run my backup exec DB job as well as my backup exec Log job should that free up disk space?
April 12, 2012 at 12:13 pm
It won't free up disk space directly. You'd have to either delete files or shrink files for that.
Log backups, in Full recovery, will usually free up space inside the log files. But not space on the drive.
Are the log files on your C drive?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2012 at 12:15 pm
Important note: Don't start shrinking files till you've done a few more steps to prep for that. I mentioned shrinking files, and don't want you to get ahead of yourself on this one. Done incorrectly, that can cause more problems than it solves.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 12, 2012 at 12:18 pm
I may have gotten ahead of myself without your suggestion as I've already done the shrink db and shrink log a couple days ago.
The disk space on the actual drive is not an issue, but rather the space allocated to the data and logs.
The data and logs are on a seperate drive then the C drive.
April 12, 2012 at 1:38 pm
Shrinking data and log files usually just results in them filling up and growing again. With the added benefit, in the data file, of fragmenting your indexes and thus slowing the whole database down.
Done is done, so don't worry about it for now. But you might want to check for and remedy index fragmentation issues. Bing/Google "SQL Server index fragmentation" and you'll get good articles on the subject, both how to diagnose and how to repair.
Are the log files set to auto-grow? You can get that by going to the database properties in Management Studio and checking the Files data. If they are set to auto-grow, by what increment (percent or fixed amount, and how much)?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply