Sql Public Role: purge

Showing posts with label purge. Show all posts

Friday, March 23, 2012

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 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

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 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 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.

Wednesday, March 21, 2012

Purge Transaction Log File

My database file is growing and growing and growing. This is good...
The bad news, at least for storage limitations, is the Transaction log file
is growing significantly faster. At the present time my data file is around
150 MB and the Transaction log file is just under 10GB......
Do I need to let the Transaction log file grow or is there a way to prevent
the file from getting to large? Any suggestions on solving this problem are
greatly appreciated.
WByou have to back up the transaction log to keep it from growing for ever.
OR Put your Database in "Simple Recovery Mode"
Greg Jackson
PDX, OR|||Ta add to Greg's response, the proper database recovery model depends on
your recovery requirements. If your plan is to simply restore from your
last backup, you should use the SIMPLE model. Committed data will be
removed from the log automatically to keep your log size reasonable.
If you need to further minimize potential data loss, you need to use the
FULL or BULK_LOGGED recovery model and schedule regular log backups between
database backups. The log backups will provide the means for forward
recovery following a database restore and also remove committed data from
the log.
You can read more about recovery models in the Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>|||I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>|||When you do a log backup, the log file(s) will be emptied. It is not normal to do BACKUP LOG WITH
TRUNCATE_ONLY in such scenario (this will break your chain of log backups!!!) nor to shrink file log
file (http://www.karaszi.com/SQLServer/info_dont_shrink.asp). You should investigate *why* the log
keep growing despite your regular log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is good...
>> The bad news, at least for storage limitations, is the Transaction log file
>> is growing significantly faster. At the present time my data file is around
>> 150 MB and the Transaction log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way to prevent
>> the file from getting to large? Any suggestions on solving this problem are
>> greatly appreciated.
>> WB
>>
>|||Thank you for all the input. I was able to backup the log file and then
shrink it down to under 100MB. The problem now is I can't set the max file
size to say 2GB because the file size is still 9GB. How can I prevent the
file from growing beyond 2GB'
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
file
> is growing significantly faster. At the present time my data file is
around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
prevent
> the file from getting to large? Any suggestions on solving this problem
are
> greatly appreciated.
> WB
>|||"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
>I had the same problem.
> I must have my database in FULL recovery model, and my log file growes
> larger and larger.
> Except normaly daily backup (and hourly incremantaly too )
> Ones a week I make the following step:
> 1) Make backup of my database.
> 2) than do:
> backup log mydatabasename with truncate_only
> dbcc shrinkdatabase('mydatabasename')
> 3) Make again backup of my shrink database
> On course, I put this steps in DTS package and put in Agent.
Why do you have Full recovery model if you're truncating the logs? It
doesn't make any sense. Put the recovery model to simple and you will gest
the same result, but the cheaper way.
Best regards
Wojtek|||I don't understand. You say you shrunk it down to 100MB. Then you say the file is still 9GB?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> Thank you for all the input. I was able to backup the log file and then
> shrink it down to under 100MB. The problem now is I can't set the max file
> size to say 2GB because the file size is still 9GB. How can I prevent the
> file from growing beyond 2GB'
>
> "WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is good...
>> The bad news, at least for storage limitations, is the Transaction log
> file
>> is growing significantly faster. At the present time my data file is
> around
>> 150 MB and the Transaction log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way to
> prevent
>> the file from getting to large? Any suggestions on solving this problem
> are
>> greatly appreciated.
>> WB
>>
>|||Yes, There are two listings for the transaction file.
1. Current size = ~ 9GB
2. Space used = ~100MB
I am referencing the details listed under Shrink Database -> files
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> I don't understand. You say you shrunk it down to 100MB. Then you say the
file is still 9GB?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> > Thank you for all the input. I was able to backup the log file and then
> > shrink it down to under 100MB. The problem now is I can't set the max
file
> > size to say 2GB because the file size is still 9GB. How can I prevent
the
> > file from growing beyond 2GB'
> >
> >
> > "WB" <none> wrote in message
news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> >> My database file is growing and growing and growing. This is good...
> >> The bad news, at least for storage limitations, is the Transaction log
> > file
> >> is growing significantly faster. At the present time my data file is
> > around
> >> 150 MB and the Transaction log file is just under 10GB......
> >>
> >> Do I need to let the Transaction log file grow or is there a way to
> > prevent
> >> the file from getting to large? Any suggestions on solving this
problem
> > are
> >> greatly appreciated.
> >>
> >> WB
> >>
> >>
> >
> >|||I see. I don't use the EM GUI much, but I assume it means that the file size is 9GB and it is only
used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might require a few BACKUP,
SHRINK, BACKUO, SHRINK iteration where you monitor the status between using DBCC LOGINFO. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"WB" <none> wrote in message news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> Yes, There are two listings for the transaction file.
> 1. Current size = ~ 9GB
> 2. Space used = ~100MB
> I am referencing the details listed under Shrink Database -> files
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
>> I don't understand. You say you shrunk it down to 100MB. Then you say the
> file is still 9GB?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "WB" <none> wrote in message news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
>> > Thank you for all the input. I was able to backup the log file and then
>> > shrink it down to under 100MB. The problem now is I can't set the max
> file
>> > size to say 2GB because the file size is still 9GB. How can I prevent
> the
>> > file from growing beyond 2GB'
>> >
>> >
>> > "WB" <none> wrote in message
> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> >> My database file is growing and growing and growing. This is good...
>> >> The bad news, at least for storage limitations, is the Transaction log
>> > file
>> >> is growing significantly faster. At the present time my data file is
>> > around
>> >> 150 MB and the Transaction log file is just under 10GB......
>> >>
>> >> Do I need to let the Transaction log file grow or is there a way to
>> > prevent
>> >> the file from getting to large? Any suggestions on solving this
> problem
>> > are
>> >> greatly appreciated.
>> >>
>> >> WB
>> >>
>> >>
>> >
>> >
>|||ok, thanks for staying with the thread.
WB
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23ONr3GbFHA.616@.TK2MSFTNGP12.phx.gbl...
> I see. I don't use the EM GUI much, but I assume it means that the file
size is 9GB and it is only
> used 100MB. You need to shrink the file using DBCC SHRINKFILE. It might
require a few BACKUP,
> SHRINK, BACKUO, SHRINK iteration where you monitor the status between
using DBCC LOGINFO. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more info.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "WB" <none> wrote in message
news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
> > Yes, There are two listings for the transaction file.
> >
> > 1. Current size = ~ 9GB
> > 2. Space used = ~100MB
> >
> > I am referencing the details listed under Shrink Database -> files
> >
> > WB
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
> >> I don't understand. You say you shrunk it down to 100MB. Then you say
the
> > file is still 9GB?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "WB" <none> wrote in message
news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
> >> > Thank you for all the input. I was able to backup the log file and
then
> >> > shrink it down to under 100MB. The problem now is I can't set the
max
> > file
> >> > size to say 2GB because the file size is still 9GB. How can I
prevent
> > the
> >> > file from growing beyond 2GB'
> >> >
> >> >
> >> > "WB" <none> wrote in message
> > news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> >> >> My database file is growing and growing and growing. This is
good...
> >> >> The bad news, at least for storage limitations, is the Transaction
log
> >> > file
> >> >> is growing significantly faster. At the present time my data file
is
> >> > around
> >> >> 150 MB and the Transaction log file is just under 10GB......
> >> >>
> >> >> Do I need to let the Transaction log file grow or is there a way to
> >> > prevent
> >> >> the file from getting to large? Any suggestions on solving this
> > problem
> >> > are
> >> >> greatly appreciated.
> >> >>
> >> >> WB
> >> >>
> >> >>
> >> >
> >> >
> >
> >
>|||And rather than setting a max size for the file, then remember to put it to
SIMPLE recovery - that will keep the file in a decent size but it have the
space to grow if it needs it for some reason.
/Steen
WB wrote:
> ok, thanks for staying with the thread.
> WB
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:e%23ONr3GbFHA.616@.TK2MSFTNGP12.phx.gbl...
>> I see. I don't use the EM GUI much, but I assume it means that the
>> file size is 9GB and it is only used 100MB. You need to shrink the
>> file using DBCC SHRINKFILE. It might require a few BACKUP, SHRINK,
>> BACKUO, SHRINK iteration where you monitor the status between using
>> DBCC LOGINFO. See
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some more
>> info.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "WB" <none> wrote in message
> news:ek1%23BsGbFHA.3144@.TK2MSFTNGP14.phx.gbl...
>> Yes, There are two listings for the transaction file.
>> 1. Current size = ~ 9GB
>> 2. Space used = ~100MB
>> I am referencing the details listed under Shrink Database -> files
>> WB
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:e1QZv0FbFHA.3196@.TK2MSFTNGP14.phx.gbl...
>> I don't understand. You say you shrunk it down to 100MB. Then you
>> say the file is still 9GB?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "WB" <none> wrote in message
> news:O0AG6BFbFHA.3848@.TK2MSFTNGP10.phx.gbl...
>> Thank you for all the input. I was able to backup the log file
>> and then shrink it down to under 100MB. The problem now is I
>> can't set the max file size to say 2GB because the file size is
>> still 9GB. How can I prevent the file from growing beyond 2GB'
>>
>> "WB" <none> wrote in message
>> news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
>> My database file is growing and growing and growing. This is
>> good... The bad news, at least for storage limitations, is the
>> Transaction log file is growing significantly faster. At the
>> present time my data file is around 150 MB and the Transaction
>> log file is just under 10GB......
>> Do I need to let the Transaction log file grow or is there a way
>> to prevent the file from getting to large? Any suggestions on
>> solving this problem are greatly appreciated.
>> WB|||A terrible solution and an indication that you have not yet discovered the
true purpose of the transaction log.
You have much left to learn, Grasshopper.
Anthony Thomas
"msnews.microsoft.com" <radovan@.servis24.hr> wrote in message
news:%2308n9EAbFHA.612@.TK2MSFTNGP12.phx.gbl...
I had the same problem.
I must have my database in FULL recovery model, and my log file growes
larger and larger.
Except normaly daily backup (and hourly incremantaly too )
Ones a week I make the following step:
1) Make backup of my database.
2) than do:
backup log mydatabasename with truncate_only
dbcc shrinkdatabase('mydatabasename')
3) Make again backup of my shrink database
On course, I put this steps in DTS package and put in Agent.
"WB" <none> wrote in message news:emcHwouaFHA.720@.TK2MSFTNGP15.phx.gbl...
> My database file is growing and growing and growing. This is good...
> The bad news, at least for storage limitations, is the Transaction log
> file
> is growing significantly faster. At the present time my data file is
> around
> 150 MB and the Transaction log file is just under 10GB......
> Do I need to let the Transaction log file grow or is there a way to
> prevent
> the file from getting to large? Any suggestions on solving this problem
> are
> greatly appreciated.
> WB
>

Purge Transaction Log File

purge process from large table

Hi,
We have specific request here: there is a large table, around 5,000,000 rows
(3GB). It has unique clustered index, created on 6 of it's 10 columns. We
have to delete around 15% of rows every day, but in a way that table keeps
being available all the time. Deletion criteria is date (where
column_date<=getdate()). There is nonclustered index created on column_date
table. Since there is availability criteria, simple: delete <table name>
where column_date<=getdate() is out of the question because of exclusive
table lock on this table.
Any ideas?
Thanks,
PedjaBy "available" do you mean readable via a SELECT?
If you are using SQL 2005, you can take advantage of the new Read Committed
Snapshot Isolation level, which allows a SELECT to read the most
recently-committed version of a set of data, even while that data is being
modified.
If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
to the SELECT statements, putting the transaction into read uncommited. Your
readers won't have to wait for writers, but you may get inaccurate data.
Is it possible that you can split this delete operation so that it occurs
several times a day? That way it can delete fewer rows, resulting in less
blocking time.
"Pedja" wrote:

> Hi,
> We have specific request here: there is a large table, around 5,000,000 ro
ws
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on column_dat
e
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedja|||Mark,
We use sql server 2000. By available, I mean both, read/write operations.
NOLOCK hint won't help, because once it is grabbed by purge process, table i
s
being locked until it is completed (that is why I posted this question
initially), so neither reads nor writes are allowed during this time. My ide
a
was to split deletion process to batches of 1000 rows (set rowcount 1000),
but I wanted to hear some other ideas too.
Thanks
"Mark Williams" wrote:
> By "available" do you mean readable via a SELECT?
> If you are using SQL 2005, you can take advantage of the new Read Committe
d
> Snapshot Isolation level, which allows a SELECT to read the most
> recently-committed version of a set of data, even while that data is being
> modified.
> If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
> to the SELECT statements, putting the transaction into read uncommited. Yo
ur
> readers won't have to wait for writers, but you may get inaccurate data.
> Is it possible that you can split this delete operation so that it occurs
> several times a day? That way it can delete fewer rows, resulting in less
> blocking time.
> --
> "Pedja" wrote:
>|||Pedja,
you could split up your data into several tables, one table per day.
You can access them via a UNION ALL view. Then the purge is very fast,
you just re-create the view, which is a snap, and drop the oldest
table. There are some di

vantages: some of queries against the view
will work slower, and you will not be able to enforse unique (and
sometimes other) constraints just as easily|||Hi
Divide your deletion into small batches
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Here your DML Statement
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:38445382-60B6-4327-A3FE-C5AD8BF88E41@.microsoft.com...
> Hi,
> We have specific request here: there is a large table, around 5,000,000
> rows
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on
> column_date
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedjasql

Purge old ROWS

looking for a script that will purge rows that are 2
months old. I would like to run this as a Scheduled job..
On Tue, 1 Jun 2004 11:05:01 -0700, Rick wrote:

>looking for a script that will purge rows that are 2
>months old. I would like to run this as a Scheduled job..
Hi Rick,
Assuming your table is called MyTable and the creation date of each row is
stored in a column named CrDate, use this:
DELETE FROM MyTable
WHERE CrDate < dateadd(month, -2, getdate())
Untested - test it first, in a transaction. Rollback or commit as needed.
Don't put it in a scheduled job until you've tested it thoroughly.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the information, It worked perfectly. FYI: I am using this
for ODBC logging on IIS web servers. They send the logs to a SQL server
and I purge them as they get old.
Works very well thanks,
DELETE FROM inetlog
WHERE LogTime < dateadd(month, -2, getdate())
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Purge Database

Is there any way to purge/truncate a database where the dataase is full or does not have free space ?If you mean your transaction log is full, check out the DUMP TRAN options - you need to do a non-logged truncate if you don't have enough space in your log to log the truncation. I think that option is NO_LOG, and you should immediately backup your database after executing it.

Then, you need to manage your log so it doesn't happen again.

Finally, buy more disk space. It costs more to manage space with limited resources than to buy the additional resources.|||The transanction logs are really small. the database is actually part of a intruder detection system and the database can grow pretty rapidly even with alerts.|||A good IDS comes with archiving capabilities and filtering that needs to be fine-tuned to satisfy your business needs as well as to ensure you're not running out of space by trying to collect every login/logout. Check if archiving is turned on and exclude events that your business is not interested in. A serious security investigation will always bring tools like nCase so whatever you filtered out can always be answered through other means.|||Aggregation would seem like a no-brainer.

How much space we talking here? We have a database that logs every event on every web site we host - and we're doing > 45 tps. Our processes aggregate and delete data based on business rules: for example, maintain 90 days (moving window) of detail, but aggregate every night.

I'm clueless how this compares to IDS, but we are capturing every bit of information available and that comes out to over 4,800 bits per transaction. (No worries - nothing personally identifiable.)

This has been running for more 1.5 years on the same box and we have only needed the detail once - when we screwed up and lost some aggregates. Fortunately, our customers are rather perceptive and caught the problem w/in a couple of days. Everthing was recovered by re-running the aggregates.

Again, buy more disk space. Seriously, how much is your time worth? It is not the solution, but more resources will give you time to solve the problem.

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:

> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this da
y.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:
> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this day.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>sql

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.
Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:

> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this day.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>

purge all history from msdb

I am looking for a way to setup a periodic delete from all history tables in
msdb, to prevent excessive growth. I am wondering if the following procedures
includes all necessary deletes.
1) In the maintenance plan wizard reporting tab, setting the number of
records to retain in sys_dbmaintplanhistory; older records will be deleted.
2) Run sp_delete_backuphistory to delete records from several backup and
restore history tables.
3) Run sp_purge_jobhistory to remove history from other jobs.
Am I missing anything? There is also an sp_purgehistory, but BOL says this
is for backward compatibility only; does anyone use this?
--
MunchingBillHi Munching,
Thanks for your post.
It is not necessary for you to execute sp_purgehistory. Based on my
knowledge, these three steps you have listed are sufficient for you to
delete from all history tables.
Also, please note that this question appears to be consulting in nature. We
would also like to introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/default.aspx?pr=AdvisoryService
Outside of the US/Canada:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

purge all history from msdb

I am looking for a way to setup a periodic delete from all history tables in
msdb, to prevent excessive growth. I am wondering if the following procedure
s
includes all necessary deletes.
1) In the maintenance plan wizard reporting tab, setting the number of
records to retain in sys_dbmaintplanhistory; older records will be deleted.
2) Run sp_delete_backuphistory to delete records from several backup and
restore history tables.
3) Run sp_purge_jobhistory to remove history from other jobs.
Am I missing anything? There is also an sp_purgehistory, but BOL says this
is for backward compatibility only; does anyone use this?
--
MunchingBillHi Munching,
Thanks for your post.
It is not necessary for you to execute sp_purgehistory. Based on my
knowledge, these three steps you have listed are sufficient for you to
delete from all history tables.
Also, please note that this question appears to be consulting in nature. We
would also like to introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/defaul...AdvisoryService
Outside of the US/Canada:
http://support.microsoft.com/defaul...ernational.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

purge all history from msdb

I am looking for a way to setup a periodic delete from all history tables in
msdb, to prevent excessive growth. I am wondering if the following procedures
includes all necessary deletes.
1) In the maintenance plan wizard reporting tab, setting the number of
records to retain in sys_dbmaintplanhistory; older records will be deleted.
2) Run sp_delete_backuphistory to delete records from several backup and
restore history tables.
3) Run sp_purge_jobhistory to remove history from other jobs.
Am I missing anything? There is also an sp_purgehistory, but BOL says this
is for backward compatibility only; does anyone use this?
MunchingBill
Hi Munching,
Thanks for your post.
It is not necessary for you to execute sp_purgehistory. Based on my
knowledge, these three steps you have listed are sufficient for you to
delete from all history tables.
Also, please note that this question appears to be consulting in nature. We
would also like to introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/default...dvisoryService
Outside of the US/Canada:
http://support.microsoft.com/default...rnational.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Purge a Transaction Log?

Hi Guys,
I have MS SQL Server 2000 which, among others, has a database on it used for
McAffee protection pilot (A policy driven AV managment system). Over the las
t
month or so the Tlog ldf has exploded in size to over 10GB (the mdf is only
25MB). How can I purge this log with minimal disruption to the database.
I tried shrinking the database and restoring from a backup but this seems to
have had no effect on the ldf file. I sopke to someone who sugested exportin
g
the data, objects and procedures etc. but during a test run this came back
with errors and also it seems quite long winded.
I don't have that much background in SQLserver but I know in Exchange - the
backup API will purge the tlogs once a backup has been successful - can
something similar be configured?. I noticed the maximum log file size
settings - which I will set once I have purged the ldf file.
Any ideas?
Thanks,
Niall.Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"Niall" wrote:

> Hi Guys,
> I have MS SQL Server 2000 which, among others, has a database on it used f
or
> McAffee protection pilot (A policy driven AV managment system). Over the l
ast
> month or so the Tlog ldf has exploded in size to over 10GB (the mdf is onl
y
> 25MB). How can I purge this log with minimal disruption to the database.
> I tried shrinking the database and restoring from a backup but this seems
to
> have had no effect on the ldf file. I sopke to someone who sugested export
ing
> the data, objects and procedures etc. but during a test run this came back
> with errors and also it seems quite long winded.
> I don't have that much background in SQLserver but I know in Exchange - th
e
> backup API will purge the tlogs once a backup has been successful - can
> something similar be configured?. I noticed the maximum log file size
> settings - which I will set once I have purged the ldf file.
> Any ideas?
> Thanks,
> Niall.sql

Friday, March 23, 2012

Purging Transaction Log

Purging SQL Server Log Files

Purging SQL Server Log Files

purging old tranlogs

Purging Log File

Purging data off of an MSDE database.

Purging a database data while keeping the structure intact

Wednesday, March 21, 2012

Purge Transaction Log File

Purge Transaction Log File

Purge Transaction Log File

purge process from large table

Purge old ROWS

Purge Database

Purge data dynamically

Purge data dynamically

Purge data dynamically

purge all history from msdb

purge all history from msdb

purge all history from msdb

Purge a Transaction Log?

Sql Public Role

Blog Archive

About Me