| Author |
Topic |
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
 Posted - 2008-01-09 : 19:39:22
|
| Hello everyone. Have a little situation here. I am not normally the DBA at my work, but i've been poking around a little with a SQL Server 2005 implementation. I'm pretty new to this, so please pardon my lack of knowledge.This particular server is running a lightly used SQL server, or so im told. As part of my Sys Admin duties, I logged into the box today for some routine checks, only to be notified that I was running out of disk space (which is another story).After poking around awhile, I see that my tempdb is almost 15gb in size. I don't have a lot of experience with SQL Server 2005. I've worked mostly with 2000. I did read a few things where tempdb has changed a bit in 2005? I wasn't sure.So I have about 10gigs of space free on my HD (I'm adding more space tomorrow) and I need to figure out what to do.I came across this thread here:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64914[/url]is that something I should do? I'm a little concerned I might run out of space overnight and not make it in time.Anyone have some suggestions?Thanks.TCG |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-09 : 22:49:37
|
| What are data file and log file size? How much free space in it? Can try shrink it first, worst case is restarting sql that will create empty tempdb. |
 |
|
|
montu
Yak Posting Veteran
60 Posts |
Posted - 2008-01-09 : 22:54:36
|
| isn't there is any other solution other then restart server. isn't truncate log only will work in this case. please advice |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-09 : 23:03:08
|
| Truncating log will not reduce file size, that's why need shrink it. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-01-10 : 12:46:39
|
| Right click on tempdb, tasks, shrink files.when this appears select File Type = Log default is data.Then do reorganize pages before realeasing unused spaceshrink file put in size here.I would do it in increments couple gigs at a time.I have seem my tempdb go to 115 gigs before.Tracey |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2008-01-14 : 12:28:34
|
Hi everyone. Sorry it took me so long to get back. My wife and I had our first son.  Back on topic to follow up some additional questions.the tempdb file is continuing to grow. I just checked and it is at 17gb and growing. The story is sort of long, but to make it brief, the application and SQL server were installed by a contractor for a specific application. When I created the server, I created two partitions; an OS partition and a Data partition. The contractor said there would be no need to have a seperate array for the t-logs and database files (Should have known better)Anyway, the guy installs everything into the OS partition, which is only 30gig. When I realized this, I was pretty perturbed and ended having to move the tempdb database to the second partion, which has a little over 100gig.This leads me to where I am now; tempdb 17gig and growing. I should point out, that I see the tempdb.MDF file is the one growing, not the .LDF file.I am going to try what TRACEYSQL said and report back.Thanks.TCG |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2008-01-16 : 13:29:42
|
quote:
Originally posted by TRACEYSQL
Right click on tempdb, tasks, shrink files.when this appears select File Type = Log default is data.Then do reorganize pages before realeasing unused spaceshrink file put in size here.I would do it in increments couple gigs at a time.I have seem my tempdb go to 115 gigs before.Tracey
Can you do this for the actual .mdf file? That is the one that is growing pretty good. Wasn't sure if that would affect anything.Thanks.TCG |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-16 : 13:49:38
|
| you're going to want to move the .mdfs for the user DBs to other partition too...you can try shrinking mdf, but may want to do as rmiao says and restart sql service (hopefully at night or when no one needs access).should then move tempdb to the larger partition. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2008-01-16 : 14:03:46
|
quote:
Originally posted by russell
you're going to want to move the .mdfs for the user DBs to other partition too...you can try shrinking mdf, but may want to do as rmiao says and restart sql service (hopefully at night or when no one needs access).should then move tempdb to the larger partition.
Oh, forgot to mention I successfully moved that tempdb .ldf and .mdf files over to the new partition this past weekend. Went very smoothly.Now, it is a matter of shrinking the tempdb .mdf file, since it is growing pretty rapidly.Much appreciated. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-16 : 14:21:53
|
| if it is growing rapidly, then the usage requires large tempdb. may need to look at more disks. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2008-01-16 : 15:16:49
|
quote:
Originally posted by russell
if it is growing rapidly, then the usage requires large tempdb. may need to look at more disks.
That is the plan, to move it to our SAN environment. Looks like I will be going to some SQL training here soon (im a Active Directory, Exchange, VMWare guy), so that will help.In the meantime, can I shrink the actual .mdf file, that one that is at 18gigs right now, using the method above? I don't know the innerworkings of things yet, so before I start pushing buttons, I want to make sure I don't nuke anything. :) |
|
|
|
|
Managing the tempdb: Help