January 23, 2010 at 12:54 am
Hello Experts
I need some suggestion from your end.
In one of the production sql sever, the "TEMPDB" database have 9 data files and 1 log file. The total size of the database is: 110 GB.
In the disk, we have only 9 gb of space and alert is coming.
I did the below steps:
(1) Tried to shrink all the data files and log file. Able to claim some space to the OS disk.
(2) Taken the log backup of the tempdb database.
But I neeeded to free up more space to the drive.
Can you suggest any other favourable steps (other than expanding the drive) to free up some space from DBA perspective.
Many thanks ...
Thanks.
January 23, 2010 at 3:56 am
The only way you're going to fill up that much space in temp DB is to have a big reindexing job or huge reports.
If this comes from indexing, make sure you index / rebuild depending on fragmentation and only IF required. You can also spread the job out throught the day if 24 / 7. (if you know you have 100 index to defrag daily, then do 3-4 / hour). That last one might be tricky to implement but you most likely don't need it.
Another thing you might try to is NOT do sort in temp DB for indexing jobs... see what it does for you.
For the reports, you can always move those on a report server (backup/restore de db or do full replication or whatever you need).
Or you can also run the reports at night when the server is less used.
Of course the real solution is to add disk space as soon as you can!!!
P.S. I once had a single query load up tempdb to 25 GB on a 17 GB DB. It was a bug with sql 7 no SP. I never got it fixed since we were movin gto 2008. So maybe you are experiencing a single query that could use optimisation. You can run standard performance audits to find the single most intensive queries or the ones that take the most ressources when all the runs are added up.
Most likely that all these answer will help you a little bit. But I don't know which one will solve your problem for you system.
January 23, 2010 at 10:39 am
You can take outage and restart the SQL Serever services, which will rebuild the tempdb with default size.
Thanks and Regards!!
Thank You.
Regards,
Raghavender Chavva
January 23, 2010 at 10:47 am
There is another scenario that could be causing excessive use of the tempdb.
There is a poorly written runaway sql statement causing heavy tempdb usage.
There is also the possibility of a legit sql statement that is using temp tables or the like.
In either scenario, you will need to find what is causing your tempdb consumption and correct the code or process or both.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 23, 2010 at 11:35 am
CirquedeSQLeil (1/23/2010)
There is a poorly written runaway sql statement causing heavy tempdb usage.
Concur. In fact, I've found that to be the norm rather than the exception.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2010 at 11:39 am
Jeff Moden (1/23/2010)
CirquedeSQLeil (1/23/2010)
There is a poorly written runaway sql statement causing heavy tempdb usage.Concur. In fact, I've found that to be the norm rather than the exception.
In what I have seen, this is the most prevalent. Second most is the index rebuilds - but those should be expected and should be somewhat better controlled.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 25, 2010 at 2:56 pm
The weekly update stats job was running.
Thanks.
January 25, 2010 at 3:11 pm
Sourav-657741 (1/25/2010)
The weekly update stats job was running.
i would keep looking at it. I can't imagine a weekly update stats job bloating your database like that all by itself. There was probably another process running that helped create the problem.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 25, 2010 at 6:36 pm
It's not completly impossible. Do you have a table that's bigger than all the ram of the server??? or at least pretty darn close?
January 25, 2010 at 11:39 pm
No friends. I was consistently running the "select * from sysprocesses" to find the active connections. But there was no blocking \ running jobs during that time other than the "update stats".
What happened you know, the job was running for 16 hrs. And I found that the job status was in "suspended" status for about 4 hours. I had a doubt and finally decided to kill the blocked process which was something showing as "Fetch...cursor" in its input buffer.
After that the job again started. But again after sometimes (around 1 hr) it moved into 'suspended' status. I killed the job and started from the failed step. Then it completed taking another 5 hrs.
Update stats was updating the stats of tables of two big databases.
Thanks.
January 26, 2010 at 12:19 am
Thanks for the update.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 7:01 pm
At the risk of commandeering your post, I have a similar but somewhat different observation (issue?) regarding TempDB.
I take weekly screenshots of the Disk Usage Report for all databases and send them to my management. Each week, for growth management, I compare them to the prior week's screenshots and decide if I need to manually grow any of the databases. I've noticed that the TempDB data file is rarely more than 5% utilized, but the TempDB log file grows steadily for 2-3 weeks until it is about 65-70% utilized, then suddenly shrivels to almost 0% utilized. Then, the slow steady growth of the log file begins again.
Lately, I've been observing this phenomenon several times a day to verify what I'm seeing. The steady growth is always going on but the TempDB data file is rarely even 5% utilized.
What could be causing this phenomenon?
LC
February 10, 2010 at 2:13 pm
I wouldn't run Update stats job in SQL 2005, by default auto update stats option is set to on in SQL 2005, That update stats job cause you lot of overhead in production. I wouldn't run that job in production. Make sure Auto update stats is set on, run sp_helpdb. And also when ever you rebuild indexes it will update stats too.
EnjoY!
February 10, 2010 at 7:50 pm
Thanks for the advice.
"Auto-update statistics" is enabled on all of our databases but it is not quite enough.
Our databases have a very high level of inbound traffic. Looking at a histogram of the data, the high rate of data inflows alter data distribution significantly. We have found through experimentation, that selectively updating data statistics, where they are no longer accurate, takes very little resources or time, and improves the Optimizer's selection of good query plans.
We perform this selective statistic update process every 6 hours, 3 times per day, and re-index, if needed, once per day, which of course, also updates the statistics.
LC
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply