Shrinking the database...
-
Easy, use the Shrink database in EM, or DBCC SHRINKFILE. Alas, not so in this case. The systemdb tempdb has been involved in a huge transaction. The tempdb is over 9 Gb large, of which currently only 9 Mb is used. When using the EM to shrink I see the log has shrunk, but not the datafile. Using Fileshrink in EM does not help, nor the DBCC command. I do receive the message Database shrunk succesfully when using EM, I receive output when using DBCC in QA: currentsize 11668832, minimumsize 64000. We're talking about the tempdb here.
Now I know I could stop MSSQLServer, delete the tempdb and restart, but that is only as a last resort: we are talking about a prioductionserver here.
What can I do here?
Greetz,
Hans Brouwer -
That's strange... I never had problems shrinking tempdb via EM. My standard action is : Shrink database/Files, then select "Shrink file to", set the size (e.g. 5000), then OK, and then Cancel. That thing is a bit irritating, because if you click OK again, DB will be shrunk once more and to the size which is stored in the field "shrink file to" as default. However, this can only shrink the file more than necessary - not cause its growth, as far as I know... so it doesn't solve your problem.
-
Try shrinking the file using Query Analyzer and the command dbcc ShrinkFile
If you need help then ask here
-
Shai,
I tried that, but same result: no shrink.
Tnx for responding
Greetz,
Hans Brouwer -
Try the following commands
USE master
go
DBCC SHRINKDATABASE('tempdb', NOTRUNCATE)
go
DBCC SHRINKDATABASE('tempdb', TRUNCATEONLY)
go
The NOTRUNCATE option moves all the data in the database to the front of the files before attempting the shrink.
Hope this helps
Andy Llewellyn
-
Allas, no luck Andrew. Tnx for responding tho.
Greetz,
Hans Brouwer -
Try a DBCC UPDATEUSAGE first
-
Nope, still no dice.
I'll use the next scheduled off-time to drop the tempdb and see how things are going from there.
Tnx all.
Greetz,
Hans Brouwer -
Sounds like you might have an uncommitted transaction.
-
Oops hit 'post' too soon.
As I said , it sounds like you may have a long running uncommitted transaction. This will prevent SQL Server from advancing the MinLSN. This can cause the log to grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model (which tempdb does).
Use DBCC OPENTRAN to view info about your oldest running tran.
Sean
-
Graps,
It's not the logfile, it's the datafile which is full of empty space.
Tnx for responding tho
Greetz,
Hans Brouwer -
Doh, I really should learn to read.
You're not trying to shrink the data file to a size smaller than that for model db are you?
-
Initially the tempdb is about 2 Mb; currently it's over 8 Gb, of which 2 Mb is used...
Greetz,
Hans Brouwer -
Its a long shot, I know, but my point was that no data file can be shrunk to a size smaller than the size of the data file for model db. So, if the data file for model db is 20 mb and you try to shrink your file to 10mb the operation will fail even though your db only uses 2mb.
-
Hi,
Did you tried setting the target size for the datafle in DBCC SHRINKFILE in QA or in EM ? or if you solved the same pls share the same.
Thank You
Jeswanth
--------------------------------
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply