January 5, 2010 at 2:35 pm
We have a GIS system based in SQL Server 2005. The log file from one of the DB's keeps filling up and we cannot figure out what is causing it. It usually happens when no editing is going on. When edits are made to the data the changes are made permanant when you compress the data in the GIS software. The only one who can make edits to the data is me. The log is sometimes filling up on the weekend or when I was at training for a week. I have a shrink database in the maintenance package. Not sure it is doing any good. We recently upgraded from sql server 2005 express to standard and moved the DB's to the instance. We didn't have any trouble before that with log files. The DB that was originally created allowed for unlimited growth but I changed that when we filled up the hard drive. I have auto growth enabled and set to 10% with a max size of 6GB.
Thanks for the help
Quinn
January 5, 2010 at 2:40 pm
Quinn-793124 (1/5/2010)
We have a GIS system based in SQL Server 2005. The log file from one of the DB's keeps filling up and we cannot figure out what is causing it. It usually happens when no editing is going on. When edits are made to the data the changes are made permanant when you compress the data in the GIS software. The only one who can make edits to the data is me. The log is sometimes filling up on the weekend or when I was at training for a week. I have a shrink database in the maintenance package. Not sure it is doing any good. We recently upgraded from sql server 2005 express to standard and moved the DB's to the instance. We didn't have any trouble before that with log files. The DB that was originally created allowed for unlimited growth but I changed that when we filled up the hard drive. I have auto growth enabled and set to 10% with a max size of 6GB.Thanks for the help
Quinn
What is the current size of the log file?
Disk Size?
What is the recovery model of the database (simple full bulk-logged)?
What is your backup plan for the transaction logs?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 5, 2010 at 2:41 pm
Hi,
I would turn off 6 gb limit since 1 day it might crash horribly.work on a new process to assess the growth.10% in my opinion should be changed to size in mb,probably 25mb would do.what's the size of the database?
Instead of shrinking it,change recovery mode to full and take log backup.
Hope this helps
January 5, 2010 at 2:46 pm
c00ler01 (1/5/2010)
Hi,I would turn off 6 gb limit since 1 day it might crash horribly.work on a new process to assess the growth.10% in my opinion should be changed to size in mb,probably 25mb would do.what's the size of the database?
Instead of shrinking it,change recovery mode to full and take log backup.
Hope this helps
There is some useful suggestions in this one.
Don't shrink the Log file - rather find an appropriate size for it.
Change the AutoGrowth to MB rather than % (better control down the road) - though the growth should be much larger than 25MB. A small growth pattern will cause heavy VLF fragmentation and degraded performance.
I think the 6GB limit will cause the same sort of issue as you are currently seeing - but at least you can catch it more quickly.
Additional suggestion - try setting up a monitoring solution to monitor for filegrowths - when you see a growth, immediately investigate the system to see what is going on (sp_who2, trace, profiler - typical dba stuff).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 5, 2010 at 2:52 pm
Hi again,yes - 25mb is a very rough estimate.my assumption is that your database is below 500mb in size.I have seen aspstate database with 8mb Mdf file and 20gb Ldf file,log file hasn't been backed up over a month.
Good luck
January 5, 2010 at 3:01 pm
The size of the DB is 879000Kb and the log file is 53000Kb.
If I do a
DBCC SQLPERF(LOGSPACE)
It is at 5.2% full right now. The recovery model is full. Here is the code from the maintenance package.
USE [koochvector]
GO
DBCC SHRINKDATABASE(N'koochvector', 5, TRUNCATEONLY)
GO
Thanks Much.
Quinn
January 5, 2010 at 3:13 pm
How often are you doing log backups?
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2010 at 3:32 pm
With the recovery model set to full, you should consider performing Log backups (15min, 30 min or some other interval).
Or, if there is no need to be able to perform a point in time recovery, change the recovery model to simple. However, make certain that there will be no requirement for point in time recovery.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 5, 2010 at 3:38 pm
Since this is SQL 2005, what's the value of log_reuse_wait_desc in sys.databases?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2010 at 3:42 pm
We do back ups every night.
How do you make a log file back up?
The size of the hard drive is 366GB.
That looks like a good article Gail. I will read it over tonight.
Quinn
January 5, 2010 at 3:45 pm
Quinn-793124 (1/5/2010)
We do back ups every night.
Didn't ask about full backups. They do not truncate the log
How do you make a log file back up?
Look up BACKUP LOG in the SQL books Online
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2010 at 3:46 pm
I took a look at that earlier today.
Here is the result.
select name, log_reuse_wait, log_reuse_wait_desc from sys.databases
master4ACTIVE_TRANSACTION
tempdb0NOTHING
model2LOG_BACKUP
msdb0NOTHING
AdventureWorks1CHECKPOINT
ReportServer2LOG_BACKUP
ReportServerTempDB2LOG_BACKUP
AdventureWorksDW0NOTHING
KoochData0NOTHING
TestingFromRestoreSSExpr0NOTHING
KoochVector0NOTHING
KoochRaster0NOTHING
ResTest2LOG_BACKUP
I am not sure if 0 on koochVector is a bad thing or if another number would be better.
Thanks.
Q
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply