dsolomon76
asked on
SQL log file grows too fast, kills server
Hello experts,
We have a Microsoft SQL 2005 server running on our web site. Our issue is the SQL log file keeps growing to capacity (15 GB) daily and completely killing the server.
We believe one process on the server is responsible, but aren't sure as to why. It's an Windows service that collects inventory data files and performs a number of SQL INSERTS and UPDATES every 30 minutes. It's our first project with Microsoft LINQ, if that makes any difference.
Right now we have a service running daily to backup and empty the SQL log file, but because it keeps growing faster and faster, we might have to start doing it in the middle of the day.
I have some experience with MySQL, and have never had a problem like this before. I'd be happy to provide any further information, as I know most of this information is quite vague.
Thanks.
We have a Microsoft SQL 2005 server running on our web site. Our issue is the SQL log file keeps growing to capacity (15 GB) daily and completely killing the server.
We believe one process on the server is responsible, but aren't sure as to why. It's an Windows service that collects inventory data files and performs a number of SQL INSERTS and UPDATES every 30 minutes. It's our first project with Microsoft LINQ, if that makes any difference.
Right now we have a service running daily to backup and empty the SQL log file, but because it keeps growing faster and faster, we might have to start doing it in the middle of the day.
I have some experience with MySQL, and have never had a problem like this before. I'd be happy to provide any further information, as I know most of this information is quite vague.
Thanks.
Last Comment
chapmandew
Are you doing log backups? If not, then you'll need to start.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>Are you doing log backups? If not, then you'll need to start.
same question I would ask.
alternative: IF YOU REALLY know that you don't need a tlog backup/restore, you can change the database to simple recovery mode.
note: the backup/recovery mode won't shrink the file. for that, you need the DBCC SHRINKFILE command.
same question I would ask.
alternative: IF YOU REALLY know that you don't need a tlog backup/restore, you can change the database to simple recovery mode.
note: the backup/recovery mode won't shrink the file. for that, you need the DBCC SHRINKFILE command.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yes, log backups are running nightly, but the log file is growing faster with the more inventory we have coming in.
Is this a common problem, or are we doing something completely wrong? Is there some process that we are executing, but not properly stopping or closing?
angellll: I'm curious about "Simple Recovery Mode." What is the disadvantage to using it? It doesn't make sense to me that a 39 MB database should have a transaction log of 15 GB. That's almost 400 times the size.
Is this a common problem, or are we doing something completely wrong? Is there some process that we are executing, but not properly stopping or closing?
angellll: I'm curious about "Simple Recovery Mode." What is the disadvantage to using it? It doesn't make sense to me that a 39 MB database should have a transaction log of 15 GB. That's almost 400 times the size.
Do them more often than nightly...should be doing them every 15-30 minutes.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Alright, it sounds like we'll try "Simple Recovery Mode," knowing the disadvantages of not being able to recover back to a specific point in time. I'll report back if there are any other problems. Much thanks!
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
