Re: How can I assign the result of dateadd to a variable ?
From: Paul fpvt2 (Paulfpvt2_at_discussions.microsoft.com)
Date: 12/16/04
- Next message: David Portas: "Re: stored procedure to copy a stored procedure to a different DB"
- Previous message: David Browne: "Re: Oracle Sequence in Sql Server"
- In reply to: Hugo Kornelis: "Re: How can I assign the result of dateadd to a variable ?"
- Next in thread: Hugo Kornelis: "Re: How can I assign the result of dateadd to a variable ?"
- Reply: Hugo Kornelis: "Re: How can I assign the result of dateadd to a variable ?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 16 Dec 2004 08:03:04 -0800
Thank you very much for your reply.
>Are you aware that this exposes you to the risk of losing all data
>modifications since your last (full or incremental) backup?
Yes, I think it should be fine. Once data is entered into the database, it
is never modified. It then stays in the database until 4 days later when it
will be deleted.
Thank you for clearing up about the integrity, I thought it was checking for
foreign key constraints.
I now check the "Check Database Integrity" option with "Include Indexes",
and I schedule it to perform once a week
on Sunday 12 am.
I have now changed the recovery model to "Simple" (In Enterprise Manager,
right-click the database and select "Properties".
Go to the Options tag. In the section "Recovery", under "Model" select
"Simple").
Since yesterday was the first day I collected the data, the _Data.mdf file
has grown to 900 meg, and the _Log.ldf
file has grown to 1 gig. Since today I just change the recovery model to
"Simple" (thanks for the direction),
from now on the log file will grow until 3 days later when the 4 days old
data are deleted. Then,
it will no longer grow but keep reusing free space. Is this correct ?
Thanks a lot.
"Hugo Kornelis" wrote:
> On Wed, 15 Dec 2004 14:27:09 -0800, Paul fpvt2 wrote:
>
> >Thank you Hugo and David for your reply.
> >Since the data will only be kept for 4 days anyway, I will use the simple
> >recovery method.
>
> Hi Paul,
>
> Are you aware that this exposes you to the risk of losing all data
> modifications since your last (full or incremental) backup? Since you
> intend to take a full backup once daily, you might (in the worst case)
> lose a whole day's worth of data modifications.
>
>
> >Is this the correct order that I want to do the maintenance for the DB ?
> >1. Every evening, for about 30 minutes, keep users out of the database
> >(close the VB app that write to the db and retrieve from the DB)
> >2. Run VB app to delete all records that are 4 days or older
> >3. Run SQL Server Maintenance Plan to backup the database.
>
> I don't think it's even necessary to keep users out of the db. The delete
> operation and the insertion of new rows should not interfere with each
> other: they affect different rows and, since the date is also part of the
> index, different parts of the index. Read operations wouldn't block the
> delete either, unless selects are performed with specific option to keep
> the read rows locked after the select statement finishes execution.
>
> Both the delete and the inserts will be slower, of course. But if you lock
> the application that inserts rows, you'll either lose data or you'll have
> to store it someplace else and insert those rows later.
>
> SQL Server's backup utility is designed to work on a live database, so no
> need to lock out the users for the backup either.
>
>
> >For maintenance plan to backup the database, I select nothing in the
> >following tabs: "Optimizations", "Integrity" (since I only have 1 table in
> >the db), "Transaction Log Backup", and "Reporting".
> >In tab "Complete Backup", "Back up the database as part of the maintenance
> >plan" and "Verify the integrity of the backup upon completion" are checked. I
> >use "Default backup directory", and I schedule the backup to occur everyday
> >at 4:30 pm.
>
> Having only one table doesn't mean that you don't need to check database
> integrity. This is not about checking foreign key constraints, but about
> checking internal integrity: are all index pages linked properly, are
> there any broken pointer chains, are free pages really marked as free,
> etc.
>
>
> >Shall I "Remove files older than 4 days"?
>
> That depends on your needs. If you never need to restore older data, then
> you can safely delete the backups.
>
>
> >Is that maintenance plan consider a simple recovery model ?
>
> No. This maintenance is appropriate for a database in the simple recovery
> model (other recovery models really need to have a scheduled backup of the
> log as well; for simple recovery, full and optinally incremental backups
> suffice). But it does not set the recovery model.
>
> In Enterprise Manager, right-click the database and select "Properties".
> Go to the Options tag. In the section "Recovery", you should see a
> drop-down box labeled "Model". If this is not set to "Simple", change it
> and cllick "OK". That sets your database to use the simple recovery model.
>
>
> >So, by scheduling this maintenance plan to run everyday at 4:30 pm, the log
> >file
> >will grow in the beginning, until it has reached it's "normal" working size;
> >then it will
> >no longer grow but keep reusing free space ?
>
> Yes.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
- Next message: David Portas: "Re: stored procedure to copy a stored procedure to a different DB"
- Previous message: David Browne: "Re: Oracle Sequence in Sql Server"
- In reply to: Hugo Kornelis: "Re: How can I assign the result of dateadd to a variable ?"
- Next in thread: Hugo Kornelis: "Re: How can I assign the result of dateadd to a variable ?"
- Reply: Hugo Kornelis: "Re: How can I assign the result of dateadd to a variable ?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
