November 5, 2010 at 9:27 am
hi,
Tempdb mdf file and ldf file grow at a consistent rate on many of my servers.
As backup log script is not allowed on tempdb database and tempdb is in simple recovery mode too, that means that only way of reducing the file size is a shrink command.
But what should we do, when the shrink command also doesnt delete the unused space. It decreases only a little space , for example my mdf file was 20 gb and now after shrink command its 7 gb..
Is that the only way to shrink tempdb?
How can we further decrease file size?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 5, 2010 at 9:31 am
I know that once we restart sql server, the tempdb starts from scratch but
I cant perform that as they are on my prod server.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 5, 2010 at 9:31 am
I wonder if Gail has a 6th sense and cringes every time a database shrinking post pops up...
Anyway, the reason your tempdb is 20 gigs is because there were big enough concurrent transactions to use 20gb worth of space. Unless you've figured out the issue (if there even is one) and made a code change, it will happen again. Constantly shrinking/growing a database file causes havoc on it, and you get massive fragmentation. On a database as important as tempdb, you DO NOT want to do this.
November 5, 2010 at 9:50 am
Derrick Smith (11/5/2010)
I wonder if Gail has a 6th sense and cringes every time a database shrinking post pops up...
Something like that...
Anyway, the reason your tempdb is 20 gigs is because there were big enough concurrent transactions to use 20gb worth of space.
20GB is not exactly large if that's a production server with anything more that toy databases on it. Unless you're seriously misjudged disk space, leave it alone.
Oh, you are aware that shrinking TempDB while it's in use can cause corruption? If it does, you will need to restart SQL to resolve that.
http://support.microsoft.com/kb/307487
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
November 5, 2010 at 9:53 am
SKYBVI (11/5/2010)
Tempdb mdf file and ldf file grow at a consistent rate on many of my servers.
Common cause of that is poorly written queries and/or bad indexing.
As backup log script is not allowed on tempdb database and tempdb is in simple recovery mode too, that means that only way of reducing the file size is a shrink command.
Um, shrink is the only way to reduce file size on any database. Backup Log DOES NOT change file size. There's no need to even consider it on TempDB. Simple recovery means that the inactive portions of the log are regularly marked as reusable automatically. It's only on databases in full and bulk-logged recovery where you need to run a log backup to mark the inactive portions of the log as reusable
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
November 5, 2010 at 10:11 am
@ gail
That 20 gb increase is just in 1 week,
Also, you told that Backup log DOES NOT change file size?
I did backup log on my database and it reduced 70 gb?
Howz that related?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 5, 2010 at 10:28 am
SKYBVI (11/5/2010)
That 20 gb increase is just in 1 week,
If TempDB is growing, you have some bad code somewhere, long running transactions or similar. Find and fix the cause. Trying to fix the symptoms while ignoring the cause is ultimately a waste of time. It's just gonna grow again and again and again.
Also, you told that Backup log DOES NOT change file size?
I did backup log on my database and it reduced 70 gb?
It does not change file size. Either you did a manual shrink or you have autoshrink enabled. If the latter, turn it off.
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
November 5, 2010 at 10:36 am
@ gail
I did backup log with truncate_only.
If backup log doesnt change file size then why do people recommend to do it to decrease growing log file size.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 5, 2010 at 10:47 am
SKYBVI (11/5/2010)
I did backup log with truncate_only.
If file size reduced after that then you have autoshrink on. For the health of your database, turn it off.
p.s. You should never be running that anyway. Do you know what it does? Do you know what impact it has and on what?
If backup log doesnt change file size then why do people recommend to do it to decrease growing log file size.
Because they don't understand what it does and haven't a clue how to properly manage their databases.
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
November 5, 2010 at 10:58 am
Derrick Smith (11/5/2010)
I wonder if Gail has a 6th sense and cringes every time a database shrinking post pops up...
Too funny Derrick.... yup, a shrinking post is like the bat signal.
If I ever need her, I'll know how to find her.... my next post will be titled "how do I delete my transaction log"
November 5, 2010 at 11:10 am
Thanks gail
My auto shrink is OFF.
After the backup log script , i had to manually shrink the log files thru SSMS(tasks-->shrink-->files), then the size got dereased
@ swayneBell,,,Now you are too funny...:)
Regards
Sushant Kumar
MCTS,MCP
November 5, 2010 at 11:17 am
SKYBVI (11/5/2010)
After the backup log script , i had to manually shrink the log files thru SSMS(tasks-->shrink-->files), then the size got dereased
EXACTLY what I was saying all along.
From earlier in this thread:
Also, you told that Backup log DOES NOT change file size?
I did backup log on my database and it reduced 70 gb?
It does not change file size. Either you did a manual shrink or you have autoshrink enabled.
After the backup log script , i had to manually shrink the log files thru SSMS(tasks-->shrink-->files), then the size got dereased
Backup log does not reduce the size of the file on disk.
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
November 5, 2010 at 11:33 am
@ gail
Ya, you were correct but i want to say that
firstly i tried running the manually shrink only, but that didnt decrease much space...
So, that means that the combination of hte 2 steps decreases log files not individual steps.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 5, 2010 at 11:35 am
SKYBVI (11/5/2010)
So, that means that the combination of hte 2 steps decreases log files not individual steps.
No, it does not! The two 'steps' do different things and I know I've explained them to you before.
Please, please, please do some reading up on the transaction log and how to maintain it properly.
You should never be running backup log ... truncate only and the only time a log should be shrunk is if something unusual has happened and grown the file far beyond what it needs to be.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply