Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««89101112»»

A Story of the Deleted Transaction Log Expand / Collapse
Author
Message
Posted Saturday, August 8, 2009 2:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
jose (8/8/2009)
I think I would have detach and attach single file

EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.


sp_attach_single_file_db is deprecated in SQL 2005 and will be removed in a future version. The replacement is CREATE DATABASE ... FOR ATTACH_REBUILD_LOG

Both sp_attach_single_file_db and CREATE DATABASE ... FOR ATTACH_REBUILD_LOG require that the database was shut down cleanly. If it was not, the log cannot be rebuilt and an attempt to do so will produce the errors that the article discussed.

For more info see - http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #767386
Posted Saturday, August 8, 2009 5:22 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
GilaMonster (8/7/2009)
Mark Horninger (8/7/2009)
GilaMonster (8/7/2009)
Bill Whitman (7/14/2008)
A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.


Absolutely not. That's terrible advice to be giving.

Backup the log, sure, though more often than nightly is usually necessary. Truncate and shrink are not things that should be done to the log on a regular basis, if at all.


the only time I would do that is maybe on a dev server if space is a premium...


On a dev server I'd just set all the DBs to Simple recovery. That way there's no need to worry about log backups in the first place.


the instance I was thinking about had alot of nightly crap that caused the log to expand... then the stuff the next day on the other DB's would run out of disk space.

The right answer was to add disk - but it wasn't an option for that client.
Post #767399
Posted Sunday, August 9, 2009 6:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 20, 2009 12:03 AM
Points: 1, Visits: 5
I think i would have cried then punched the technician for being so Stupid!!!!!!
Great Result !!!!
Post #767583
Posted Monday, August 10, 2009 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 9:13 AM
Points: 20, Visits: 26
The same happend to me due to the entire array failure where db logs were stored. I had to do the same steps for 69 db's.
Post #767924
Posted Tuesday, August 11, 2009 12:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 12:58 PM
Points: 196, Visits: 126
good story.
One more lesson to be learned from above story to add the Maintenance Job of Truncating Transactional log files regularly so that you dont run out of space.
Post #768390
Posted Tuesday, August 11, 2009 1:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
Hemantjemmy (8/11/2009)
good story.
One more lesson to be learned from above story to add the Maintenance Job of Truncating Transactional log files regularly so that you dont run out of space.


Bad idea. Very bad idea.

Back them up, yes. Truncate them, no.
Please read through this - Managing Transaction Logs



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #768393
Posted Tuesday, August 11, 2009 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 1,093, Visits: 2,620
I am still surprised on how people emit opinions after (hopefully) having read this thread and stating again obviously wrong comments.....




_______________________________________________________________________
For better assistance in answering your questions, click here
Post #768536
Posted Tuesday, August 11, 2009 10:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 12:58 PM
Points: 196, Visits: 126
Hi,
I think I have missed two word in above comment which created confusion.
What i meant was truncate & shrink transactional log file only after taking full backup.If your database is in Full Recovery Mode, it is good to truncate & shrink transaction log file regularly after full backup.
Post #769094
Posted Tuesday, August 11, 2009 10:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 12:58 PM
Points: 196, Visits: 126
Hi,
I think I have missed two word in above comment which created confusion.
What i meant was truncate & shrink transactional log file only after taking full backup.If your database is in Full Recovery Mode, it is good to truncate & shrink transaction log file regularly after full backup.
Post #769095
Posted Tuesday, August 11, 2009 10:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 12:58 PM
Points: 196, Visits: 126
Hi,
I think I have missed two word in above comment which created confusion.
What i meant was truncate & shrink transactional log file only after taking full backup.If your database is in Full Recovery Mode, it is good to truncate & shrink transaction log file regularly after full backup.
Post #769096
« Prev Topic | Next Topic »

Add to briefcase «««89101112»»

Permissions Expand / Collapse