delete ldf file?

FAQ

Profile

delete ldf file?

Database Help (Home) -> Server

Next: Diagnosing suddenly long running queries

Author

Message

Dan

External

Since: Jan 11, 2008
Posts: 11

(Msg. 1) Posted: Tue Oct 07, 2008 1:13 pm
Post subject: delete ldf file?
Archived from groups: microsoft>public>sqlserver>server (more info?)

I have a database that was in full recovery mode and built up a large (40 Gb)
log file. We determined that simple recovery mode will suffice for this db.
My question is, now that it's in simple recovery mode, can I just delete the
ldf file, or do I need to keep it there and attempt to shrink it?

>> Stay informed about: delete ldf file?

David Hay

External

Since: Jan 23, 2008
Posts: 58

(Msg. 2) Posted: Tue Oct 07, 2008 1:27 pm Post subject: Re: delete ldf file? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You need to keep it, but you should truncate and shrink it! Even simple recovery requires a log file. David Hay On Oct 7, 4:13 pm, Dan <dantheri... DeleteThis @newsgroup.nospam> wrote: > I have a database that was in full recovery mode and built up a large (40 Gb) > log file. We determined that simple recovery mode will suffice for this db. > My question is, now that it's in simple recovery mode, can I just delete the > ldf file, or do I need to keep it there and attempt to shrink it? >> Stay informed about: delete ldf file?

Tibor Karaszi

External

Since: Jan 29, 2004
Posts: 1548

(Msg. 3) Posted: Tue Oct 07, 2008 5:25 pm Post subject: Re: delete ldf file? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Shrink it. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some help if the shrink doesn't work. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Dan" <dantheriver.DeleteThis@newsgroup.nospam> wrote in message news:B6442261-C960-481B-9566-8624C519ACDF@microsoft.com... >I have a database that was in full recovery mode and built up a large (40 Gb) > log file. We determined that simple recovery mode will suffice for this db. > My question is, now that it's in simple recovery mode, can I just delete the > ldf file, or do I need to keep it there and attempt to shrink it? >> Stay informed about: delete ldf file?

Dan

External

Since: Jan 11, 2008
Posts: 11

(Msg. 4) Posted: Tue Oct 07, 2008 5:25 pm Post subject: Re: delete ldf file? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Tibor, Thanks for the info. When I ran DBCC LOGINFO('myDatabase') , it shows over 600 virtual log files that are all status of 2. Obviously the SHRINKFILE cmd isn't working. How do I get inactive all the virtual log files? Thanks, -Dan "Tibor Karaszi" wrote: > Shrink it. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for some help if the shrink > doesn't work. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Dan" <dantheriver.RemoveThis@newsgroup.nospam> wrote in message > news:B6442261-C960-481B-9566-8624C519ACDF@microsoft.com... > >I have a database that was in full recovery mode and built up a large (40 Gb) > > log file. We determined that simple recovery mode will suffice for this db. > > My question is, now that it's in simple recovery mode, can I just delete the > > ldf file, or do I need to keep it there and attempt to shrink it? > > > >> Stay informed about: delete ldf file?

Kalen Delaney

External

Since: Oct 27, 2003
Posts: 289

(Msg. 5) Posted: Tue Oct 07, 2008 5:25 pm Post subject: Re: delete ldf file? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Did you trying truncating the log? BACKUP LOG your_db_name WITH TRUNCATE_ONLY -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com www.SQLTuners.com "Dan" <dantheriver.DeleteThis@newsgroup.nospam> wrote in message news:13057D03-0D14-4159-9526-C36F137CC5A5@microsoft.com... > Tibor, > > Thanks for the info. When I ran DBCC LOGINFO('myDatabase') , it shows over > 600 virtual log files that are all status of 2. Obviously the SHRINKFILE > cmd > isn't working. How do I get inactive all the virtual log files? > > Thanks, > > -Dan > > > "Tibor Karaszi" wrote: > >> Shrink it. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for >> some help if the shrink >> doesn't work. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Dan" <dantheriver.DeleteThis@newsgroup.nospam> wrote in message >> news:B6442261-C960-481B-9566-8624C519ACDF@microsoft.com... >> >I have a database that was in full recovery mode and built up a large >> >(40 Gb) >> > log file. We determined that simple recovery mode will suffice for this >> > db. >> > My question is, now that it's in simple recovery mode, can I just >> > delete the >> > ldf file, or do I need to keep it there and attempt to shrink it? >> >> >> >> Stay informed about: delete ldf file?

Dan

External

Since: Jan 11, 2008
Posts: 11

(Msg. 6) Posted: Tue Oct 07, 2008 5:25 pm Post subject: Re: delete ldf file? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Yes I tried to truncate the log, and DBCC LOGINFO is still showing over 600 virtual log files with a status of 2. Any ideas? "Kalen Delaney" wrote: > Did you trying truncating the log? > > BACKUP LOG your_db_name WITH TRUNCATE_ONLY > > -- > HTH > Kalen Delaney, SQL Server MVP > www.InsideSQLServer.com > www.SQLTuners.com > > > "Dan" <dantheriver DeleteThis @newsgroup.nospam> wrote in message > news:13057D03-0D14-4159-9526-C36F137CC5A5@microsoft.com... > > Tibor, > > > > Thanks for the info. When I ran DBCC LOGINFO('myDatabase') , it shows over > > 600 virtual log files that are all status of 2. Obviously the SHRINKFILE > > cmd > > isn't working. How do I get inactive all the virtual log files? > > > > Thanks, > > > > -Dan > > > > > > "Tibor Karaszi" wrote: > > > >> Shrink it. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for > >> some help if the shrink > >> doesn't work. > >> > >> -- > >> Tibor Karaszi, SQL Server MVP > >> http://www.karaszi.com/sqlserver/default.asp > >> http://sqlblog.com/blogs/tibor_karaszi > >> > >> > >> "Dan" <dantheriver DeleteThis @newsgroup.nospam> wrote in message > >> news:B6442261-C960-481B-9566-8624C519ACDF@microsoft.com... > >> >I have a database that was in full recovery mode and built up a large > >> >(40 Gb) > >> > log file. We determined that simple recovery mode will suffice for this > >> > db. > >> > My question is, now that it's in simple recovery mode, can I just > >> > delete the > >> > ldf file, or do I need to keep it there and attempt to shrink it? > >> > >> > >> > > > >> Stay informed about: delete ldf file?

Kalen Delaney

External

Since: Oct 27, 2003
Posts: 289

(Msg. 7) Posted: Wed Oct 08, 2008 1:55 am Post subject: Re: delete ldf file? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

There may be something that is keeping the log from clearing. Are you running replication? Also, make sure you are in the right database before running DBCC LOGINFO. What info does this query give you? select log_reuse_wait_desc,* from sys.databases where name = '<your db name>' -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com www.SQLTuners.com "Dan" <dantheriver DeleteThis @newsgroup.nospam> wrote in message news:1551A3E8-0A3B-41BC-8DA8-E4FD026216EC@microsoft.com... > Yes I tried to truncate the log, and DBCC LOGINFO is still showing over > 600 > virtual log files with a status of 2. > > Any ideas? > > > > "Kalen Delaney" wrote: > >> Did you trying truncating the log? >> >> BACKUP LOG your_db_name WITH TRUNCATE_ONLY >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> www.InsideSQLServer.com >> www.SQLTuners.com >> >> >> "Dan" <dantheriver DeleteThis @newsgroup.nospam> wrote in message >> news:13057D03-0D14-4159-9526-C36F137CC5A5@microsoft.com... >> > Tibor, >> > >> > Thanks for the info. When I ran DBCC LOGINFO('myDatabase') , it shows >> > over >> > 600 virtual log files that are all status of 2. Obviously the >> > SHRINKFILE >> > cmd >> > isn't working. How do I get inactive all the virtual log files? >> > >> > Thanks, >> > >> > -Dan >> > >> > >> > "Tibor Karaszi" wrote: >> > >> >> Shrink it. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> >> for >> >> some help if the shrink >> >> doesn't work. >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> >> >> "Dan" <dantheriver DeleteThis @newsgroup.nospam> wrote in message >> >> news:B6442261-C960-481B-9566-8624C519ACDF@microsoft.com... >> >> >I have a database that was in full recovery mode and built up a large >> >> >(40 Gb) >> >> > log file. We determined that simple recovery mode will suffice for >> >> > this >> >> > db. >> >> > My question is, now that it's in simple recovery mode, can I just >> >> > delete the >> >> > ldf file, or do I need to keep it there and attempt to shrink it? >> >> >> >> >> >> >> >> >> >> Stay informed about: delete ldf file?

Tibor Karaszi

External

Since: Jan 29, 2004
Posts: 1548

(Msg. 8) Posted: Wed Oct 08, 2008 2:25 am Post subject: Re: delete ldf file? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You might need to run shrinkfile and backup log several times. If that doesn't cut it, then look for open transactions in the database (DBCC OPENTRAN). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Dan" <dantheriver.DeleteThis@newsgroup.nospam> wrote in message news:1551A3E8-0A3B-41BC-8DA8-E4FD026216EC@microsoft.com... > Yes I tried to truncate the log, and DBCC LOGINFO is still showing over 600 > virtual log files with a status of 2. > > Any ideas? > > > > "Kalen Delaney" wrote: > >> Did you trying truncating the log? >> >> BACKUP LOG your_db_name WITH TRUNCATE_ONLY >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> www.InsideSQLServer.com >> www.SQLTuners.com >> >> >> "Dan" <dantheriver.DeleteThis@newsgroup.nospam> wrote in message >> news:13057D03-0D14-4159-9526-C36F137CC5A5@microsoft.com... >> > Tibor, >> > >> > Thanks for the info. When I ran DBCC LOGINFO('myDatabase') , it shows over >> > 600 virtual log files that are all status of 2. Obviously the SHRINKFILE >> > cmd >> > isn't working. How do I get inactive all the virtual log files? >> > >> > Thanks, >> > >> > -Dan >> > >> > >> > "Tibor Karaszi" wrote: >> > >> >> Shrink it. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for >> >> some help if the shrink >> >> doesn't work. >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> >> >> "Dan" <dantheriver.DeleteThis@newsgroup.nospam> wrote in message >> >> news:B6442261-C960-481B-9566-8624C519ACDF@microsoft.com... >> >> >I have a database that was in full recovery mode and built up a large >> >> >(40 Gb) >> >> > log file. We determined that simple recovery mode will suffice for this >> >> > db. >> >> > My question is, now that it's in simple recovery mode, can I just >> >> > delete the >> >> > ldf file, or do I need to keep it there and attempt to shrink it? >> >> >> >> >> >> >> >> >> >> Stay informed about: delete ldf file?

TheSQLGuru

External

Since: Jan 11, 2008
Posts: 563

(Msg. 9) Posted: Wed Oct 08, 2008 11:03 am Post subject: Re: delete ldf file? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

IF you can take the db offline, check out sp_detach_db and the associated attach sprocs (there is a simple one for single-file dbs). This can allow you to get the database back with a fresh log file, which you should then place/size appropriately. Be sure to read up in BOL about these actions prior to doing them!! -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Dan" <dantheriver.TakeThisOut@newsgroup.nospam> wrote in message news:1551A3E8-0A3B-41BC-8DA8-E4FD026216EC@microsoft.com... > Yes I tried to truncate the log, and DBCC LOGINFO is still showing over > 600 > virtual log files with a status of 2. > > Any ideas? > > > > "Kalen Delaney" wrote: > >> Did you trying truncating the log? >> >> BACKUP LOG your_db_name WITH TRUNCATE_ONLY >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> www.InsideSQLServer.com >> www.SQLTuners.com >> >> >> "Dan" <dantheriver.TakeThisOut@newsgroup.nospam> wrote in message >> news:13057D03-0D14-4159-9526-C36F137CC5A5@microsoft.com... >> > Tibor, >> > >> > Thanks for the info. When I ran DBCC LOGINFO('myDatabase') , it shows >> > over >> > 600 virtual log files that are all status of 2. Obviously the >> > SHRINKFILE >> > cmd >> > isn't working. How do I get inactive all the virtual log files? >> > >> > Thanks, >> > >> > -Dan >> > >> > >> > "Tibor Karaszi" wrote: >> > >> >> Shrink it. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> >> for >> >> some help if the shrink >> >> doesn't work. >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> >> >> "Dan" <dantheriver.TakeThisOut@newsgroup.nospam> wrote in message >> >> news:B6442261-C960-481B-9566-8624C519ACDF@microsoft.com... >> >> >I have a database that was in full recovery mode and built up a large >> >> >(40 Gb) >> >> > log file. We determined that simple recovery mode will suffice for >> >> > this >> >> > db. >> >> > My question is, now that it's in simple recovery mode, can I just >> >> > delete the >> >> > ldf file, or do I need to keep it there and attempt to shrink it? >> >> >> >> >> >> >> >> >> >> Stay informed about: delete ldf file?

Display posts from previous:

	Database Help (Home) -> Server	All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum