I had a normal SQL backup fail (one of hundreds that happen every day) with
this error
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:BACKUP DATABASE is terminating abnormally. (Microsoft
OLE DB Provider for SQL Server (80040e14): The WriteFileEx system function
executed on file 'F:\MSSQL\BACKUP\Mocom\RB_Mocom_db_FULL_99.BAK' only wrote
235008 bytes, expected 983040.)
Step Error code: 80040E14
Step Error Help File:
Step Error Help Context ID:0
I can't find any hints on microsoft.com for WriteFileEx or the error numbers.
I suspect its a simple out of space condition, but I can't be sure.
Anyone seen one of these ?
TIAFrom oledberr.h file, which ships with the OLEDB SDK:
0x80040E14 The command contained one or more errors
Backup failed for some other reason, but SEM reported a generic error. You
should check your SQL errorlogs for backup errors and post them here.
Adrian
"Jim Trowbridge" <JimTrowbridge@.discussions.microsoft.com> wrote in message
news:DF3297D4-F0E1-4348-9D3A-44E539995F9D@.microsoft.com...
>I had a normal SQL backup fail (one of hundreds that happen every day) with
> this error
> Step Error Source: Microsoft OLE DB Provider for SQL Server
> Step Error Description:BACKUP DATABASE is terminating abnormally.
> (Microsoft
> OLE DB Provider for SQL Server (80040e14): The WriteFileEx system function
> executed on file 'F:\MSSQL\BACKUP\Mocom\RB_Mocom_db_FULL_99.BAK' only
> wrote
> 235008 bytes, expected 983040.)
> Step Error code: 80040E14
> Step Error Help File:
> Step Error Help Context ID:0
> I can't find any hints on microsoft.com for WriteFileEx or the error
> numbers.
> I suspect its a simple out of space condition, but I can't be sure.
> Anyone seen one of these ?
> TIA
>
Showing posts with label step. Show all posts
Showing posts with label step. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Backup fail
Hi All,
I have a basic DB backup question:
I make DB backups every day at night using maintenance plan, where all DB
are saved. In 1st step the database backup is made, in 2nd step the
transaction log is saved. Sometimes, usually once a week (e.g. on Monday
1.00 am), one of the database backups failed with "... failed because DB log
is full" error. Now I make a manual backup, which is normally proceeded.
Then, the next backups work normally approx. 1 week. Situation repeates, but
not exactly every week.
After the log backup, I think it will truncate to small size. It is not
true, usual size of log is about 130 MB (database size is 80 MB). But after
a backup failes, the log size is about 270 MB.
In backup parameters the log truncate option is included, but in maintenance
plan this option is missing. So if I'll make backups manually, no problems
will occur. But I want work automatically.
What am I do to correct backup job?
Thank you in advance for your help
Vlastik
Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
generally do db backup once a day and log backup one per hour.
Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a basic DB backup question:
> I make DB backups every day at night using maintenance plan, where all DB
> are saved. In 1st step the database backup is made, in 2nd step the
> transaction log is saved. Sometimes, usually once a week (e.g. on Monday
> 1.00 am), one of the database backups failed with "... failed because DB log
> is full" error. Now I make a manual backup, which is normally proceeded.
> Then, the next backups work normally approx. 1 week. Situation repeates, but
> not exactly every week.
> After the log backup, I think it will truncate to small size. It is not
> true, usual size of log is about 130 MB (database size is 80 MB). But after
> a backup failes, the log size is about 270 MB.
> In backup parameters the log truncate option is included, but in maintenance
> plan this option is missing. So if I'll make backups manually, no problems
> will occur. But I want work automatically.
> What am I do to correct backup job?
> Thank you in advance for your help
> Vlastik
>
>
|||Hi, Tibor,
thank you for your response, it helps me to understand some functions of SQL 2000. Of course I've made both data and log backups daily, but something in them doesn't work.
Atfer your response, I checked the settings of my DBs and all settings are O.K. including autoshrink. I've only added a scheduled shrink daily after backup, because an automatic shrink didn't work and I don't know why. For example today, after backups and autoshrink, the database and log size were 112/149 MB. I made manual shrink and sizes changed to 84/41 MB. It's really crazy.
I checked the plan history in [msdb], but I'm not pretty enough to understand it. Database shrink was done every day. Here is a selection for database 'Kredit', which is the biggest:
database_name ;activity ;succeeded ;end_time ;error_number
Kredit ;Backup database ;False ;26.4.2004 1:00:09 ;9002
Kredit ;Backup transaction log ;True ;26.4.2004 1:31:00 ;0
Kredit ;Verify Backup ;True ;26.4.2004 1:31:22 ;0
Kredit ;Rebuild Indexes ;True ;26.4.2004 2:01:43 ;0
Kredit ;Shrink Database ;True ;26.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;27.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;27.4.2004 1:30:49 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:31:07 ;0
Kredit ;Rebuild Indexes ;True ;27.4.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;27.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;28.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;28.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;28.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;28.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;29.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;29.4.2004 1:30:43 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:30:59 ;0
Kredit ;Rebuild Indexes ;True ;29.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;29.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;30.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;30.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;30.4.2004 2:01:48 ;0
Kredit ;Shrink Database ;True ;30.4.2004 2:02:04 ;0
Kredit ;Backup database ;False ;3.5.2004 1:00:11 ;9002
Kredit ;Backup transaction log ;True ;3.5.2004 1:31:01 ;0
Kredit ;Verify Backup ;True ;3.5.2004 1:31:24 ;0
Kredit ;Rebuild Indexes ;True ;3.5.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;3.5.2004 2:01:58 ;0
Note, than there is no reason for failing backup on Mondays, sometimes it fails on another day. From Friday 19:00 to Monday, 5:00, no DB activity is performed.
On the disks, there is enough space for all backup files and a half disk is permanently free.
After these corrections, I hope it'll go better.
Thanks once more, tomorrow I'll write, how it continues.
Best regards, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v diskusnm pspvku news:%23DiHakrMEHA.2592@.tk2msftngp13.phx.gbl...
> Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
> generally do db backup once a day and log backup one per hour.
> Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
> background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
> DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
>
|||Bad results.
Today the backup was proceeded normally, but database wasn't shrunk. After
2nd manual backups and shrinking the "empty" log has 142 MB!
|||Did you check the VLF layout? (See the article I referred to.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...
> Bad results.
> Today the backup was proceeded normally, but database wasn't shrunk. After
> 2nd manual backups and shrinking the "empty" log has 142 MB!
>
>
|||It's incredible! After 3rd manual backup and shrinking the log has
"compressed" to 8MB.
The VLF layout shows now 22 blocks, where 1st, 3rd and 22th are in use. Now
it's O.K., because shrink setting is to leave 10% of free blocks.
Why can't this work automatically and in the first try?
So, I'll try to make backups more often. Perhaps it helps.
Thank you a lot.
Rgds, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
|||So, after some experiments, I've written my own program for DB backup, which
works with all database files, save and shrink them as I need. It works
fine. It's the only thing, what I've had to do before my holidays.
My program makes backup and than shrinks the trnsact. log so many times,
till its size remains constant. May be it is strange, but it works. Because
this backups is done at 0:30, I'm not afraid of complications.
Thanks for your tips, I've read all, but I wasn't satisfied with this.
Best regards
Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
sql
I have a basic DB backup question:
I make DB backups every day at night using maintenance plan, where all DB
are saved. In 1st step the database backup is made, in 2nd step the
transaction log is saved. Sometimes, usually once a week (e.g. on Monday
1.00 am), one of the database backups failed with "... failed because DB log
is full" error. Now I make a manual backup, which is normally proceeded.
Then, the next backups work normally approx. 1 week. Situation repeates, but
not exactly every week.
After the log backup, I think it will truncate to small size. It is not
true, usual size of log is about 130 MB (database size is 80 MB). But after
a backup failes, the log size is about 270 MB.
In backup parameters the log truncate option is included, but in maintenance
plan this option is missing. So if I'll make backups manually, no problems
will occur. But I want work automatically.
What am I do to correct backup job?
Thank you in advance for your help
Vlastik
Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
generally do db backup once a day and log backup one per hour.
Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a basic DB backup question:
> I make DB backups every day at night using maintenance plan, where all DB
> are saved. In 1st step the database backup is made, in 2nd step the
> transaction log is saved. Sometimes, usually once a week (e.g. on Monday
> 1.00 am), one of the database backups failed with "... failed because DB log
> is full" error. Now I make a manual backup, which is normally proceeded.
> Then, the next backups work normally approx. 1 week. Situation repeates, but
> not exactly every week.
> After the log backup, I think it will truncate to small size. It is not
> true, usual size of log is about 130 MB (database size is 80 MB). But after
> a backup failes, the log size is about 270 MB.
> In backup parameters the log truncate option is included, but in maintenance
> plan this option is missing. So if I'll make backups manually, no problems
> will occur. But I want work automatically.
> What am I do to correct backup job?
> Thank you in advance for your help
> Vlastik
>
>
|||Hi, Tibor,
thank you for your response, it helps me to understand some functions of SQL 2000. Of course I've made both data and log backups daily, but something in them doesn't work.
Atfer your response, I checked the settings of my DBs and all settings are O.K. including autoshrink. I've only added a scheduled shrink daily after backup, because an automatic shrink didn't work and I don't know why. For example today, after backups and autoshrink, the database and log size were 112/149 MB. I made manual shrink and sizes changed to 84/41 MB. It's really crazy.
I checked the plan history in [msdb], but I'm not pretty enough to understand it. Database shrink was done every day. Here is a selection for database 'Kredit', which is the biggest:
database_name ;activity ;succeeded ;end_time ;error_number
Kredit ;Backup database ;False ;26.4.2004 1:00:09 ;9002
Kredit ;Backup transaction log ;True ;26.4.2004 1:31:00 ;0
Kredit ;Verify Backup ;True ;26.4.2004 1:31:22 ;0
Kredit ;Rebuild Indexes ;True ;26.4.2004 2:01:43 ;0
Kredit ;Shrink Database ;True ;26.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;27.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;27.4.2004 1:30:49 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:31:07 ;0
Kredit ;Rebuild Indexes ;True ;27.4.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;27.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;28.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;28.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;28.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;28.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;29.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;29.4.2004 1:30:43 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:30:59 ;0
Kredit ;Rebuild Indexes ;True ;29.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;29.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;30.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;30.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;30.4.2004 2:01:48 ;0
Kredit ;Shrink Database ;True ;30.4.2004 2:02:04 ;0
Kredit ;Backup database ;False ;3.5.2004 1:00:11 ;9002
Kredit ;Backup transaction log ;True ;3.5.2004 1:31:01 ;0
Kredit ;Verify Backup ;True ;3.5.2004 1:31:24 ;0
Kredit ;Rebuild Indexes ;True ;3.5.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;3.5.2004 2:01:58 ;0
Note, than there is no reason for failing backup on Mondays, sometimes it fails on another day. From Friday 19:00 to Monday, 5:00, no DB activity is performed.
On the disks, there is enough space for all backup files and a half disk is permanently free.
After these corrections, I hope it'll go better.
Thanks once more, tomorrow I'll write, how it continues.
Best regards, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v diskusnm pspvku news:%23DiHakrMEHA.2592@.tk2msftngp13.phx.gbl...
> Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
> generally do db backup once a day and log backup one per hour.
> Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
> background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
> DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
>
|||Bad results.
Today the backup was proceeded normally, but database wasn't shrunk. After
2nd manual backups and shrinking the "empty" log has 142 MB!
|||Did you check the VLF layout? (See the article I referred to.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...
> Bad results.
> Today the backup was proceeded normally, but database wasn't shrunk. After
> 2nd manual backups and shrinking the "empty" log has 142 MB!
>
>
|||It's incredible! After 3rd manual backup and shrinking the log has
"compressed" to 8MB.
The VLF layout shows now 22 blocks, where 1st, 3rd and 22th are in use. Now
it's O.K., because shrink setting is to leave 10% of free blocks.
Why can't this work automatically and in the first try?
So, I'll try to make backups more often. Perhaps it helps.
Thank you a lot.
Rgds, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
|||So, after some experiments, I've written my own program for DB backup, which
works with all database files, save and shrink them as I need. It works
fine. It's the only thing, what I've had to do before my holidays.
My program makes backup and than shrinks the trnsact. log so many times,
till its size remains constant. May be it is strange, but it works. Because
this backups is done at 0:30, I'm not afraid of complications.
Thanks for your tips, I've read all, but I wasn't satisfied with this.
Best regards
Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
sql
Tuesday, February 14, 2012
backup
Hello, I have a backup plan as below:
A. Everyday midnight
Step 1.
USE myDB
BACKUP LOG myDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(myDB_Log, 1)
Step 2.
USE myDB
SET @.db_backup_path = @.db_backup_path + 'myDB'+ '.bak'
BACKUP DATABASE myDB TO DISK = @.db_backup_path WITH INIT
B. Every 2 hours
USE myDB
SET @.db_myDBlog = @.db_backup_path + 'myDB_log_'+ x + '.trn'
BACKUP LOG myDB TO DISK = @.db_myDBlog
Can I move Step 1 after Step 2( backup database first then shrinkthe dblog)?
Any suggestions about this plan? Thanks.Hi,
No need to shrink the file daily. To restrict the LDF growth you could
perform the transaction log backup every 30 minutes.
This will ensure the LDF will not grow heavily.
So :-
1. Do a full database backup daily once at midnight
2. Do a transaction log every 30 minutes.
Thanks
Hari
SQL Server MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:eUOSH6irFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Hello, I have a backup plan as below:
> A. Everyday midnight
> Step 1.
> USE myDB
> BACKUP LOG myDB WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(myDB_Log, 1)
> Step 2.
> USE myDB
> SET @.db_backup_path = @.db_backup_path + 'myDB'+ '.bak'
> BACKUP DATABASE myDB TO DISK = @.db_backup_path WITH INIT
> B. Every 2 hours
> USE myDB
> SET @.db_myDBlog = @.db_backup_path + 'myDB_log_'+ x + '.trn'
> BACKUP LOG myDB TO DISK = @.db_myDBlog
> Can I move Step 1 after Step 2( backup database first then shrinkthe
> dblog)?
> Any suggestions about this plan? Thanks.
>
>
>|||Thanks for the quick response.
"Hari Pra
" wrote in message :
> No need to shrink the file daily. To restrict the LDF growth you could
> perform the transaction log backup every 30 minutes.
> This will ensure the LDF will not grow heavily.
is it shrink file a bad thing? what situation shrink file is good?
> So :-
> 1. Do a full database backup daily once at midnight
> 2. Do a transaction log every 30 minutes.
What's diff between 30 minutes and 2 hours(except the Frequency)?
Please advice.|||> is it shrink file a bad thing? what situation shrink file is good?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> What's diff between 30 minutes and 2 hours(except the Frequency)?
You do more modification during two hours compared to 30 minutes. I.e., you
produce more log records
during a longer time period. Also, more frequent backups allow you to lose l
ess data in the unlikely
event...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"js" <js@.someone@.hotmail.com> wrote in message news:e$Sm8IjrFHA.3884@.TK2MSFTNGP11.phx.gbl..
.
> Thanks for the quick response.
> "Hari Pra" wrote in message :
> is it shrink file a bad thing? what situation shrink file is good?
>
> What's diff between 30 minutes and 2 hours(except the Frequency)?
> Please advice.
>
>
>
>|||Thanks Tibor,
> You do more modification during two hours compared to 30 minutes. I.e.,
> you produce more log records during a longer time period. Also, more
> frequent backups allow you to lose less data in the unlikely event...
>
Is it use lot of resource (for the end user, they will fell access sql is
slow when backing up log)?|||Whether a log backup will be noticable to users depends on the load of the s
erver and also your disk
layout (if you separated the tlog file, RAID config etc). But if you do log
backup every two hours,
it will take 4 times as long time as if you do it every 30 minutes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"js" <js@.someone@.hotmail.com> wrote in message news:eevedBnrFHA.3444@.TK2MSFTNGP12.phx.gbl..
.
> Thanks Tibor,
>
> Is it use lot of resource (for the end user, they will fell access sql is
slow when backing up
> log)?
>
>
>|||Thanks Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:empQRttrFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Whether a log backup will be noticable to users depends on the load of the
> server and also your disk layout (if you separated the tlog file, RAID
> config etc). But if you do log backup every two hours, it will take 4
> times as long time as if you do it every 30 minutes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:eevedBnrFHA.3444@.TK2MSFTNGP12.phx.gbl...
>
A. Everyday midnight
Step 1.
USE myDB
BACKUP LOG myDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(myDB_Log, 1)
Step 2.
USE myDB
SET @.db_backup_path = @.db_backup_path + 'myDB'+ '.bak'
BACKUP DATABASE myDB TO DISK = @.db_backup_path WITH INIT
B. Every 2 hours
USE myDB
SET @.db_myDBlog = @.db_backup_path + 'myDB_log_'+ x + '.trn'
BACKUP LOG myDB TO DISK = @.db_myDBlog
Can I move Step 1 after Step 2( backup database first then shrinkthe dblog)?
Any suggestions about this plan? Thanks.Hi,
No need to shrink the file daily. To restrict the LDF growth you could
perform the transaction log backup every 30 minutes.
This will ensure the LDF will not grow heavily.
So :-
1. Do a full database backup daily once at midnight
2. Do a transaction log every 30 minutes.
Thanks
Hari
SQL Server MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:eUOSH6irFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Hello, I have a backup plan as below:
> A. Everyday midnight
> Step 1.
> USE myDB
> BACKUP LOG myDB WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(myDB_Log, 1)
> Step 2.
> USE myDB
> SET @.db_backup_path = @.db_backup_path + 'myDB'+ '.bak'
> BACKUP DATABASE myDB TO DISK = @.db_backup_path WITH INIT
> B. Every 2 hours
> USE myDB
> SET @.db_myDBlog = @.db_backup_path + 'myDB_log_'+ x + '.trn'
> BACKUP LOG myDB TO DISK = @.db_myDBlog
> Can I move Step 1 after Step 2( backup database first then shrinkthe
> dblog)?
> Any suggestions about this plan? Thanks.
>
>
>|||Thanks for the quick response.
"Hari Pra
" wrote in message :> No need to shrink the file daily. To restrict the LDF growth you could
> perform the transaction log backup every 30 minutes.
> This will ensure the LDF will not grow heavily.
is it shrink file a bad thing? what situation shrink file is good?
> So :-
> 1. Do a full database backup daily once at midnight
> 2. Do a transaction log every 30 minutes.
What's diff between 30 minutes and 2 hours(except the Frequency)?
Please advice.|||> is it shrink file a bad thing? what situation shrink file is good?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> What's diff between 30 minutes and 2 hours(except the Frequency)?
You do more modification during two hours compared to 30 minutes. I.e., you
produce more log records
during a longer time period. Also, more frequent backups allow you to lose l
ess data in the unlikely
event...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"js" <js@.someone@.hotmail.com> wrote in message news:e$Sm8IjrFHA.3884@.TK2MSFTNGP11.phx.gbl..
.
> Thanks for the quick response.
> "Hari Pra
" wrote in message : > is it shrink file a bad thing? what situation shrink file is good?
>
> What's diff between 30 minutes and 2 hours(except the Frequency)?
> Please advice.
>
>
>
>|||Thanks Tibor,
> You do more modification during two hours compared to 30 minutes. I.e.,
> you produce more log records during a longer time period. Also, more
> frequent backups allow you to lose less data in the unlikely event...
>
Is it use lot of resource (for the end user, they will fell access sql is
slow when backing up log)?|||Whether a log backup will be noticable to users depends on the load of the s
erver and also your disk
layout (if you separated the tlog file, RAID config etc). But if you do log
backup every two hours,
it will take 4 times as long time as if you do it every 30 minutes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"js" <js@.someone@.hotmail.com> wrote in message news:eevedBnrFHA.3444@.TK2MSFTNGP12.phx.gbl..
.
> Thanks Tibor,
>
> Is it use lot of resource (for the end user, they will fell access sql is
slow when backing up
> log)?
>
>
>|||Thanks Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:empQRttrFHA.3556@.TK2MSFTNGP10.phx.gbl...
> Whether a log backup will be noticable to users depends on the load of the
> server and also your disk layout (if you separated the tlog file, RAID
> config etc). But if you do log backup every two hours, it will take 4
> times as long time as if you do it every 30 minutes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:eevedBnrFHA.3444@.TK2MSFTNGP12.phx.gbl...
>
Subscribe to:
Posts (Atom)