Solved

templog.ldf size too large

Posted on 2014-02-06
7
5,050 Views
Last Modified: 2014-02-06
I have a SQL 2005  installation on 2003 sbs pk.2. The templog.ldf file is 581GB. What
is in this file and how can I reduce the size? I know nothing about SQL.


i found following info while trying to find a solution. Is the following ACCURATE and TRUE, as i would not want to Loose ANY DATA...

 

The templog.ldf file is the transaction log for the TEMPDB database, and if
it's 37GB, that's an indication something is not correctly configured in
your MSDE installation, or some major activity significantly expanded the
temp database, and it didn't get cleaned up properly.

Since the TEMPDB database is rebuilt everytime the system starts, or the
database server is restarted, I would suggest this remediation:

[1] Stop the "Update Services" service.
[2] Stop the MSSQL$WSUS service.
[3] Delete the tempdb.mdf and templog.ldf files.
[4] Reboot the server. If you cannot reboot the server, then
[a] Restart the MSSQL$WSUS service.
* Restart the "Update Services" service.

[5] Continue to monitor the size of the tempdb.mdf and templog.ldf files.


Everything you need for WSUS is at
http://technet2.microsoft.com/windowsserver/en/technologies/featured/wsus/default.mspx

And, almost everything else is at
http://wsusinfo.onsitechsolutions.com
0
Comment
Question by:gstevederby
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 39840153
Hi,

Do you have the SQL Server Management Studio for the SQL 2005?
If you don't, here it is...

http://www.microsoft.com/en-nz/download/details.aspx?id=8961

OK, once you have installed expand the nodes so you see your tempdb.
Right click....Reports....then look for Disk Usage.
Trans-log-TempDB-Pie-chart.docx
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39840159
tempdb is just that...temporary.  You wouldn't want to do anything destructive to it while the instance is running but if you stopped the SQL Server service and then deleted the temp .mdf and .ldf files you could restart the instance without any problems.  Tempdb should be in simple recovery mode which should prevent the file from getting too large.  I don't know how yours has managed to grow so out of control.

execute the following command to find out how much of the log file is actually being used:

EXECUTE ('DBCC SQLPERF (LOGSPACE)')
0
 

Author Comment

by:gstevederby
ID: 39840181
can you tell me the exact syntex to the EXECUTE ('DBCC SQLPERF (LOGSPACE)')  command, having trouble...and do i just run from cmd?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39840224
you would run it in a SSMS query.  download and install the 2005 SSMS client tools or you can even use the more current 2012 SSMS client tool which is backward compatible.

http://www.jasonstrate.com/2013/05/get-just-the-tools-ssms-download/

As to your original question.  I don't see anything damaging about the instructions you found.  Stopping the service, deleting the tempdb files, and restarting the service should alleviate your file size issues at least temporarily.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39840239
I've never seen a tempdb log size anywhere close to that.

Copy this code into a "New Query..." window in Mgmt Studio and run it:

DBCC SQLPERF (LOGSPACE) --1
DBCC OPENTRAN --2
SELECT * FROM sys.databases WHERE name = 'tempdb' --3

and report the results for:
% free in tempdb from the first command;
any open trans shown by the second command;
the entire output from the third command;
0
 

Author Comment

by:gstevederby
ID: 39840365
just rebooting now...does 2003sms r2 work for the SSMS query download and install?
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39840431
I think so, the sms version is effectively a Windows Standard Server 2003.

Failing that you could install the SSMS 2008 and connect "back" to your 2005 database.

http://www.microsoft.com/en-nz/download/details.aspx?id=22985

I use SSMS 2008 to interogate both by 2005 and 2008 SQL instances.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’m often asked about newer and larger USB drives connected to SBS2008 and 2011 failing Windows Server Backup vs the older USB drives not failing. As disk space continues to grow and drive technology change SBS2008 and some SBS2011 end up with the f…
You may have discovered the 'Compatibility View Settings' workaround for making your SBS 2008 Remote Web Workplace 'connect to a computer' section stops 'working around' after a Windows 10 client upgrade.  That can be fixed so it 'works around' agai…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now