DENY permission,SQL server: grown

Showing posts with label grown. Show all posts

Sunday, March 25, 2012

Deleting log files

Hi,
We have many databases in production on our server. Each are generating log
files which have grown very large. I'd like to delete them & basically start
'growing them' again.
Basically, I want to stop tracing, delete the log files then start tracing
again in order to generate new ones. We have nightly backups & don't need any
point of time restores prior to today.
What are the dangers in this. Should I simply go ahead & do this?
Thanks for any advice on this
AntHi
Please refer to the documentation on Backup in SQL Server:
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Do no just delete the log files as you may end up with a database that is
not usable and this process is not supported by Microsoft, rather set the
correct recovery mode and/or do regular log backups.
--
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> Hi,
> We have many databases in production on our server. Each are generating
> log
> files which have grown very large. I'd like to delete them & basically
> start
> 'growing them' again.
> Basically, I want to stop tracing, delete the log files then start tracing
> again in order to generate new ones. We have nightly backups & don't need
> any
> point of time restores prior to today.
> What are the dangers in this. Should I simply go ahead & do this?
> Thanks for any advice on this
> Ant|||Hello,
Are you talking about the Transaction log file (LDF) or the SQL profiler
output files. If it Transaction log files and if the data is production
you should take the Transaction log backup in regular intervals. Thsi will
keep you your LDF file in control.
If it is trace files you could only trace the required events.
Thanks
Hari
"Michael Epprecht [MSFT]" <michael.epprecht@.online.microsoft.com> wrote in
message news:eOn9K7%23SHHA.920@.TK2MSFTNGP05.phx.gbl...
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> Hi,
>> We have many databases in production on our server. Each are generating
>> log
>> files which have grown very large. I'd like to delete them & basically
>> start
>> 'growing them' again.
>> Basically, I want to stop tracing, delete the log files then start
>> tracing
>> again in order to generate new ones. We have nightly backups & don't need
>> any
>> point of time restores prior to today.
>> What are the dangers in this. Should I simply go ahead & do this?
>> Thanks for any advice on this
>> Ant
>|||Hi Michael,
Thanks for the response,
The problem we are facing is we are running out of data on our drive.
"...and/or do regular log backups."
We backup every evening. Would it then be safe to delete these log files?
How can the data base be left unusable if we delete them? Is it just a
matter of not being able to do a point in time restore or will it have more
dire effects.
If I do go ahead & delete them, will the transaction logs just start from
the point that tracing begins again?
Thanks very much for your help.
Ant
"Michael Epprecht [MSFT]" wrote:
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> > Hi,
> >
> > We have many databases in production on our server. Each are generating
> > log
> > files which have grown very large. I'd like to delete them & basically
> > start
> > 'growing them' again.
> >
> > Basically, I want to stop tracing, delete the log files then start tracing
> > again in order to generate new ones. We have nightly backups & don't need
> > any
> > point of time restores prior to today.
> >
> > What are the dangers in this. Should I simply go ahead & do this?
> >
> > Thanks for any advice on this
> > Ant
>|||Don't just delete the ldf files. They are an integral part of the database. It's like saying that
you need more space on the hd and want to remove the windows folder. Shrink the file (if really
needed), then do a manual grow to the necessary size. And then either put the database in simple
recovery mode, or do regular transaction log backups. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start tracing
>> > again in order to generate new ones. We have nightly backups & don't need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>|||> We backup every evening.
What types of backups?. Note that a database backup does not remove
uncommitted transactions from the log so the log files will grow
indefinitely. The only way to remove committed log data is with a LOG
backup or by keeping the database in the SIMPLE recovery model. In the FULL
or BULK_LOGGED model, one normally schedules periodic LOG backups in between
to full backups.
Once you've backed up your logs, you can shrink the log file back to a
reasonable size as Tibor suggested.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start
>> > tracing
>> > again in order to generate new ones. We have nightly backups & don't
>> > need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>|||Hello,
If your recovery model is FULL or BULK_LOGGED you need to perform
Transaction Log Backup in frequent intervals. This will make sure that LDF
file will not grow.
Follw the below steps:-
1. Schedule a Daily full backup once a day.
2. Schedule a Transaction Log backup every 30 minutes
3. After the completion of daily full database backup you could delete all
the previous transction log backup files
This approach will help you to recover the database fully.
If your current LDF file is huge then use DBCC SHRINKFILE to reduce the
size. Before shrink either truncate the Log or Backup the log.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>> Hi
>> Please refer to the documentation on Backup in SQL Server:
>> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
>> Do no just delete the log files as you may end up with a database that is
>> not usable and this process is not supported by Microsoft, rather set the
>> correct recovery mode and/or do regular log backups.
>> --
>> Regards
>> Michel Epprecht [MSFT]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>> > Hi,
>> >
>> > We have many databases in production on our server. Each are generating
>> > log
>> > files which have grown very large. I'd like to delete them & basically
>> > start
>> > 'growing them' again.
>> >
>> > Basically, I want to stop tracing, delete the log files then start
>> > tracing
>> > again in order to generate new ones. We have nightly backups & don't
>> > need
>> > any
>> > point of time restores prior to today.
>> >
>> > What are the dangers in this. Should I simply go ahead & do this?
>> >
>> > Thanks for any advice on this
>> > Ant
>>

Tuesday, February 14, 2012

Delete Many Rows

Hi All,
I am designing a purge process for a db that has grown to almost 200GB.
My purge process will remove about 1/3 of the 500 million rows spread
over seven tables. Currently there are about 35 indexes defined on
those seven tables. My question is will there be a performance gain by
dropping those indexes, doing my purge, and re-creating the indexes. I
am afraid that leaving those indexes in place will create a lot of
extra overhead in my delete statements by having to maintain the
indexes. I know that it could take many hours to rebuild the indexes
afterward, but I am planning on doing that anyway. The reason that I
want to know whether I should drop the indexes ahead of time, is I may
not be able to do the entire purge at once and the tables may need to
be accessed between purges. If this occurs, I will need to have those
indexes in place.

So do I drop the indexes before the purge and re-create them later or
do I leave them in place and re-index them afterward?

Thanks In Advance

p.h.How often are queries run on these tables?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<hallpa1@.yahoo.com> wrote in message
news:1142607720.889826.79740@.p10g2000cwp.googlegro ups.com...
> Hi All,
> I am designing a purge process for a db that has grown to almost 200GB.
> My purge process will remove about 1/3 of the 500 million rows spread
> over seven tables. Currently there are about 35 indexes defined on
> those seven tables. My question is will there be a performance gain by
> dropping those indexes, doing my purge, and re-creating the indexes. I
> am afraid that leaving those indexes in place will create a lot of
> extra overhead in my delete statements by having to maintain the
> indexes. I know that it could take many hours to rebuild the indexes
> afterward, but I am planning on doing that anyway. The reason that I
> want to know whether I should drop the indexes ahead of time, is I may
> not be able to do the entire purge at once and the tables may need to
> be accessed between purges. If this occurs, I will need to have those
> indexes in place.
> So do I drop the indexes before the purge and re-create them later or
> do I leave them in place and re-index them afterward?
> Thanks In Advance
> p.h.|||I understand that the tables are accessed frequently during the day. I
know that I have to be very sensitive to affecting the response times
of queries against these tables. I am not the DBA that owns this
database, so I do not have access to specific details about the queries
against the tables.|||Without knowing the query access during this time it's hard to say, but
another idea
is you can create a Temporary Table (a persistent backup table with all the
date , not a sql temp table) and move the records there. (the ones you want
to keep)
Truncate your parent table. And move the records back to the original table
with the records you want.
Do the tables have clustered indexes?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<hallpa1@.yahoo.com> wrote in message
news:1142610809.679117.5620@.p10g2000cwp.googlegrou ps.com...
> I understand that the tables are accessed frequently during the day. I
> know that I have to be very sensitive to affecting the response times
> of queries against these tables. I am not the DBA that owns this
> database, so I do not have access to specific details about the queries
> against the tables.|||Most of the tables do not have a clustered index, one does.

I thought about the temp table option, but the database may get rows
updated while purging the old rows. I would then lose those updates
when I trunc the parent table.|||(hallpa1@.yahoo.com) writes:
> I am designing a purge process for a db that has grown to almost 200GB.
> My purge process will remove about 1/3 of the 500 million rows spread
> over seven tables. Currently there are about 35 indexes defined on
> those seven tables. My question is will there be a performance gain by
> dropping those indexes, doing my purge, and re-creating the indexes. I
> am afraid that leaving those indexes in place will create a lot of
> extra overhead in my delete statements by having to maintain the
> indexes. I know that it could take many hours to rebuild the indexes
> afterward, but I am planning on doing that anyway. The reason that I
> want to know whether I should drop the indexes ahead of time, is I may
> not be able to do the entire purge at once and the tables may need to
> be accessed between purges. If this occurs, I will need to have those
> indexes in place.
> So do I drop the indexes before the purge and re-create them later or
> do I leave them in place and re-index them afterward?

If you really want to know - benchmark. In any case, you should not
run such a heavy operation in prodction, before testing it on a copy
of the live data. In a test environment, you can try different strategies.

Of course, if there is a requirement that that the database is accessible
while you are doing your purge, you will have to find a low-impact purges
that delete fairly small slices at time, and in this case dropping index
out the question.

Again, this why running this in a test environment is important. If
you can say "I was able to run the purge, including restoring of
indexes that I dropped, in eight hours", then it may be deemed that
it is acceptable to take the database offline.

There is another factor to it. If you drop and recreate the indexes,
you will address fragmentation in the indexes, that your purge inevitably
will cause. Worrying here, though, is that only one table has a a clustered
index. This means that the data pages of the other six tables will
reamin fragmented. For this reason, I would recommend that once your
purge has completed, that you build clustered indexes on these tables.
I would also recommend that you keep them in place, but if your DBA thinks
they are bad, you can just drop them. In such case, drop it before you
recreate the non-clustered indexes. (Else it is a costly operation to
drop clustered indexes.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I have been testing heavily on a full copy of the production data.
Unfortunately the test server that I was given is a much slower machine
than the production box.
In my test environment, doing the purge without the indexes is much
faster than purging with the indexes in place. However, it took over
17 hours to rebuild the indexes afterward.
My estimates suggest that purging without the indexes then rebuilding
them will take about 40 straight hours. If I try to do the purge with
the indexes in place, it will take about 200 hours.
If I can get a window large enough, dropping the indexes, purging, and
rebuilding looks like the way to go. If I cannot get that window, then
I have to do it over multiple weekends leaving the indexes in place.
The key is that the indexes must be in place at the end of the window
so that production processing can resume.
Of course these numbers will be much lower on the production box, but
since I cannot test there, I cannot guess how much lower they will be.|||Rather than doing the delete continuously, you may want to delete the
rows in batches ordered by the clustered index column. It may take you
several hours, but at least it'll be a scripted job rather than
watching it do nothing. And, if something blows up, then if you commit
the transaction periodically, the rollback won't suck as much.|||My process involves breaking the process into many smaller chunks.
Purging that chunk and doing a commit, then grab the next chunk. So
while it will run continuously during my available window, there will
be a commit after every chunk, which is a few thousand rows. The issue
is that I would like to be able to finish all of the chunks in one
window then rebuild the indexes.|||hallpa1@.yahoo.com (hallpa1@.yahoo.com) writes:
> I have been testing heavily on a full copy of the production data.
> Unfortunately the test server that I was given is a much slower machine
> than the production box.

That is probably a good thing. :-) At least, you will not get estimates
that are overly optimistic.

> In my test environment, doing the purge without the indexes is much
> faster than purging with the indexes in place. However, it took over
> 17 hours to rebuild the indexes afterward.

That's indeed a long time.

> My estimates suggest that purging without the indexes then rebuilding
> them will take about 40 straight hours. If I try to do the purge with
> the indexes in place, it will take about 200 hours.

Hm, unless you have some extra non-business days around Easter you can
use, it sounds like a purge bit by bit with the indexes in place.

Of course, if you can get a window from Friday night to Monday morning,
that will suffice, but it will be a little nervous. Then again,
indexes can always be recreated by restoring a backup, but then all
purge job would be lost.

Stu's suggestion to go by clustered index is a good one, but I recall
that only one of your tables had one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hallpa1@.yahoo.com (hallpa1@.yahoo.com) writes:
> My process involves breaking the process into many smaller chunks.
> Purging that chunk and doing a commit, then grab the next chunk. So
> while it will run continuously during my available window, there will
> be a commit after every chunk, which is a few thousand rows. The issue
> is that I would like to be able to finish all of the chunks in one
> window then rebuild the indexes.

A few thousand? That's too small! It depends on how little wide your
tables are, but I would suggest 100000 rows. To small batches can cost
you time, because it takes time to locate the chunks.

While it takes a lot of time, benchmarking is the only way to find out
what is a good value.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<hallpa1@.yahoo.com> wrote in message
news:1142972121.442240.87670@.j33g2000cwa.googlegro ups.com...
> I have been testing heavily on a full copy of the production data.
> Unfortunately the test server that I was given is a much slower machine
> than the production box.
> In my test environment, doing the purge without the indexes is much
> faster than purging with the indexes in place. However, it took over
> 17 hours to rebuild the indexes afterward.

One suggestion if you can. Move the indexes to separate spindles. (but not
the clustered index).

Also, Enterprise Server can parallize a lot of index work so may be faster.

(one job I run quarterly runs 3-4x faster on one box and it appears the
deciding factor is probably Enterprise vs. Standard.)

> My estimates suggest that purging without the indexes then rebuilding
> them will take about 40 straight hours. If I try to do the purge with
> the indexes in place, it will take about 200 hours.
> If I can get a window large enough, dropping the indexes, purging, and
> rebuilding looks like the way to go. If I cannot get that window, then
> I have to do it over multiple weekends leaving the indexes in place.
> The key is that the indexes must be in place at the end of the window
> so that production processing can resume.
> Of course these numbers will be much lower on the production box, but
> since I cannot test there, I cannot guess how much lower they will be.|||boy, i sure am not a fan of clustered indexes, and for sure where you
are deleting chunks of data throughout, i do NOT understand any drive
to move to clustered indexes. But I'm sure listening and wanting to
learn!

The basic issues are understood. The sheer scale of this project leads
you to some interesting issues.
If you can knock off access to teh database, then do it in one fell
swoop. If not, then figure out an aritficial way to limit the deletes,
and do it in chunks. Rebuild the indexes one by one in off hours, and
just bite the bullet it will take a few weeks.

Sunday, March 25, 2012

Deleting log files

Deleting log files

Deleting log files

Tuesday, February 14, 2012

Delete Many Rows

DENY permission,SQL server

Blog Archive

About Me