Transaction Log Truncate Makes You Loose Current Information?

Jun 28, 2007

Hi,



I have one database configured with the Recovery Model "Simple".

I am getting alot of full transaction log messages... is this supposed to happen?



Another question is:



Imagine i am in a middle of a big select into statement... and in another query i run the backup truncate log... am i going to loose information on the other batch ("select into?")??



Kind Regards,

Luis Simões

View 5 Replies


ADVERTISEMENT

Current Date And Time Information

Jul 23, 2007

Riding MVJ's excellent F_TABLE_DATE function, I often use this view for current date and time information.SELECTCURRENT_TIMESTAMP AS Now,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0.00069445, CURRENT_TIMESTAMP), 0) AS previousMinute,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CURRENT_TIMESTAMP), 0) AS thisMinute,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CURRENT_TIMESTAMP), 0.00069445) AS nextMinute,
DATEADD(HOUR, DATEDIFF(HOUR, 0.0416667, CURRENT_TIMESTAMP), 0) AS previousHour,
DATEADD(HOUR, DATEDIFF(HOUR, 0, CURRENT_TIMESTAMP), 0) AS thisHour,
DATEADD(HOUR, DATEDIFF(HOUR, 0, CURRENT_TIMESTAMP), 0.0416667) AS nextHour,
DATEADD(DAY, DATEDIFF(DAY, 1, CURRENT_TIMESTAMP), 0) AS previousDay,
DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) AS thisDay,
DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1) AS nextDay,
DATEADD(WEEK, DATEDIFF(WEEK, 7, CURRENT_TIMESTAMP), 0) AS previousWeek,
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0) AS thisWeek,
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 7) AS nextWeek,
DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AS previousMonth,
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AS thisMonth,
DATEADD(MONTH, DATEDIFF(MONTH, -1, CURRENT_TIMESTAMP), 0) AS nextMonth,
DATEADD(QUARTER, DATEDIFF(QUARTER, 92, CURRENT_TIMESTAMP), 0) AS previousQuarter,
DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CURRENT_TIMESTAMP), 0) AS thisQuarter,
DATEADD(QUARTER, DATEDIFF(QUARTER, -1, CURRENT_TIMESTAMP), 0) AS nextQuarter,
DATEADD(YEAR, DATEDIFF(YEAR, 365, CURRENT_TIMESTAMP), 0) AS previousYear,
DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0) AS thisYear,
DATEADD(YEAR, DATEDIFF(YEAR, -1, CURRENT_TIMESTAMP), 0) AS nextYearFor example, If I want this weeks data I just join against this view and useWHEREDtCol >= thisWeek
AND DtCol < nextWeekOr previous months dataWHEREDtCol >= previousMonth
AND DtCol < thisMonth
EDIT: Faster week calculations.

View 5 Replies View Related

Can&#39;t Truncate Transaction Log

Oct 7, 1999

Hello,

We have a SQL 6.5 Server with several DB's on it. Specially there's a critical DB on two separate devices for the Data and Transaction Log. The Data Device is 700MB and the Transaction Log started 210MB. Yet, the truncate function of the Log Device is not freeing space on it. We have been forced to expand the transaction device up to 860MB!!!! which is an outrageous size for it. We have tried the DBCC CHECKTABLE(syslogs)followed by DUMP TRANSACTION <<db_name>> WITH NO_LOG and then once again the DBCC CHECKTABLE(syslogs).
We even tried to create a new DB only with the dat file, but this also didn't work. Our Server Disks are almost full, and we can't grow the device no more.

Any suggestions??? PLEASE IT's REALLY URGENT!!!!

Best Regards,

BIT CeLA
Pfizer Labs.
Colombia

View 4 Replies View Related

Truncate Transaction Log

Feb 6, 2002

How do you truncate the transaction log in SQL 2000?

View 1 Replies View Related

Truncate Transaction Log

Feb 13, 2002

I need to truncate the transaction log, however, to do a backup on it we need 15GB of space free on the server which we don't have. How do we just force it to truncate it? I know the actual database is backedup and is OK...

Thanks,
Keith

View 1 Replies View Related

How To Truncate Transaction Log

Aug 1, 2001

SQL Server 6.5
Hi!
We have trans.log 200MB in total and 71 MB free space.
I run DBCC OPENTRAN and it shows no active transactions exist.
I run DuMP TRANSACTION .... WITH TRUNCATE_ONLY and it doesny clean log also.
What to do to get space back?

Thank you,
Elena.

View 4 Replies View Related

Transaction Log - Truncate

Oct 17, 2001

SQL Server 6.5
NT

A number of procedures where run that filled the transaction log. Can I truncate the log during regular working hours or should I take the database down to single user mode and truncate?

Thanks in advance..

View 4 Replies View Related

Truncate Transaction Log

Feb 19, 2004

Hi all,
please show me the way how to truncate transaction log.
thank alot

View 2 Replies View Related

Cannot Truncate Transaction Log.

Feb 27, 2004

DB Newbie question ahead.

I have a mere 100MB db with a 4GB transaction log. I want to truncate the log as I understand that truncating it will shrink the log by removing the transactions that have already taken place. However, the option to do a transaction backup is greyed out. I suspect this is from the db being in transactional replication with another server; however, I don't know for sure.

Are there any other ways that I can shrink the transaction log? I would like to do shrink it without taking the db offline either.

View 3 Replies View Related

Truncate Transaction Log

Dec 11, 2007

Hi

I need a simple command to truncate my transaction log. Currently it is 4gb and my .mdf file is only 300mb.

I have tried BOL, but all I need is a command to truncate it.

Thanks!

View 3 Replies View Related

Truncate Transaction Log

Dec 11, 2007

We do full backup every day and recovery model is Full, but we never done transaction log backup, so the transaction log files keep growing. What should I do? I think I should set recovery model to Simple, and actually we do DBCC Shrinkdatabase after full backup every day, but the transaction log file is still around 15GB.

Any suggestion would be appreciated.

View 9 Replies View Related

Possible TRUNCATE / BCP Into A Table In One TRANSACTION?

Aug 12, 1998

Hi all,

Is it possible to TRUNCATE a table and BCP data into the same table in one
TRANSACTION?
My problem is that I want to refresh(delete and via BCP append new data) a
table without disturbing running applications. Can I run BCP from a
SQL-script or a stored procedure?

Thank`s
Jonas Dahlqvist
Alfa Laval Automation AB

View 1 Replies View Related

Unable To Truncate The Transaction Log

Jan 27, 2005

Hello;

Within SQL Ent Manager - I am unable to truncate the tran log using the shrink file option window (although I can shrink the database file) - nor can can I truncate the log using command line sql in a query analzer window (dump tran < > with truncate only)....

Anyone out there offer any suggestions??

Thanks.

View 4 Replies View Related

Transaction Log Doesn't Truncate

Dec 29, 2007

I use SSME to do a full backup of both the database & transaction log, selected "Truncate ..." in the options for the log backup. The log doesn't truncate.


I have looked at the reasons logs don't truncate in Books Online & can not find any that apply. There are no open transactions & in sys.databases log_reuse_wait is 0.

Any help would be appreciated.

Don Seydel

View 1 Replies View Related

Edit / Update Query - Change Current Sales Information For A Specific Product

Apr 11, 2012

I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.

The top part of the following code selects the specific product however I cannot get the update query to work.

Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";

$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";

$query = sqlsrv_query($link, $describeQuery);

View 3 Replies View Related

Truncate Transaction Log Through Sql Query Analyser

Oct 20, 2005

Hi guys

My website is in asp and sql2000. My problem is the ISP gave access to database through query analyser. some days the transaction log is growing too high. so i want to clear it. i call up them and clear it. My question is can truncate the log file through query analyser ?
I had limited access to database.

Jini

View 11 Replies View Related

How To Truncate Transaction Logs In Sql Server 7.0

Mar 6, 2007

friend can i ask something like this

what query that can i truncate the transaction logs or shrink it into
2mb.

Please response to this. I need help


Thanks

View 7 Replies View Related

Truncate Transaction Log And SHRINK DATABASE

Aug 28, 2007

Is it possible to truncate Transaction Log and Shrink DATABASE while the database is being used by users or the database becomes unuvailable during this operations?

Thank you.

View 2 Replies View Related

Truncate Transaction Log Script Won't Work On Db Name With '-'

Feb 25, 2008

Hello all, I have a very simple script which I use to truncate and reclaim space on all the transaction logs in a SQL Server 2005 database. However, I have some Sharepoint db names I can't change that have dashes ('-') in the names, e.g., SharePoint_AdminContent_dc27334f-fb2d-4453-9764-5d8b730fb9e1. The script won't back up those databases because it has a problem with the dashes in the names. Does anyone have any thoughts on how I could modify the script to get it to work? Here is the script:

ALTER PROCEDURE [dbo].[SP_GlobalTruncate_transaction_logs]

AS

Set quoted_identifier off

DECLARE @dataname varchar(300)

DECLARE @dataname_header varchar(75)

DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases

WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind')

OPEN datanames_cursor

FETCH NEXT FROM datanames_cursor INTO @dataname

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status = -2)

BEGIN

FETCH NEXT FROM datanames_cursor INTO @dataname

CONTINUE

END

SELECT @dataname_header = 'Database ' + RTRIM(UPPER(@dataname))

PRINT ' '

PRINT @dataname_header

EXEC('BACKUP LOG ' + @dataname + ' WITH TRUNCATE_ONLY')

EXEC('DBCC SHRINKDATABASE (' + @dataname + ',TRUNCATEONLY)')

FETCH NEXT FROM datanames_cursor INTO @dataname

END

DEALLOCATE datanames_cursor

PRINT ''

PRINT ' '

PRINT 'Free space removed and transaction log truncated for each user database'
GO

And here is the error I get:
Database SHAREPOINT_ADMINCONTENT_DC27334F-FB2D-4453-9764-5D8B730FB9E1

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.

Msg 319, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.


Thanks so much for the help.

View 4 Replies View Related

SQL 2012 :: Cannot Truncate Log File Which Is Part Of Transaction Replication

Feb 9, 2015

SQL 2012 Ent SP2

Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.

I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.

The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.

I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).

I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.

I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.

Do I have to remove replication completely to get round this? Surely not.

I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?

I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.

View 1 Replies View Related

Truncate Transaction Log On A Pull Transactional Replication Environment

Oct 3, 2007



I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?

Thanks in advance.

View 1 Replies View Related

Mirroring :: How To Truncate / Shrink Transaction Log If Mirror Is Enabled

Aug 18, 2015

We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.

We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.

How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.

View 17 Replies View Related

Where Is The Transaction Log Information Kept?

Feb 27, 2004

I'm assuming that in one of the MSDB tables I can find transaction log information (current size, amount of space used.. blah blah).. but I can't seem to figure out which one.. any help is appreciated.. SQL 7.0

Travis

View 4 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Transaction Log Does Not Truncate And Shrink Using A SQL Server 2005 Maintenance Plan

May 15, 2006

We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.

View 29 Replies View Related

Getting Information Out Of A Transaction That Rollsback

Sep 13, 2007



I have a client who is logging diagnostic information in the midst of lengthy, nested transactions, and finds that the log informaiton disappears when the outer transaction is rolled back. What he wants to do is essentially defeat the transaction, and get records added to a table from within a transaction that don't participate in the transaction.

I've thought about using an Extended Stored Proc, or an RPC with SET_REMOTE_PROC_TRANSACTIONS set to "OFF", or trying to create a linked server and defeat the distributed transaction system, but before I set out building and testing I'd like to ask the experts how to purposefully get data out of a transaction.

Yes, there are work-arounds possible within the T_SQL Procs, we've talked about loading the debug information into a table var which is passed around until all the transactions are cleared and then is logged, but he doesn't hant to go back and revise hundreds of procs. We're hoping to adjust the single logging function to make a "transaction-proof" INSERT.

Any ideas?

GA Dean

View 5 Replies View Related

Retrieve Information From Before A Transaction

May 11, 2006

I have a report and it retrieves information from an etl logging table which contains this info: etl_process_name, active_since_date, last_run_date.

When the packages are executing, the table cant be queried because it is in a transaction. Therefore the report keeps generating :s
The thing I want is that: If I query that table (with the report to show the logging table information) and the packages are executing, I want to see the values of the table before the transaction.

What I tried:
From sql management studio:

ALTER DATABASE MyDataBase
SET ALLOW_SNAPSHOT_ISOLATION ON
set transaction isolation level snapshot
go
SELECT etl_process, last_run, active_since FROM config.etl_settings

I did this while the packages were executing and it works but it won't work when I execute the same SELECT statement from the Dataset in the report. How is that possible? Does it have something to do with setting the database options for all users?

Additional info:
I'm database owner
The report solution has a shared datasource which refers to the same database

View 3 Replies View Related

Select 'Current LSN' And 'Transaction ID' From ::fn_dblog

Mar 23, 2006

Gurus,How do I choose multi-name 'columns' like "Current LSN" or "TransactionID" from ::fn_dblog?I CAN select single 'column' names like 'Operation' and 'Context'.I do NOT want all 'columns' returned:- I don't want Select (*) from ::fn_dblogDoug

View 2 Replies View Related

Accessing The Current Transaction In BOL (April 05')

Jul 23, 2006

Here is the text I do not believe is correct or at the least misleading...

"If you want to terminate the external transaction from within your stored procedure or function. In this case, you call the Transaction.Current.Rollback method. "

You cannot terminate transactions declared in TSQL in SQLCLR routines.

View 3 Replies View Related

Get The Current Transaction ID During The Transaction

Jan 15, 2008

Hi Everyone-
i wonder how can i get the current transaction ID during the running of transaction

thanx
Maylo

View 3 Replies View Related

Enlisting Script Component In Current The Transaction

Mar 8, 2007

I have an SSIS package with its TransactionOption set to 'Required'. A data flow in the package has a script component that performs updates to a database table. The TransactionOption on the data flow is set to 'Supported'. However the script component apparently does no get enlisted to the current transaction because the updates made in the script component do not get rolled back when the package fails.

Documentation about the AquireConnection method indicates passing a 'transaction handle' parm to the AquireConnection method. I'm thinking that I need to pass this 'transaction handle' to the AquireConnection method of the connection used by the script in order for the script to become part of the current transaction.

How do I determine it what the transaction handle is? I have no idea what value I should be passing. I am new to Sql Server beginning with Sql Server 2005. I have no background with DTC.

My code in the script currently is:

dbConnMgr = Me.Connections.ADONetConnection

dbConnection = CType(dbConnMgr.AcquireConnection(Nothing), SqlConnection)

I need to pass the transaction handle rather than 'Nothing' to the AquireConnection method but don't know what the transaction handle is.

Thanks.

D.

View 2 Replies View Related

Loose ODBC Conection

Dec 20, 2007

I am very new to all thisI have a access databse wich uses a ODBC conection to mysql server. Ican open the and run the database find but if i leave the dadabaseloaded but do not use it when i go back to it is has lost theconection and i need to close the databse and reopen itIf there a way to make sure i alwasy have a coenctionThanks

View 1 Replies View Related

How Do You Roll Back Transactions For A Single Table From The Current Transaction Log?

Nov 1, 2007



Goofed up and ran an update query. It messed up all the data in a single table. I'm trying not to restore the table from a previous backup since the backup is more than 20 GB. It's going to take forever to restore it. Any advice would be much appreciated!

Thanks!
Charles.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved