Purging, Delete or Archive

Hi All,

I have a database that has grown to over 6 GB in the last week. This database comes with a third party application tool that was implemented in our company over 3 months ago. My team tells me it's a SQL database, and to correct the current situation we'll need to purge the database or archive the records to a different server.If we purge the records will we be able to retrieve it later if needed?Why would I purge and not archive.

Excuse my questions but we don’t have a DBA on broad and I would like to better understand options we are faced with.

Well D, we don't have DBA's on broads here either (HR would frow on that!)... Anyways, the word PURGE means it's gone... now if you archive off to a different server and TEHn purge from your main database, then you have it on the archive database... You sure the row count is high, and it's not a large file because of large autogrowths? that type of thing? Look at teh database and see how large the actual USED space is... If you are using 6GB, maybe it needs to be reviewd as to why it's taking up all those rows? Might just need a shrink... Bruce|||

Don't forget to check the log file. Make sure it's backed up on a regular basis if you want the log files, or else check the box for Truncate on Checkpoint. You didn't say how large the file was 3 weeks ago to the 6 GB is is now, but check the autogrowth and the log file.

Thanks,

Angel

|||

Is the 6GB really a problem? Unless the problem is as described in the posts above and you are having 'autogrowth' issues this should not be a problem if you have the right hardware and a properly designed application.

I would never purge data unless I was absolutely sure I NEVER EVER needed it again.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Thank you all for your helpful response.

Yes the 6GB is a problem, and the time frame it took to grow to 6 GB. We are having hardware availability issues right now. We hope to rectify this problem by the end of the year.

If purging the data really boils down to permanently deleting it from the db...I don't think it the way to go. I am not sure if we'll ever need this data in the future.

Thanks,

Devon Brooks

Purging, Delete or Archive

Hi All,

I have a database that has grown to over 6 GB in the last week. This database comes with a third party application tool that was implemented in our company over 3 months ago. My team tells me it's a SQL database, and to correct the current situation we'll need to purge the database or archive the records to a different server.If we purge the records will we be able to retrieve it later if needed?Why would I purge and not archive.

Excuse my questions but we don’t have a DBA on broad and I would like to better understand options we are faced with.

Well D, we don't have DBA's on broads here either (HR would frow on that!)... Anyways, the word PURGE means it's gone... now if you archive off to a different server and TEHn purge from your main database, then you have it on the archive database... You sure the row count is high, and it's not a large file because of large autogrowths? that type of thing? Look at teh database and see how large the actual USED space is... If you are using 6GB, maybe it needs to be reviewd as to why it's taking up all those rows? Might just need a shrink... Bruce|||

Don't forget to check the log file. Make sure it's backed up on a regular basis if you want the log files, or else check the box for Truncate on Checkpoint. You didn't say how large the file was 3 weeks ago to the 6 GB is is now, but check the autogrowth and the log file.

Thanks,

Angel

|||

Is the 6GB really a problem? Unless the problem is as described in the posts above and you are having 'autogrowth' issues this should not be a problem if you have the right hardware and a properly designed application.

I would never purge data unless I was absolutely sure I NEVER EVER needed it again.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Thank you all for your helpful response.

Yes the 6GB is a problem, and the time frame it took to grow to 6 GB. We are having hardware availability issues right now. We hope to rectify this problem by the end of the year.

If purging the data really boils down to permanently deleting it from the db...I don't think it the way to go. I am not sure if we'll ever need this data in the future.

Thanks,

Devon Brooks

Purging Transaction Log

I am trying to purge the logfile (ldf) for our database.
I thought I had done it in the past through Enterprise
Manager by clicking the database, selecting
AllTasks/Shrink Database and then reducing the size of the
transaction log to its minimum. The logfile is currently
150 MB. I specified to shrink it to the minimum (8 MB).
It told me that it succeeded but the size is not changing.
Is this the appropriate way to reduce the log size?
THanks,
-RobYou must back up the log if you are in Full Recovery Mode prior to
shrinking...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rob C" <robnocspam@.ctnosoftwarespam.com> wrote in message
news:0a4701c3bf26$c8d635d0$a401280a@.phx.gbl...
> I am trying to purge the logfile (ldf) for our database.
> I thought I had done it in the past through Enterprise
> Manager by clicking the database, selecting
> AllTasks/Shrink Database and then reducing the size of the
> transaction log to its minimum. The logfile is currently
> 150 MB. I specified to shrink it to the minimum (8 MB).
> It told me that it succeeded but the size is not changing.
> Is this the appropriate way to reduce the log size?
> THanks,
> -Rob|||Hi Rob,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Our MVP, Wayne had provided a good explanation of how to shrink the log
file.
I just want to add some more information of this part.
When SQL Server finishes backing up the transaction log, it automatically
truncates the inactive portion of the transaction log. This inactive
portion contains completed transactions and so is no longer used during the
recovery process. Conversely, the active portion of the transaction log
contains transactions that are still running and have not yet completed.
SQL Server reuses this truncated, inactive space in the transaction log
instead of allowing the transaction log to continue to grow and use more
space. That is why although you use Enterprise Manager to set the log file
size, but it remains the same. You shoud backup it first to let it become
inactive.
Although the transaction log may be truncated manually, it is strongly
recommended that you do not do this, as it breaks the log backup chain.
Until a full database backup is created, the database is not protected from
media failure. Use manual log truncation only in very special
circumstances, and create a full database backup as soon as practical.
The ending point of the inactive portion of the transaction log, and hence
the truncation point, is the earliest of the following events:
1)The most recent checkpoint.
2)The start of the oldest active transaction, which is a transaction that
has not yet been committed or rolled back.
This represents the earliest point to which SQL Server would have to roll
back transactions during recovery.
3)The start of the oldest transaction that involves objects published for
replication whose changes have not been replicated yet. This represents the
earliest point that SQL Server still has to replicate.
You can refer to SQL Server Books on Line (BOL) for more information for
active portion and inactive portion of log files and some DBCC commands to
carry out log truncation by searching :
"Truncating the Transaction Log" in the BOL
I hope will be helpful to resolve your question. If you still have any
questions, please feel free to post any new message here, I am ready to
offer you further help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you both fo you replies.
I was able to successfully shrink the log once I ran the
backup process on the transaction logfile.
I also appreciated the explanation behind it.
Regards,
-Rob
>--Original Message--
>Hi Rob,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>Our MVP, Wayne had provided a good explanation of how to
shrink the log
>file.
>I just want to add some more information of this part.
>When SQL Server finishes backing up the transaction log,
it automatically
>truncates the inactive portion of the transaction log.
This inactive
>portion contains completed transactions and so is no
longer used during the
>recovery process. Conversely, the active portion of the
transaction log
>contains transactions that are still running and have not
yet completed.
>SQL Server reuses this truncated, inactive space in the
transaction log
>instead of allowing the transaction log to continue to
grow and use more
>space. That is why although you use Enterprise Manager to
set the log file
>size, but it remains the same. You shoud backup it first
to let it become
>inactive.
>Although the transaction log may be truncated manually,
it is strongly
>recommended that you do not do this, as it breaks the log
backup chain.
>Until a full database backup is created, the database is
not protected from
>media failure. Use manual log truncation only in very
special
>circumstances, and create a full database backup as soon
as practical.
>The ending point of the inactive portion of the
transaction log, and hence
>the truncation point, is the earliest of the following
events:
>1)The most recent checkpoint.
>2)The start of the oldest active transaction, which is a
transaction that
>has not yet been committed or rolled back.
>This represents the earliest point to which SQL Server
would have to roll
>back transactions during recovery.
>3)The start of the oldest transaction that involves
objects published for
>replication whose changes have not been replicated yet.
This represents the
>earliest point that SQL Server still has to replicate.
>You can refer to SQL Server Books on Line (BOL) for
more information for
>active portion and inactive portion of log files and some
DBCC commands to
>carry out log truncation by searching :
>"Truncating the Transaction Log" in the BOL
>I hope will be helpful to resolve your question. If you
still have any
>questions, please feel free to post any new message here,
I am ready to
>offer you further help.
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||How does one manually truncate a transaction log?|||Hi Gayle,
Thank you for using MSDN Newsgroup!
You can manually truncate the trasaction log. Please refer to the following
article for detailed information:
http://support.microsoft.com/?id=272318
If you still have question, please post new message here and I am ready to
help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for replying! The drive where the transaction log resides -
drive L - ran out of space (the transaction log is 97GB). This has,
naturally, made it to where we cannot access our databases through
Enterprise Manager. When we try to open Query Analyzer, it times out
but then still shows a query window with the Master database selected,
but we cannot change from the Master to any of our application's
databases. We tried to do the following via Query Analyzer anyway:
backup database (to the S drive, which had space)
backup log with truncate_only
dbcc shrinkfile on the log file
backup database
We let it run ("Processing query batch") for an hour before we stopped
it. We assume that it didn't work because it was trying to write to
the transaction log but couldn't. Is that correct? Does SQL write
administrative queries (like backup statements) to the transaction
log? We know now what we need to do to prevent the transaction log
from outgrowing its space and we're going to try to set up an alert so
that SQL will page us when the log gets too big.
I guess what I'd like to know is, should our preventive measures fail
and the log's drive fill up again, what is the best approach to take?
Thank you for any info you can share.sql

Purging the transmission queue

My transmission queue has lots of messages that will never, ever be delivered because the transmission_status = "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

How can I purge the transmission queue to get rid of this junk?

END CONVERSATION @.dh WITH CLEANUP will purge the messages of the conversation with the conversation_handle '@.dh'

HTH,
~ remus

Purging the SQL Server Log

I am required by my customer to audit all logins to the SQL Server database. I have the Audit Level set to 'All'. What I have noticed is that SQL Agent is constantly logging in which is rapidly bloating the size of the ERRORLOG. It's now at 90MB, but once it gets beyond 12 MB it becomes pretty much unusable as a tool.

Is there some way to eliminate SQL Agent from the Audit process?

Alternatively, is there some way to "roll" the log (much like SQL Server does at start-up) while SQL Server is still running?

Any input is welcome.

Regards,

Hugh Scottwhat version of SQL Server are you running? If 2k then try sp_cycle_errorlog.

I am not aware of a way to exclude a user's activity from the audit process.|||Thank you sir!!

Hugh Scott

Originally posted by Paul Young
what version of SQL Server are you running? If 2k then try sp_cycle_errorlog.

I am not aware of a way to exclude a user's activity from the audit process.

Purging TempDB

Hi DB Experts

I have a question on shrinking tempDB. Currently, I am using MS SQL 2005 Server, I have a software that used SQL DB but I had created a separate database to store my data. The tempDB is totally not used at all.

Problem - Whenever I export data into my created database on MS SQL server, tempDB also grew, I noticed that the files grew so large that it crashed the server. I am running 50GB free space on my drive where by the MS SQL server was installed.

Question - May I know are there any solution to shrink or freeze the growth of tempDB size?

Best regards

TEWCT

This statement

The tempDB is totally not used at all.

is totally wrong!

TempDb is constantly used as a staging area (or 'scratchpad') when gathering data for queries.

TempDb will continue to grow as long as there is a need. You can take the server off line (or stop the server service) and remove the TempDb database, and it will be recreated when the server starts up.

|||

You should in fact size your tempdb appropriately to increase performance.

If the export is causing your tempdb to grow to 50GB you should carefully look at the queryplans used in the export (or you are exporting a massive amount of data).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Here's some more info on how to properly plan for TempDB sizing and also how to troubleshoot when it grows too large. The second link also includes instructions on how to limit the size of TempDB.

http://msdn2.microsoft.com/en-us/library/ms176029(sql.90).aspx

http://msdn2.microsoft.com/en-us/library/ms175527(SQL.90).aspx

Hope that helps,

-Steve

|||

Hi Steve and guys

Greetings. Thks for your advices & responses. Apologized, I am not a SQL guru. Am I right to say tempDB will definitely be used even if I created a separated DB for my application; even when I perform any simple query or update? My growth rate is about 500MB per day which I have been monitoring it over 3 days for now.

I had also performed some research from MSDN library that Steve had adviced, it seemed that it is a Microsoft methodology that SQL server behaviour;

i) TempDB size will definitely grow;

ii) DB tuning is required

iii) Factor more disk space for the growth?

iv) Restarting SQL service on regular basis

I would like to know if there is any specific way other then to re-provision and solve this SQL server tempDB growing issue?

|||

In a production server, the growth of TempDb 'should' eventually stablize. There may be regularly scheduled reports, etc., that require a large amount of space. There may be 'regular' data import/exports (ETL) activities that will require a large amount of space.

After your database is in production, and the initial 'load' activity has subsided, you may be able to reduce the size of TempDb by restarting the SQL Service -but if there is extensive data querying for reports or ETL activities, it may just grow again. So give it time, determine the 'appropriate' size, and let it be.

|||

Also have a look at this article:

How to shrink the tempdb database in SQL Server
http://support.microsoft.com/kb/307487/en-us

|||Hi guys. Thanks for your information. I will try that out and update at a later stage.

Purging SQL Server Log Files

I am currently maintaining a database at work that somehow has a
transaction log of 20 GB. Is there an easy way to purge this log
file?
Thanks,
JeffCertainly :-) Have a look at
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256650
To understand why it has grown check out
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q110139
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...
> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff|||Hi,
This is because of the recovary model you selected, if the database is
defined as "FULL" recovary, you may need to perform a Transaction log backup
using (Backup Log) command, otherwise the Transaction log file records and
wont clear the Logs and it grows to higher limit incase you are not limiting
the transaction log size.
In your case do,
1. Change the recovary model to Simple
2. Run "Backup log dbname with no_log"
3. Use dbcc shrinkfile command to shrink the log file
4. Change the recovary model to "FULL"
5. Schedule a Backup log command atleast twice a day .
Thanks
Hari
MCDBA
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...
> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff

Purging SQL Server Log Files

I am currently maintaining a database at work that somehow has a
transaction log of 20 GB. Is there an easy way to purge this log
file?
Thanks,
JeffCertainly :-) Have a look at
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...b;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...b;en-us;Q256650
To understand why it has grown check out
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...b;en-us;Q110139
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/defaul...kb;EN-US;317375
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...

quote:

---

> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff

---

|||Hi,
This is because of the recovary model you selected, if the database is
defined as "FULL" recovary, you may need to perform a Transaction log backup
using (Backup Log) command, otherwise the Transaction log file records and
wont clear the Logs and it grows to higher limit incase you are not limiting
the transaction log size.
In your case do,
1. Change the recovary model to Simple
2. Run "Backup log dbname with no_log"
3. Use dbcc shrinkfile command to shrink the log file
4. Change the recovary model to "FULL"
5. Schedule a Backup log command atleast twice a day .
Thanks
Hari
MCDBA
"Jeff" <jeffpuro@.yahoo.com> wrote in message
news:7851a310.0401121127.2d279759@.posting.google.com...

quote:

---

> I am currently maintaining a database at work that somehow has a
> transaction log of 20 GB. Is there an easy way to purge this log
> file?
> Thanks,
> Jeff

---

sql

purging old tranlogs

hi !
I have a job that creates tranlog backups every 15 mins and makes a full database backup at midnight.
I want to purge the old logs after the full backup , how can I do that ?
thanks
SamiHave you considered instead using the Database Maintenance Wizard? You can suppy a value to specify how long to retain both full backups and transaction log backups. It won't precisely meet your requirements since unneeded transaction logs will be retained even after a full backup, but it is simple to create, easy to maintain and easy for anyone to understand (since the database maintenance wizard is pretty well documented in MS texts).

I generally create two maintenance plans: one for system databases (where transaction logs do not need to be backed up) and one for user databases (where transaction logs are backed up).

Regards,

hmscott

hi !
I have a job that creates tranlog backups every 15 mins and makes a full database backup at midnight.
I want to purge the old logs after the full backup , how can I do that ?

thanks
Sami|||The maintenance wizard simply creates a job the class the xp_sqlmaint procedure. Frankly, you are better off bypassing the wizard and creating a the job yourself. You can look up all the parameters available (including purging old files) in Books Online under "sqlmaint Utility".

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnHi John
You could use SET ROWCOUNT 100 before issuing the delete statement. i.e. to
delete in blocks of 100
DECLARE @.earlierstdate datetime
SET @.earlierstdate = getdate()-90
SET ROWCOUNT 100
delete [<table>] where date < @.earlierstdate
WHILE @.@.ROWCOUNT > 0
delete [<table>] where date < @.earlierstdate
SET ROWCOUNT 0
John
"John J. Hughes II" wrote:

> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
>|||John J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.

> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...[vbcol=seagreen]
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>|||Hi John
"John J. Hughes II" wrote:

> John,
> Guess I did not see it before, thanks. By the way the BOL says to use top
> in new development since ROWCOUNT will no longer be supported. If I
> understand correctly then it should be the following:
> delete top(100) [<table>] where data < @.earlierstdate;
>
TOP is only for SQL 2005.

> I was thinking of putting this in a JOB set to when idle Do you think th
is
> would cause too much thrashing or would it be better to put it as you do b
ut
> with a waitfor delay. It would be nice to allow it to run until the serv
er
> became busy and then exit until next idle.
> Something like:
> while(@.@.rowcount > 0 and @.@.cpu < (something)
> delete top(100) [<table>] where data < @.earlierstdate;
> FROM BOL:
> Important:
> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements i
n
> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
> INSERT, and UPDATE statements in new development work, and plan to modify
> applications that currently use it. We recommend that DELETE, INSERT, and
> UPDATE statements that currently are using SET ROWCOUNT be rewritten to us
e
> TOP.
>
How you implement it will depend on how long your quiet periods are and how
many records you are deleting. I would probably start of with a single job
but delting in batches of 5000 (say) and then see if it is an issue. If you
have a policy of only keeping n days then rather than doing that as a monthy
process I would do it more gradually (say daily or weekly).
Make sure you do this deletion before you re-index.
If you do manage to get the system onto SQL 2005 then I would look at using
table partitions.
There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't think
they will be useful to you.
HTH

> regards,
> John
>
John|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>
> TOP is only for SQL 2005.
>
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>
> John
>

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnJohn J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date >
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much. I
> was hoping for something like the above but will a limit of say 100 records
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> > getdate()-90". The problem is this really puts a load on the system.
>> I would like this to be an idle job that does not load the system as
>> much. I was hoping for something like the above but will a limit of say
>> 100 records each time, is that possible?
>> Regards,
>> John
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> >
>> getdate()-90". The problem is this really puts a load on the system.
>> I
>> would like this to be an idle job that does not load the system as much.
>> I
>> was hoping for something like the above but will a limit of say 100
>> records
>> each time, is that possible?
>> Regards,
>> John
>>|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>> John,
>> Guess I did not see it before, thanks. By the way the BOL says to use
>> top
>> in new development since ROWCOUNT will no longer be supported. If I
>> understand correctly then it should be the following:
>> delete top(100) [<table>] where data < @.earlierstdate;
> TOP is only for SQL 2005.
>> I was thinking of putting this in a JOB set to when idle Do you think
>> this
>> would cause too much thrashing or would it be better to put it as you do
>> but
>> with a waitfor delay. It would be nice to allow it to run until the
>> server
>> became busy and then exit until next idle.
>> Something like:
>> while(@.@.rowcount > 0 and @.@.cpu < (something)
>> delete top(100) [<table>] where data < @.earlierstdate;
>> FROM BOL:
>> Important:
>> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements
>> in
>> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
>> INSERT, and UPDATE statements in new development work, and plan to modify
>> applications that currently use it. We recommend that DELETE, INSERT, and
>> UPDATE statements that currently are using SET ROWCOUNT be rewritten to
>> use
>> TOP.
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>> regards,
>> John
> John
>

Purging Log File

How can I reset a log file for a database that is active. The file is 8
gigs. I want to restrict its growth to 50 MB and purge the contents.
How can I do this.General info on file shrinking as well as link to shrinking log file size:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ross B." <ross@.__computaught__.com> wrote in message
news:u%2382JFQDEHA.1600@.tk2msftngp13.phx.gbl...
> How can I reset a log file for a database that is active. The file is 8
> gigs. I want to restrict its growth to 50 MB and purge the contents.
> How can I do this.
>

Purging data off of an MSDE database.

How do I purge data off of an MSDE database. I only want to keep 6 months of data in the database. Right now I have data going back to 2004. I get errors about every 10 seconds. "Primary File Group is Full" is the error I am getting.

Hi,

you may archive it to another database/server, refer http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8846 http://sqljunkies.com/Forums/ShowPost.aspx?PostID=1855 and http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic814.aspx for "Primary File Group is Full" error.

Hemantgiri S. Goswami

|||

Hi,

So basically what you are telling me is that there is no way to purge data off an MSDE database?

---

|||hi;

if you dont want to extend your primary group and you want to delete the old data you will have to implement some logic, assuming that the table contain all the same field for saving the data where data has to be filtered, I would create a script to run within a job. If you want to automate the script creation you can do something like:

SELECT 'DELETE FROM ' + '[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + '] WHERE SomeDateColumn >= DATEADD(m,-6,GETDATE())'

FROM INFORMATION_SCHEMA.TABLES

WHERE OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'), 'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Travis H wrote:

Hi,

So basically what you are telling me is that there is no way to purge data off an MSDE database?

---

definately their is a way to purge off the data as explained by Jens but i thought if you would like to archive it rather then purging, thus i spoted some links FYI.

Hemantgiri S. Goswami

|||Depending on your version and edition you could also use horizantal partitioning which would move the older records to the other partition on probably another file group.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

Purging data from database every 3 months

Every 3 months I need to backup that sql database and archive it off. I only want to keep 3 months of data in the database and want to know how you can set a backup to run and then remove all the data older than a specified date. I am guessing you will have to back it all up then run a query to remove the data I dont want. ANy ideas if this can be done with a simple backup routine ?You can't purge the old data using BACKUP. You have to execute TSQL commands to delete the old data,
which in turn of course mean that the data need some datetime column from which you can determine if
it is > 3 months old. Basically, the TSQL script will look something like:
DECLARE @.now datetime
SET @.now = CURRENT_TIMESTAMP
DELETE FROM tbl1 WHERE DATEDIFF(month, dtcol, @.now) > 3
DELETE FROM tbl2 WHERE DATEDIFF(month, dtcol, @.now) > 3
Make sure you check that I get the DATEDIFF right. Also, you might want to protect the delete's
inside a transaction. And if you have relationships between the tables you need to start with the
referencing table before you can delete from the referenced table (unless you have cascading foreign
keys). If you aren't well versed in TSQL, I suggest you get someone who is which can help you with
that TSQL script.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"c1tr1cks" <anonymous@.discussions.microsoft.com> wrote in message
news:EED1D1C0-4A93-4822-AB09-1CDEBCBA6277@.microsoft.com...
> Every 3 months I need to backup that sql database and archive it off. I only want to keep 3 months
of data in the database and want to know how you can set a backup to run and then remove all the
data older than a specified date. I am guessing you will have to back it all up then run a query to
remove the data I dont want. ANy ideas if this can be done with a simple backup routine ?|||You can see an example here:
http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"c1tr1cks" <anonymous@.discussions.microsoft.com> wrote in message
news:EED1D1C0-4A93-4822-AB09-1CDEBCBA6277@.microsoft.com...
Every 3 months I need to backup that sql database and archive it off. I only
want to keep 3 months of data in the database and want to know how you can
set a backup to run and then remove all the data older than a specified
date. I am guessing you will have to back it all up then run a query to
remove the data I dont want. ANy ideas if this can be done with a simple
backup routine ?

Purging Data Cache

How do I go abouts purging the data cache for a particular database? Is there a stored procedure that can be executed??Try the :

DBCC DROPCLEANBUFFERS -- for clear out the buffer cache
DBCC FREEPROCCACHE -- to clear out the procedure cache.

Worse, just shutdown & restart the server - I have to post a waiver for this :-)

SVTsql

Purging a database data while keeping the structure intact

Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis? If I simply delete all the records the identities for the tables do not revert back to 1.

You can use the TRUNCATE TABLE <tableName> statement, but there are some restrictions (e.g. can't use it on a table referenced by a foreign key, Books Online has the full list of restrictions). If you have to use delete, you can update the identity value with the DBCC CHECKIDENT statement (e.g. DBCC CHECKIDENT ('tableName', RESEED, 0)).

Hope that helps.

Aaron

|||Thanks for your quick respond.