Solved

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

Posted on 2013-06-18
18
917 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
ID: 39256617
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
ID: 39256682
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
ID: 39256693
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:phoenix81
ID: 39256806
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
ID: 39256824
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
ID: 39256864
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
ID: 39256888
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
ID: 39256915
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
ID: 39256935
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
 

Author Comment

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

Expert Comment

by:Scott Pletcher
ID: 39260127
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
ID: 39260133
This is not Exchange though, it is WSUS.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39260329
"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
ID: 39260648
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:
Scott Pletcher earned 250 total points
ID: 39260778
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
ID: 39260862
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:Scott Pletcher
ID: 39260901
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
ID: 39385506
thank you
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

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…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

830 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