Solved

templog.ldf and templog.mdb how do we limit these from growing ? - sbs 2003 server

Posted on 2013-06-18
18
876 Views
Last Modified: 2013-08-06
We have an sbs 2003 server in a working environrment - running exchange etc our free disc space gets swalloed up by these logs - we then have to reboot the server and then the log files clear and we have 10gb free on the c drive again !

How do we limit the mx file size of these logs so that we dont keep losing all our available disc space and therefore dont need to keep rebooting our server etc.

please advise?

could we stop the sql services completely? as far as we know backup exec and windows updates the only programs likely to be using this ?? anything that we can check to see why they are filling up so rapidly it grows 8 - 9gb in a week at the moment??

many thanks
0
Comment
Question by:phoenix81
  • 8
  • 6
  • 4
18 Comments
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 250 total points
Comment Utility
These look like they are TempDB which is why the disk space gets freed on reboot.  This database is recreated on SQL Server startup and you can limit them from growing past a certain point, but you should find out what is making them fill up if you want to solve the bigger problem.

But to keep them from growing past a certain point you can open Enterprise Manager (because I believe the SQL Server version is 2000 on SBS 2003) and right click on the Database TempDB (it is a system database) and look in the files and change the limit of growth to be a size that you want it to grow to.

Something like this if you want to do it in Query Analyzer:

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, MAXSIZE=5000MB) -- This is 5 GB
GO
ALTER DATABASE  tempdb
MODIFY FILE (NAME = templog, MAXSIZE=2000MB) -- This is 2 GB
GO
0
 

Author Comment

by:phoenix81
Comment Utility
thanks for this I will try this later today. How can we find out what is filling up the logs eg what database and what problem etc? We are not sql experts in anyway so idiots guide will be helpful :)
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
Because it is Tempdb, the tables and objects are going to be transient, so you could monitor the size or free space during the day and find out when things are going crazy and see what is running at the time.

But other than that, there are a few methods, but it is much more difficult in SQL 2000 and for non-DBAish people.

I will see if I can come up with a decent way to find out.
0
 

Author Comment

by:phoenix81
Comment Utility
I have just take a look on the server I cant find enterprise managed - we have a microsoft sql server 2005 - then only have configuration manager - (no studio express or anything?)

here is a little screenshot of what we have? please advise thanks again.

also would it be ok if we set the temp limits to say 500mb each or 1gb each ? We would like to keep them as small as possible?
sqlscreen1.jpg
sqlscreen2.jpg
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
This means that you have an instance of SQL Express on the machine and Backup Exec uses this to keep track of the backups it does.

So I am certainly not sure why the tempdb is growing large, but I would say that you could limit them to be that small.

In this case you could stop and start the SQL Services from the Configuration Manager if you needed to do something temporarily.

You can download SQL 2005 Management Studio Express and mange the files the way I said if you wanted to. Then they could not grow past what you set as the limit.
0
 

Author Comment

by:phoenix81
Comment Utility
ok I dont suppose you can point us in the right direction of the studio express download we need to download and install? We can plan to install this on the data partition which is larger so we dont affect the c drive space.

Also are you saying that if we restart the sql services this will clear the tempbd files therefore clearing the c space? if so this is a temporary solution at least!

thanks again
0
 

Author Comment

by:phoenix81
Comment Utility
I have just rebooted all 3 sql services and the middle sql instance eg "Windows internal database SSEE" must be the sql instance creating the problem as it has now freed up 10gb of space again!
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
Yes, I can see that. I had a hard time believing that the BACKUP EXEC instance was causing that kind of growth for the TempDB.

But yes, the restarting of that instance will free you up the space.  Once the space is allocated there really isn't a way to get it back unless you restart the services.

I will see if there is a way to fix this.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
Here is an article about what is installed on your server.

http://www.mssqltips.com/sqlservertip/1577/administering-your-windows-internal-database-microsoftssee-instance/

What I would recommend is to download the Management Studio tools and move the database files to a larger partition.

http://www.microsoft.com/en-us/download/details.aspx?id=7011
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:phoenix81
Comment Utility
ok thank you very much we shall await your reply you have been very helpful so far :)
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
10GB is not that large for Exchange support.  You really need to add more disk space: either another drive or increase the size of the existing drive.  It will take you a significant amount of time and expertise to pinpoint a tempdb issue.  The disk space will be a LOT cheaper and infinitely less frustrating.

After adding the space, you can still review the processes to see if there is a "runaway" task issue or something else that needs addressed.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
This is not Exchange though, it is WSUS.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
"We have an sbs 2003 server in a working environrment - running exchange etc"

In any event, 10GB nowadays is not a large amount of disk space; I wouldn't waste time trying to cut that tiny amount of disk vs other things one could be doing.
0
 

Author Comment

by:phoenix81
Comment Utility
I agree 10gb is not alot these days we inherited this server a few years ago the c partition is only 20gb and the d: data partition is 250gb exchange is installed o the bigger partition and we have recommended them replacing the server but thy are a charity and have no money at the moment so this is proving a difficult thanks again
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
You can either (1) move the tempdb file(s) to the D: drive (requires restarting SQL Server), or (2) add a log file on the D: drive to tempdb, which takes affect immediately w/o a restart.


--1) move tempdb file(s) to different drive, requires restart of SQL (does not
--have to be immediate, you can wait for normal restart if you want)

ALTER DATABASE tempdb MODIFY FILE ( NAME = templog,
    FILENAME = 'D:\desired\path\to\file\templog.ldf', MAXSIZE = 30GB )

--ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev,
    --FILENAME = 'D:\desired\path\to\file\tempdb.mdf', MAXSIZE = 30GB )


(2)
ALTER DATABASE tempdb
    ADD LOG FILE ( NAME = templog2, SIZE = 3GB, FILENAME =  'D:\desired\path\to\file\templog2.ldf', MAXSIZE = 30GB, FILEGROWTH = 40MB )
0
 

Author Comment

by:phoenix81
Comment Utility
Thanks Scott! Looks complicated :) the sql instance causing the logs is the windows sql logs built into sbs 2003 I believe ! Therefore is there any risk in moving them ? Also these commands where would I need to run them from ? I'm a sql novice remember :)
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If you have SSMS (SQL Server Management Studio) installed, you can run the commands from there.  Just click on "New Query", copy the commands in, change the file path(s) to what you want it to be, and run.  No other changes should be needed to the commands.
0
 

Author Closing Comment

by:phoenix81
Comment Utility
thank you
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

9 Experts available now in Live!

Get 1:1 Help Now