New answers tagged transaction-log
1
Is there a way to decrypt TDE log files on disk for use with 3rd party software?
The short answer is -- this cannot be done.
Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted ...
1
It could be that the transactions were rolled back. Or the rows were deleted by someone. You can use fn_dblog to read the transaction log to see what's in the log.
SQL Server didn't just lose the transactions. Your app either didn't write them correctly, or rolled them back, or they were deleted.
3
You can use ApexSQL Log to read the transaction log (online, backup, detached). Even in the trial, it shows in GUI all transactions it finds in logs. That can help you troubleshoot the issue.
5
Alright, spent yesterday day and night investigating, testing and trying to reproduce the problem. Found the root cause.
MODEL database simple recovery model.
If the model database has been set to SIMPLE recovery model, and user databases are created with SIMPLE recovery model, SQL Server somehow is treating it as if it is on FULL recovery model. Hence ...
-2
Most likely your database is in FULL recovery.
Post the output of following
select name, log_reuse_wait_desc from sys.databases
where name = '<DB Name Here'
0
To start with - check the values of log_reuse_wait and log_reuse_wait_desc columns in the sys.databases catalog view. You can find if anything is preventing log truncation and act based on that. You can find a list of available values and their meaning here:
http://msdn.microsoft.com/en-us/library/ms190925.aspx
0
Thank you.
Unfortunately, I'm sorry, I forgot to include this info in my original post:
I cannot change the growth model of my existing log file - it fails with the error:
transaction log is full, with wait_desc of 'CHECKPOINT'
I also cannot add another log file - it fails with the same error.
The current log growth model is +10%. The current log ...
1
It sounds like you've got a huge transaction that has remained open which has done a lot of work. Run DBCC OPENTRAN on the database and see how long the oldest transaction has been open for. You'll probably need to kill that transaction (or have the user commit if it's someone in Management Studio). Then the log will clear automatically.
0
Check the autogrowth setting of the log file. Make sure it is smaller than the max file size and can grow. You can use this query for that:
SELECT DB_NAME(mf.database_id) database_name,
mf.file_id,
mf.type_desc,
mf.name,
LTRIM(STR(vfs.size_on_disk_bytes/1024.0/1024.0,30,3)) size_mb,
CASE
WHEN mf.max_size = 0 OR mf.growth = 0 THEN '--'
WHEN mf.max_size = ...
9
When performing a cross-database transaction, in which transaction log(s) is the information stored?
The transaction log isn't recording the SQL statements being executed, as you might be expecting. Instead, it's recording the changes to the raw data in each database, independently.
It's possible for a stored proc from one database to be working entirely in the transaction log of another database.
... database1..my_stored_procedure AS
BEGIN
INSERT INTO ...
6
You can import the data by using operations that can be minimally logged. See Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging in Bulk Import.
And you should consider reducing the duration of transactions during the import. Use batches of limited size and commit periodically.
2
Here is answer for my own question.
Run the below query to get information about log files.
select log_reuse_wait_desc from sys.databases where name = 'DBName'
It will give output if there is any. I got following output.
og_reuse_wait_desc
REPLICATION
There was some replication related objects remaining the database even after removing the ...
2
Steps for shrinking the log are going to be
Backup transaction log through either SSMS or T-SQL and then perform a shrink
commands for SSMS are under the tasks if you right click the database name
BACKUP LOG <Databasename> TO DISK N'<path\database_log.ldf';
GO
DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
You will ...
3
Read How to Shrink SQL Server log for an explanation how the circular nature of the log may prevent shrink after truncation. Is possible that you log's last LSN point into a VLF that is at the tail of the LDF. Counter intuitively you must advance the log, by generating log writes, to allow it to shrink.
0
You need to create a backup first, dependent on the backup model that is set up for the database before you can shrink the database.
You can try running this:
USE <databasename>
GO
BACKUP DATABASE <databasename> TO DISK '<absolute path goes here>\<databasename>.bak';
GO
Or you can do that from SSMS and use the graphical tools ...
Top 50 recent answers are included
