September 21, 2010 at 12:37 am
Hi Folks,
I've recently taken on responsibility for implementing a technical soloution into our organisation which logs and controls access to USB removable media on client PCs. The systems logs to a central database which has hit some issues of recent whereby the tempdb (MDF) has grown exponentially to 80GB! I'm by no means a DBA unfortunately so forgive me if the info I provide is a little vaugue etc.
The growth of tempdb has not been a gradual process, a spike seems to have occoured as a result of some reports I had run (from the applications front end) but had to abandom due to time taken (this was done by just closing out of the application console). I've done some programming in the past and so thought perhaps the issue could have been in the way its coded, using a # to generate a temp table but not dropping it (since I closed improperly or did not allow the report to run correctly)
From my research on the forum, I beleive tempdb is copied from the model db and its 2.2 GB (the mdf file anyway).
My actual database MDF is approximately 40GB in size (again MDF).
I've tried to restart the SQL agent to see if this would scrap and rebuild the tempdb but it hasn't made any difference. This fuels my thinking that its attempted to create temp tables for the reports and has not been able to discard them. Unfortunately space is a slight issue on the box and between the tmpdb and the application database its almost filling my drive. Our server hardware team have looked at the box and unfortunately we dont have any room for expansion, other than installing new hard discs and recovering to them (really a last resort).
I'd be grateful for any advice in shrinking this tempdb down - as safely as possible - as unfortunately this is a fairly critical application.
Thanks,
Chris
September 21, 2010 at 1:00 am
Since you admit you're not a DBA I'll do my best to keep this easy. 🙂
You can shrink the tempdb via the interface. Open the server up, open databases and then system databases. Right click 'tempdb' and then 'Tasks', then 'shrink', then 'files'. You want the data file.
I would NOT recommend shrinking this db to nothing, sql server uses it for a bunch of tasks. Take it down to 10-20 gigs if you're tight on space. However, if you start running gigantic reports (or runaway loops of some kind) this will re-grow. Just be aware of that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 1:06 am
Craig,
Thanks for the advice and for keeping it simple.
Is it safe to carry out a shrink on the tempdb? and would simply stopping our application service on the server release all activity on it? Our issue with the reports is that there is a whole lot of ... crap effectively ... in our database that needs to be cleared, which is probably why the reports took so long and I cancelled out (not closing the loop).
Thanks again,
Chris
September 21, 2010 at 1:07 am
If you are concerned about space you should also limit the maximum size of tempdb files (right click tempdb -> Properties -> Files -> autogrowth column -> Set maximum size of each data file)
If shrinking tempdb doesn't work you'll have to do a stop start of the sql server service to fix it.
September 21, 2010 at 1:09 am
Don't worry about tables in tempdb, when you closed the application, the connection closed and sql server got rid of the temporary data on it's own.
If you have any doubt you can open the Activity monitor (in management tab) and see if there are any query still running that might hold the temporary table.
September 21, 2010 at 1:09 am
Thanks for the info again - unfortunately a I'd already carried out a restart of the SQL service (SQL Server agent?) and it didnt shrink the DB.
September 21, 2010 at 1:11 am
Its the SQL Server Service (usualy MSSQLSERVER or MSSQL$<instancename>), the SQL Server Agent is a SQL scheduler.
September 21, 2010 at 1:13 am
Ah crap (novice-ness striekd again) thanks Oiiii, so I'll find that service under the normal server services will I? I can bring down the server quite easiy if it only requires a reboot of this nature. It sounds less harrasing than shrinking the db. I had thought previously that restarting the SQL Agent was effectively restarting the service.
September 21, 2010 at 1:14 am
chrisjkelly (9/21/2010)
Craig,Thanks for the advice and for keeping it simple.
Is it safe to carry out a shrink on the tempdb? and would simply stopping our application service on the server release all activity on it? Our issue with the reports is that there is a whole lot of ... crap effectively ... in our database that needs to be cleared, which is probably why the reports took so long and I cancelled out (not closing the loop).
Thanks again,
Chris
What Oliii above said is true, a stop/start will help clear the issue. However, tempdb should have let the resources go when it expanded, it just hung onto the space on the drive in case it needed it again. Physically growing a file, for SQL Server, takes time. It tries not to have to do it constantly, creates a lot of fragmentation churn on the drive, amongst other performance issues.
It is perfectly safe to carry out the shrink on the tempdb. It will whine and complain at you if there's not enough room free to do what you want. At which point, issue a user alert, and do a stop/start on the server. Shouldn't take long if you do it cleanly (IE: Go through Computer-Manage-Services and STOP/START the service, don't hit the reboot button). When you restart the service, make sure to also restart SQL AGENT. Taking the primary down brings Agent with it.
Cancelling in MS SQL Server (not just crashing the connection, but actually cancelling) should issue a rollback command to the server, which should release resources, so this shouldn't be an issue. If it is, you're going to need a restart anyway. Now, as to your report of doom that's taking 3 years... you probably need some optimization, indexing, and a solid grasp of the data. That's for the next post. 🙂 If you like, check out the links in my signature, specifically the optimization one, if you want help with that part.
[EDIT:] I need to type faster, Oliiii's got you on track though. :)[/EDIT]
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 1:26 am
One item of note: If you manually set the tempdb size via properties, it rebuilds to that size even though it resets the database. You may still need to perform a manual shrink, I can't confirm it will behave itself off the auto-growth... well, not yet.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 1:26 am
Chaps - I thoroughly appreciate your help. I've restarted the service (not the agent this time (Doh!) and its cleared the space issues. I cant beleive how stupid I was in stopping the agent and thinking it should have been clearing the problems. Thanks very much again for your posts.
Craig - I'll take a look at the links as suggested. As I mentioned we have some really crappy data in there that we have to tidy. I think once this is in place then running some proper indexing should save us alot of greif with future reports.
A simple fix for you chaps but a godsend for me. Thanks again.
September 21, 2010 at 1:38 am
chrisjkelly (9/21/2010)
Chaps - I thoroughly appreciate your help. I've restarted the service (not the agent this time (Doh!) and its cleared the space issues. I cant beleive how stupid I was in stopping the agent and thinking it should have been clearing the problems. Thanks very much again for your posts.Craig - I'll take a look at the links as suggested. As I mentioned we have some really crappy data in there that we have to tidy. I think once this is in place then running some proper indexing should save us alot of greif with future reports.
A simple fix for you chaps but a godsend for me. Thanks again.
No problem, but do a double check. Make sure SQL Agent is up and running. I've shot myself in the foot once or twice with that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 2:20 am
Repost - sorry ...
Chris
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply