InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 18:47:41 GMT20http://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxHello tapaskumardm,you are using replication. What does dbcc opentranget you? Any not distributed transactions? RegardskarlFri, 07 Sep 2012 06:47:52 GMTKarl Klinglerhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxMaybe this will help you identify the source of the tempdb log file usage; although indirectly because it's looking more at tempdb objects/** tempdb usage by task **/SELECT distinct t1.session_id, login_name, s.login_time, s.last_request_start_time, s.last_request_end_time, 'dbcc inputbuffer(' + cast(t1.session_id as varchar) + ')','exec sp_who2 ' + cast(t1.session_id as varchar),s.host_name, (t1.internal_objects_alloc_page_count + task_alloc) as allocated,(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated from sys.dm_db_session_space_usage as t1inner join(select session_id, sum(internal_objects_alloc_page_count) as task_alloc, sum (internal_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id) as t2on t1.session_id = t2.session_id and t2.session_id >50inner join sys.dm_exec_sessions s on t1.session_id = s.session_idorder by allocated DESC/** tempdb usage by current running request **/--- Tempdb - statements using tempdb------ This script is provided "AS IS" with no warranties, and confers no rights. --- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htmSELECT t1.session_id,(t1.internal_objects_alloc_page_count + task_alloc) as allocated,(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated , t3.sql_handle, t3.statement_start_offset , t3.statement_end_offset, t3.plan_handlefrom sys.dm_db_session_space_usage as t1, sys.dm_exec_requests t3,(select session_id, sum(internal_objects_alloc_page_count) as task_alloc, sum (internal_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id) as t2where t1.session_id = t2.session_id and t1.session_id >50and t1.database_id = 2 --- tempdb is database_id=2and t1.session_id = t3.session_idorder by allocated DESCThu, 06 Sep 2012 14:06:25 GMT2ndHelpinghttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspx[quote][b]makrandghanekar (9/5/2012)[/b][hr]try restarting SQL Server Agent (MSSQLSERVER) from services.msc[/quote]what do you think this will do?Wed, 05 Sep 2012 03:49:19 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxsounds like checkpointing may have stopped - you get that a lot on slow IO when you have large amounts of memorytry the followingUSE TEMPDBGOCHECKPOINTif the script takes more than a few seconds then you can just cancel it and it should al least clear the inactive portion of the logWed, 05 Sep 2012 03:45:43 GMTMVDBAhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxtry restarting SQL Server Agent (MSSQLSERVER) from services.mscWed, 05 Sep 2012 03:39:10 GMTmakrandghanekarhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxDepending on how long ago tempdb physically grew you may be able to query the default trace file if you have it enable. It is enabled by default. It captures among other events, the auto-growths of the database files and transaction log files. It may show who was running the transaction that caused the physical file to grow. If the default trace does not go back far enough then you will have to wait until after you have reclaimed the tempdb and wait for it to happen again. There is an excellent article with code on Simple-Talk for querying the default trace file. http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/?utm_source=simpletalk&utm_medium=email-main&utm_content=DefaultTrace-20110321&utm_campaign=SQLIf you want to take a heavy handed approach you restrict the max size of the tempdb database. When the guilty transaction fills it up without finishing the transaction will eventually timeout and rollback which SHOULD release the the internal space (but NOT the physical space). I don't recommend this approach but....LeeTue, 04 Sep 2012 19:57:23 GMTLee Linareshttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxSounds to me like you have a quarterly job that is running a really large transcation running. Are you able to add a second SAN drive? Perhaps you could do this and add a .ndf file to the tempdb which would help with the space. You really need to determine what is the cause of the massive grown.Tue, 04 Sep 2012 08:52:53 GMTbgdjwoodshttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxAssuming there is an inactive portion, assuming the inactive portion is at the end of the log file and assuming that the shrink does not cause problems when used on a live, in-use TempDB (see Robert's comment)Mon, 03 Sep 2012 05:28:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxI think DBCC SHRINKFILE is worth to use.It will make some space because in log file there is active and inactive portion.You can reclaim inactive space in log file.Mon, 03 Sep 2012 04:41:21 GMTdb_expert_pradeephttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxHi,In your case you can use DBCC SHRINKFILE if it doesn't worked out try once again, then look for log space if the reduction is not much satisfied then use the queryselect name,log_reuse_wait_desc form sys.databases where dbname='<dbname>' look for the rows that query returns. if it is active transaction identify the spid and troubleshoot or kill the transaction. If you dont have permission to kill then you can use DBCC FREEPROCCACHE---> this will release the plan cache in the memory and gives some spaceMon, 03 Sep 2012 00:14:25 GMTprasanna.vinayhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspx[quote][b]durai nagarajan (8/29/2012)[/b][hr]any problem in using DBCC SHRINKFILE?[/quote]Yes, using DBCC Shrink[File|Database] on a live tempdb has in some cases caused corruption.Sun, 02 Sep 2012 22:23:05 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxWhat I do is create a 2 or 5Gb dummy file on each SQL disk (ldf,mdf,tmpdb). Whenever there is an urgent disk storage problem I can delete the dummy file and buy myself some time. It is no final solution but it helped me out some a few time.Sun, 02 Sep 2012 08:24:46 GMTT2000http://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxTempDB automatically re-uses space. So, if the transaction log continues to grow it is because it has rows which may not be deleted due to open transactions.I have found that using connection pooling in ADO if you don't close your connection, the connection pool won't complete implicit transactions. This results in SQL Transactions that remain. Until the transaction is committed, or rolled back, the transaction log cannot re-use this space.Since you say this only happens once a quarter I would guess the ADO issue may not be the cause. You may have a longer running job requiring more time and thus grows tempdb transaction log. If you find this is the case, then consider taking work for larger jobs and breaking them down into smaller batches.Finally, make sure CheckPoint is at a short enough interval. This is what causes SQL Server to go and remove completed transactions from transaction logs of databases in simple recovery mode. TempDB is always in simple recovery mode.Thu, 30 Aug 2012 07:43:41 GMTbtaylor 78431http://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspx[quote][b]tapaskumardm (8/28/2012)[/b][hr]Thanks,usually it is around 2 GB. we don't have space in SAN drive to add.I want to release it now.. Is there any way to fix it ?????[/quote]any problem in using DBCC SHRINKFILE?Wed, 29 Aug 2012 04:52:36 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxNo, it won't release space. If the log is not growing more, leave it.Tue, 28 Aug 2012 08:47:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxSpace will not release once it has been consumed, this is due to the expensive procedure of allocating more space to files.If it grew to 5GB it will stay at 5GB unless you manually restart SQL or if possible run DBCC SHRINKFILE as per the article I provided earlier.Tue, 28 Aug 2012 08:43:27 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxIt is not happening daily basis.. once in a quater or so I faced the problem..All the time I can not restart the server,,, right.So, please tell me is there any option to make release the unused space on tempdb ??Tue, 28 Aug 2012 08:42:14 GMTtapaskumardmhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxI know one job is using tempdb.but tempdb is not releasing the space where as 4 Gb of space is not used.I am using SQL 2005. also running replication. so temp db is always in use.Tue, 28 Aug 2012 08:38:49 GMTtapaskumardmhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxAs Gail mentioned, figure out what is causing the log file to grow, otherwise if it happens again you will be in the same boat.Something wanted the log file to grow to 5GB so if it happens again and you have shrunk the file already it will regrow to 5GB.Then you might get away with using DBCC SHRINKFILE[url]http://support.microsoft.com/kb/307487[/url]Otherwise restart SQL or add more space.Tue, 28 Aug 2012 08:00:51 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxThanks,usually it is around 2 GB. we don't have space in SAN drive to add.I want to release it now.. Is there any way to fix it ?????Tue, 28 Aug 2012 07:57:33 GMTtapaskumardmhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxadd more space to the driveorrestart SQLTue, 28 Aug 2012 07:51:05 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxAdd more drive spaceFigure out what's preventing TempDB's log from being reused and resolve that problem[url]http://www.sqlservercentral.com/articles/Transaction+Log/72488/[/url]Tue, 28 Aug 2012 07:50:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1350936-146-1.aspxtempdblog file is growing to 5 gb. I dont have more space on the drive.want a immediate solution . without restarting the server.please somebody reply..Tue, 28 Aug 2012 07:43:58 GMTtapaskumardm