Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

why i can't shrink tempdb data file Expand / Collapse
Author
Message
Posted Tuesday, December 7, 2010 12:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 50, Visits: 71
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.


Post #1031465
Posted Tuesday, December 7, 2010 1:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 50, Visits: 71
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.


Post #1031473
Posted Tuesday, December 7, 2010 3:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 2, 2011 9:50 PM
Points: 189, Visits: 335
Go here... and read ..
http://www.sqldev.org/getting-started-with-sql-server/unable-to-shrink-tempdb-in-2005-104030.shtml
also here.
http://support.microsoft.com/kb/307487
Post #1031579
Posted Wednesday, May 9, 2012 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 11:58 AM
Points: 3, Visits: 3
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!!!
Post #1297322
Posted Wednesday, May 9, 2012 9:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
mike schreiner (5/9/2012)
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!!!!!
Why we require this ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1297564
Posted Thursday, May 10, 2012 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 7,194, Visits: 6,338
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?



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1298069
Posted Thursday, May 10, 2012 11:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 7,194, Visits: 6,338
Bhuvnesh (5/9/2012)
mike schreiner (5/9/2012)
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!!!!!

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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1298070
Posted Thursday, May 10, 2012 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 11:58 AM
Points: 3, Visits: 3
I just did this yesterday. I followed a microsoft knowledgebase article and it worked perfectly.
Post #1298077
Posted Thursday, May 10, 2012 12:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 11:58 AM
Points: 3, Visits: 3
I had a 26 Gig tempdb.mdf file . I did everything exactly as i postted. After everything was done, the size was only 100mb.
Post #1298080
Posted Thursday, May 10, 2012 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, August 10, 2014 6:47 PM
Points: 125, Visits: 266
Restarting SQLServer works for me. We are using SQL Server 2005.
Post #1298135
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse