Adding log file to TempDB

Question

Post reply

Adding log file to TempDB

shovankar

Say Hey Kid

Points: 694
More actions
March 15, 2012 at 9:57 pm

#265844

Hi All,
I need to add a log file to one of my production SQL instance's TempDB. Then, I want to restrict the existing TempDB log file growth on a particular drive so that, it uses the new TepmDB log file. I want to know if this can be done without restarting the SQL Services. Below is a small background :
The TempDB data and log file is hosted on a 300 GB drive. Currently, the datafile size is 3 Gb and the Log file occupies 296 GB. Due to this, the disk has got no space. I can't restart the SQL instance as it is hosting databases of various appllications. Also, there is a shrink job running on the tempDB, but its hardly helping to reduce the log size. In this scenario, could anyone please let me know if I can restrict the existing log file growth and then add another log file on a different disk to the TempDB without restarting the SQL instance ?
PS- Im using SQL server 2005 enterprise edition SP3.
Regards,
Shovan

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

You really need to find out what made the log file grow that big and fix it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

baabhu SSCertifiable Points: 6205 More actions · Answer 2

296 GB for a tempdb. I would say too much of activity going on from application. You said too many application is using your server.

You have default trace capturing the log growth and data file growth. I am not sure whether this is available in 2005.

You can define your new trace if not available to find the growth information about the tempdb file. From there you can start looking what application jobs are running.

DECLARE @filename NVARCHAR(1000);

DECLARE @bc INT;

DECLARE @ec INT;

DECLARE @bfn VARCHAR(1000);

DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace

SELECT @filename = CAST(value AS NVARCHAR(1000))

FROM ::fn_trace_getinfo(DEFAULT)

WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces

SET @filename = REVERSE(@filename);

SET @bc = CHARINDEX('.',@filename);

SET @ec = CHARINDEX('_',@filename)+1;

SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));

SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number

SET @filename = @bfn + @efn

select @filename

-- process all trace files

SELECT

ftg.StartTime

,te.name AS EventName

,DB_NAME(ftg.databaseid) AS DatabaseName

,ftg.Filename

,(ftg.IntegerData*8)/1024.0 AS GrowthMB

,(ftg.duration/1000)AS DurMS

FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg

INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id

WHERE (ftg.EventClass = 92 -- Date File Auto-grow

OR ftg.EventClass = 93) -- Log File Auto-grow

ORDER BY ftg.StartTime

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

shovankar (3/15/2012)
In this scenario, could anyone please let me know if I can restrict the existing log file growth and then add another log file on a different disk to the TempDB without restarting the SQL instance ?

Yes it is possible. But it is a temporary measure.

296GB log file for 3GB data file means something is wrong.

As Jeff mentioned, you need to identify that issue and fix it.

Once the issue is fixed, remove the additional log file.

Run this query to find out what is preventing log truncation:

select name, log_reuse_wait_desc from sys.databases where database_id = DB_ID('tempdb')

shovankar Say Hey Kid Points: 694 More actions · Answer 4

Hi All,

Thanks a lot for your reply on this. Could you please let me know if the addition of the new log file is possible for TempDB without restarting the SQL instance ? If yes, then I can go for it as a temporary measure and then probably identify the issue that is causing the TempDB log file to grow !

The SQL instance is hosting a bunch of sharepoint content databases.

Please let me know if SQL restart is not required in this case.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 5

shovankar (3/15/2012)
Could you please let me know if the addition of the new log file is possible for TempDB without restarting the SQL instance ?.

Yes, it is possible to add additional log file to TempDB without restarting the SQL instance.

Please let me know if SQL restart is not required in this case.

SQL restart is not required.

I suggest you to try this once in a test server to become comfortable with the process.

shovankar Say Hey Kid Points: 694 More actions · Answer 6

Hi Suresh,

I tried the following : select name, log_reuse_wait_desc from sys.databases where database_id = DB_ID('tempdb')

Its showing as "NOTHING".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

shovankar (3/16/2012)
Hi Suresh,
I tried the following : select name, log_reuse_wait_desc from sys.databases where database_id = DB_ID('tempdb')

All that means is that there is currently nothing keeping the log active which means you don't need another log file, there's free space in this one. You can confirm that by running DBCC SQLPerf(LogSpace) and seeing what % of the log file is in use. I suspect it will be a very low %

To be useful, you'd have needed to get that log_reuse_wait while whatever process was growing the log as there's nothing after the fact that records why a log grew.

Do you have any logging on that server, anything that could tell you what was running at a point in time? You can get the times that the log grew from the default trace but again, unless you have some record of what was going on at the time the times may not be that useful.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shovankar Say Hey Kid Points: 694 More actions · Answer 8

Hi Gail,

Thanks a lot. Yes you are correct regarding the log % usage...only 2% of the log is currently being used. Further I tried to shrink the 296 GB tempDb log with TRUNCATEONLY option but, its not truncating the file. What I found that TempDB log file is holding 38912000 pages (size=296 GB) which it it is not releasing. Hence, shrinking the TempLog is not able to release the unused space.

I am wondering why it is not deallocating the pages!!

The SQL instance has not been restarted since last 4 months. Now my main concern is that, as the tempdb files are hosted on a mounted drive with 300 GB allocated space, and 3 GB of tempdb data file, the datafile is not able to grow beyond 1 GB. Because of this, many of my weekely maintenance jobs are failing with the below error :

““Could not allocate space for object 'dbo.SORT temporary run storage: 194466956050432' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup”

It would be great if you can put some light how the high number of pages in the log file can be deallocated.

Thanks,

Shovan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

shovankar (3/16/2012)
Thanks a lot. Yes you are correct regarding the log % usage...only 2% of the log is currently being used. Further I tried to shrink the 296 GB tempDb log with TRUNCATEONLY option but, its not truncating the file.

The Truncate_Only option on shrinkfile is only for data files/

I am wondering why it is not deallocating the pages!!

Well, technically a log file doesn't have pages... Probably won't shrink because the active portion of the log is at the end of the file, and VLFs can never be relocated. You can check that with DBCC LOGINFO, see where the VLFs with a status of 2 are. You can wait for the log to cycle and try to shrink again, or you can run some 'fake' activity designed just to force the log to cycle around and then shrink

The SQL instance has not been restarted since last 4 months. Now my main concern is that, as the tempdb files are hosted on a mounted drive with 300 GB allocated space, and 3 GB of tempdb data file, the datafile is not able to grow beyond 1 GB. Because of this, many of my weekely maintenance jobs are failing with the below error :
““Could not allocate space for object 'dbo.SORT temporary run storage: 194466956050432' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup”

I would recommend you restart the SQL Service, tempDB is not supposed to be shrunk online anyway. If you can't, add another data file temporarily. No point in adding another log file, plenty of free space in the log and it's the data file that's full causing those errors.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass