June 24, 2009 at 10:27 pm
hi all,
i'm preparing the job for our databses in production.
we have job plan like every day full backup, every one hour transactional log backups.
what i'm following is
in the full backup job:
1. CHECKPOINT
2. TRUNCATE ONLY LOG
3. SHRINKING LOG FILE
4. FULL BACKUP.
IS THERE ANY PROBLEM WITH THIS,
PLEASE HELP ME.
🙂
June 24, 2009 at 10:48 pm
vrabhadram (6/24/2009)
hi all,i'm preparing the job for our databses in production.
we have job plan like every day full backup, every one hour transactional log backups.
what i'm following is
in the full backup job:
1. CHECKPOINT
2. TRUNCATE ONLY LOG
3. SHRINKING LOG FILE
4. FULL BACKUP.
IS THERE ANY PROBLEM WITH THIS,
PLEASE HELP ME.
you dont need to perform step 3 on a daily basis. You'll face lots of permonance issues because of this.
you also dont need step 1 and 2.
Full backup automatically fires checkpoint before actual backup starts.
Also why do you want to truncate log file?
You hourly transaction log backups will keep on doing this automatically..
You just need step 4 for your daily Full backup and another job for your hourly transaction log backups.
June 24, 2009 at 10:50 pm
G'day Badra.
You should omit the log truncate and log shrink steps.
If the system falls over during your full backup but you've truncated the log you will not be able to recover any transactions that occurred since the log truncation. There may be theoretically be no users using the system then, but you'd have to go to a bit of trouble to guarantee that, but for no real purpose because SQL Server is designed to do online backups and maintain transaction integrity during backups.
There should never be a need to shrink the transaction log, except maybe after some out-of-the-ordinary task (eg. a one-off load of lots of data). The log file will grow to as large as it needs to be, eg. during a reindex, and if you shrink it it's just going to grow again the next time that task runs. It's much better to size it as large as it is expected to be (but with scope for additional growth) and leave it at that size (the same applies to all database files).
The checkpoint step is unnecessary and I personally wouldn't include it but it won't cause any issues if it stays there.
June 25, 2009 at 12:12 am
You hourly transaction log backups will keep on doing this automatically..
i have checked with the log file size after transaction log backup, but it's not decreased.After shrinking the database also, size was not decreased.
🙂
June 25, 2009 at 12:19 am
vrabhadram (6/25/2009)
i have checked with the log file size after transaction log backup, but it's not decreased.After shrinking the database also, size was not decreased.
You're right. Transaction log file's physical size will not decrease after log backup(shrinking log files does). Instead, space occupied by committed transactions are marked for reuse in future. You must be knowing the transaction log's space requirement for logging 1 hours worth of transaction. So why shrink it? if the log file's size is very small, everytime there is a space requirement, SQL server will ask underlying operating system for more space which the OS allots. This is a time consuming process and will affect performance of your system.
I suggest yo go through this article by Gail on Managing Transaction Logs.
http://www.sqlservercentral.com/articles/64582/
June 25, 2009 at 2:23 am
vrabhadram (6/24/2009)
IS THERE ANY PROBLEM WITH THIS,PLEASE HELP ME.
Lots of problems, but we'll start with the log.
If the DB is in full recovery you need to be backing up the transaction log (not truncating it, backing it up). That allows for point-in-time recovery should there be a problem.
If you don't need point in time recovery (restore to the last full backup's fine), then set the DB into simple recovery and the log will manage itself.
Read the article that ps linked to.
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
June 25, 2009 at 4:06 am
hi,
i'm taking daily full backup, so i think truncate the log is not a problem before taking the full backup.
please provider suggetions.
🙂
June 25, 2009 at 4:26 am
vrabhadram (6/25/2009)
please provider suggetions.
Don't truncate the transaction log. Don't shrink the transaction log. Both are not recommended for regular maintenance. Only time you should be shrinking the transaction log is after some unusual operation caused it to grow massively.
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
June 25, 2009 at 4:33 am
thank you,
i have one more issue:
i have a job to archive data from the production to another log database(600 tables). for this purpose i have created the statements for each and every table like:
insert into DBLog.dbo.logtabl1
select * from DBPROD.dbo.um_tbl1
go
truncate table dbprod.dbo.um_tbl1
like this i have created for the 600 tables and make it as a job.
but it is giving error:
Executed as user: NT AUTHORITY\SYSTEM.
Incorrect syntax near 'um_'. [SQLSTATE 42000] (Error 102). The step failed.
please help me.
🙂
June 25, 2009 at 4:55 am
vrabhadram (6/25/2009)
thank you,i have one more issue:
i have a job to archive data from the production to another log database(600 tables). for this purpose i have created the statements for each and every table like:
insert into DBLog.dbo.logtabl1
select * from DBPROD.dbo.um_tbl1
go
truncate table dbprod.dbo.um_tbl1
like this i have created for the 600 tables and make it as a job.
but it is giving error:
Executed as user: NT AUTHORITY\SYSTEM.
Incorrect syntax near 'um_'. [SQLSTATE 42000] (Error 102). The step failed.
please help me.
Try this:
SET QUOTED_IDENTIFIER ON
insert into [DBLog].[dbo].[logtabl1]
select * from [DBPROD].[dbo].[um_tbl1]
go
truncate table [dbprod].[dbo].[um_tbl1]
June 25, 2009 at 4:56 am
vrabhadram (6/25/2009)
i have one more issue:i have a job to archive data from the production to another log database(600 tables). for this purpose i have created the statements for each and every table like:
insert into DBLog.dbo.logtabl1
select * from DBPROD.dbo.um_tbl1
go
truncate table dbprod.dbo.um_tbl1
like this i have created for the 600 tables and make it as a job.
but it is giving error:
Executed as user: NT AUTHORITY\SYSTEM.
Incorrect syntax near 'um_'. [SQLSTATE 42000] (Error 102). The step failed.
please help me.
Is there an SP for this? How do you do this? Is there a job that calls the SP to do this? This error looks like that of linked server? Is there a linked server?
Pls provide more details on this
June 25, 2009 at 5:26 am
when i run this script in query window it's not giving any issues.
but by executing as a job it's giving the error.
🙂
June 25, 2009 at 5:32 am
Haver you checked that the script hasn't been truncated? Job steps only allow so much text in them.
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
June 25, 2009 at 7:39 am
hi,
i've tried with your script, but still job is giving error.But in query analyzer it's not gving error.
is there any problem with the version:
my server version is : SQL server version RTM 9.00.1399.06 (EEE)
🙂
June 26, 2009 at 12:01 am
the whole problem seems to be the QUOTED_IDENTIFIER options:
Try this:
SET QUOTED_IDENTIFIER ON
USE DBPROD
insert into [DBLog.dbo.logtabl1]
select * from [dbo.um_tbl1]
go
truncate table [dbo.um_tbl1]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply