I've been reading around in the BOL to figure out what backup strategy is th
e
"best" available. The database I want to backup, is ~4.5 after a shrink, but
it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used du
ring
a normal workday, by around 15 people. What backup setup is the recommended
in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log e
very
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every
24
hours, does that mean that I can then afterward safely truncate the log (I l
ike
keeping things neat
? What do I do with the *.bak and *.trn when they are over
two days old?
I doubt, therefore I might be.I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest thi
s.
burt_king@.yahoo.com
"Kim Noer" wrote:
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
the
> "best" available. The database I want to backup, is ~4.5 after a shrink, b
ut it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
during
> a normal workday, by around 15 people. What backup setup is the recommende
d in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup eve
ry 24
> hours, does that mean that I can then afterward safely truncate the log (I
like
> keeping things neat
? What do I do with the *.bak and *.trn when they are over
> two days old?
> --
> I doubt, therefore I might be.
>|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...[vbcol=seagreen]
> I'd suggest a good, thorough read through of books on line to make sure yo
u
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest t
his.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
>|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat
? What do I do with the *.bak and> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before
.Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result i
s
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set
the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean th
at
this logfile will never go below 2.06GB unless I truncate it manually? Secon
dly,
what about the VLF's below, since they are unused will this cause SQL Server
to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
I doubt, therefore I might be.|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl
> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, the
n
insert a lot of records, which showed me that SQL Server reuses unused VLF,
even
when they are "before" used VLF's.
I doubt, therefore I might be.
No comments:
Post a Comment