| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2006-08-24 : 17:52:21
|
| I have some questions about DB review which is done by outside.. Does this sounds make sense to you guys? Any wrong information on this review and next step that we need to take? especially the trans log file and back up issue? I am not sure why i need to shrink the database?• Database Review – All databases are operating with a FULL recovery model. The Transaction Logs have not been truncated in some time and are EXTREAMLY large. This will affect performance especially when Tran Log backups occur. It is imperative that these databases are shrunk immediately. The Tran Logs are backed up every hour during business hours via the maintenance plan. • SQL Maintenance plans – The current maintenance plans are inadequate for the installation. When reviewed, it was discovered that the Tran Log backups were not being removed from the SQL server. If the server fails (due to HD failure), the Tran Log backups could be lost and would prove to be of no value. There is no integrity check of the databases being performed. There is no Index integrity check being performed. The maintenance plan should be revamped to include integrity checks and repairs, backup of the Tran Logs to a separate storage device, and most importantly – the truncation of the Tran Logs.next step• Shrink the Databases and make this part of the maintenance plan.• Install SQL Server 2000 SP4. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-24 : 18:15:24
|
| If the Transaction Logs are backed up every hour, there should be no need to truncate the log. In fact, that would be a bad idea, since it would invalidate recovery using transaction logs.Perhaps they are suggesting that you shrink the tranaction log files? I can't say if that is a good idea or not without more information about the file sizes and amount of available space.I would ask them to explain in much more detail, especially exactly what the mean by "truncation of the Tran Logs".CODO ERGO SUM |
 |
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-24 : 18:32:36
|
| thanks Michael,That's what I thought.. it should be "Shrink log file" right?Is it a good idea copy your logs to a seperate device / server - remove old ones - only keep last 7 days max" and shrink log file in the databasee every Sunday? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-24 : 18:37:13
|
| It is not wise to shrink your files unless you know for sure that you won't need the additional disk space in the near future. This is because you'll receive a huge performance hit if the file needs to expand. Also, you should never have a shrink job for a production database, especially one that runs weekly. The vendor doesn't sound like they know what they are talking about in point 1. In point 2, they mention repairs. You should never select that option in a maintenance plan. Any repairs should be done manually by a DBA when DBCC CHECKDB has errors.Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-24 : 18:38:48
|
quote:
Originally posted by jung1975
Is it a good idea copy your logs to a seperate device / server - remove old ones - only keep last 7 days max" and shrink log file in the databasee every Sunday?
All backups should be copied somewhere else. We put our backup files on tape, but a remote server would work too.And no shrinking the database every Sunday!Tara Kizer |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-24 : 18:54:57
|
| thanks tarawhen you said"It is not wise to shrink your files unless you know for sure that you won't need the additional disk space in the near future."you are talking about the data file not the log file right? |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-08-24 : 18:56:59
|
| Hi Jung,* They might have a point in that the logs are overdimensioned for the database and should be shrunk,I would see this as a one-time operation.* The size of the transaction log files themselves will not affect performance, only the active part of the log will be backed up.The claim that it will affect performance when transaction logs occur is bollocks.* For a database it is best NOT to shrink the files (data or log) unless you need to reclaim any wasted space to the OS.(eg. you have purged data, maintenance jobs have made the transaction log innappropriately large)Growing and shrinking the files is resource intensive, and should be avoided if not necessary.It is best to keep the datafiles to a size that will accomodate for reasonable future datagrowth,and the transaction logs to a size that will accomodate all activity that takes place in the db during normal usage(that includes daily transactions and daily/weekly/monthly maintenance (=reindexing)).* They have a good point in backups being moved to another location in case of hardware failure.Backups should be moved to other physical storage in case something really nasty happens to the db server.* repairs should be run only as a last resort, not as part of daily maintenance.* there have been some issues with sp4 on some sql installations. there is a post sp4 cumulative hotfix that can be applied (2187).(sorry, google). I think that sp4 should be applied (after due testing).* do NOT shrink the databases as part of the maintenance plan.* Maintenance plan... well it might be appropriate for your operation to use this feature.Personally I prefer having customized scripts for backups and maintenance, it gives much better control over the process.Anyway, there is not anything magic in the maintenace plan that can't be done with regular sql & dbcc commands.Paul Randall has lots of good stuff about this, that you really should check out.Here is an example:http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/13/629059.aspxrockmoose |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-24 : 18:57:13
|
quote:
And no shrinking the database every Sunday!
I meant shrink the log file not the data file..So, what should i do if the log file get too big?Or if i do the log back up hourly, It won't get big because the log back up will truncate the inactive part of log files? |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-24 : 19:12:24
|
| Thansk! Rock.. I have a coupl eof questionsI am not sure what you mean by "The claim that it will affect performance when transaction logs occur is bollocks.""the transaction logs to a size that will accomodate all activity that takes place in the db during normal usage"-What if the transaction log file become too big..should i shrink it down manually?"you have purged data, maintenance jobs have made the transaction log innappropriately large"How do I fugure out whether i have puged data or the maintenance plan have generated the huge transaction log?Last, is the size of tempdb will affect the db performance? Is it a good idea to stop and restaart SQL server services to clear out the tempdb? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-24 : 19:23:42
|
quote:
Originally posted by jung1975
quote:
And no shrinking the database every Sunday!
I meant shrink the log file not the data file..
The answer is still the same. Neither file should be shrunk, especially on a scheduled basis.quote:
Originally posted by jung1975
So, what should i do if the log file get too big?Or if i do the log back up hourly, It won't get big because the log back up will truncate the inactive part of log files?
Well it could get to about the same size as the MDF file if you do any reindexing. But leave it that way. Add disk space if needed so that you don't need to shrink.Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-24 : 19:27:27
|
quote:
Originally posted by jung1975
I am not sure what you mean by "The claim that it will affect performance when transaction logs occur is bollocks."
He means their claim is crap.quote:
Originally posted by jung1975
"the transaction logs to a size that will accomodate all activity that takes place in the db during normal usage"-What if the transaction log file become too big..should i shrink it down manually?
Define too big. quote:
Originally posted by jung1975
"you have purged data, maintenance jobs have made the transaction log innappropriately large"How do I fugure out whether i have puged data or the maintenance plan have generated the huge transaction log?
You'd have to keep an eye on it during the normal jobs. quote:
Originally posted by jung1975
Last, is the size of tempdb will affect the db performance? Is it a good idea to stop and restaart SQL server services to clear out the tempdb?
The size does not impact performance. What will impact performance is if you shrink it and a query needs more space and it has to grow. You will encounter negative performance there. Restarting the service should not be done IMO except for patching/service packs/problems/etc...Tara Kizer |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-24 : 19:28:56
|
| thank you thakn you.. |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-24 : 19:43:35
|
| WE are doing a trnasaction log back up hourly, so if the log file is too big ( I.e. Log file - 6GB data file is 5.5 GB), isn't it going to slow down the performance when the log backup occured? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-24 : 19:51:25
|
quote:
Originally posted by jung1975
WE are doing a trnasaction log back up hourly, so if the log file is too big ( I.e. Log file - 6GB data file is 5.5 GB), isn't it going to slow down the performance when the log backup occured?
NoCODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-24 : 19:54:02
|
| The size of the file isn't what impacts the performance of the backup. It's how much space is in use inside the file that will impact it. The fact that you are doing hourly transaction log backups leads me to believe that the space in use is probably small. It's probably mostly in use during a reindex.Tara Kizer |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-08-24 : 20:14:53
|
You can runDBCC SQLPERF(logspace)and you can see how much of the log file is actually used.The logfile itself can be very large, but if there is only a small amount of transactions in it, then a backup will be quick & small.The size of the file itself does not affect the performance, only the amount of transactions that need backing up.-What if the transaction log file become too big..should i shrink it down manually?Yes, you can use: DBCC SHRINKFILE(nameOfLogile)Of course you have to define "too big"  .Keep the file to a descent size that is not too small.If after a time you see that the file has augmented in size, then:1. You have underestimated and should let it be bigger.2. Something is filling it up (batch jobs, maintenance etc..) and you might want to investigate where all the activity is coming from.-How do I fugure out whether i have purged data or the maintenance plan have generated the huge transaction log?You can't, after the fact.-Last, is the size of tempdb will affect the db performance? Is it a good idea to stop and restaart SQL server services to clear out the tempdb?No.How big is it now?But ok, if you restart sql server it will allocate space for tempdb according to it's initial size.After restart you should increase tempdb's default size to an estimated max size for tempdb.This way it will not need to autogrow. It is better to have it a good big size right from the start.Do you have several physical disks? it can help performance if you place tempdb files on other physical disks. (maybe not a problem iyc though).rockmooe |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-24 : 21:38:46
|
| thanks"but if there is only a small amount of transactions in it,"How do I know if there is a small amount of transactions in the log file? Is the transaction amount same as the % of spaced used ( DBCC SQLPERF(logspace) )?How big is it now?--the maximum size of tempdb is set to 2 GB ( but 80% of the spece is free) and log file is set to 12MB.. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-24 : 23:06:12
|
quote:
Originally posted by jung1975...How do I know if there is a small amount of transactions in the log file?...
When you run a tranaction log backup, the output file size a a good indication.Take a look at the size of your transaction log backup files to see what size they normally are.CODO ERGO SUM |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-08-25 : 01:45:40
|
| I meant DBCC SHRINKFILE(nameOfLogile,targetsize) in my previous post.See BOL for documentation of this command. The idea is to shrink a (too) large file to a more convenient size.> How do I know if there is a small amount of transactions in the log file? Is the transaction amount same as the % of spaced used ( DBCC SQLPERF(logspace) )?See BOL as well.I don't know the exact mechanism and how the % is calculated, but the assumption seems reasonable. If it's in 90's% usage you know that the file will soon grow, unless the log is backed up or truncated.> the maximum size of tempdb is set to 2 GB ( but 80% of the spece is free) and log file is set to 12MBNothing to worry about. 2GB may be overkill, but that all depends on your system.We have servers where tempdb is set to a size much larger than that, and some smaller too.rockmoose |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-08-25 : 10:35:08
|
| I have a maint job like belowEXECUTE master.dbo.xp_sqlmaint N'-PlanID DF1ABC60-29EE-4116-9497-C76B51B4C098 -Rpt "d:\Program Files\Microsoft SQL Server\MSSQL\LOG\ Full Recovery Model DB Maintenance Plan0.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 2000 10 'The job does:Reorganize data and index pages : change free space per page percentage to 10% -RmUnusedSpace 50 10Remove unused space from database files: when it grows beyond: 50MB Amount of free space to remain after srink: 10% of the data spaceso , it's not a good idea since we are shrink the database regurarly? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-25 : 13:04:24
|
quote:
Originally posted by jung1975
The job does:Reorganize data and index pages : change free space per page percentage to 10% -RmUnusedSpace 50 10Remove unused space from database files: when it grows beyond: 50MB Amount of free space to remain after srink: 10% of the data spaceso , it's not a good idea since we are shrink the database regurarly?
No it's not a good idea to do this! I don't know how many more times we need to put it in this thread.Tara Kizer |
|
|
|
Next Page
|
DB review