Archiving is Not reducing DB Size

I created few jobs that would archive the production DB and delete the archived data...
but it looks like the DB size is not reducing!!! Some times it looks like the size has increased!!

I think this is because of the log file size has increaded by the DELETE operations...But what can I do for this?

Please Help!!Action\All Tasks\Shrink Database in EM
or
DBCC SHRINKDATABASE in QA

Archive Database

Hi Everybody,
I'm working on a new database of 25GB in size with an expected 25% growth per year and an estimated 1000 TPS. Since I need to retain the old data for atleast 7 years. I would like to know whether I should Archive the database or is there any other means of storing the data, which will be used just to generate reports. Also pl. do let me know the advantages/disadvantages.

Thanks
KishoreYou said 7 years of total retention. But how old should the data be in your production environment? If 7 years as well, - you're looking at 200GB worth of data (I am yet to see an app that survives that long, but it's possible ;))|||archiving is dependent on your query requirements.
if your working set is getdate() - 6 months then archive
but if you report CONSISTENTLY all the way back. there obviously is no need to archive.
250GB is nothing to sneeze at but you can easily manage a db of that size with sql server 2k or any othe enterprise class db for that matter.

archiving really causes more headaches than it is worth.|||In production we need atleast 1 year old data and the rest can be archived which will be used to generate reports|||If 1 year is a working set then I'd recommed to have a reporting server where the archived data would be transferred to. You can also set this server as a linked server in your prod environment, and create additional views/stored procedures to have an easier access to the archived data without having to explicitly connect to a different server.

Applying Tran logs in SQL 2000

Hi,
I have a SQL 2k database thats around 200 GB in size. I have restored this
from a backup, but while restoring I failed to realise that I needed to apply
tran logs (didnt specify NORECOVERY) once the full backup was restored.
There are 6 tran log files to apply now.
Can I do this without once again restoring the database (with NORECOVERY)? I
am asking this because it will save a lot of time.
Thank you.
Regards,
Karthik
No, once you did RECOVERY, you can't restore any more backups, since SQL Server performed the UNDO
phase. The other way around is possible, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:DAC80787-AC08-47B8-8046-CCF6DD770639@.microsoft.com...
> Hi,
> I have a SQL 2k database thats around 200 GB in size. I have restored this
> from a backup, but while restoring I failed to realise that I needed to apply
> tran logs (didnt specify NORECOVERY) once the full backup was restored.
> There are 6 tran log files to apply now.
> Can I do this without once again restoring the database (with NORECOVERY)? I
> am asking this because it will save a lot of time.
> Thank you.
> Regards,
> Karthik
|||Hi,
You cant. You need to start the full database restore again with NORECOVERY
option.
SQL Server requires that the WITH NORECOVERY option be used on all but the
final RESTORE statement when restoring a database
backup and multiple transaction logs. NORECOVERY Instructs the restore
operation to not roll back any uncommitted transactions
Thanks
Hari
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:DAC80787-AC08-47B8-8046-CCF6DD770639@.microsoft.com...
> Hi,
> I have a SQL 2k database thats around 200 GB in size. I have restored this
> from a backup, but while restoring I failed to realise that I needed to
> apply
> tran logs (didnt specify NORECOVERY) once the full backup was restored.
> There are 6 tran log files to apply now.
> Can I do this without once again restoring the database (with NORECOVERY)?
> I
> am asking this because it will save a lot of time.
> Thank you.
> Regards,
> Karthik

Applying Tran logs in SQL 2000

Hi,
I have a SQL 2k database thats around 200 GB in size. I have restored this
from a backup, but while restoring I failed to realise that I needed to appl
y
tran logs (didnt specify NORECOVERY) once the full backup was restored.
There are 6 tran log files to apply now.
Can I do this without once again restoring the database (with NORECOVERY)? I
am asking this because it will save a lot of time.
Thank you.
Regards,
KarthikNo, once you did RECOVERY, you can't restore any more backups, since SQL Ser
ver performed the UNDO
phase. The other way around is possible, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:DAC80787-AC08-47B8-8046-CCF6DD770639@.microsoft.com...
> Hi,
> I have a SQL 2k database thats around 200 GB in size. I have restored this
> from a backup, but while restoring I failed to realise that I needed to ap
ply
> tran logs (didnt specify NORECOVERY) once the full backup was restored.
> There are 6 tran log files to apply now.
> Can I do this without once again restoring the database (with NORECOVERY)?
I
> am asking this because it will save a lot of time.
> Thank you.
> Regards,
> Karthik|||Hi,
You cant. You need to start the full database restore again with NORECOVERY
option.
SQL Server requires that the WITH NORECOVERY option be used on all but the
final RESTORE statement when restoring a database
backup and multiple transaction logs. NORECOVERY Instructs the restore
operation to not roll back any uncommitted transactions
Thanks
Hari
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:DAC80787-AC08-47B8-8046-CCF6DD770639@.microsoft.com...
> Hi,
> I have a SQL 2k database thats around 200 GB in size. I have restored this
> from a backup, but while restoring I failed to realise that I needed to
> apply
> tran logs (didnt specify NORECOVERY) once the full backup was restored.
> There are 6 tran log files to apply now.
> Can I do this without once again restoring the database (with NORECOVERY)?
> I
> am asking this because it will save a lot of time.
> Thank you.
> Regards,
> Karthik

Applying Tran logs in SQL 2000

Hi,
I have a SQL 2k database thats around 200 GB in size. I have restored this
from a backup, but while restoring I failed to realise that I needed to apply
tran logs (didnt specify NORECOVERY) once the full backup was restored.
There are 6 tran log files to apply now.
Can I do this without once again restoring the database (with NORECOVERY)? I
am asking this because it will save a lot of time.
Thank you.
Regards,
KarthikNo, once you did RECOVERY, you can't restore any more backups, since SQL Server performed the UNDO
phase. The other way around is possible, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:DAC80787-AC08-47B8-8046-CCF6DD770639@.microsoft.com...
> Hi,
> I have a SQL 2k database thats around 200 GB in size. I have restored this
> from a backup, but while restoring I failed to realise that I needed to apply
> tran logs (didnt specify NORECOVERY) once the full backup was restored.
> There are 6 tran log files to apply now.
> Can I do this without once again restoring the database (with NORECOVERY)? I
> am asking this because it will save a lot of time.
> Thank you.
> Regards,
> Karthik|||Hi,
You cant. You need to start the full database restore again with NORECOVERY
option.
SQL Server requires that the WITH NORECOVERY option be used on all but the
final RESTORE statement when restoring a database
backup and multiple transaction logs. NORECOVERY Instructs the restore
operation to not roll back any uncommitted transactions
Thanks
Hari
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:DAC80787-AC08-47B8-8046-CCF6DD770639@.microsoft.com...
> Hi,
> I have a SQL 2k database thats around 200 GB in size. I have restored this
> from a backup, but while restoring I failed to realise that I needed to
> apply
> tran logs (didnt specify NORECOVERY) once the full backup was restored.
> There are 6 tran log files to apply now.
> Can I do this without once again restoring the database (with NORECOVERY)?
> I
> am asking this because it will save a lot of time.
> Thank you.
> Regards,
> Karthik

Application Log - Overwrite as Needed

In the event viewer: In Application Log properties, there are options on
what to do "When maximum log size is reached." I set it to "Overwrite as
Needed", but the next day it is changed to "Overwrite Events Older than 5
Days."
Why does it do this and how can I make it stop? It has been going on for
several months.Sheila,
If it is possible, perhaps a Group Policy at a higher level could be making
this change.
HTH
Jerry
"Sheila" <Sheila@.discussions.microsoft.com> wrote in message
news:E4ADF2FF-F543-4FC6-A2E4-8CB0E717850F@.microsoft.com...
> In the event viewer: In Application Log properties, there are options on
> what to do "When maximum log size is reached." I set it to "Overwrite as
> Needed", but the next day it is changed to "Overwrite Events Older than 5
> Days."
> Why does it do this and how can I make it stop? It has been going on for
> several months.
>

Application Log - Overwrite as Needed

In the event viewer: In Application Log properties, there are options on
what to do "When maximum log size is reached." I set it to "Overwrite as
Needed", but the next day it is changed to "Overwrite Events Older than 5
Days."
Why does it do this and how can I make it stop? It has been going on for
several months.
Sheila,
If it is possible, perhaps a Group Policy at a higher level could be making
this change.
HTH
Jerry
"Sheila" <Sheila@.discussions.microsoft.com> wrote in message
news:E4ADF2FF-F543-4FC6-A2E4-8CB0E717850F@.microsoft.com...
> In the event viewer: In Application Log properties, there are options on
> what to do "When maximum log size is reached." I set it to "Overwrite as
> Needed", but the next day it is changed to "Overwrite Events Older than 5
> Days."
> Why does it do this and how can I make it stop? It has been going on for
> several months.
>

Application Log - Overwrite as Needed

In the event viewer: In Application Log properties, there are options on
what to do "When maximum log size is reached." I set it to "Overwrite as
Needed", but the next day it is changed to "Overwrite Events Older than 5
Days."
Why does it do this and how can I make it stop? It has been going on for
several months.Sheila,
If it is possible, perhaps a Group Policy at a higher level could be making
this change.
HTH
Jerry
"Sheila" <Sheila@.discussions.microsoft.com> wrote in message
news:E4ADF2FF-F543-4FC6-A2E4-8CB0E717850F@.microsoft.com...
> In the event viewer: In Application Log properties, there are options on
> what to do "When maximum log size is reached." I set it to "Overwrite as
> Needed", but the next day it is changed to "Overwrite Events Older than 5
> Days."
> Why does it do this and how can I make it stop? It has been going on for
> several months.
>

append property value

i am using sp_OASetProperty for setting property value for a cdo.message
object. But it might happen body size is more than 8000 character... in that
case i want to write follwoing statement in a loopHi
I don't think you can do this, have you looked at xp_smtp?
http://www.sqldev.net/xp/xpsmtp.htm
John
"Vikram" wrote:

> i am using sp_OASetProperty for setting property value for a cdo.message
> object. But it might happen body size is more than 8000 character... in th
at
> case i want to write follwoing statement in a loop
>
>