Tag Info

New answers tagged

1

The main question to ask yourself is how much data can you afford to lose. That will determine how often you need to backup the log. But there is nothing wrong with running transaction log backups very often, even every minute. This accomplishes 2 things: 1. Provides you with the security that you won't lose data; 2. Improves performance - the less data you ...


2

(yeah, I added a new column to a multi-billion row table.) Adding a column to a very large table can have implications but there is a clever way of adding column as well. From : Adding NOT NULL Columns as an Online Operation Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when ...


0

In the scenario of adding a column to a VLDB-class table, it may be worth exploring creating a new table with the new structure and moving records from the old table to the new table in small ranges. It'll keep the individual transaction size small so the high-water mark for the Tlog in Simple recovery would be relatively low. You can't avoid ACID ...


0

As Max mentioned the alert probably fired just prior to the log growing/needing to grow. SCOM collects transaction log free space % although I am not sure at what threshold the alert will fire. here is a quick example to show you what state tempdb is probably in when you get these alerts but no log file growth. first create a database, set recovery to ...


0

Brent, Your approach is going to depend on what your recovery mode the database is in. This is the MSDN DBCC SHRINKFILE reference for your version of SQL Server. For databases that are in SIMPLE recovery mode you should be able to execute the DBCC SHRINKFILE (N'<logical_file_name_of_the_log>'<Size In MB>) That should be all ...


2

Does anyone know how I can redefine the Log file with an Init size of 200 mb? You have to use ALTER DATABASE [YOUR_DB_NAME] ... MODIFY FILE ... SIZE ALTER DATABASE [your_db_name] SET RECOVERY SIMPLE; CHECKPOINT; -- shrink the log file to 200MB DBCC SHRINKFILE (your_db_name_log,200); --- define the initial size to 200MB as well.. CHANGE it as per ...


1

First of all please note initial size is not actually what it means. Yes you can do it but make sure you don't use SSMS GUI. ALTER DATABASE [db_name] SET RECOVERY SIMPLE; sp_helpdb db_name --get logical log file name CHECKPOINT; DBCC SHRINKFILE (db_name_log,0); ALTER DATABASE [db_name] MODIFY FILE ...


2

1) Below is a great script from Paul's blog Script: open transactions with text and planswhich should get you going: SELECT [s_tst].[session_id], [s_es].[login_name] AS [Login Name], DB_NAME (s_tdt.database_id) AS [Database], [s_tdt].[database_transaction_begin_time] AS [Begin Time], [s_tdt].[database_transaction_log_bytes_used] AS [Log ...


0

We have the below script handy when have a scenario to restore multiple tran logs in sequence during an issue from here DECLARE @databaseName sysname DECLARE @backupStartDate datetime DECLARE @backup_set_id_start INT DECLARE @backup_set_id_end INT -- set database to be used SET @databaseName = '<your_database_name_here>' SELECT @backup_set_id_start ...


4

I would suggest to use Restore Gene : Automating SQL Server Database Restores - written by Paul Brewer The Restore Gene stored procedure generates the required restore scripts, including the DBCC CHECKDB command, if required. It is a useful tool, by itself; in disaster recovery situations, it can construct a restore script, in seconds. It optionally ...


0

This uses xp_cmdshell to look at the file system for the log files. If you're OK with that.... DECLARE @databaseName AS VARCHAR(100) DECLARE @restoreName AS VARCHAR(100) SET @databaseName = 'OriginalDBName' SET @restoreName = 'RestoredDBName' CREATE TABLE #dirList ( id int identity(1,1), line nvarchar(1000) ) DECLARE @cmdShellStatement ...


1

Transaction log is the most important element in SQL Server. It is like a sequential journal that logs all the changes made to the database and contains enough information to undo / redo the changes in the event of crash recovery to keep the database in a consistent state. Highly recommend to read : Understanding Logging and Recovery in SQL Server All your ...


-1

Here is a good article that explains what the Transaction Log is, and why do you need one!


2

If you are sure that everything is replicated to all subscribers and you really need to shrink it right now, you can try this. Execute the following snippet inside your replication database. It will mark all replications as successful and your replication queue will be empty. Be sure that this is really the case! Otherwise you may loose some elements which ...



Top 50 recent answers are included