April 29, 2010 at 11:48 am
Hi,
We have MOSS 2007 databases in SQL Server 2005 EE x64 and the application went to Production 1 month ago. From last 1 month, the tempDB log file size is increasing significantly. Right now its log file size is 10 GB and daily it's almost increasing by 500 MB. So my question is until what size it can grow like this? I have set the initial size as 20 GB for Tempdb Log file size and Tempdb is on separate drive with 50 GB allocated space.
Thanks
April 29, 2010 at 12:02 pm
How big it will get depends on how big the transactions are. Is the database set to SIMPLE recovery mode?
April 29, 2010 at 12:36 pm
Why dont you try shrinking the log ???? 🙂
April 29, 2010 at 12:44 pm
How big it will get depends on how big the transactions are. Is the database set to SIMPLE recovery mode?
It's a TempDB which will be always in SIMPLE recovery mode!!
Thanks
April 29, 2010 at 12:46 pm
Why dont you try shrinking the log ????
I'm NOT sure whether we can shrink the log file size for TempDB or not? I believe it's NOT a good practice though!
April 29, 2010 at 12:47 pm
Yes ofcourse.You can !!!
April 29, 2010 at 12:51 pm
Hi,
check what is given in auto growth if it is restricted then it will grow only till the size specified, if is auto grown given in percentage or mb then it will grow till the disk space out of free space.
Regards
Durai Nagarajan
April 29, 2010 at 3:38 pm
Contact the folks at MOSS 2007... they created the problem and they need to fix it. Simply shrinking TempDB now and again isn't going to do anything except frag it up until the next server bounce.
This isn't the first time of heard of suach a problem with MOSS 2007 (whatever it is) and the more people that complain, the more likely they are to fix it.
Tell them Jeff want's to know if they like pork chops or what. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2010 at 11:57 pm
I agree with Jeff. This needs to be taken up with the folks at MS.
If you want good ammunition, find out what in MOSS is causing the growth first.
Here's an article[/url] to help with that.
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
April 30, 2010 at 12:39 am
Hi,
In MOSS 2007, probably the one who configured it has enabled complete logging.
There are 2 options in MOSS 2007, one where in complete logging & second partial .
Sp probably ask MOSS 2007 people to do the correction
April 30, 2010 at 6:13 am
Mani-584606 (4/29/2010)
Why dont you try shrinking the log ????
I'm NOT sure whether we can shrink the log file size for TempDB or not? I believe it's NOT a good practice though!
You absolutely can shrink log files for TempDB. It is NOT a best practice, though, and I don't recommend doing it unless the world is about to come to an end. @=)
May 4, 2010 at 12:33 pm
Hi Jason,
Can I run the below commands on Production TempDB directly or do I need run these commands only for user database?
DBCC OpenTran(mydb)
DBCC InputBuffer(SPID)
Thanks
May 4, 2010 at 12:48 pm
It doesn't matter what database you're on for the InputBuffer command (and I believe the same for the OpenTran). A SPID is unique in the whole server instance. It should give you the same answer, regardless of database.
I run InputBuffer from MASTER.
May 4, 2010 at 12:53 pm
Sorry, I misspoke. I don't run InputBuffer anymore. I have better code now that gives me the entire context of the command instead of just the first X number of characters.
Try running this:
DECLARE @Handle BINARY(20)
SELECT @Handle = sql_handle
FROM SysProcesses
WHERE SPID = 133 --Change this SPID as appropriate
SELECT *
FROM ::fn_get_sql(@handle)
May 4, 2010 at 1:38 pm
Or for 2005, try this:
SELECT [Spid] = session_Id
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
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 18 total)
You must be logged in to reply to this topic. Login to reply