July 16, 2012 at 1:26 pm
I assume you have autogrow enabled on your log file. Check the default trace to see when it grew, that can help establishing a timeframe of when the log bloat started, and hopefully mostly occurred:
Reviewing AutoGrow events from the default trace by Aaron Bertrand
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2012 at 1:48 pm
Lynn Pettis (7/16/2012)
lawson2305 (7/16/2012)
masterNOTHING
tempdbACTIVE_TRANSACTION
modelLOG_BACKUP
msdbNOTHING
ReportServerNOTHING
ReportServerTempDBNOTHING
InfinityQSLOG_BACKUP
ActivplantDBLOG_BACKUP
vRangerProNOTHING
activplantdb is the one in question.
July 16, 2012 at 1:50 pm
Looks to me that you have an active VLF near the end of your t-log file.
July 16, 2012 at 1:58 pm
opc.three (7/16/2012)
I assume you have autogrow enabled on your log file. Check the default trace to see when it grew, that can help establishing a timeframe of when the log bloat started, and hopefully mostly occurred:Reviewing AutoGrow events from the default trace by Aaron Bertrand
ok from this script I do see a pattern.
I'm guessing this script tells me everytime the file grows.
ActivplantDBActivplantDB_Log5726130002012-07-15 04:37:01.4972012-07-15 04:37:04.110Log
InfinityQSInfinityQS_Data65200002012-07-15 03:07:03.6672012-07-15 03:07:03.687Data
ActivplantDBActivplantDB_Log7721130002012-07-15 03:06:27.5902012-07-15 03:06:29.703Log
ActivplantDBActivplantDB_Log7721030002012-07-15 03:05:05.5372012-07-15 03:05:07.640Log
ActivplantDBActivplantDB_Log7721430002012-07-15 03:04:33.5102012-07-15 03:04:35.653Log
ActivplantDBActivplantDB_Log7721560002012-07-15 03:03:56.7302012-07-15 03:03:58.887Log
ActivplantDBActivplantDB_Log7721230002012-07-15 03:03:26.4502012-07-15 03:03:28.573Log
ActivplantDBActivplantDB_Log7720760002012-07-15 03:02:47.0902012-07-15 03:02:49.167Log
ActivplantDBActivplantDB_Log7720930002012-07-15 03:02:14.9002012-07-15 03:02:16.993Log
ActivplantDBActivplantDB_Log7721730002012-07-15 03:01:41.9902012-07-15 03:01:44.163Log
ActivplantDBActivplantDB_Log7722160002012-07-15 03:01:09.4002012-07-15 03:01:11.617Log
ActivplantDBActivplantDB_Log7722630002012-07-15 03:00:34.7432012-07-15 03:00:37.007Log
ActivplantDBActivplantDB_Log7721000002012-07-15 03:00:02.6102012-07-15 03:00:04.710Log
ActivplantDBActivplantDB_Log7720500002012-07-15 02:59:29.3832012-07-15 02:59:31.433Log
ActivplantDBActivplantDB_Log7721130002012-07-15 02:58:57.0502012-07-15 02:58:59.163Log
ActivplantDBActivplantDB_Log7720630002012-07-15 02:58:23.7832012-07-15 02:58:25.847Log
ActivplantDBActivplantDB_Log7721030002012-07-15 02:57:52.2202012-07-15 02:57:54.323Log
ActivplantDBActivplantDB_Log7720660002012-07-15 02:57:19.9972012-07-15 02:57:22.063Log
ActivplantDBActivplantDB_Log7754000002012-07-15 02:56:46.1232012-07-15 02:56:51.523Log
ActivplantDBActivplantDB_Log7720900002012-07-15 02:56:13.2072012-07-15 02:56:15.297Log
ActivplantDBActivplantDB_Log7727300002012-07-15 02:55:43.0202012-07-15 02:55:45.750Log
ActivplantDBActivplantDB_Log7722530002012-07-15 02:55:08.6972012-07-15 02:55:10.950Log
ActivplantDBActivplantDB_Log7721460002012-07-15 02:54:39.0872012-07-15 02:54:41.233Log
ActivplantDBActivplantDB_Log7722330002012-07-15 02:54:02.6532012-07-15 02:54:04.887Log
ActivplantDBActivplantDB_Log7721230002012-07-15 02:53:33.2332012-07-15 02:53:35.357Log
ActivplantDBActivplantDB_Log7720700002012-07-15 02:52:58.9932012-07-15 02:53:01.063Log
ActivplantDBActivplantDB_Log7721230002012-07-15 02:52:29.8232012-07-15 02:52:31.947Log
ActivplantDBActivplantDB_Log7721960002012-07-15 02:51:55.1232012-07-15 02:51:57.320Log
ActivplantDBActivplantDB_Log7720830002012-07-15 02:51:25.6602012-07-15 02:51:27.743Log
ActivplantDBActivplantDB_Log7720560002012-07-15 02:50:52.0132012-07-15 02:50:54.070Log
ActivplantDBActivplantDB_Log7720660002012-07-15 02:50:22.4402012-07-15 02:50:24.507Log
ActivplantDBActivplantDB_Log7721000002012-07-15 02:49:43.8602012-07-15 02:49:45.960Log
ActivplantDBActivplantDB_Log7720860002012-07-15 02:49:14.2002012-07-15 02:49:16.287Log
ActivplantDBActivplantDB_Log7720960002012-07-15 02:48:39.5272012-07-15 02:48:41.623Log
ActivplantDBActivplantDB_Log7720530002012-07-15 02:48:09.8572012-07-15 02:48:11.910Log
ActivplantDBActivplantDB_Log7721360002012-07-15 02:47:35.2672012-07-15 02:47:37.403Log
ActivplantDBActivplantDB_Log7720930002012-07-15 02:47:05.8302012-07-15 02:47:07.923Log
ActivplantDBActivplantDB_Log7721000002012-07-15 02:46:31.5902012-07-15 02:46:33.690Log
ActivplantDBActivplantDB_Log7722560002012-07-15 02:46:00.8072012-07-15 02:46:03.063Log
ActivplantDBActivplantDB_Log7720200002012-07-15 02:45:26.1502012-07-15 02:45:28.170Log
ActivplantDBActivplantDB_Log7721130002012-07-15 02:44:56.2772012-07-15 02:44:58.390Log
ActivplantDBActivplantDB_Log7719700002012-07-15 02:44:22.3102012-07-15 02:44:24.280Log
ActivplantDBActivplantDB_Log7720130002012-07-15 02:43:52.2802012-07-15 02:43:54.293Log
ActivplantDBActivplantDB_Log7720630002012-07-15 02:43:18.7672012-07-15 02:43:20.830Log
ActivplantDBActivplantDB_Log7720760002012-07-15 02:42:49.1532012-07-15 02:42:51.230Log
ActivplantDBActivplantDB_Log7721430002012-07-15 02:42:11.8202012-07-15 02:42:13.963Log
ActivplantDBActivplantDB_Log7721200002012-07-15 02:41:41.7502012-07-15 02:41:43.870Log
ActivplantDBActivplantDB_Log7720800002012-07-15 02:41:08.7102012-07-15 02:41:10.790Log
ActivplantDBActivplantDB_Log7721630002012-07-15 02:40:37.5302012-07-15 02:40:39.693Log
ActivplantDBActivplantDB_Log7721060002012-07-15 02:40:04.1972012-07-15 02:40:06.303Log
ActivplantDBActivplantDB_Log7719800002012-07-15 02:39:33.1972012-07-15 02:39:35.177Log
ActivplantDBActivplantDB_Log7719400002012-07-15 02:38:59.9902012-07-15 02:39:01.930Log
ActivplantDBActivplantDB_Log7722430002012-07-15 02:38:28.9232012-07-15 02:38:31.167Log
ActivplantDBActivplantDB_Log7720930002012-07-15 02:37:55.5672012-07-15 02:37:57.660Log
ActivplantDBActivplantDB_Log7720930002012-07-15 02:37:24.3472012-07-15 02:37:26.440Log
ActivplantDBActivplantDB_Log7721600002012-07-15 02:36:50.9202012-07-15 02:36:53.080Log
ActivplantDBActivplantDB_Log7720330002012-07-15 02:36:19.8802012-07-15 02:36:21.913Log
ActivplantDBActivplantDB_Log7721430002012-07-15 02:35:44.2932012-07-15 02:35:46.437Log
ActivplantDBActivplantDB_Log7720160002012-07-15 02:35:12.0602012-07-15 02:35:14.077Log
ActivplantDBActivplantDB_Log7747230002012-07-15 02:34:42.2472012-07-15 02:34:46.970Log
ActivplantDBActivplantDB_Log7721030002012-07-15 02:34:12.2872012-07-15 02:34:14.390Log
ActivplantDBActivplantDB_Log7720560002012-07-15 02:33:46.6902012-07-15 02:33:48.747Log
ActivplantDBActivplantDB_Log7721000002012-07-15 02:33:14.5072012-07-15 02:33:16.607Log
ActivplantDBActivplantDB_Log7719460002012-07-15 02:32:47.5972012-07-15 02:32:49.543Log
ActivplantDBActivplantDB_Log7721830002012-07-15 02:32:12.0602012-07-15 02:32:14.243Log
ActivplantDBActivplantDB_Log7716900002012-07-15 02:31:46.0532012-07-15 02:31:47.743Log
ActivplantDBActivplantDB_Log9619200002012-07-14 02:00:03.7232012-07-14 02:00:05.643Log
ActivplantDBActivplantDB_Log5725560002012-07-12 10:10:01.6302012-07-12 10:10:04.187Log
ActivplantDBActivplantDB_Log5834360002012-07-10 09:52:31.1532012-07-10 09:52:34.590Log
I'm guessing what was running at process 77 is my issue. I do run maintenance task at this time I will review the history.
July 16, 2012 at 2:13 pm
lawson2305 (7/16/2012)
opc.three (7/16/2012)
I assume you have autogrow enabled on your log file. Check the default trace to see when it grew, that can help establishing a timeframe of when the log bloat started, and hopefully mostly occurred:Reviewing AutoGrow events from the default trace by Aaron Bertrand
ok from this script I do see a pattern.
I'm guessing this script tells me everytime the file grows.
<truncated content>
I'm guessing what was running at process 77 is my issue. I do run maintenance task at this time I will review the history.
The default trace records each time a file autogrow operation. The default trace is always running by default, but rotates through a fixed number of files so older events will eventually fall off the set of files.
I am happy you caught the pattern. If you need point-in-time recovery there isn't much you can do in terms of reducing the total size of your log backups, but you can manage the size of each one by taking them more frequently while you're doing index maintenance.
Is your 'maintenance task' running a SQL Server Maintenance Plan (MP) doing index rebuilds? One thing I see a lot is people rebuilding ALL their indexes at one time using a MP which is wasteful because it rebuild all indexes regardless of whether they need it or not. This practice is a common source of log bloat.
If you're using an MP consider checking into an index maintenance solution that only rebuilds or reorganizes indexes based on their level of fragmentation, i.e. only when they need it. Here is one I use and recommend often:
SQL Server Index and Statistics Maintenance by Ola Hallengren
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2012 at 2:20 pm
I do have a full maintenance plan run every Sunday. So I retract my statement before I run a log backup appended everyday but Sunday 2AM.
in the plan I:
Check DB integrity
Backup DB
Backup Log
Rebuild Index - reorganize pages with default amount of free space and sort results in a tempdb
update statistics
shrink DB
July 16, 2012 at 2:23 pm
lawson2305 (7/16/2012)
I do have a full maintenance plan run every Sunday. So I retract my statement before I run a log backup appended everyday but Sunday 2AM.in the plan I:
Check DB integrity
Backup DB
Backup Log
Rebuild Index - reorganize pages with default amount of free space and sort results in a tempdb
update statistics
shrink DB
Shrinking the database after completing the rebuild index undoes the rebuild index.
July 16, 2012 at 2:25 pm
In fact, you should not be shrinking your database on a regular basis. Should you determine how much space your databse requires over the next 3 to 6 months and size it appropriately. Then, as you approach the 3 to 6 month window, you should evaluate again and add enough space to allow your database to grow over the next 3 to 6 months.
July 16, 2012 at 2:37 pm
lawson2305 (7/16/2012)
I do have a full maintenance plan run every Sunday. So I retract my statement before I run a log backup appended everyday but Sunday 2AM.in the plan I:
Check DB integrity
Backup DB
Backup Log
Rebuild Index - reorganize pages with default amount of free space and sort results in a tempdb
update statistics
shrink DB
Are you shrinking the data files too, or only the log file?
At any rate, as mentioned before, shrinking log or data files is futile unless you know what's making them grow.
Please read this and all linked articles. It's a great information portal on this topic. Just take the rant-like tone with a grain of salt, he knows what he is talking about:
Stop Shrinking Your Database Files. Seriously. Now.[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2012 at 2:40 pm
this maintenance.sql what does it do?
I'm a DB admin that only looks when required. My main job is everything else in the company from net admin, server admin, exchange and so on...
So I don't completely understand what this sql script does. We are in the process of having the application archive the data in the db to reduce the overall size.
I have skrinks put in because we don't have all the space in the world. I don't want a file growing to 20GB because of something done but then after a backup the file really is sitting around 1GB all the time but because of that special time the file grew out of this world.
July 16, 2012 at 2:43 pm
it is the standard shrink command it does not give me the option to define what I shrink from what I can tell. This is the code it runs.
USE [ActivplantDB]
GO
DBCC SHRINKDATABASE(N'ActivplantDB', 10, TRUNCATEONLY)
GO
USE [InfinityQS]
GO
DBCC SHRINKDATABASE(N'InfinityQS', 10, TRUNCATEONLY)
July 16, 2012 at 2:49 pm
lawson2305 (7/16/2012)
this maintenance.sql what does it do?
You do not have to install the Maintenance package he provides but it is turnkey so if you're having trouble with backups and checkdb consider it. The Index Maintenance portion (the direct link in my earlier post) examines the fragmentation level of indexes and rebuilds or reorganizes it only if it is above some threshold. The built in MP task rebuilds everything regardless of the level of fragmentation. The chances are extremely good that implementing Ola's Index Maintenance solution will significantly reduce the amount of rebuilds you do, likely resolving a lot of your log bloat and log backup size issues.
I'm a DB admin that only looks when required. My main job is everything else in the company from net admin, server admin, exchange and so on...
Understood. I am showing you a common solution to a common problem. It only requires a bit of your time to dedicate to it, otherwise you'll be chasing this issue indefinitely.
So I don't completely understand what this sql script does. We are in the process of having the application archive the data in the db to reduce the overall size.
I have skrinks put in because we don't have all the space in the world. I don't want a file growing to 20GB because of something done but then after a backup the file really is sitting around 1GB all the time but because of that special time the file grew out of this world.
If you want to get beyond this issue read the article in my last post and consider spending a couple hours implementing Ola's solution, that's all it should take if you dedicate some time to it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2012 at 2:51 pm
lawson2305 (7/16/2012)
it is the standard shrink command it does not give me the option to define what I shrink from what I can tell. This is the code it runs.USE [ActivplantDB]
GO
DBCC SHRINKDATABASE(N'ActivplantDB', 10, TRUNCATEONLY)
GO
USE [InfinityQS]
GO
DBCC SHRINKDATABASE(N'InfinityQS', 10, TRUNCATEONLY)
At the very least, until you decide what to do long term, stop shrinking your data files. Use DBCC SHRINKFILE on your log file only:
DBCC SHRINKFILE (SQL Server 2005)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2012 at 2:52 pm
I changed the daily code to run:
USE [ActivplantDB]
GO
DBCC SHRINKFILE (N'ActivplantDB_Log' , 0, TRUNCATEONLY)
GO
July 16, 2012 at 2:58 pm
lawson2305 (7/16/2012)
I changed the daily code to run:USE [ActivplantDB]
GO
DBCC SHRINKFILE (N'ActivplantDB_Log' , 0, TRUNCATEONLY)
GO
Shrinking to 0 is not a good approach no matter what your situation. Growing a log file is expensive, very expensive as a matter of fact, so shrink it to a size that is as large as you can tolerate. You know it will continue to grow again so shrinking it to 0 is asking for more activity on your server. If you can tolerate 50GB, shrink to 50GB. If only 3GB, then shrink to 3GB.
edit: punctuation
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 30 (of 61 total)
You must be logged in to reply to this topic. Login to reply