Footer rows to skip: maintenance

Showing posts with label maintenance. Show all posts

Friday, March 9, 2012

Faster SQL Server Backups

My database is 300 GB is size using SQL Server 2000. This is a
high-availability cluster server. My present daily maintenance job backups
take approximately 4 hours to complete.
I would like to know alternatives to faster database backups without
changing the database.
Thanks,
Where to do you take the backups? Same disk as data? Over network? What RAID level? Also, consider
backup compression software. I've mentioned a few of those on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1C2E2810-E1A2-4707-8047-F98FBE74C009@.microsoft.com...
> My database is 300 GB is size using SQL Server 2000. This is a
> high-availability cluster server. My present daily maintenance job backups
> take approximately 4 hours to complete.
> I would like to know alternatives to faster database backups without
> changing the database.
>
> Thanks,
|||Hi,
Split-Mirror Backup is the fastest method to backup the VLDB. see the link
for details:-
http://www.microsoft.com/technet/pro.../spltmirr.mspx
Thanks
Hari
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:1C2E2810-E1A2-4707-8047-F98FBE74C009@.microsoft.com...
> My database is 300 GB is size using SQL Server 2000. This is a
> high-availability cluster server. My present daily maintenance job
> backups
> take approximately 4 hours to complete.
> I would like to know alternatives to faster database backups without
> changing the database.
>
> Thanks,
|||backup to multiple files (Devices).
This can speed the backup process a ton.
backups should be on own RAID Volume (RAID 1 or RAID 1+0)
IF you can afford SQL Lite-speed, then get it.
Otherwise, try a script similar to the one attached to improve current
performance
Greg Jackson
PDX, Oregon
begin 666 spFULL_DATABASE_BACKUP.SQL
M#0HO*@.T*<W!&=6QL1&%T86)A<V5"86-K=7 -"@.T*4'5R<&]S93H-"E!E<F9O
M<FUS($9U;&P@.0F%C:W5P#0H-"D%U=&AO<CH-"D=R96=O<GD@.02!*86-K<V]N
M#0H-"D1A=&4Z#0HQ,B\R,B\R,# S#0H-"E5S96%G93H-"G1A:V5S(#,@.<&%R
M86US.@.T*"41"($Y!344-"@.E,;V-A=&EO;B!&;W(@.0D%C:W5P<R H4VAO=6QD
M(&)E(&QO8V%L(&1R:79E(&%R<F%Y(&YO="!A('-H87)E*0T*"4YU;6)E<B!O
M9B!S97!E<F%T92!B86-K=7 @.9FEL97,@.=&\@.8W)E871E("A)(')E8V]M;65N
M9"!L96%V:6YG('1H:7,@.870@.-"D-"@.T*#0H-"F5X.B @.97AE8R!!9&UI;BYD
M8F\N<W!&=6QL7T1A=&%"87-E7T)A8VMU<" G0W)E9&ET;F5T)RPG1CHO3&]C
M871I;VXO)RPT( T*#0I)('5S=6%L;'D@.8W)E871E(&$@.3D57($1A=&%B87-E
M(&YA;65D(")!1$U)3B(@.;VX@.96%C:"!397)V97(N#0H-"E1H:7,@.<W!R;V,@.
M=V]U;&0@.9V\@.:6X@.=&AE($%D;6EN($1"( T**B\-"@.T*0U)%051%('!R;V-E
M9'5R92!D8F\N<W!&=6QL7T1A=&%"87-E7T)A8VM5< T*#0HH#0I 1&%T84)A
M<V5.86UE('9A<F-H87(H,3 P*2P-"D!&:6QE4&%T:"!V87)C:&%R*#(U,"DL
M#0I 3G5M8F5R3V9&:6QE<R!T:6YY:6YT#0HI#0H-"F%S( T*#0H-"F)E9VEN
M#0H-"G-E="!N;V-O=6YT(&]N#0H-"FEF($!$871A0F%S94YA;64@.;F]T(&EN
M*'-E;&5C="!N86UE(&9R;VT@.;6%S=&5R+BYS>7-D871A8F%S97,@.#0IW:71H
M*&YO;&]C:RDI#0IB96=I;@.T*<F%I<V5R<F]R("@.G)7,@.9&]E<R!N;W0@.97AI
M<W0@.87,@.82!D871A8F%S92!O;B!T:&ES('-E<G9E<B<L(#$Q+" Q+ T*0$1A
M=&%"87-E3F%M92D-"G)E='5R;B@.P*0T*96YD#0H-"FEF($!N=6UB97)/9D9I
M;&5S(&YO="!B971W965N(#$@.86YD(#@.-"F)E9VEN#0IR86ES97)R;W(@.*"=.
M=6UB97(@.;V8@.8F%C:W5P(&9I;&5S('1O(&-R96%T92!M=7-T(&)E(&)E='=E
M96X@.,2!A;F0@.."<L( T*,3$L#0HQ*0T*<F5T=7)N*# I#0IE;F0-"@.T*#0H-
M"@.D)"0T*"0D)8F5G:6X-"@.D)"0T*"0D)9&5C;&%R92! 1&%T92!V87)C:&%R
M*#4P*2P@.0'1I;64@.=F%R8VAA<B@.U,"D-"@.T*"0D)<V5T($!$871E(#T@.8V]N
M=F5R="AV87)C:&%R+&=E=&1A=&4H*2PQ,3(I#0H-"@.D)"7-E="! =&EM92 ]
M(&-A<V4@.=VAE;@.T*;&5N*&1A=&5P87)T*&AH+&=E=&1A=&4H*2DI( #T@.,2!T
M:&5N("<P)R K(&-O;G9E<G0H=F%R8VAA<B@.S,RDL#0ID871E<&%R="AH:"QG
M971D871E*"DI*0T*"0D)"0EE;'-E(&-O;G9E<G0H=F%R8VAA<B@.S,RDL#0ID
M871E<&%R="AH:"QG971D871E*"DI*2!E;F0@.*R -"@.D)"0D)8V%S92!W:&5N
M#0IL96XH9&%T97!A<G0H;6DL9V5T9&%T92@.I*2D@./2 Q('1H96X@.)S G("L@.
M8V]N=F5R="AV87)C:&%R*#,S*2P-"F1A=&5P87)T*&UI+&=E=&1A=&4H*2DI
M#0H)"0D)"65L<V4@.8V]N=F5R="AV87)C:&%R*#,S*2P-"F1A=&5P87)T*&UI
M+&=E=&1A=&4H*2DI(&5N9" -"@.T*#0H)"0ED96-L87)E($!344Q3=')I;F<@.
M=F%R8VAA<B@.U,# P*2P@.0$9I;&5.=6UB97(-"G1I;GEI;G0-"@.T*"0D)<V5T
M($!344Q3=')I;F<@./2 H)T)!0TM54"!$051!0D%312<@.*R!C:&%R*#$S*2 K
M#0I 1&%T84)A<V5.86UE("L@.)R!43R G("L@.8VAA<B@.Q,RDI#0H-"@.D)"7-E
M="! 1FEL94YU;6)E<B ](#$-"@.T*"0D)=VAI;&4@.0$9I;&5.=6UB97(@./"!
M3G5M8F5R3V9&:6QE<PT*#0H)"0EB96=I;@.T*#0H)"0ES970@.0 %-13%-T<FEN
M9R ]("@.-"@.D)"0D)"4!344Q3=')I;F<@.*R G1$E32R ])R K#0IC:&%R*#$S
M*2 K("<G)R<@.*PT*"0D)"0D)0$9I;&50871H("L@.0$1A=&%"87-E3F%M92 K
M#0HG,"<@.*R!#;VYV97)T*&-H87(H,2DL0$9I;&5.=6UB97(I("L-"@.D)"0D)
M"2=?9G5L;%\G("L@.0$1A=&4@.("L-"D!T:6UE("L@.)RY"04LG("L@.)R<G)R K
M("<L)R K(&-H87(H,3,I#0H)"0D)"2D-"@.T*"0D)<V5T($!&:6QE3G5M8F5R
M(#T@.0$9I;&5.=6UB97(@.*R Q#0H-"@.D)"65N9 T*#0H-"@.D)"7-E="! 4U%,
M4W1R:6YG(#T@.* T*"0D)"0D)0%-13%-T<FEN9R K("=$25-+(#TG("L-"F-H
M87(H,3,I("L@.)R<G)R K#0H)"0D)"0E 1FEL95!A=&@.@.*R! 1&%T84)A<V5.
M86UE("L-"B<P)R K($-O;G9E<G0H8VAA<B@.Q*2Q 1FEL94YU;6)E<BD@.*PT*
M"0D)"0D))U]F=6QL7R<@.*R! 1&%T92 @.*PT*0'1I;64@.*R G+D)!2R<@.*R G
M)R<G("L@.8VAA<B@.Q,RD@.*PT*"0D)"0D))U=)5$@.G("L@.8VAA< B@.Q,RD@.*PT*
M"0D)"0D))TE.250L)R K(&-H87(H,3,I("L-"@.D)"0D)"2=.3T9/4DU!5"PG
M("L@.8VAA<B@.Q,RD@.*PT*"0D)"0D))U-40513(#T@.,3 G#0H)"0D)"2D-"@.T*
M#0H)"0EE>&5C*$!344Q3=')I;F<I#0H-"@.D)#0H@."0D)96YD#0H-"@.T*#0H@.
8<F5T=7)N*# I#0H@.96YD#0H@.#0I'3PT*
`
end
|||In article <1C2E2810-E1A2-4707-8047-F98FBE74C009@.microsoft.com>, =?Utf-
8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> says...
> My database is 300 GB is size using SQL Server 2000. This is a
> high-availability cluster server. My present daily maintenance job backups
> take approximately 4 hours to complete.
> I would like to know alternatives to faster database backups without
> changing the database.
Not knowing your backup method, I can only suggest that you get a
couple large disk arrays, do not use the same that your database is on,
and backup to disk and then disk to tape. Backing up across a network to
a share would require at least 1GB connection to make it worth your
effort, but backup to physical disk is faster.
--
spam999free@.rrohio.com
remove 999 in order to email me
|||Most certainly write your backups to local disk as a first option, disks
different from the LUNs your data and log files are resident on.
Next, regardless if you use the maintenance plans or hand code the backups,
having multiple files per filegroup will spawn parallel processes for SQL
Server to read from for the backup.
If you want both your backups and potential restores to be faster, you'll
have to hand code a job to backup the database. Back up to multiple backup
files. This will increase the disk throughput but also spawn multiple
processes if you should ever have to restore, reducing the outage time.
Finally, check out SQL Litespeed by Imceda. It allows you to create
multiple process even without multiple files as well as gives you several
compression options to keep your to disk backups more reasonably sized.
Sincerely,
Anthony Thomas

"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.1d10169943fb39819898d1@.news-server.columbus.rr.com...
In article <1C2E2810-E1A2-4707-8047-F98FBE74C009@.microsoft.com>, =?Utf-
8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> says...
> My database is 300 GB is size using SQL Server 2000. This is a
> high-availability cluster server. My present daily maintenance job
backups
> take approximately 4 hours to complete.
> I would like to know alternatives to faster database backups without
> changing the database.
Not knowing your backup method, I can only suggest that you get a
couple large disk arrays, do not use the same that your database is on,
and backup to disk and then disk to tape. Backing up across a network to
a share would require at least 1GB connection to make it worth your
effort, but backup to physical disk is faster.
--
spam999free@.rrohio.com
remove 999 in order to email me

Faster SQL Server Backups

Friday, February 24, 2012

Failure to Remove Old Transaction Log Backups

I've just become the DBA. The origianl Maintenance Plan was setup to remove
old TRasact Log Backups. This has not worked in several months. Does anyone
have an idea why a working plan would fail? Everything appears to be set
correctly. Is there any advantage to placing optimimization in a seperate
plan from the backups?
Here is a post from Bill at MS that outlines the most common reasons for
this:
-- Log files don't delete --
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"mark_at_msb" <mark_at_msb@.discussions.microsoft.com> wrote in message
news:486897A4-7C19-4924-845D-0A724D4E4899@.microsoft.com...
> I've just become the DBA. The origianl Maintenance Plan was setup to
remove
> old TRasact Log Backups. This has not worked in several months. Does
anyone
> have an idea why a working plan would fail? Everything appears to be set
> correctly. Is there any advantage to placing optimimization in a seperate
> plan from the backups?

Friday, March 9, 2012

Faster SQL Server Backups

Faster SQL Server Backups

Faster SQL Server Backups

Friday, February 24, 2012

Failure to Remove Old Transaction Log Backups

Footer rows to skip

Blog Archive

About Me