October 27, 2010 at 11:58 am
what is the diff between these two :-
Backup log dbname with truncate_only
and
DBCC SHRINKFILE(dbname_Log, 8192)
??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 27, 2010 at 12:27 pm
Two completely and totally different commands. Unrelated.
1) Discards inactive log records, break the log chain, marks the space that those log records occupied as reusable. Log backups are impossible after running this until a full or diff backup is taken. This command is deprecated and no longer works in SQL 2008.
2) Shrinks the physical file, releases unused space to the operating system,
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 28, 2010 at 7:22 am
I performed last night :-
SSMS-->Database-->task-->shrink-->file-->log-->release usused space and mentioned 10gb to be final size.
It went from 85GB to just 78GB...
What more could I do to get the size down?
Moreover, my log shipping got out of sync also.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 28, 2010 at 7:42 am
I would suggest you implement Gail's suggestions ...
change to bulk logged receovery model
implement reindex or reorganize index via a script depending on the fragmentation levels ...
and DO NOT SHRINK the logs or DB files ... get bigger disks
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
November 1, 2010 at 8:40 am
As I've been mostly involved with DB2, Sybase, mySQL and SQL Server 2000 over the last several years, I'm just now getting back into SQL Server 2005.
Our development environment is dynamic and we can easily see 60+gb log file sizes overnight.
I am disheartend, after coming back to SQL Server 2005 after such a long hiatus that Microsoft STILL hasn't figured out to properly manage log file sizes. Many other RDMSs are fully capable of automatically shrinking the log files after a full backup is performed and all uncomitted transactions are written to the database.
Statements like "leave it alone", "let it grow", "never shrink it - worse thing ever" and "buy larger drives" are fine and dandy for the weekend warrior with a database on a local server but in institutions with Service Level Agreements and fixed allocations on a SAN this is not easy to do at all.
In short, Microsoft is seriously missing the mark when there is a 10gb database file and an 88gb (and growing) log file with no acceptable automatic method of managing it without resorting to "hacks" such as changing recovery modes on systems that need to be up 24/7.
Again, most other RDMSs handle this basic & elementary housekeeping chore as a matter of course and without having to throw workaround hacks at it. Why can't Microsoft get there act together in this regard? Sybase (from which MSSQLS originated) does this with nary a peep.
Thoughts?
November 1, 2010 at 11:06 am
First, I take exception to the "Weekend Warrior" comment. I have supported GB+ databases on SAN and DAS systems and have had to worry about disk space constraints on both.
Second, full backups on MS SQL Server only backups up enough of the transaction log to ensure a consistant databse restore. If it backed up the entire t-log and released that space for reuse, full backups would essentially break the log chain, which it doesn't.
I had a 14 GB database that would grow the t-log to 32GB nightly after I switched the database to use the full recovery model. Turned out the DBA previously responsible for this database was rebuilding every index on every table every night. Explained why the database was using Bulk Recovery model. Dropped that nightly step and replaced it with the process I used on the PeopleSoft databases that only rebuilt indexes if more 30% fragmented, reorganized those between 10-30% fragmented and left the rest alone. Nightly processing never exceeded the 8 GB I allocated to the t-log after that.
As DBAs we are responsible for maintaining the databases and that includes the t-logs and their growth. One way of doing that is to ensure that t-log backups are run frequently enought to help manage the size of the t-logs as well as to meet our SLAs for recovery.
Constant shrinking and growing of the t-log files will lead to file level fragmentation as well as poor system performance when the t-log has to grow. IIRC the t-log does not benefit from zero-file (proper term?) as do the database files.
November 1, 2010 at 11:28 am
HoustonFirefox (11/1/2010)
Statements like "leave it alone", "let it grow", "never shrink it - worse thing ever" and "buy larger drives" are fine and dandy for the weekend warrior with a database on a local server but in institutions with Service Level Agreements and fixed allocations on a SAN this is not easy to do at all.
:Wow: Gail. Weekend Warrior? BWAHAHAHAHAHAHAHAHA.
And guess what. If you shrink your log files, and don't handle the maintenance (as described below), then they just regrow. So, now, instead of just taking the time to actually write everything to the log, they also need to regrow, and fragment physically (and logically) even further. Thus it takes more time to do the operation (that it already did), and you've spent time shrinking it. So, you get answers like 'leave it alone', 'let it grow', and 'never shrink it'.
Why? Because us weekend warriors on SAN systems fighting to try to get sysadmins to actually split our LUNs properly (actually SHRINKING the dataspace available to us, usually, when we do) into proper RAID structures know where we need room and where we don't. We're not the playground warriors assuming they know everything.
In short, Microsoft is seriously missing the mark when there is a 10gb database file and an 88gb (and growing) log file with no acceptable automatic method of managing it without resorting to "hacks" such as changing recovery modes on systems that need to be up 24/7.
What hack? It's called BACKUP LOG. The Log and the Database are related but separate entities. You need to uniquely backup the log. As to automatic, most places do log backups every 15 minutes to 1 hour. You schedule it via SQL Agent. Why the heck are you swapping recovery modes? If you're going to break your log chain, put it to simple and leave it there.
Again, most other RDMSs handle this basic & elementary housekeeping chore as a matter of course and without having to throw workaround hacks at it. Why can't Microsoft get there act together in this regard? Sybase (from which MSSQLS originated) does this with nary a peep.
Thoughts?
My thought is... you're wrong. There are many methods to handle the log, determine size, last active transaction, and backing them up to keep your log chain.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 11:42 am
I suspect that what was meant was that less experienced SQL DBA's, or people thrust into the role of DBA are the "weekend warriors" trying to make things work without enough experience .... while the experts here know what needs to be done.
November 1, 2010 at 11:51 am
homebrew01 (11/1/2010)
I suspect that what was meant was that less experienced SQL DBA's, or people thrust into the role of DBA are the "weekend warriors" trying to make things work without enough experience .... while the experts here know what needs to be done.
If that was what was meant, the phrasing was horribly poor. He didn't write as an ESL person, who I'd be more forgiving in phrasing and tone to. It was presented as thsoe giving the advice above were both inexperienced and unfamiliar professionally, as well as presenting that MS SQL didn't have a way to work with the log files that was simple and clean.
I'm sorry, homebrew, but I must respectfully disagree with your interpretation of Houston's post.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 11:58 am
Craig Farrell (11/1/2010)
homebrew01 (11/1/2010)
I suspect that what was meant was that less experienced SQL DBA's, or people thrust into the role of DBA are the "weekend warriors" trying to make things work without enough experience .... while the experts here know what needs to be done.If that was what was meant, the phrasing was horribly poor. He didn't write as an ESL person, who I'd be more forgiving in phrasing and tone to. It was presented as thsoe giving the advice above were both inexperienced and unfamiliar professionally, as well as presenting that MS SQL didn't have a way to work with the log files that was simple and clean.
I'm sorry, homebrew, but I must respectfully disagree with your interpretation of Houston's post.
You're probably right ..... my "benefit of the doubt" scenario got the better of me.
But it does bring up a question I wondered about years ago when migrating from the AS/400. How did that system avoid the need to manage log files & indexes ?
November 1, 2010 at 12:06 pm
To clarify;
This was not meant with disrespect to any DBA, regardless of experience level or on whatever database platform we must support. If any here took that as my meaning then please know that was not the intent of the post. I admire and learn a great deal from my collegues here and respect everyones point of view. It may be new, different, lateral or just plain strange but everyone here has a voice at the table to be sure.
Since working with almost every SQL database in existence since the late 80s, I was simply voicing my concern (and consternation) that Microsoft does not handle log file size management as elegantly as some of the other platforms I have run across. Maybe they will manage log file sizes a bit better in SS 2008. Regardless, we all work with the platforms (cards) we are dealt and move on from there 😉
November 1, 2010 at 1:10 pm
Unfortunately, HoustonFirefox, your clarification does not really match what you wrote in your original post.
I believe SQL Server does a good job of managing the t-log files as long as your DBA is properly scheduling maintenance tasks (don't rebuild every index on every table every night), scheduling t-log backups to meet SLAs and manage t-log space (you may run t-log backups more frequently during high use periods), and watch for unusual events that may spike t-log usage.
November 1, 2010 at 3:53 pm
HoustonFirefox (11/1/2010)
As I've been mostly involved with DB2, Sybase, mySQL and SQL Server 2000 over the last several years, I'm just now getting back into SQL Server 2005......
Why can't Microsoft get there act together in this regard? Sybase (from which MSSQLS originated) does this with nary a peep.
Thoughts?
Ouch! I've been working with MS-SQL since SQL 6.5. That was about the version MS inherited from Sybase. Then 3 years ago I had to go back and support both Sybase and SQL 2000/2005, and it was like going backwards 12 years.
If you think MS-SQL haven't got there act together you don't know enough about SQL 2005 or 2008. Or even SQL 2000. The issue is not being able to shrink the logs, or any other process. MS-SQL manages these with ease, in fact it's a lot easier to configure in MS-SQL than in Sybase. The issues are with what happens in the back ground, this is the same on Sybase as on MS-SQL. If you shrink a log file and then it has to grow again in chunks, whether you use MS-SQL or Sybase there is no guarantee that you will get contiguous sectors on the disk. The result is file fragmentation which will eventually result in performance degradation. This is why most good DBAs, both MS-SQL and Sybase (and I don't think supporting databases in the 600GB + range makes me a weekend warrior) would recommend leaving it once it grows, since this is possibly the least fragmented it will ever be.
The real problem the initial poster had was his original process was poorly thought out. Shrinking the log isn't the problem. They needed to FIRST fix the re-org/re-index process to not generate such large logs, then worry about the log sizes and possibly shrinking them.
If the logs are just going to grow again, then there is no good reason to shrink them every time they grow. It's a waste of resources with no benefit. Plus there is the performance hit next time the database engine needs log space and has to initialize disk space again, AND the issue of fragmentation as each new chunk of the file is created on a different section of disk.
Regards
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 2, 2010 at 8:55 am
Stop using maintenance plans. PLEASE do yourself a HUGE favor and get a copy of Ola Hallengren's FREE and DOCUMENTED maintenance suite (ola.hallengren.com). Learn to use it's goodness. For indexes, it is highly configurable and will allow you to only do NECESSARY maintenance (including stats updates) instead of building everything every time.
Analyze the output too so you can trend fragmentation levels over time which allows you to make intelligent adjustments to your fill factors which can be KEY for optimal performance on several fronts.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 2, 2010 at 9:18 am
HoustonFirefox (11/1/2010)
I admire and learn a great deal from my collegues here and respect everyones point of view.
Ha ha, pull your tongue out of his backside will you :Whistling:
Making statements like that will only serve to make you unpopular. SQL Server has become much more of a seasoned RDBMS since the Sybase days, anyone can see that. Every RDBMS has it's strengths and weaknesses.
Paul Randall has some very good blogs explaining the shrink issue in SQL Server, I suppose he is a weekend warrior too is he?? :Whistling:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply