IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.
Course Of The Month
6 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
templog.ldf size too large
Posted on 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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2-
2
- +1
7 Comments
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
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
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)')
execute the following command to find out how much of the log file is actually being used:
EXECUTE ('DBCC SQLPERF (LOGSPACE)')
can you tell me the exact syntex to the EXECUTE ('DBCC SQLPERF (LOGSPACE)') command, having trouble...and do i just run from cmd?
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.
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.
Active 1 day ago
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;
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;
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.
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.
Featured Post
Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month6 days, 9 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.