July 21, 2010 at 10:24 pm
I use the SQL 2005 SP2 Maintenance Plan to work with schedule.
My Maintenance Plan has :-
1. Rebuild Index
2. T-SQL (with command as below) :-
use DM_SCNYLDMS03_docbase
backup log DM_SCNYLDMS03_docbase with truncate_only
dbcc shrinkfile('DM_SCNYLDMS03_docbase',1)
dbcc shrinkfile('DM_SCNYLDMS03_log',1)
After MA start, Rebuild index step already done. But T-SQL step has got the error.
dbcc shrinkfile('DM_SCNYLDMS01_log',1)
" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.
How can i do??? Thank you.
July 21, 2010 at 11:25 pm
See this: http://weblogs.sqlteam.com/tarad/archive/2007/11/08/60394.aspx
You should not be truncating the transaction log. You should either be backing up your transaction log frequently, such as every 15 minutes, or switching your recovery model to SIMPLE.
You should also not be shrinking the database. This is a very bad idea and is causing fragmentation. Shrinks should rarely be done and only be done manually.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 21, 2010 at 11:44 pm
As per your blog, we have to disbale T-Log backup before starting Shirnk db or file operation.
But in the above question, Tlog backup is being executed before shrink file operation.
So, you say that when shrink file operation is started to being executed, Tlog backup was not completed and it thrown the error.
July 22, 2010 at 12:53 am
Frankly you should remove both the truncate and the shrink step. There's no need for either.
If the rebuild is causing excessive log growth, switch to bulk-logged recovery before the rebuild and back to full after.
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
July 22, 2010 at 12:57 am
Thanks for your advice.
My meta-data size (.mdf) is 93.7 GB and log file (.ldf) is 30 GB. When i imported many images in sql per week. I have log file size around 30 GB per week. And then i will use maintenance plan to reduce sizing of .mdf and .ldf.
Could you please advise me to maintenance for my all database sizing. Thank you.
July 22, 2010 at 1:03 am
nuengharit (7/22/2010)
My meta-data size (.mdf) is 93.7 GB and log file (.ldf) is 30 GB.
That sounds fairly reasonable.
When i imported many images in sql per week. I have log file size around 30 GB per week. And then i will use maintenance plan to reduce sizing of .mdf and .ldf.
Don't. Leave the files alone, all you're doing by shrinking is hindering performance, introducing fragmentation and forcing them to grow again next time data is added. You should not be regularly shrinking either. 120GB is small for a DB, the log file sounds reasonably sized for the data file size. Give them space to grow and leave them alone.
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
July 22, 2010 at 1:37 am
Yep!!! I only use maintenance plan every month. But my space isn't enough.
I don't have budget to buy the new one. Please advise if i need to reduce sizing every month.
Thank you.
July 22, 2010 at 4:54 pm
Why isn't the space enough? You need it to log the imports, regardless of whether you shrink.
Also shrinking means that you are introducing fragmentation, and undoing much of the reindexing. The reindexing will use space as well, so you need to have free space.
If the log is an issue, you can run more frequent log backups to help manage things, and break your updates into smaller transactions so the log space can be reused.
Right now you need the space, you use it, then reclaim it with a shrink, and then use it again. A waste of time and resources.
July 22, 2010 at 5:16 pm
Also consider that you may have a large number of indexes that aren't fragmented, and don't require rebuilding. You may have a situation where you're washing (rebuilding) your already clean car (indexes), then driving it straight through the mud (shrink/truncate). Of course, you're not starting from a clean car at the moment - it's constantly dirty due to the shrinks.
Remember also that if your log grows to 30 GB each month, then you obviously must have room to accommodate this amount, unless you have multiple databases that want to grow in this fashion.
If you have no room to perform the maintenance, then you have a choice - either secure funding for more disk space, or inform the business that performance may suffer due to an ability to perform maintenance.
July 23, 2010 at 1:34 am
nuengharit (7/22/2010)
i imported many images in sql per week.
Well in this case , you can opt for SIMPLE recover model and take log backup on 15 min interval.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 23, 2010 at 2:28 am
Bhuvnesh (7/23/2010)
Well in this case , you can opt for SIMPLE recover model and take log backup on 15 min interval.
You cannot take log backups in simple recovery. Log backups are only allowed in full and bulk-logged recovery because in Simple the log is truncated every time a checkpoint runs.
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
July 24, 2010 at 8:11 am
nuengharit (7/22/2010)
Yep!!! I only use maintenance plan every month. But my space isn't enough.I don't have budget to buy the new one. Please advise if i need to reduce sizing every month.
Thank you.
How often are you taking full backups and logfile backups?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply