[MS SQL Server] Rebuilding / creating indexes as a bluk-logged operation and point-in-time recovery

---

• Rebuilding / creating indexes as a bluk-logged operation and point-in-time recovery
• Refresh Development from Production Backup Disk Space Error
• stack dumps
• Snapshot agent failing: Cannot promote the transaction to a distributed transaction

Rebuilding / creating indexes as a bluk-logged operation and point-in-time recovery Posted: 01 Jul 2013 04:58 AM PDT So, skimming blogs this fine day, I came across a suggestion to switch a DB from Full Recovery to Bulk-Logged when doing index rebuilds (especially LARGE indexes.) The stated benefit is that you don't lose the ability to recover to point-in-time (well, presumably except for the period when you're in Bulk-Logged)[url=http://msdn.microsoft.com/en-us/library/ms191484(v=sql.105).aspx]MSDN certainly backs up the recommendation[/url] to go to Bulk-Logged for such things, but doesn't (including in the "[url=http://msdn.microsoft.com/en-us/library/ms175987(v=sql.105).aspx]Choosing a recovery model[/url]" topic) explicitly state that you won't lose PiTR.So, is this true? Can you, say when you're planning an index rebuild, switch the DB in question from Full Recovery to Bulk-Logged, run your rebuild, then kick back to Full and still have a valid log chain? Hypothetically, say taking a log backup before you start (and before you change the recovery model,) then one when you're done, kick it back to Full, and the next log backup will still be OK?Thanks,Jason

Refresh Development from Production Backup Disk Space Error Posted: 01 Jul 2013 04:18 AM PDT Post removed.

stack dumps Posted: 01 Jul 2013 01:43 AM PDT I'm running integrity checks on my databases and a couple of days ago I started seeing this job failed. It's basically:USE [database]GODBCC CHECKDB(N'database') WITH NO_INFOMSGSWhen I ran the script above manually I get:Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.I'm also getting files created under the logs directory with stack dumps. I'm not experience with this problem, I haven't actually gone through something like this. What does it mean? How do I start troubleshooting it? How do I fix it?Thank you.

Snapshot agent failing: Cannot promote the transaction to a distributed transaction Posted: 30 Jun 2013 11:07 PM PDT Hi,I'm receiving the next error when i create my publication snapshot (my publication only have a table/article):"Error messages:Message: Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction. Command Text: sp_MSactivate_auto_subParameters: @publication = Publication_Tables @article = % @status = initiatedStack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command) at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout) at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.SetPublisherTranSequenceNumViaAutoSub(PublicationActivationState publicationActivationState, SqlConnection connection) at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ActivateCSSPublicationAndSetTranSequenceNums(PublicationActivationState publicationActivationState, SqlConnection connection) at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ConcurrentPreArticleFilesGenerationTransaction(SqlConnection connection) at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen) at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoConcurrentPreArticleFilesGenerationProcessing() at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoPreArticleFilesGenerationProcessing() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 3933)Get help: http://help/3933Server MYSERVER, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1249Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction. (Source: MSSQLServer, Error number: 3933)Get help: http://help/3933"I check and there aren't any open tran. I don't know what is the problem.Anyone could say me how solve the issue?Thanks.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610