Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Shrink Job in SQL Server

Posted on 2011-10-21
3
Medium Priority
?
245 Views
Last Modified: 2012-05-12
I am running a shrink job on SQL Server 2005 and the database called TempDB became massively huge as in over 200 gigabytes.  I just started a shrink job on the database, so how long will this take before this finishes?
0
Comment
Question by:VBBRett
3 Comments
 
LVL 8

Accepted Solution

by:
Som Tripathi earned 1000 total points
ID: 37010083
It might not shrink as expected.

You might be known of below, still a reminder -
Shrinking tempdb is not similar as other databases.
It can not be shrunk if any activity is going in tempdb. This is reason why you are suggested to start SQL Server in single user mode to perform this activity.

http://support.microsoft.com/kb/307487

If the question is about time consumed in shrinking any database -
The answer is - this is variable, the size depends on the way how extents are organized in your database. So, in other words, this is not possible to predict time consumed.
0
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 1000 total points
ID: 37010471
Its always recommended to shrink database files rather than shrinking database as a whole. Again while shrinking database files, you can do it in batches, i.e. shrink it in 100 MB to 5 GB batches depending upon your requirement. You would like to learn more about dbcc shrinkfile

msdn.microsoft.com/en-us/library/ms189493.aspx

Apart from that your doubt regarding tempdb is genuine. tempdb is the place where all the user activites happen. It is a master database. When you are shrinking your database, the activities involve tempdb so its growing huge. So, shrinking database files in batches is recommendable.
Again to know about which database is using what files, (the logical names), you can make use of any of the system objects:-
sysfiles
sysaltfiles
master_files
database_file

You can create your own logic to perform the task. Let us know in case there is any other thing we can help.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37010972
A database will shrink only if the space alocated to it is not used. If TempDB grew so big is because it HAD to. Some activity required sorting, grouping  or huge temporary tables, ehich cause this.

An easy way to shrink tempdb is to restart the SQL server but this doesn't guarantee that it won't groe again later. You have to find out what causes it and at the same time make sure to give enough room to tempdb files to grow.

Here is a good article:

http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question