Maintenance plans and SQL Server 2005

Hi all,
Im currently investigating a SQL Server 2005 DB maintenence plan for my
company's software product.
I'm still learning this stuff so if I'd appreciate any comments on my
questions below :)
1) Assume the DB is using a FULL recovery model.
When I do a full backup of the database, will the transaction log be
truncated?
Or will it only be truncated when I perform a transaction log backup?
2) How often should transaction log backups be run? I'm thinking every
couple of hours... probably depends on the performance though (might
make it more frequent)
What is the performance hit when running a transaction log?
3) We currently do an index rebuild nightly. Would it be worth doing
this before a full backup (I assume that the rebuild would contribute
to the transaction log, so if the full backup clears these out then it
would be worth doing it before the backup? Or are there risks involved
with the rebuild so it is better practice to do it after the full
backup).
For one site, the db needs to be up 24/7.
I was thinking about running index rebuild in online mode for this, but
would this only work on Microsoft SQL Server 2005 Enterprise Edition?
BOL under "Guidelines for Performing Online Index Operations" says that
"Online index operations are available only in Microsoft SQL Server
2005 Enterprise Edition." so I'm thinking that this might not work'
4) We do not use auto update statistics, so run updates statistics
every night.
Since we "rebuild the indexes" in the maintenance plan prior to running
the update stats, is the update stats step necessary? I think I read
somewhere that rebuilding the indexes updates the statistics
automatically...
If we use INDEXDEFRAG instead, then we would need to run update stats
yeah? and if so, would it be best to run it before running indexdefrag
or after?
4) Just for your information, I am not going to shrink the log as from
what I've read this is not good for performance as the log file will no
doubt increase in size again (and it encourages fragmentation). But I'm
hoping that the combination of regular transaction log backups will
keep the log file at a consistent size.
I know there are a few questions in there, any comments would be really
appreciated!
Cheers!
Daviddavconts@.gmail.com wrote:
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
No, a full backup will not truncate the log file
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
The frequency depends on your tolerance for data loss. If you can
afford to lose 2 hours of work, then backup the log every 2 hours. If
you can only afford to lose 5 minutes of work, backup the log every 5
minutes.
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Consider only rebuilding those indexes that are badly fragmented. See
http://www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
You could use DBCC INDEXDEFRAG, or @.Mode = 'DEFRAG' if running the
script I linked to above.
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
I assume you have auto-stats turned off due to potential performance
problems? It's very rare to encounter such problems, I've never had
cause to turn off auto stats, even on OLTP databases approaching 200GB.
I'd turn them back on.
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
Correct, DBCC INDEXDEFRAG does not update stats. You'll need to
manually update them after defragging, or turn auto-stats on.
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Do not shrink the log file. Size it properly, do a thorough
defragmentation of the OS volume, you'll enjoy optimal performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||1) Only when a TLog backup is run. This topic is much more in depth than
meets the eye, make sure you read all about it in BOL before implementing in
production.
2) I don't think there is usually much performance hit. How often you want
to do it depends on how much data loss is acceptable.
3) IMO it's never a bad idea to have a good backup before doing stuff like
this. You may want to write TSQL code to switch the recovery model from Full
to Bulk Logged before doing your rebuilds. It will make it faster and your
TLogs wont grow as fast. Again, read BOL thoroughly before doing this. Every
night seems a bit excessive too me, but if thats what you need to do
depending on Inserts/ Deletes....
Looks like it would work on EE to me, but I'll let others comment.
4) It shouldn't be necessary.
I'll usually do update stats after a defrag, but I have no idea why?
4) You are correct.
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
NO. Only if you perform BACKUP LOG File
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
It depends on your business requirements . I do it every 15 minutes
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Just make sure that REBUILD INDEX command will not concur with a BACKUP
statement
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
REBUILD INDEX updates statistics
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
After INDEXDEFRAG
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Correct. Don't be afraid that the LOG file is huge ( I assume you perform
BACKUP LOG ) , that means SQL Server has enough room to perfom its work
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks for all the great replies above.. it has really clarified a lot
of the reading i've been doing to get up to speed on this! Really
appreciate it.
Thanks for the link too Tracy, that stored procedure looks interesting,
I'll give it a go.|||In addition to the other posts, check out the Books Online that comes with SQL Server 2005,
sys.dm_db_index_physical_stats. It has code you can use that depending on the fragmentation level
either does nothing, REBUILD or REORGANIZE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks Tibor
I tried that script in BOL as you mention above (with a USE dbname
clause at the top)
but I get an error message:
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '('.
>From what I can see it relates to the following line in the script:
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
'LIMITED')
Not sure why this is failing - this is a straight copy from BOL so am a
bit confused'|||Its ok I got it working...
I added:
DECLARE @.db_id smallint;
SET @.db_id = DB_ID(N'RMHCMS');
at the top and then changed the following from:
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
to:
sys.dm_db_index_physical_stats (@.db_id, NULL, NULL , NULL, 'LIMITED')
Cheers,
David
davconts@.gmail.com wrote:
> Thanks Tibor
> I tried that script in BOL as you mention above (with a USE dbname
> clause at the top)
> but I get an error message:
> Msg 102, Level 15, State 1, Line 24
> Incorrect syntax near '('.
> >From what I can see it relates to the following line in the script:
> FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
> 'LIMITED')
> Not sure why this is failing - this is a straight copy from BOL so am a
> bit confused'|||<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
Not really. But in any case, think of it as a trade-off.
If you do it say every 2 hours vs every 15 minutes.
The 2 hour version would have 8 times as much info to backup as the 15
minute one.
So if there WERE a performance issue, it would last 8 times as long.
Generally though plan them according to what your DR model requires.
For example our Dev databases we backup nightly and that's about it. It's
really not worth the trouble to do it more often.
Our production ones we do transaction log backups every 15 minutes and full
backups 3 nights a week.