SQL Server Compact Jobs and how it effects the database

Posted on 2011-10-21
Last Modified: 2012-05-12
I have a SQL Server 2005 Database that has grown to over 200 gigabytes in a short span of time.  The database is Microsoft SQL Server's TempDB.  Now, I don't know why or how it grew so huge but if I were to shrink the database live, would it have any adverse effects?  On another note, what could be the issue and how?  I don't see how I can pull any data out of the temp tables in the TEMPDB so right now I am at a stand still.  Please let me know, thanks!
Question by:VBBRett
    LVL 8

    Accepted Solution

    Tempdb is a global resource which is used to hold temporary objects created by users, Internal objects created by the SQL Server and row versions that are generated by data modification transactions.
    So the tempDB can easily grow very large in a short amount of time when users run queries that return large results sets, create temporary tables, start a transaction in snapshot isolation, or when you rebuild indexes on large tables, etc.
    I am not a fan of shrinking a DB without a good reason, however if you’re dunning short on storage space, you can shrink the temp DB and recover the space when the pertinent operation that has created the temporary objects in the TempDB finishes.

    Author Comment

    I have a TempDB that does not get smaller and it keeps on growing.  Right now, ours is over 200 gigs.  How can I stop this database from growing so much and what can I do to stop this process from happening?  Is this an internal error that keeps on flagging?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now