Hi ,
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...
> Hi ,
> I have a huge sql server 2000 database. Everyday I am importing some
> records into it. The .mdf file size is around 20 GB. Surprisingly the
> .ldf file size is increasing rapidly and it has reached 50 GB.
> I want to import lot more records and there is a possibility that I
> may run out of disk space.
> Please let me know if I can delete this log file. I can always detach
> the database -delete the log file and reattach the database again. But
> the question is- Is it safe to do ? Also what is the possibility of
> database not getting attached properly and data loss ? any guesses.
> Thanks in advance,
> Vardhan.|||Deleting your log file will kill your database, so its
probably not a good thing to do ;)
Instead either shrink it, or change the SQL so it
immeditatly commits to disk,
J
>--Original Message--
>Hi ,
>I have a huge sql server 2000 database. Everyday I am
importing some
>records into it. The .mdf file size is around 20 GB.
Surprisingly the
>..ldf file size is increasing rapidly and it has reached
50 GB.
>I want to import lot more records and there is a
possibility that I
>may run out of disk space.
>Please let me know if I can delete this log file. I can
always detach
>the database -delete the log file and reattach the
database again. But
>the question is- Is it safe to do ? Also what is the
possibility of
>database not getting attached properly and data loss ?
any guesses.
>Thanks in advance,
>Vardhan.
>.
>|||Does backing up the DB, & then shrinking it not reduce the size?
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it and
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediately
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...
> > Hi ,
> > I have a huge sql server 2000 database. Everyday I am importing some
> > records into it. The .mdf file size is around 20 GB. Surprisingly the
> > .ldf file size is increasing rapidly and it has reached 50 GB.
> >
> > I want to import lot more records and there is a possibility that I
> > may run out of disk space.
> >
> > Please let me know if I can delete this log file. I can always detach
> > the database -delete the log file and reattach the database again. But
> > the question is- Is it safe to do ? Also what is the possibility of
> > database not getting attached properly and data loss ? any guesses.
> >
> > Thanks in advance,
> > Vardhan.
Showing posts with label everyday. Show all posts
Showing posts with label everyday. Show all posts
Sunday, March 25, 2012
Deleting log file in sql 2000 database
Hi ,
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/d... />
t_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...[Q
UOTE]
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it an
d
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediatel
y
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...
quote:|||Does backing up the DB, & then shrinking it not reduce the size?
> Hi ,
> I have a huge sql server 2000 database. Everyday I am importing some
> records into it. The .mdf file size is around 20 GB. Surprisingly the
> .ldf file size is increasing rapidly and it has reached 50 GB.
> I want to import lot more records and there is a possibility that I
> may run out of disk space.
> Please let me know if I can delete this log file. I can always detach
> the database -delete the log file and reattach the database again. But
> the question is- Is it safe to do ? Also what is the possibility of
> database not getting attached properly and data loss ? any guesses.
> Thanks in advance,
> Vardhan.
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/d... />
t_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...[Q
UOTE]
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it an
d
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediatel
y
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...
Tuesday, February 14, 2012
delete multiple tables
i have a inherited adatabase application that creates a table everyday.
because a table is created evryday the database is becoming huge. after muc
h
deliberation i have decided that i only need to keep 30 days of tables. how
can i delete all tables older than 30 days inside the database? i am fairly
new to sql and i have not been able to figure out how to delete multiple
tables based on creation date.
ronnieHi Ronnie
Ur idea in deleting old tables looks good but never try to delete a table
based on the date they were created.
This might also delete your production tables in the database which are
important
create your tables for ex: ForDelete_<Table_name>_<date>
and now u can write a stored procedure to loop arround and delete these kind
of tables.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ronnie" wrote:
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||See if this helps, but first be sure to have a backup of the db before using
it in production.
use your_db
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cd datetime
declare tables_older_than_30_days cursor local fast_forward
for
select
user_name(uid) as table_schema,
[name] as table_name,
crdate
from
sysobjects
where
xtype = 'U'
and objectproperty([id], 'IsMSShipped') = 0
and datediff(day, crdate, getdate()) > 30
open tables_older_than_30_days
while 1 = 1
begin
fetch next from tables_older_than_30_days into @.ts, @.tn, @.cd
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'drop table ' + quotename(@.ts) + N'.' + quotename(@.tn)
print @.sql
-- uncomment this line to drop the table
-- exec sp_executesql @.sql
end
close tables_older_than_30_days
deallocate tables_older_than_30_days
go
AMB
"Ronnie" wrote:
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||Ronny
Wow , it looks to me you have a poor designed database
Why do you/someone else need to create a table every day?
In sysobjects system table there is a cdate column .
"Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after
much
> deliberation i have decided that i only need to keep 30 days of tables.
how
> can i delete all tables older than 30 days inside the database? i am
fairly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||the database is for an automated vehicle location tacking. each day becomes
its own table. the table is populated with gps points for multiple vehicles
.
i am not sure why they set it up this way. i have not given it a lot of
thought yet. first i want to clean it up then look at redsigning it. thank
s
for the tip.
ronnie
"Uri Dimant" wrote:
> Ronny
> Wow , it looks to me you have a poor designed database
> Why do you/someone else need to create a table every day?
> In sysobjects system table there is a cdate column .
>
>
> "Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
> news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> much
> how
> fairly
>
>
because a table is created evryday the database is becoming huge. after muc
h
deliberation i have decided that i only need to keep 30 days of tables. how
can i delete all tables older than 30 days inside the database? i am fairly
new to sql and i have not been able to figure out how to delete multiple
tables based on creation date.
ronnieHi Ronnie
Ur idea in deleting old tables looks good but never try to delete a table
based on the date they were created.
This might also delete your production tables in the database which are
important
create your tables for ex: ForDelete_<Table_name>_<date>
and now u can write a stored procedure to loop arround and delete these kind
of tables.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ronnie" wrote:
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||See if this helps, but first be sure to have a backup of the db before using
it in production.
use your_db
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cd datetime
declare tables_older_than_30_days cursor local fast_forward
for
select
user_name(uid) as table_schema,
[name] as table_name,
crdate
from
sysobjects
where
xtype = 'U'
and objectproperty([id], 'IsMSShipped') = 0
and datediff(day, crdate, getdate()) > 30
open tables_older_than_30_days
while 1 = 1
begin
fetch next from tables_older_than_30_days into @.ts, @.tn, @.cd
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'drop table ' + quotename(@.ts) + N'.' + quotename(@.tn)
print @.sql
-- uncomment this line to drop the table
-- exec sp_executesql @.sql
end
close tables_older_than_30_days
deallocate tables_older_than_30_days
go
AMB
"Ronnie" wrote:
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||Ronny
Wow , it looks to me you have a poor designed database
Why do you/someone else need to create a table every day?
In sysobjects system table there is a cdate column .
"Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after
much
> deliberation i have decided that i only need to keep 30 days of tables.
how
> can i delete all tables older than 30 days inside the database? i am
fairly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||the database is for an automated vehicle location tacking. each day becomes
its own table. the table is populated with gps points for multiple vehicles
.
i am not sure why they set it up this way. i have not given it a lot of
thought yet. first i want to clean it up then look at redsigning it. thank
s
for the tip.
ronnie
"Uri Dimant" wrote:
> Ronny
> Wow , it looks to me you have a poor designed database
> Why do you/someone else need to create a table every day?
> In sysobjects system table there is a cdate column .
>
>
> "Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
> news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> much
> how
> fairly
>
>
Subscribe to:
Posts (Atom)