February 15, 2009 at 3:07 pm
Our SE have stoppped schedule of backing up trancation logs since last week, it would be temoporary, howver yeterday I manually backed up the logs that were greater than .5 GB and shrank those log files just to be safe for weekend. we still have over 1.3 TB space on drive, however looking at tempdb data file has negative space as well as log file has negative -5130% MB, I am concerned as I have never seen nagative for tempdb space options, please advise. should I be take any other action? As tempdb is simple recovery and I can't backup the log. Should I add that this is sharepoint db and it is on clustred environment. PLeae give me any advice and put my mind at ease.
Thank yoiu a lot
February 15, 2009 at 10:08 pm
Hi,
You can check out the actual size of the tempdb using sp_helpdb 'tempdb'. If it is not negative you dont have to worry and tempdb goes on increasing when required. And I dont see any problems as of now.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
February 16, 2009 at 7:41 am
Thanks so much, it was not negative. Cna you tell me what do you suggest for tranact log backup schedule? This is sharepoint environment and i am not sure if different than others SQL dbs, we had it set up for every 4 hours for all the dbs on this clustered environment and it was fine until a manger told System engineering to stop it because of fear of performance level comong down during morning busy hours. I thought Transact log backup actually help the performance during busy hours, is it not true?
Again thank you for any advice
February 16, 2009 at 7:50 am
eashoori (2/16/2009)
Thanks so much, it was not negative. Cna you tell me what do you suggest for tranact log backup schedule?
It really depends on your businesss needs and how much data they can afford to lose?
. I thought Transact log backup actually help the performance during busy hours, is it not true?
As far as my knowledge is concerned, definitely not it does not lead to any performance issues, Tlog backups are not take for performance rather they are more seen as part of your Disaster Recovery Plans. It gives you point in time recovery.
Yes, when you compare taking Differential backups and transactional backups this will lead to better performance as only changed units of work are backed up.
February 16, 2009 at 8:21 am
eashoori (2/16/2009)
I thought Transact log backup actually help the performance during busy hours, is it not true?
Backups are for recoverability, not performance
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
February 17, 2009 at 4:02 am
it was fine until a manger told System engineering to stop it
I think you need to discuss this with your manager. If anyone who thinks they have some authority can get changes made without reference to the manager owning the product, you have a serious problem. You need to have an approvals process for changes, and any change needs to be justified.
Does the manager who asked for the backups to be stopped have any idea of the impact of this request?
Has anyone collected statistics of performance before and after the trans log backups were stopped. Has stopping the tran log backups had any impact on performance?
Has anyone in management considered how the data would be recovered in a DR situation without tran log backups?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 1, 2009 at 12:30 am
sp_helpdb gives tempdb occupying 20GB space but properties of the DB gives negative values....
wat might be the reason?????
July 1, 2009 at 2:32 am
What properties are you looking at?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 1, 2009 at 2:37 am
right click on DB -> Tasks -> Shrink -> Files
July 1, 2009 at 3:01 am
The figure you are looking at is calculated from the control information held about tables in tempdb.
tempdb has what is known as lazy garbage collection. When an object is dropped, not all of the control information about that object gets dropped immediately. This allows SQL Server to re-use the table header information if a new object is required in tempdb.
Creating control information for new objects takes time, so SQL Server can run faster if it is able to re-use the control information of objects it knows have been dropped. When the new object is initialised, the control information will be updated to reflect the new object.
In the meantime you may have situations where the total of allocated space reported in the control information (including the dropped object information) exceeds the size of tempdb. This is nothing to worry about.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 20, 2012 at 7:26 am
I have a tempdb database which showing the default 8Mb whe you look at shrink file, but on the disk it is 33gb.
What is going on?
February 20, 2012 at 7:56 am
Jamie, have you done any research to find out what might be going on? If so, what are your results so far?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 20, 2012 at 8:00 am
Not yet I will need to start logging, just wondering if anyone had seen this before. The file size on the properties tab is correct so I can only assume the shrink file dialog does not work for tempdb.
February 20, 2012 at 8:02 am
Well shrinking TempDB online is documented to sometimes cause corruption so....
The shrink dialogue usually works fine, so try DBCC UPDATEUSAGE
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
February 21, 2012 at 5:54 am
If you have a half-GB of tlog file on a 1.3TB drive, please don't shrink it. It will just grow back out again, leading to OS file fragmentation and that will lead to poor IO performance over time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply