• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1069
  • Last Modified:

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

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
phoenix81
Asked:
phoenix81
  • 8
  • 6
  • 4
2 Solutions
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
phoenix81Author Commented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
phoenix81Author Commented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
phoenix81Author Commented:
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
 
phoenix81Author Commented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
phoenix81Author Commented:
ok thank you very much we shall await your reply you have been very helpful so far :)
0
 
Scott PletcherSenior DBACommented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
This is not Exchange though, it is WSUS.
0
 
Scott PletcherSenior DBACommented:
"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
 
phoenix81Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
phoenix81Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
phoenix81Author Commented:
thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now