Transaction Log Can Not Be Truncated Permanently
May 27, 2006
My SQL Server 2000 database log was full (2MB) and I am unable to add new column to a table. So that I follow this article to truncate my log file to 1MB. It works. But when I add a new column to a table after that. The log file went back to 2MB in size and I got the "the log file is full" error message again for the operation. BTW, my reovery mode was set to "Simple". I don't understand how the save table operation can recover my change on log size. What should I do to "permanently" truncate my log?
http://www.support.microsoft.com/?id=272318
I did this:
DBCC SHRINKFILE (myDB_Log, 1)
BACKUP LOG myDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE (myDB_Log, 1)
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
The transaction log in a database in our SQLSERVER-2000 server hasgrown to 16GB. I cannot shrink the transaction log manually because itsays that the entire 16GB log size is not free. This is strangebecause we backup the transaction log every hour, and that should havetruncated the transaction log, and should have limited the size of thetransaction log; somehow, the entire transaction log is still markedas being used.I was under the impressionI believe that must have something to do with the fact that thedatabase is a part of our nightly replication. The reason is that whenI tried the following commands, I got that error message:checkpointdump transaction isprod with no_logThe log was not truncated because records at the beginningof the log are pending replication. Ensure the Log ReaderAgent is running or use sp_repldone to mark transactionsas distributed.What does this mean? How can I get away from this mess?Thanks.Jay Chan
View 5 Replies
View Related
Jul 23, 2005
Hello All,The setting is SQL server 7, on Windows NT.If the distributor can not access the log on the publisher database ( butsubscribers are not deleted yet), will the full backup of the publisherdatabase truncate the transaction log? My guess is, it will not truncate thelog.Can any one confirm please?Thanks,Mokles
View 2 Replies
View Related
Sep 3, 2002
How, using SQL, do I turn off the identity property in the table? I need a script to run against a number of db to permanently remove the identity property from the tables without needing to use Enterprise Manager.
Thank you,
Jill
View 1 Replies
View Related
Jul 6, 2015
In SQL 2012 I'm trying to delete a log file (both physically and logically). There are two log files and one of them is unnecessary. When I click 'remove' in SSMS it will delete the log file, but then when I go back under database properties it's still showing up even though the file has been physically removed from the OS. I'm wondering what steps I can take to get rid of the file permanently?
View 12 Replies
View Related
Mar 6, 2007
I hope this is the right forum to post my question in.
Does anyone know if or when Microsoft will discontinue support for 2000 DTS Legacy packages within SQL Server 2005? We€™re in the process of migrating from 2000 to 2005 and have a lot of packages to migrate to Integration Services. We€™re having problems migrating the packages because quite a few of them are complex and won€™t run after migrating to IS without a major rewrite. Right now it seems that those packages will run just fine under the Legacy folder on the 2005 instance with the data connections pointed to 2005 databases. We€™d just like to plan appropriately if we absolutely have to migrate those packages to IS at some point soon. We realize that we'll need to create new packages using IS, though.
So I€™d appreciate it if anyone has heard anything to please let me know. I apologize if this has been asked before, but I couldn€™t seem to find any posts on it.
Thanks!
View 4 Replies
View Related
Aug 3, 2015
I've a spatial (GIS) Data which is used frequently insertion, updation.
5 lakh records insertion in daily basis. when I trying to generate reports last 3 days or one weak, it takes 20-30 minute.
very disappointing while playing with clients. how to boostup and perform fast.
I think as so once we set query plan in buffer permanently then i would be faster than ever.
View 4 Replies
View Related
Mar 17, 2008
In SQL 2005, I've created a test scenario in AdventureWorks where I:
1.) Create a database master key:
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'
GO
2.) Create a certificate:
CREATE CERTIFICATE HRCert
WITH SUBJECT = 'Comments'
GO
3.) Create a symmetric key:
CREATE SYMMETRIC KEY CommentKey
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HRCert
GO
4.) Add a test column to the HumanResources.JobCandidate table:
ALTER TABLE HumanResources.JobCandidate
ADD Comments varbinary(8000)
GO
5.) Open the symmetric key for use:
OPEN SYMMETRIC KEY CommentKey
DECRYPTION BY CERTIFICATE HRCert
GO
6.) Insert and Encrypt values ('Yes') to the newly created column:
UPDATE HumanResources.JobCandidate
SET Comments = EncryptByKey(Key_GUID('CommentKey'), 'Yes')
GO
-------
Great. Now all the textbooks say that, to view the column values in a decrypted state, you should:
SELECT CONVERT(varchar, DecryptByKey(Comments)) AS [Decrypted Comments], *
FROM HumanResources.JobCandidate
------
Great, I get it. But here's the rub. What is the best way to permanently decrypt the column and keep it in a cleartext state?
Running the following works, but keeps the column values in a varbinary (hexadecimal) state which is what we originally created:
UPDATE HumanResources.JobCandidate
SET Comments = DecryptByKey(Comments)
GO
But if I want to transform this varbinary(8000) column into a human-readable varchar column, the only thing I could come up with was a temp table solution:
UPDATE HumanResources.JobCandidate
SET Comments = DecryptByKey(Comments)
GO
DECLARE @temp varchar(1000)
SET @temp = (SELECT TOP 1 CommentsFROM Human Resources.JobCandidate)
CREATE TABLE #decrypt (Comment varchar(1000))
INSERT INTO #decrypt (Comments) VALUES (@temp)
GO
ALTER TABLE HumanResources.JobCandidate
ALTER COLUMN Comments varchar(1000)
GO
UPDATE HumanResources.JobCandidate
SET Comments = (SELECT Comments FROM #decrypt)
GO
DROP TABLE #decrypt
GO
--------
It works, but seems so inelegant to me.
Is there an easier way?
View 5 Replies
View Related
Jul 23, 2005
Dear group,I have to replicate remote data to a SQL Server in the headquarter.The data are on a site which does not have permanent online-connectionto the headquarter.I have written a script which replicates the data and i want to set upa process / batch on the headquarter-machine which roughly does thefollowing1) Connect to the remote site2) start replication script (in fact a stored procedure)3) on success disconnect from the remote siteAll this has to run automatically e.g. during nighttime.Could someone please outline how i set up the connection and keep itup during my database-action and how to automatically disconnect aftersuccess of my replication script.I'd be jolly grateful.Thanks in advance and Greetings from ViennaUli
View 1 Replies
View Related
Aug 24, 2015
I have stored procedure which runs on some period, what i want is when it first run i want to store that date permanently or till the next time it runs again, and then i need to take my data from that store proc , where hist_date between (date stored when it ran first time,example, 08/21/2015) and today date.(exampple, 08/24/2015)
Now next time when it runs , date stored should be updated in this case it should be (08/24/2015) .
How can i do this in stored proc, I tried to use temp table  but it didn't work .
View 13 Replies
View Related
May 13, 2015
User A creates the SSIS Package and provides the password for the connection manager for a task (for OLE DB source) with SQL authentication.Â
When User B tries to run the SSIS package, the error is thrown as : The package may be damaged.
On typing the password again inside the connection manager, the execution occurs successfully. Is there any workaround to save the password permamanently for the connection manager rather than each user entering it in order to run the package ?
View 4 Replies
View Related
Jul 21, 2015
last couple of months we're getting automatically generate jobs under sql server job event. causing occupy the resources.
in daily basis i've to remove mannualy. i've do disable the job no of times from properties, but it generates again.
how to remove it permanently. following are the job which generates automatically-
15559-cook.exe,
15564-dbdotas
15565-dbdotas2
15552-ftpbacks.exe
15554-install.exe
15555-kills.exe
15557-kugou2010
15558-macs.exe
15553-pdoors.exe
15561-regs.exe
15562-regsa.exe
View 9 Replies
View Related
Mar 11, 2008
This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1
Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into. Here is what my program should do:
As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:
http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
-sorry, I didn't capture the mouse cursor inside the red hexagon
I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.
Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter
'Consider using Me._adapter that is used already
CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()
'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then
MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If
Try
Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try
'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
Dim rows As Integer
rows = 0
Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)
CN.Open()
rows = CMD.ExecuteNonQuery
If rows = 1 Then
MsgBox("Table HexMap updated successfully")
Else
MsgBox("Failed to update the HexMap table")
End If
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
Thanks for reviewing my code.
Technozoide
View 1 Replies
View Related
May 31, 2008
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
View 1 Replies
View Related
Feb 22, 2007
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
View 3 Replies
View Related
Feb 6, 2007
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View 1 Replies
View Related
Dec 22, 2006
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
View 8 Replies
View Related
Jan 8, 2008
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
View 2 Replies
View Related
Apr 21, 2008
Hi
I have an SP, which queries a table and correspondingly outputs an ntext column. Before executing this SP. I used "save results as " option under Query analyzer tool bar. So that, when ever i execute this SP I can store the value under a file. The query is executing fine , but it is truncating the output of this ntext value.
Does any body knowsfaced this issue?
Iam using sql 2000
Thanks!
Santhosh
View 5 Replies
View Related
Apr 11, 2001
Hi!
In the results of our queries, the datas in the fields are truncated to 255 chars.
Is there a parameter to change to get the entire field? Their format is varchar 8000 and the fields are longer than 255 characters in the db.
Thanks for your help.
View 2 Replies
View Related
Mar 9, 2000
Hello,
I am trying to run a query
select logid, count(logid) from temp2
group by logid
order by logid
compute sum(count(logid))
when I get the result the numbers are being truncated
eg instead of 10471066 it shows 104710 so last two digits
get truncated. Any ideas or hints appreciated.
Thanks
HP
View 1 Replies
View Related
Mar 30, 2004
Recently we've upgraded SQL 7.0 to SQL 2000. We use a Database Maintenance Plans to backup the databases and transaction logs. Everything seems to be working, but I’m getting an error message in the event viewer that has been described in Microsoft Knowledge Base article 818202 "PRB: A'Database log truncated" Error is logged in the Event Log When you try to Backup the Transaction Log. The article describes the cause of this warning, but does not give any fix or some kind of work around. Does anybody knows what to do or may be had this problem before? Please help. Thanks a lot.
View 6 Replies
View Related
Mar 31, 2008
say if I am doing an simple insert (50 million records) with union all into a table.... when i check it while the query is running I see some records inserted.... however, at the end i have no data... table gets truncated. Is it so because I was out of space (I checked the drives and seems like data drive is running out of space).
Thanks for ur help.
View 2 Replies
View Related
Jan 16, 2006
I am relatively new to SQL server. I am tring to send some decimalvalues to the database using a stored procedure with parameters of typeDECIMAL. Every time it inserts the values into the database thedecimals are truncated. I saw on the MSDN library that you have to setthe precision and scale values b/f you run the stored procedure. So Iset the precision to 8 and the scale to 4 and it still didn't help. Cananyone help me?
View 6 Replies
View Related
May 24, 2006
Hello,Using SQL SERVER 2000I have 4 columns with varchar(80) each that I want to concatenate.When I look at the result, it only gives me 256 characters. What am Imissing on my code?Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+rtrim(line4))[color=blue]>From tableOne[/color]GoThank you for your input.Edgar
View 3 Replies
View Related
Jul 30, 2007
Hi,I am trying to generate an XML output form the database. I amexecutingselect * from Request_vw for xml autoThe output data is being truncated. is there a limit on the outputresult of an XML query?Thanks
View 3 Replies
View Related
Feb 22, 2008
I'm a newbie to Transact-SQL so I apologize if this is a stupid question. Whenever I try to divide two variables of type int, I get a result where the decimal part is truncated. Here is an example:
ALTER PROCEDURE [dbo].[DIVIDE_TEST]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@VAR1 int,
@VAR2 int,
@MYRESULT dec(10,5)
SELECT @VAR1 = 10
SELECT @VAR2 = 3
SELECT @MYRESULT = (@VAR1/@VAR2)
SELECT @MYRESULT
-- @MYRESULT has decimal part truncated, Returned value = 3.00000
END
Any ideas?
View 3 Replies
View Related
Jul 13, 2006
I have a problem with a NVARCHAR(MAX) parameter being truncated.
The signature of the UDF is:
[SqlFunction(FillRowMethodName = "SplitFillRow",IsPrecise=true,IsDeterministic=true,
DataAccess = DataAccessKind.None,TableDefinition = "pos INT, token NVARCHAR(10) ")]
public static IEnumerable SplitText (SqlChars input, SqlString strDelimiter)
{...
and deployed as:
CREATE FUNCTION [dbo].[SplitText](@input [nvarchar](max), @strDelimiter [nvarchar](10))
....
But it is still being truncated to 8000 characters. I tried using a sqlstring or using
([SqlFacet(MaxSize = -1,IsFixedLength=false)] on the parameter and a bunch of other things, but it is still truncated to 8000.
I am out of ideas...anyone with a tip?
Thanks
Eric
View 5 Replies
View Related
Jul 24, 2006
I'm using SQL Server 2005. I have stored procedure that selects a bunch of data from different tables and returns the results using the FOR XML clause. The problem is when I'm loading the XML, I notice that after a little over 2,000 characters, the XML string is getting truncated and thus I can't load it into an XmlDocument object. I'm also using the Microsoft Enterprise Library data access block, if that makes any difference. Here's the code:
View 1 Replies
View Related
Jul 8, 2004
I have a standard asp.net form and a single database table that I’m trying to pass data into. There are three nvarchar fields of lengths 50, 150, and 2400. Whenever I do an insert or update it truncates all data except for the first character. So if I pass “asdfasdf�, the db field will only receive “a�.
The data going from the form is correct; it gets truncated somewhere after the ExecuteNonQuery() call.
Here is the c# code:
public bool InsertCareer (DataRow row) {
SqlConnection conn = new SqlConnection(connStr);
try {
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcs[(int)StoredProcedures.Insert];
cmd.Connection = conn;
cmd.Parameters.Add("@pkCareerID", row[0]);
cmd.Parameters.Add("@jobTitle", row[1]);
cmd.Parameters.Add("@postedDate", row[2]);
cmd.Parameters.Add("@submitBy", row[3]);
cmd.Parameters.Add("@department", row[4]);
cmd.Parameters.Add("@description", row[5]);
cmd.Parameters.Add("@intranet", row[6]);
cmd.Parameters.Add("@public", row[7]);
conn.Open();
cmd.ExecuteNonQuery();
return true;
} catch (Exception ex) {
//… stuff
return false;
} finally {
conn.Close();
}
}
This is the sproc code:
CREATE PROCEDURE dbo.zak_CareersIU_sp
@pkCareerID int,
@jobTitle nvarchar,
@postedDate datetime,
@submitBy datetime,
@department nvarchar,
@description nvarchar,
@intranet bit,
@public bit
AS
IF EXISTS (SELECT pkCareerID FROM zak_Careers WHERE pkCareerID=@pkCareerID)
BEGIN
UPDATE zak_Careers SET
jobTitle=@jobTitle,
postedDate=@postedDate,
submitBy=@submitBy,
department=@department,
[description]=@description,
intranet=@intranet,
[public]=@public
WHERE
pkCareerID=@pkCareerID
END
ELSE
BEGIN
INSERT INTO zak_Careers
(jobTitle, postedDate, submitBy, department, [description], intranet, [public])
VALUES
(@jobTitle, @postedDate, @submitBy, @department, @description, @intranet, @public)
END
Please let me know if you have any ideas!
thanks
View 3 Replies
View Related
Aug 16, 2004
Hi,
I have hit a brick wall with this. My code is as below
public void fillCustomer()
{
string connectionString = "server='local'; trusted_connection= true; integrated security=sspi; database='Mrbob'";
System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "SELECT * FROM [Customer] WHERE ([CustomerID] = @CustomerID)";
System.Data.SqlClient.SqlCommand dbCommand= new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter param_CustomerID = new System.Data.SqlClient.SqlParameter();
param_CustomerID.ParameterName ="@CustomerID";
param_CustomerID.Value = customerID;dbCommand.Parameters.Add("@CustomerID", SqlDbType.Int);
dbCommand.Connection.Open();
System.Data.IDataReader dataReader = dbCommand.ExecuteReader();
dbCommand.Connection.Close();
while(dataReader.Read())
{
customerID = dataReader.GetInt32(0);
date = dataReader.GetDateTime(1);
eposCode = dataReader.GetInt32(2);
}
dataReader.Close();
}
The error I am getting is
Prepared statement '(@CustomerID int)SELECT * FROM [Customer] WHERE ([CustomerID] = ' expects parameter @CustomerID, which was not supplied.
As you can see from my queryString the @CustomerID parameter is passed in. It seems as if the string is being truncated at 64 characters long. If I remove the paramter to pass the relevant infomration and pass in a customerID I know exists it works.
I am really stumped on this and would really appreciate any pointers
View 1 Replies
View Related
Feb 7, 2005
Hi! When I run a select statement, it would retrieve a product description. In some rows, it is long. Consequently, the product description was truncated. Did anybody have resulotion for this issue?
View 7 Replies
View Related
Oct 6, 2006
Hi
I have a table Test a execute this below query
truncate table Test
I want to retrive records of Test table .How can i do.
Ranjeet Kumar Singh
View 10 Replies
View Related
