December 3, 2010 at 7:12 am
I had a tempdb mdf file which was 60 Gb, and I tried shrinkfile, and shrinkdatabase and got nothing. I noticed that I had several databases in 'readonly' mode. I changed them, executed shrinkfile file and reduced tempdb mdf file to 500 Mb, and then I turned them again to 'readonly' mode.
It worked, but I don't know why these readonly dbs didn't allow me to reduce tempdb, do some of you know why???
Thanks and br.
December 6, 2010 at 3:56 am
ignacio.aranda (12/3/2010)
I had a tempdb mdf file which was 60 Gb, and I tried shrinkfile, and shrinkdatabase and got nothing. I noticed that I had several databases in 'readonly' mode. I changed them, executed shrinkfile file and reduced tempdb mdf file to 500 Mb, and then I turned them again to 'readonly' mode.It worked, but I don't know why these readonly dbs didn't allow me to reduce tempdb, do some of you know why???
Thanks and br.
i dont think there would be any relation between readonly DB and tempdb shrinkage
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 6, 2010 at 5:13 am
The only thing I can think of is that the files had open transactions in tempdb. Just because the files are read only doesn't mean they don't use tempdb to process SELECT statements.
But the read only setting itself shouldn't have had anything to do with tempdb. I think either it was a coincidence of timing or that you somehow killed a process doing what you did.
December 6, 2010 at 1:59 pm
Nope, I tried to shrinkfiles and database some times before and I didn't get it reduced. And of course I checked that there were no transactions opened, and there weren't.
Maybe it was a coincidence...
December 7, 2010 at 11:15 am
In my case, which is a SQL2008 R2, I successfully shrank the tempdb by using Method 1 in MS KB
http://support.microsoft.com/kb/307487
A little bit tricky part is I didn't logon by using SA. I do have sysadmin privilege therefore when system asked me to restart by using command line, I hit Ctrl-C to quit and start the SQL services in Management Console.
BTW, I did above on a dev box.
December 7, 2010 at 12:46 pm
Looks like u do have a run away transaction like one of them emntioned. Either terminate that session and run shrink or wait for it to finish. There is no silver bullet to shrink as long as there is an active transaction using tempdb.
December 7, 2010 at 1:01 pm
That was a neat workout...it did not occur to me and have saved the same for future use (Near future). Should have read all the comments before my last post on this subject.
December 7, 2010 at 3:49 pm
May 9, 2012 at 12:04 pm
How you fix it-
1..Shut down SQL Services on SQL Backend.
2. Go to cmd prompt and Run as Administrator
5.Go to the Binn directory of the SQL Server instance. In this case it was d:\Program Files\Microsoft SQL Server\MSSQL10_50.AFVCSQLSERVER01\MSSQL\Binn
6.In the cmd prompt, type the following-
sqlservr -c -f -sAFVCSQLSERVER01
IF YOU DON'T have a SQL Server instance, just type sqlserver -c -f
KEEP THE CMD WINDOW OPEN!!!!!
7. Go to SQL Server Management Studio and click on the tempdb database.
8.Click on New Query
ALTER DATABASE tempdb MODIFY FILE
NAME = 'tempdev', SIZE = 100)
--Desired target size for the data file
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = 100)
--Desired target size for the log file
9. Click on Execute. You will see the query completed successfully.
10.Go to the CMD prompt and do a CNTL-C. That will exit single user mode of SQL Server.
11.Go to services and start up the SQL Services. The files will now be 100 MB.
all done!!!
May 9, 2012 at 9:43 pm
mike schreiner (5/9/2012)
2. Go to cmd prompt and Run as Administrator5.Go to the Binn directory of the SQL Server instance. In this case it was d:\Program Files\Microsoft SQL Server\MSSQL10_50.AFVCSQLSERVER01\MSSQL\Binn
6.In the cmd prompt, type the following-
sqlservr -c -f -sAFVCSQLSERVER01
IF YOU DON'T have a SQL Server instance, just type sqlserver -c -f
KEEP THE CMD WINDOW OPEN!!!!!
Why we require this ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 10, 2012 at 11:46 am
mike schreiner (5/9/2012)
How you fix it-1..Shut down SQL Services on SQL Backend.
2. Go to cmd prompt and Run as Administrator
5.Go to the Binn directory of the SQL Server instance. In this case it was d:\Program Files\Microsoft SQL Server\MSSQL10_50.AFVCSQLSERVER01\MSSQL\Binn
6.In the cmd prompt, type the following-
sqlservr -c -f -sAFVCSQLSERVER01
IF YOU DON'T have a SQL Server instance, just type sqlserver -c -f
KEEP THE CMD WINDOW OPEN!!!!!
7. Go to SQL Server Management Studio and click on the tempdb database.
8.Click on New Query
ALTER DATABASE tempdb MODIFY FILE
NAME = 'tempdev', SIZE = 100)
--Desired target size for the data file
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = 100)
--Desired target size for the log file
9. Click on Execute. You will see the query completed successfully.
10.Go to the CMD prompt and do a CNTL-C. That will exit single user mode of SQL Server.
11.Go to services and start up the SQL Services. The files will now be 100 MB.
all done!!!
Mike, have you actually used this method yourself or did you get it from somewhere else?
May 10, 2012 at 11:47 am
Bhuvnesh (5/9/2012)
mike schreiner (5/9/2012)
2. Go to cmd prompt and Run as Administrator5.Go to the Binn directory of the SQL Server instance. In this case it was d:\Program Files\Microsoft SQL Server\MSSQL10_50.AFVCSQLSERVER01\MSSQL\Binn
6.In the cmd prompt, type the following-
sqlservr -c -f -sAFVCSQLSERVER01
IF YOU DON'T have a SQL Server instance, just type sqlserver -c -f
KEEP THE CMD WINDOW OPEN!!!!!
Why we require this ?
I'm thinking it's because you're in single user mode and if you close the command prompt, you lose the query window you just opened. But I will admit that I'm just guessing.
May 10, 2012 at 11:59 am
I just did this yesterday. I followed a microsoft knowledgebase article and it worked perfectly.
May 10, 2012 at 12:01 pm
I had a 26 Gig tempdb.mdf file . I did everything exactly as i postted. After everything was done, the size was only 100mb.
May 10, 2012 at 1:13 pm
Restarting SQLServer works for me. We are using SQL Server 2005.
Viewing 15 posts - 46 through 60 (of 66 total)
You must be logged in to reply to this topic. Login to reply