I have a Database that is set to Simple Recovery mode.
It is not clearing the log and the transaction log is growing.
I thought that it would clear the log upon checkpoint?
How can I force the Transaction Log to clear since NO-TRUNCATE is no longer available?
I am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:
Msg 2714, Level 16, State 6, Line 6
There is already an object named '#mytemp2' in the database.
We have a SQL Server 6.5 with SP5a installed and are having problems with the Transaction log not clearing every night when a scheduled backup takes place.
Is there anyway to debug this?
The Scheduled task that is run is
"DUMP DATABASE DBSALES TO DBSALESBK with INIT
DUMP TRANSACTION DBSALES WITH TRUNCATE_ONLY
DUMP DATABASE DISTRIBUTION TO DSBK WITH INIT
DUMP TRANSACTION DISTRIBUTION WITH TRUNCATE_ONLY"
I have also run on it DBCC Checktable(Syslogs) but it all seems fine. Any help would be appreciated.
If i have 3 Tasks in my control flow with checkpoint enabled and the transactionoption of the tasks is required,
Transaction option of the package is supported.
if the second task fails , the package restart from the first task when its running again instead of using the checkpoint and begin from the second task
Can anyone tell me if it€™s a bug in SSIS?
I backed up my transaction log file using this:
BACKUP LOG iTest2_AELetters TO DISK = N'nul'(yes I know this breaks the log chain - it's expected in this intance)
But after I run it my t log is exact same size. I have refreshed many times.
I am using SQL Always On on this DB.
I'm taking the liberty to announce the availability of a suite of articles on my web site about error and transaction handling in SQL Server. In total there are three main parts and three appendixes.
The first part is a short jumpstart, while Part Two is a long in-depth discussion of what can happen in SQL Server in case of an error and what commands that are available. Part Three covers implementation and has lot of examples as well as a facility for logging and raising errors.
The appendixes cover special areas: linked servers, the CLR and Service Broker.
Start at [URL] ....
Hello,
How do I clear the Recently Used Server list in SQL Server Management Studio? I am talking about the "Server Name" field that is part of the "Connect To Server" drop down when you initially open SQL Server Management Studio.
Thanks.
I'm trying to create a cte to return a list of lots and every period between the first and last transaction date for each lot. I've gotten this far:
SELECTIntLotKey
,DATEPART(YYYY, StartDate)StartYear
,DATEPART(MM, StartDate)StartPeriod
,DATEPART(YYYY, EndDate)EndYear
,DATEPART(MM, EndDate)EndPeriod
[Code] ....
This gives me the following results:
IntLotKeyStartYearStartPeriodEndYearEndPeriod
271532013120135
28468201312201312
2846920131201312
2847020131201312
28472201312201312
593022013120131
593032013120131
Now what I need is something that looks like this:
LotKeyYearPeriod
2715320131
2715320132
2715320133
2715320134
2715320135
[Code] .....
Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after.
I am basically trying to update a table which reflects account transactions. Accounts get paid in full but occasionally balance payments can be reversed and I want to update the table to show this - I need to show which period the account was previously paid in full.I've created a simplified version of the scenario and below are a couple of examples of things I've tried that do not work. I understand why they do not work but I'm struggling to figure out how to update the 'PeriodPrevPaidInFull' field.
create table Trans
(
AccNo int,
Transaction_Period_Index int,
PeriodOpeningBalance money,
DebtBalance money,
PeriodPaidInFull int NULL,
PeriodPrevPaidInFull int NULL,
[code]...
Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
i want to run a transaction across mulitpule instences of sqlserver with out linked server.
distributed trnasaction can do it with link server , can it do it with out linked server.
I am creating a report query that returns all unreconciled P/O lines. I am near completion but I am unable to find a way to remove the reconciled records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
CREATE TABLE [dbo].[Purch_Ledger](
[EPDIVI] [nvarchar](3) NULL,
[EPSUNO] [nvarchar](10) NULL,
[EPSINO] [nvarchar](24) NULL,
[EPDUDT] [numeric](8, 0) NULL,
[EPTRCD] [numeric](2, 0) NULL,
[code].....
Whatever I try I cant find a way to get rid of the unwanted records.
I know Oracle provide pragma directives to execute autonomous transactions which i used before on Oracle for logging. Now i want to repeat the same in SQL Server but unfortunately i found such pragmas are not existing in SQL Server.After several google searching, i have found that i can use loopback linked server to generate autonomous transaction calls.
If i have Server A & Server B where server B is a loop back server of Server A and all my objects are existing on Server A. I just wanted to user Server B for logging only. should i have logging tables on Server B? Logging procedures on Server A? and call logging procedures (via Execute ) from application procedures residing on Server A?
I have a setup of transaction replication between one publisher and subscriber in the Same server.Now, I need to add a new subscriber to the existing publisher. So publisher database name is DB_A and Subscriber 1 name is DB_B. So the new subscriber will be DB_C. Is this kind of setup possible on one server?
If yes then at the time of reinitialization is it going to apply the snapshot on DB_B as well as DB_C?Also let say if due to disk error DB_B gets corrupted then will data be still replicated between DB_A and DB_C? (Assuming publisher, subscriber 1 and 2 are sitting on individual disks).
I have a stored procedure that does the following
BEGIN TRANSACTION OUTERTXN
BEGIN TRANSACTION
Copy records from live to archive
END TRANSACTION with commit or rollback
execute sproc to write audit log with success or fail
IF transaction was committed
BEGIN TRANSACTION
Delete records from live the archive
END TRANSACTION with commit or rollback
execute sproc to write audit log with success or fail
End IF
END TRANSACTION OUTERTXN with commit if both inner transactions were successful or rollback if either failed
If either inner transaction rolled back execute sproc to write audit log saying whole process is rolling back End IfMy problem is that if the outer transaction rolls back then I am losing the two audit records because they are part of the transaction scope. I want these executes to commit even if the master transaction fails.
We're running the Microsoft product SMS 2003 SP1 for software deployment, patching, hardware inventory, etc. The back-end is SQL 2000 Enterprise SP4 which is installed on the same box as the SMS 2003 SP1 product, and the DB is 145GB's.
We started noticing that the server would freeze every minute or so for 30 seconds. We started logging stats via perfmon and saw that the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 for 30 seconds at the same time the freezing occurred. I have determined that this is occurring during the checkpoint. The recovery interval option is set to the default of 0 on SQL, when I changed the setting to every 5 minutes, the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 every 5 minutes and would subside after 2 minutes. I understand the need for the checkpoint / recovery interval option, but don't believe this high average disk queue length should be occurring.
Does anyone know why this is happening and how to fix this ? The freezing of the box while checkpointing is killing me.
Thanks
Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR
[Code] ....
I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)
[Code] ...
Hello, and sorry if this is really obvious.
Is there a way to wipe all data from a table, besides the obvious manual way?
We have a production sql server V6.5 sp5a (running on NT 4.0 sp3) that has a tempdb that is fulling up about every two weeks. (The log to tempdb is okay, it is not filling up). Tempdb is defined as 400 MB. I have been reading about "The two most common db errors"; 1) The db log ran out of space and 2) The db ran out of space. We are receiving "The db ran out of space." Once tempdb gets near full is there anyway to clear tempdb without rebooting the nt server? Is there a stored procedure that can be scheduled to free up tempdb?
My books tell me the tempdb is used for creating temporary tables or storing temporary information. For example, a temporary table in a stored procedure or sql may create a temporary work table as a result of a query with a 'group by' or 'order by' clause. If these tables are temporary why don't sql release them or free up the space used by tempdb?
Thanks in advance!
When I truncate the transaction log the log space available does not change?
View 2 Replies View RelatedHello!
I'd like to ask you what means the term "database clearing", if such a concept exists in the Database Theory.
Maybe this is not directly related to SQL Server but any help would be very appreciated. So please anybody who have an idea of what this could mean is welcome to reply here
Thank you very much in advance.
With best regards,
Sorin
I have entered some temporary values in a table located in an SQL database, to test a project to see if it was working. Now I want to remove the values so that later, the program I have created will be capable of using the table for it's true purpose. However, when I try to delete the values located there, I receive an error message. How can I clear the table?
View 2 Replies View Related
Hello All,
I am here again with a query.
In SQL SERVER 2000, there is a command DBCC OutputBuffer which returns the current output buffer in hexadecimal and ASCII format for the specified system process ID.
Is it so that everytime we execute a stored procedure the contents of the outputbuffer are automatically cleared so that the new contents are inserted into the outputbuffer ?
If it is not cleared then how can we clear this outputbuffer.
Thanks,
Kapadia Shalin P.
Hi all,
Few clarification needed.
1. Actually when does the cache gets cleared in SQL Server ?
2. Is clearing the cache in production environment recommended ?
3. What is the main purpose of DBCC DROPCLEANBUFFERS & DBCC FREEPROCCACHE commands ?
Thanks ,
DBLearner
Just wondering if there was a way of querying the transaction log to ascertain particularly large queries filling it?
If you have taken over another persons solution and the transaction log seems to be filling very fast and want to break it down to find out what are the main causes, what would the best way be?
Hi
I have two questions:
1. Is there a way to clear all records in a table from within a store procedure? (Instead of having to go into the table and manually selecting all rows and deleting them that way.)
2. I want to sort a list of records so that the only one left are the ones with just single words in. So I have used
WHERE SearchText LIKE '% %'
to remove the cells with spaces in, but I need to be able to remove the ones with punctuation too. Is there an easy way to do this?
Thanking you in advance.
Hello:
We have been testing a new vendor_purchased application and are now
running some month-end processes/jobs now. One of the processes that we
kicked off stopped when a 2gb tempdb filled up with only 5mb or so remaini
g. In reviewing what happened, I noticed that tempdb is in the process of
clearing itself out slowly when we restarted the process. The database has
80% of its disk space remaining and the transaction log has also 75%
remaining.
My questions are:
1) Although I don't think so, is there nay way of speeding up the process
of tempdb clearing out the data in it?
2) We will need to examine what sql code the vendor has used that caused
this to happen. Aside from group by and order by, if there are a lot of
'select into' code, what alternatives do we have?
Any information that can be provided will be fine. THanks in advance.
DAvid Spaisman
Having issues with a table clearing out properly which is causing problems in the program.
By design there should only be one row in this table which updates with the most current record. However on this one system the table does not clear/update like it is supposed to, and instead starts adding multiple rows.
I have tried truncating the table but the problem does not resolve itself. Does anyone have any ideas as to why this might be happening?
Thanks!
Shawn
Hi everyone,
I have a question that I believe should be simple to answer yet I cannot find the answer anywhere. I am trying to make it possible for my report to clear the input box whenever the report is run or when anything in a dropdown list is selected. The reason why I want this is because my report has a dropdown list that inputs date ranges for "quick" report info. The other option is to manually type in the begin and end date. If anyone could help me out with this I would be very grateful.
Thanks,
Roy
I am doing some general housekeeping for a couple of our SQL boxes in the Development environment. All the databases are set to Simple recovery mode. No need in anything else for these boxes. I have a database on all the boxes named "DatabaseMaintenance" Keeps things like all the sprocs for any type of database maintenance, etc....
I would like to schedule a single sproc that is located in the DatabaseMaintenance database to shrink the Transaction logs on a set schedule. They sometimes grow quite large while testing and developing. The thing that I cannot seem to get around, is when using the ShrinkFile command, one must use the Log Name. If this code is in a sproc that is located in the DatabaseMaintenance database, it will fail when attempting to call out to a different database. Because the Log does not exist on the database that the sproc is located.
How can I get around this small dilemma? There are only about 10 databases per box. To a point we really do not care what happens to them. They are on a Full backup schedule daily, just to keep the objects. As I stated previously, the logs will still grow huge at times while pumping data.
Is there a way to create a piece of code that will run against each database on the server, and be stored in a single database? Other than the system databases of course.
I have a data set that comprises of a transaction_id and then a list of line_id's for that transaction.
eg:
transaction_id = 12345 : Line_id = 129172
transaction_id = 12345 : Line_id = 1654572
transaction_id = 12345 : Line_id = 56229172
This would imply that 3 items were purchased for a single transaction.
The output I'm looking for is follows:
transaction_id = 12345 : Line_id = 1
transaction_id = 12345 : Line_id = 2
transaction_id = 12345 : Line_id = 3
If there are 5 Line_id's for a transaction_id then the output I'm looking for would be
transaction_id = 992345: Line_id = 1
transaction_id = 992345: Line_id = 2
transaction_id = 992345: Line_id = 3
transaction_id = 992345: Line_id = 4
transaction_id = 992345: Line_id = 5
If there are duplicate Line_id's i.e purchased two of the same product, then the logic must carry on as above
eg:
transaction_id = 56474: Line_id = 1
transaction_id = 56474: Line_id = 2
I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.
I need to configure a replication which will gather logs from the publication once in a day.