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)
master NOTHING
tempdb ACTIVE_TRANSACTION
model LOG_BACKUP
msdb NOTHING
ReportServer NOTHING
ReportServerTempDB NOTHING
InfinityQS LOG_BACKUP
ActivplantDB LOG_BACKUP
vRangerPro NOTHING
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.
ActivplantDB ActivplantDB_Log 57 2613000 2012-07-15 04:37:01.497 2012-07-15 04:37:04.110 Log
InfinityQS InfinityQS_Data 65 20000 2012-07-15 03:07:03.667 2012-07-15 03:07:03.687 Data
ActivplantDB ActivplantDB_Log 77 2113000 2012-07-15 03:06:27.590 2012-07-15 03:06:29.703 Log
ActivplantDB ActivplantDB_Log 77 2103000 2012-07-15 03:05:05.537 2012-07-15 03:05:07.640 Log
ActivplantDB ActivplantDB_Log 77 2143000 2012-07-15 03:04:33.510 2012-07-15 03:04:35.653 Log
ActivplantDB ActivplantDB_Log 77 2156000 2012-07-15 03:03:56.730 2012-07-15 03:03:58.887 Log
ActivplantDB ActivplantDB_Log 77 2123000 2012-07-15 03:03:26.450 2012-07-15 03:03:28.573 Log
ActivplantDB ActivplantDB_Log 77 2076000 2012-07-15 03:02:47.090 2012-07-15 03:02:49.167 Log
ActivplantDB ActivplantDB_Log 77 2093000 2012-07-15 03:02:14.900 2012-07-15 03:02:16.993 Log
ActivplantDB ActivplantDB_Log 77 2173000 2012-07-15 03:01:41.990 2012-07-15 03:01:44.163 Log
ActivplantDB ActivplantDB_Log 77 2216000 2012-07-15 03:01:09.400 2012-07-15 03:01:11.617 Log
ActivplantDB ActivplantDB_Log 77 2263000 2012-07-15 03:00:34.743 2012-07-15 03:00:37.007 Log
ActivplantDB ActivplantDB_Log 77 2100000 2012-07-15 03:00:02.610 2012-07-15 03:00:04.710 Log
ActivplantDB ActivplantDB_Log 77 2050000 2012-07-15 02:59:29.383 2012-07-15 02:59:31.433 Log
ActivplantDB ActivplantDB_Log 77 2113000 2012-07-15 02:58:57.050 2012-07-15 02:58:59.163 Log
ActivplantDB ActivplantDB_Log 77 2063000 2012-07-15 02:58:23.783 2012-07-15 02:58:25.847 Log
ActivplantDB ActivplantDB_Log 77 2103000 2012-07-15 02:57:52.220 2012-07-15 02:57:54.323 Log
ActivplantDB ActivplantDB_Log 77 2066000 2012-07-15 02:57:19.997 2012-07-15 02:57:22.063 Log
ActivplantDB ActivplantDB_Log 77 5400000 2012-07-15 02:56:46.123 2012-07-15 02:56:51.523 Log
ActivplantDB ActivplantDB_Log 77 2090000 2012-07-15 02:56:13.207 2012-07-15 02:56:15.297 Log
ActivplantDB ActivplantDB_Log 77 2730000 2012-07-15 02:55:43.020 2012-07-15 02:55:45.750 Log
ActivplantDB ActivplantDB_Log 77 2253000 2012-07-15 02:55:08.697 2012-07-15 02:55:10.950 Log
ActivplantDB ActivplantDB_Log 77 2146000 2012-07-15 02:54:39.087 2012-07-15 02:54:41.233 Log
ActivplantDB ActivplantDB_Log 77 2233000 2012-07-15 02:54:02.653 2012-07-15 02:54:04.887 Log
ActivplantDB ActivplantDB_Log 77 2123000 2012-07-15 02:53:33.233 2012-07-15 02:53:35.357 Log
ActivplantDB ActivplantDB_Log 77 2070000 2012-07-15 02:52:58.993 2012-07-15 02:53:01.063 Log
ActivplantDB ActivplantDB_Log 77 2123000 2012-07-15 02:52:29.823 2012-07-15 02:52:31.947 Log
ActivplantDB ActivplantDB_Log 77 2196000 2012-07-15 02:51:55.123 2012-07-15 02:51:57.320 Log
ActivplantDB ActivplantDB_Log 77 2083000 2012-07-15 02:51:25.660 2012-07-15 02:51:27.743 Log
ActivplantDB ActivplantDB_Log 77 2056000 2012-07-15 02:50:52.013 2012-07-15 02:50:54.070 Log
ActivplantDB ActivplantDB_Log 77 2066000 2012-07-15 02:50:22.440 2012-07-15 02:50:24.507 Log
ActivplantDB ActivplantDB_Log 77 2100000 2012-07-15 02:49:43.860 2012-07-15 02:49:45.960 Log
ActivplantDB ActivplantDB_Log 77 2086000 2012-07-15 02:49:14.200 2012-07-15 02:49:16.287 Log
ActivplantDB ActivplantDB_Log 77 2096000 2012-07-15 02:48:39.527 2012-07-15 02:48:41.623 Log
ActivplantDB ActivplantDB_Log 77 2053000 2012-07-15 02:48:09.857 2012-07-15 02:48:11.910 Log
ActivplantDB ActivplantDB_Log 77 2136000 2012-07-15 02:47:35.267 2012-07-15 02:47:37.403 Log
ActivplantDB ActivplantDB_Log 77 2093000 2012-07-15 02:47:05.830 2012-07-15 02:47:07.923 Log
ActivplantDB ActivplantDB_Log 77 2100000 2012-07-15 02:46:31.590 2012-07-15 02:46:33.690 Log
ActivplantDB ActivplantDB_Log 77 2256000 2012-07-15 02:46:00.807 2012-07-15 02:46:03.063 Log
ActivplantDB ActivplantDB_Log 77 2020000 2012-07-15 02:45:26.150 2012-07-15 02:45:28.170 Log
ActivplantDB ActivplantDB_Log 77 2113000 2012-07-15 02:44:56.277 2012-07-15 02:44:58.390 Log
ActivplantDB ActivplantDB_Log 77 1970000 2012-07-15 02:44:22.310 2012-07-15 02:44:24.280 Log
ActivplantDB ActivplantDB_Log 77 2013000 2012-07-15 02:43:52.280 2012-07-15 02:43:54.293 Log
ActivplantDB ActivplantDB_Log 77 2063000 2012-07-15 02:43:18.767 2012-07-15 02:43:20.830 Log
ActivplantDB ActivplantDB_Log 77 2076000 2012-07-15 02:42:49.153 2012-07-15 02:42:51.230 Log
ActivplantDB ActivplantDB_Log 77 2143000 2012-07-15 02:42:11.820 2012-07-15 02:42:13.963 Log
ActivplantDB ActivplantDB_Log 77 2120000 2012-07-15 02:41:41.750 2012-07-15 02:41:43.870 Log
ActivplantDB ActivplantDB_Log 77 2080000 2012-07-15 02:41:08.710 2012-07-15 02:41:10.790 Log
ActivplantDB ActivplantDB_Log 77 2163000 2012-07-15 02:40:37.530 2012-07-15 02:40:39.693 Log
ActivplantDB ActivplantDB_Log 77 2106000 2012-07-15 02:40:04.197 2012-07-15 02:40:06.303 Log
ActivplantDB ActivplantDB_Log 77 1980000 2012-07-15 02:39:33.197 2012-07-15 02:39:35.177 Log
ActivplantDB ActivplantDB_Log 77 1940000 2012-07-15 02:38:59.990 2012-07-15 02:39:01.930 Log
ActivplantDB ActivplantDB_Log 77 2243000 2012-07-15 02:38:28.923 2012-07-15 02:38:31.167 Log
ActivplantDB ActivplantDB_Log 77 2093000 2012-07-15 02:37:55.567 2012-07-15 02:37:57.660 Log
ActivplantDB ActivplantDB_Log 77 2093000 2012-07-15 02:37:24.347 2012-07-15 02:37:26.440 Log
ActivplantDB ActivplantDB_Log 77 2160000 2012-07-15 02:36:50.920 2012-07-15 02:36:53.080 Log
ActivplantDB ActivplantDB_Log 77 2033000 2012-07-15 02:36:19.880 2012-07-15 02:36:21.913 Log
ActivplantDB ActivplantDB_Log 77 2143000 2012-07-15 02:35:44.293 2012-07-15 02:35:46.437 Log
ActivplantDB ActivplantDB_Log 77 2016000 2012-07-15 02:35:12.060 2012-07-15 02:35:14.077 Log
ActivplantDB ActivplantDB_Log 77 4723000 2012-07-15 02:34:42.247 2012-07-15 02:34:46.970 Log
ActivplantDB ActivplantDB_Log 77 2103000 2012-07-15 02:34:12.287 2012-07-15 02:34:14.390 Log
ActivplantDB ActivplantDB_Log 77 2056000 2012-07-15 02:33:46.690 2012-07-15 02:33:48.747 Log
ActivplantDB ActivplantDB_Log 77 2100000 2012-07-15 02:33:14.507 2012-07-15 02:33:16.607 Log
ActivplantDB ActivplantDB_Log 77 1946000 2012-07-15 02:32:47.597 2012-07-15 02:32:49.543 Log
ActivplantDB ActivplantDB_Log 77 2183000 2012-07-15 02:32:12.060 2012-07-15 02:32:14.243 Log
ActivplantDB ActivplantDB_Log 77 1690000 2012-07-15 02:31:46.053 2012-07-15 02:31:47.743 Log
ActivplantDB ActivplantDB_Log 96 1920000 2012-07-14 02:00:03.723 2012-07-14 02:00:05.643 Log
ActivplantDB ActivplantDB_Log 57 2556000 2012-07-12 10:10:01.630 2012-07-12 10:10:04.187 Log
ActivplantDB ActivplantDB_Log 58 3436000 2012-07-10 09:52:31.153 2012-07-10 09:52:34.590 Log
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