March 15, 2010 at 8:55 am
I am a baby DBA and I have a large problem with my SQL2005 tempdb. It's growing out of control daily and I've been having to restart it every day. I've scoured this forum to find good soloutions but I really don't know what else to do. Tempdb is on a 100GB SAN drive. I have it set to simle recovery. I also have alot of errors in my logs.
Error: 17187 Severity 16
Error: 9002 Severity 17
Error: 17053 Severity 16
Error:18456 Severity 14
Error: 926 Severity 21
Error: 1105 Severity 17
I have no transactions open, I'm not sure what else to look at? any suggestions?
March 15, 2010 at 8:59 am
See this
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 15, 2010 at 9:09 am
Looked at that...
here is what space_used says
tempdb101.75 MB95.68 MB
1544 KB744 KB736 KB64 KB
March 15, 2010 at 9:21 am
Molly Cary (3/15/2010)
I am a baby DBA and I have a large problem with my SQL2005 tempdb. It's growing out of control daily and I've been having to restart it every day. I've scoured this forum to find good soloutions but I really don't know what else to do. Tempdb is on a 100GB SAN drive. I have it set to simle recovery. I also have alot of errors in my logs.Error: 17187 Severity 16
Error: 9002 Severity 17
Error: 17053 Severity 16
Error:18456 Severity 14
Error: 926 Severity 21
Error: 1105 Severity 17
I have no transactions open, I'm not sure what else to look at? any suggestions?
Error: 9002 Severity 17 - transaction log is full. What backup mode are you using? Commonly this is caused by being in full backup mode, but not doing (sufficiently frequent) log backups. As well as causing this problem, it also puts your data at risk - are logs being backed up, nd if so how often?
There can be other issues causing this suck as long transactions etc. See
http://support.microsoft.com/kb/317375
Error:18456 Severity 14 - failed login. The state in the error message in the log will tell you the reason for the login failure. Eg. 5 wrong password, 8 wrong login (IIRC)
Error: 926 Severity 21
*Can* be caused by DBCC CHECKDB running out of discspace. Is this happening during your overnight checks?
Error: 1105 Severity 17 - out of discspace so a file can't grow
March 15, 2010 at 9:33 am
My transaction logs are backed up every hour. and a full back up at midnight
March 15, 2010 at 9:36 am
I did see this just now
Date3/15/2010 12:00:21 AM
LogSQL Server (Archive #1 - 3/15/2010 6:32:00 AM)
Sourcespid18s
Message
This instance of SQL Server has been using a process ID of 5964 since 3/12/2010 3:12:41 PM (local) 3/12/2010 8:12:41 PM (UTC). This is an informational message only; no user action is required.
March 15, 2010 at 9:43 am
Just a quick check, as you are checking on issues, are you sure you are checking tempdb? Typically tempdb isn't being backed up, and operates in Simple mode. Is that the case?
It's easy to run commands against your user database, and not check tempdb.
The other thing I'd note is that perhaps you need more space in tempdb? you might not be setting it large enough for the activity on your server.
March 15, 2010 at 9:55 am
Molly Cary (3/15/2010)
I did see this just nowDate3/15/2010 12:00:21 AM
LogSQL Server (Archive #1 - 3/15/2010 6:32:00 AM)
Sourcespid18s
Message
This instance of SQL Server has been using a process ID of 5964 since 3/12/2010 3:12:41 PM (local) 3/12/2010 8:12:41 PM (UTC). This is an informational message only; no user action is required.
Not a problem
March 15, 2010 at 10:54 am
Adding to Steve questions, Did you try to find out what causing TempDB to grow?
March 15, 2010 at 11:00 am
I recommend you follow the steps outlined in the Log Growing pains article here:
http://www.sqlservercentral.com/articles/Log+growth/69476/
This can help you identify the source of the growth and then you can optimize and correct that code.
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
March 15, 2010 at 11:05 am
With that level of growth I was tempted to suggest looking for the sp where someone loads the 5 largest tables into temp tables and then joins them using cursors
March 15, 2010 at 11:41 am
Well I'm pretty sure it's the tempdb that's my problem, but no I haven't figured out what it is.
March 15, 2010 at 12:24 pm
Molly Cary (3/15/2010)
Well I'm pretty sure it's the tempdb that's my problem, but no I haven't figured out what it is.
Tempdb will grow due to temp table usage, large sort operations, poorly written queries, etc.
If you follow the steps in that article I provided, it should help you find the queries causing the problems in Tempdb.
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
March 15, 2010 at 12:37 pm
Great! thanks. I am seeing that it's happening around 2-3 am.
March 15, 2010 at 12:40 pm
Molly Cary (3/15/2010)
Great! thanks. I am seeing that it's happening around 2-3 am.
Do you by chance have an index rebuild job scheduled at that time?
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply