Truncate Database Statement Help
Jul 23, 2005
Hello,
I hope you can help me.
We have a SQL Server 2000 database at work, (which works with a VB6
frontend) which grew to a considerable size, so one of my past colleagues
sent me this truncate statement to use on the database.
I ran it like this, and all appeared to be well, it shrank the database,
Shortly afterwards, after I ran this I noticed something not quite right.
for example, one of the tables we keep is a User_Admin table which has 50
records in there, when I accessed this using the frontend it only showed 1
record, and this happened with every table in the database accessed by the
frontend.
The data in the tables is still there, but only shows one record through the
frontend.
Can anyone help me, and check this statement I ran, as its weird that all
this happened after I ran this statement.
Thanks,
Jayne Heger
sp_dboption PBTDEV,'trunc. log on chkpt.', true
checkpoint
--sp_dboption sadev,'trunc. log on chkpt.', false
sp_dboption PBTDEV,'autoshrink', true
--sp_dboption sadev,'autoshrink', false
DBCC SHRINKDATABASE (PBTDEV, NOTRUNCATE)
View 2 Replies
ADVERTISEMENT
Nov 17, 2006
Yogesh writes "I execute the Trancate command for a table without using transaction statements. So there is any option by which I can recover the table data. Pl. advice"
View 2 Replies
View Related
May 22, 2007
Hi
Can we have truncate table with in a transaction using SSIS package.
here is the scenario.
OledbDestinationConnection 's "retainsameconnection" property has been set to true.
I have added a Execute SQL Query task to "begin transaction".
second Execute SQL Query task to truncate a table. say table "A" . truncate table a(table does not exists in the database).
third to rollback a transactions if the second task fails.
Now when I am running my package, I am getting an error saying
The Rollback transaction has no corresponding "Begin transaction".
However if , I truncate a correct table , which exists in database, then all steps gets executed successfully.
And the truncaion also got rollbacked.Same goes with "Commit transaction" in the third steps(if a replace Rollback with Commit).
I don't understand this strange behavior.
Any clue is highly appreciated.
My real problem is I want to rollback a transaction, if Truncate table step fails.
View 14 Replies
View Related
Sep 30, 2007
I have two very simple stored procedures that truncate (clear out) the rows of my 'Transactions' table, then import rows from another table into it, thus updating the table with the appropriate data.
I would like to call these two stored procedures from a web form - asp.net 2.0 web page, using 2 buttons or possibly 1 button if I could, but can't find any information on how to do this.
here is code for:
1. my button for 'truncate'
<asp:Button ID="Truncate" runat="server" Text="Truncate" style="left: 45px; position: relative; top: -2px" Font-Size="8pt" /><br />
2. my stored procedure for 'truncate'
ALTER PROCEDURE dbo.TruncateTrans
AS
SET NOCOUNT ON;
Truncate Table Transactions;
2. my button click event and page load event handler for the web form;
Imports System.Data
Imports System.Data.SqlClientPartial Class Admin_Default
Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End SubProtected Sub Truncate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Truncate.Click
End Sub
End Class
Also, here is my other stored procedure called updateTransact. how can i execute these using the web form and buttons? or is there another way to do this?
ALTER PROCEDURE [dbo].[UpdateTransAct]
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Transactions INNER JOIN
UpdateTrans ON Transactions.TransID = UpdateTrans.TransID
INSERT INTO Transactions (Account, TransID, TransType, Transkind, Status, Amount, Transdate, Note, Ticker, TickerType, BaseDate, BasePrice, UnitPrice, Shares, UnitsPerContract, Commission, StopTypes, FixedValue, Strategy, WithHolding, SavUnitPrice, SavCommis, ReInvestDividends, ReInvestCapGains, StrategyType, OrderID, ReferenceID, Fees)
SELECT Account, TransID, TransType, Transkind, Status, Amount, Transdate, Note, Ticker, TickerType, BaseDate, BasePrice, UnitPrice, Shares, UnitsPerContract, Commission, StopTypes, FixedValue, Strategy, WithHolding, SavUnitPrice, SavCommis, ReInvestDividends, ReInvestCapGains, StrategyType, OrderID, ReferenceID, Fees
FROM UpdateTrans
WHERE (TransType= 'C' OR TransType='O')
ORDER BY TransDate
END
Any help would be appreciated.
Craig
View 3 Replies
View Related
Oct 26, 2006
Hello,
I want to truncate all tables present in the particular database, Is there any simple way to do it? or do I have to do it on individula basis (table by table)?
regards
View 4 Replies
View Related
Jan 26, 2005
I have a SQL Server 2K db with some 10 tables and I want to setup a nightly job to truncate all db records which are more than 15 days old. Can anyone provide me with steps involved? Any help will be highly appreciated.
View 3 Replies
View Related
Nov 27, 2000
I'm trying to make sure my database options are set up properly. Our database is used for our Decision Support System. The data is loaded in once a day. Should I have the truncate log on checkpoint on and should I limit the size that the log grows to. I just had to shrink the log. It grew so much that it took up all the space left. If I have the truncate log on checkpoint do I just have to issue the checkpoint command in order for it to truncate the transaction log?
View 1 Replies
View Related
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
Jan 3, 2008
I was working on my Lab Environment and i had a DB which was mirrored in synchronous mode without witness server. I truncated one of the tables in this DB and did a failover to the mirror server. The table got truncated in the mirror too. Though we know that truncate is a non logged operation how does this mechanism work? Can someone please expain me??? Thanks in advance. Your replies will be very much helpful to me.
View 4 Replies
View Related
Dec 30, 2004
With a database size of almost 2 GB, I run the 'truncate table eventlog command' which completes successfully, but the database size only decreases by about 10 MB so stays too large - indeed the number of rows in the eventlog table is minimal, but the otehr tables in this database don't show such an amount of tables large enough to cause the size issue either. What could be the reason and how can I reduce it (possibly truncating another table but then which one, how could I determine which is too large and needs truncating?).
View 3 Replies
View Related
Nov 5, 2007
Guys,
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table
My truncate script should be
TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE
Is there any automated way to figure out parent and child tables and generate truncate script for the same.
Thanks
View 1 Replies
View Related
Apr 30, 2015
I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
View 9 Replies
View Related
Apr 17, 2008
Hi all...
Please forgive the elementary nature of my question, but could someone please explain the differences between these two database backup types:
1. Log backup
2. Log backup no truncate
From what I understand and have read, the "no truncate" backup method keeps the entire transaction log indefinitely. Using the truncation method, the transaction log is either 1) compressed or 2) cleaned up so that any completed transactions are removed from the log. Which one of these is true?
And, for the big question: is it better to run a backup of the transaction log with truncation or not? Our current backup scheme is similar to the following:
Full backup every 24 hours
transaction log backup every hour with no truncation
Should we insert a truncation backup somewhere in here? What is the danger of removing (or compressing) parts of the transaction log? Will this affect the restore process?
Thanks in advance!
View 6 Replies
View Related
Aug 23, 2006
Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 3 Replies
View Related
Dec 4, 1998
I'm having a problem with the following script:
use master
declare database_cursor CURSOR for
SELECT name from sysdatabases
OPEN database_cursor
DECLARE @databasename varchar (30)
FETCH next from database_cursor into @databasename
while (@@fetch_status <> -1)
BEGIN
use @databasename
exec sp_spaceused
FETCH next from database_cursor into @databasename
END
The substitution of database name in the USE statement does not work correctly. It substitutes in the length, '30', from the declare variable statement. I've had one suggestion to try declaring the variable as DECLARE @DATABASENAME SYSNAME but that has not worked. Any ideas?
View 1 Replies
View Related
Aug 27, 2006
I Have an error: Server Error in '/quanlythietbi' Application. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_yeucau_nhanvien'. The conflict occurred in database 'equipment', table 'nhanvien', column 'manv'. The statement has been terminated. Source Error: Line 129:mycommand.Parameters.Add(new SqlParameter("@noidung_yc1",System.Data.SqlDbType.Text));
Line 130:mycommand.Parameters["@noidung_yc1"].Value = TextBox1.Text;
Line 131:int i = mycommand.ExecuteNonQuery();
Line 132:if (i>0)
Line 133:{and this is my code:string sqlstring = "Select * from yeucau where ngayGiaiQuyetxong='"+ Label8.Text +"' and date_yc='" + Label7.Text + "' and manv_yc='"+ TextBox2.Text + "' and noidung_yc='"+ TextBox1.Text+ "'";myconnection = new SqlConnection(stringconn);mycommand = new SqlCommand(sqlstring,myconnection);myconnection.Close();myconnection.Open();mycommand = new SqlCommand(insertquery,myconnection); mycommand.Parameters.Add(new SqlParameter("@ngayGiaiQuyetxong1",System.Data.SqlDbType.Char,10));mycommand.Parameters["@ngayGiaiQuyetxong1"].Value = Label8.Text;mycommand.Parameters.Add(new SqlParameter("@date_yc1",System.Data.SqlDbType.SmallDateTime));mycommand.Parameters["@date_yc1"].Value = Label7.Text;mycommand.Parameters.Add("@manv_yc1",System.Data.SqlDbType.Char,10);mycommand.Parameters["@manv_yc1"].Value = TextBox2.Text;mycommand.Parameters.Add(new SqlParameter("@noidung_yc1",System.Data.SqlDbType.Text));mycommand.Parameters["@noidung_yc1"].Value = TextBox1.Text;int i = mycommand.ExecuteNonQuery();if (i>0){lbcheck.Text = "Ä?ã Cáºp Nháºt Yêu Cầu.";} --------------------------------------------------------------------------------------------------------------------------------I don't know what I must do to repair it :(
View 2 Replies
View Related
Apr 19, 2008
Hi All,
I have watched one of the video tutorials under SQL Server section called "Connecting your web application to SQL server 2005 express edition" (number 8).
It was about creating a database with single table and then updating it by inserting new records in a text box an clicking on submit button. In code section there is a sql insert statement and connection string is via configurationManager (name of the connection string is taken from web.config and is spelled properly)
Anyway, my problem is that updating that database by filling the textbox and clicking submit button is not working.
I have no error message (nothing happens) - database is not updated.
If I add to the page a data grid view with updating and inserting selected then it all works. I was trying to use debbuging with break point setup but couldn't find any errors.I have done everything the same way as on the video.... I am beginner in C# so your help will be much appreciated.
Here is the code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void Submitbutton_Click(object sender, EventArgs e)
{
SqlDataSource dashDataSource = new SqlDataSource();dashDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["dashConnectionString1"].ToString();
dashDataSource.InsertCommandType = SqlDataSourceCommandType.Text;dashDataSource.InsertCommand = "INSERT INTO Email(EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
dashDataSource.InsertParameters.Add("EmailAddress", EmailAddressTextBox.Text);dashDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
dashDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());int rowsaffected = 0;
try
{
rowsaffected = dashDataSource.Insert();
}catch (Exception ex)
{Server.Transfer("Newsletter_problem.aspx");
}
finally
{dashDataSource = null;
}if (rowsaffected != 1)
{Server.Transfer("Newsletter_problem.aspx");
}
else
{Server.Transfer("Newsletter_confirm.aspx");
}
}
}
View 4 Replies
View Related
Nov 24, 1998
I'm having a problem getting the database name to substitute properly in the
USE database statement when used in a SQL script. Essentially, I have a
variable defined as follows:
declare @databasename varchar(30)
use @databasename
After the variable is initialized with a database name, the USE statement
does not substitute the correct value, instead it uses 30, the length of its
description in the 'declare' statement. Any ideas to force a correct value
substitution?
View 2 Replies
View Related
Oct 20, 2004
hi
is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)
View 2 Replies
View Related
Aug 1, 2007
i read that i can Truncate a table even if child table has no records so i tries to disable constraints but still can't get it to workCannot truncate table 'InventoryPC' because it is being referenced by a FOREIGN KEY constraint. disabling code on lines 9-13 and enabling codes on 36-40 1 ALTER PROCEDURE dbo.RevertDB
2
3 /* Reverts Database to original "Clean" State */
4 AS
5 SET NOCOUNT OFF
6 DECLARE @Log AS varchar(MAX), @RowsInDB AS int
7 SET @Log = 'RevertDB Started at ' + CAST(GETDATE() AS varchar(50)) + ''
8
9 /* *** Disable Constraints *** */
10 ALTER TABLE Booking NOCHECK CONSTRAINT ALL
11 ALTER TABLE InventoryPC NOCHECK CONSTRAINT ALL
12 ALTER TABLE PC NOCHECK CONSTRAINT ALL
13 ALTER TABLE Platform NOCHECK CONSTRAINT ALL
14
15 /* *** Start Truncates *** */
16 TRUNCATE TABLE Booking
17 SET @Log = @Log + 'Trucate Table Booking - Done' + ''
18 SET @RowsInDB = (SELECT COUNT(BookingID) FROM Booking)
19 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + ''
20
21 TRUNCATE TABLE InventoryPC
22 SET @Log = @Log + 'Trucate Table InventoryPC - Done' + ''
23 SET @RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC)
24 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + ''
25
26 TRUNCATE TABLE PC
27 SET @Log = @Log + 'Trucate Table PC - Done' + ''
28 SET @RowsInDB = (SELECT COUNT(PCID) FROM PC)
29 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + ''
30
31 TRUNCATE TABLE Platform
32 SET @Log = @Log + 'Trucate Table Platform - Done' + ''
33 SET @RowsInDB = (SELECT COUNT(PlatformID) FROM Platform)
34 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + ''
35
36 /* *** Enable Constraints *** */
37 ALTER TABLE Booking WITH CHECK CHECK CONSTRAINT ALL
38 ALTER TABLE InventoryPC WITH CHECK CHECK CONSTRAINT ALL
39 ALTER TABLE PC WITH CHECK CHECK CONSTRAINT ALL
40 ALTER TABLE Platform WITH CHECK CHECK CONSTRAINT ALL
41
42 SET @Log = @Log + '*** End Truncates ***' + ''
43 /* *** End Truncates *** */
44
45 /* *** Start Insert Platform *** */
46 SET @Log = @Log + 'Start Insert Platform' + ''
47
48 EXEC dbo.InsertPlatform 'Windows XP SP2 Professional Edition', 'Some description for Windows XP SP2 Professional Edition over here …'
49 EXEC dbo.InsertPlatform 'Windows Vista Ultimate', 'See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance.Website: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx'
50 EXEC dbo.InsertPlatform 'Apple Mac OS X Tiger', 'Some description for Apple Mac OS X Tiger over here …'
51 EXEC dbo.InsertPlatform 'Apple Mac OS X Leopard', 'Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace.Finder: Browse your files like you browse your music with Cover Flow.Time Machine: See how your system looked on any given day and restore files with aWebsite: http://www.apple.com/macosx/leopard/features/'
52 EXEC dbo.InsertPlatform 'Red Hat Linux', 'Some description for Red Hat Linux over here …'
53
54 SET @Log = @Log + 'Rows In Platform: ' + (SELECT COUNT(PlatformID) FROM Platform) + ''
55 /* *** Start Insert PC *** */
56 SET @Log = @Log + 'Start Insert PC' + ''
57
58 DECLARE @WinXP int, @WinVista int, @OSXTiger int, @OSXLeopard int, @RedHat int
59 SET @WinXP = (SELECT PlatformID FROM Platform WHERE Title = 'Windows XP SP2 Professional Edition')
60 SET @WinVista = (SELECT PlatformID FROM Platform WHERE Title = 'Windows Vista Ultimate')
61 SET @OSXTiger = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Tiger')
62 SET @OSXLeopard = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Leopard')
63 SET @RedHat = (SELECT PlatformID FROM Platform WHERE Title = 'Red Hat Linux')
64
65 EXEC dbo.InsertPC 'Fusion PC One', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '1GB Dual Channel DDR2 667 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @WinXP
66 EXEC dbo.InsertPC 'Fusion PC Two', 'Description here ...', 'Intel Core2 Duo E6850 3 GHz 1333MHz', '2GB Dual Channel DDR2 800 SDRAM', '240GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '24" 3000:1 Wide Screen LCD', @WinVista
67 EXEC dbo.InsertPC 'Fusion PC Three', 'Description here ...', 'AMD Athlon 64 X2 Dual Core 6000+ 3 GHz', '2GB Dual Channel DDR2 667 SDRAM', '240GB SATA2 NCQ HDD', 'ATI Radeon Cross Fire 2900 256MB GDDR3', '24" 3000:1 Wide Screen LCD', @WinVista
68 EXEC dbo.InsertPC 'Fusion X1', 'Description here ...', 'Intel Core2 Extreme Q6850 3 GHz 1333MHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '30" 3000:1 Wide Screen LCD', @OSXLeopard
69 EXEC dbo.InsertPC 'Fusion X2', 'Description here ...', 'AMD Athlon 64 FX 74 3 GHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3', '30" 3000:1 Wide Screen LCD', @WinVista
70 EXEC dbo.InsertPC 'Fusion Tiger 1', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '2GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3 SLI', '22" 3000:1 Wide Screen LCD', @OSXTiger
71 EXEC dbo.InsertPC 'Fusion Linux 1', 'Description here ...', 'AMD Athlon 64 X2 6000+ 3 GHz', '1GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @RedHat
72
73 SET @Log = @Log + 'Rows In PC: ' + (SELECT COUNT(PCID) FROM PC) + ''
74
75 /* *** Start Insert Inventory *** */
76 SET @Log = @Log + 'Start Insert Inventory' + ''
77
78 DECLARE @F1 int, @F2 int, @F3 int, @FX1 int, @FX2 int, @FT1 int, @FR1 int
79 SET @F1 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC One')
80 SET @F2 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Two')
81 SET @F3 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Three')
82 SET @FX1 = (SELECT PCID FROM PC WHERE Title = 'Fusion X1')
83 SET @FX2 = (SELECT PCID FROM PC WHERE Title = 'Fusion X2')
84 SET @FT1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Tiger One')
85 SET @FR1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Linux One')
86
87 EXEC dbo.InsertInventory 10, @F1, 2.5, 'iCluster Fusion One'
88 EXEC dbo.InsertInventory 10, @F2, 2.5, 'iCluster Fusion Two'
89 EXEC dbo.InsertInventory 10, @F3, 2.5, 'iCluster Fusion Three'
90 EXEC dbo.InsertInventory 6, @FX1, 6, 'iCluster Fusion X1'
91 EXEC dbo.InsertInventory 6, @FX2, 6, 'iCluster Fusion X2'
92 EXEC dbo.InsertInventory 10, @FT1, 3, 'iCluster Fusion Tiger One'
93 EXEC dbo.InsertInventory 30, @FR1, 2, 'iCluster Fusion Linux One'
94
95 SET @Log = @Log + 'Rows In Inventory: ' + (SELECT COUNT(InventoryID) FROM InventoryPC) + ''
96
97 RETURN @Log
98
View 1 Replies
View Related
Nov 23, 1999
I would like to ask if I backuped the transaction log already, SQL will truncate the transaction log automatically or not. If not what should I do?
View 1 Replies
View Related
Nov 19, 2001
Is there an ISQL statement to truncate the transaction log??? A Stored Procedure maybe?
View 4 Replies
View Related
Jan 8, 2005
I have two tables in my SQL CE database. tblTempData and tblBatchData. When the user wants to move the data from 'temp' into 'batch' I would like to truncate the 'temp' table. When I do this I get the unable to parse query error.
strSQL = "TRUNCATE TABLE tblTempData"
goADOcn.Execute (strSQL)
Is there something that I am doing wrong, or is TRUNCATE not supported in SQLCE 2000?
View 2 Replies
View Related
Dec 29, 2005
can TRuncate statement be rolled back ?
View 5 Replies
View Related
May 1, 2006
Does anyone know how to truncate log without grow up space used?
Thanks
Sbahri
View 4 Replies
View Related
May 13, 2004
Hi there,
I have a simple stored procedure that will truncate the log file but I now need to make this dynamic so that this proc can be run with any DB name. I know you can look up the logical filename in sysfiles but I'm not too sure how to write this procedure so that it will simply truncate the log file of the currently selected DB.
This is what I have so far which as you can see is hardcoded:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE TruncateLog AS
BACKUP LOG [FICaches] with TRUNCATE_ONLY
DBCC SHRINKFILE (FICaches_log, 50)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Any help would be greatly appreciated!
Thanks
NewToSql
View 2 Replies
View Related
May 13, 2008
Hi
I have a Log which is 15 gb. Recovery mode is simple.
What command can I use to truncate this log? Or is it best to shrink it?
Thanks
View 4 Replies
View Related
May 26, 2008
I have a small doubt .
Truncate is not a logged operation.
In log shipping if we perform any truncate operation on the primary server will its get reflected on the secondary server.
View 2 Replies
View Related
Jun 13, 2008
I have 2 tables.
Table 1 and table 2.
Table 1 has a foreign key related to a particular column on table 2.
Suppose if i have applied a truncate command on table 2 .will it work or not.
We have to check for dependencies on the table before we should apply a truncate command.
Could any one tell me can we directly apply a truncate command on any object irrespective of the dependencies on that object.
View 2 Replies
View Related
Dec 13, 2007
How to setup a sql 2005 maint plan for truncate log ?
Is it possible or do need to create a job and and in job step mention below
Backup log DatabaseName with truncate_only
How do i issue the checkpoint?
...please let me know?
View 5 Replies
View Related
Dec 14, 2007
I need to truncate log for one DB
do i need to create a job and and in job step mention below
Backup log DatabaseName with truncate_only
is there any other way to do this?
How do i issue the checkpoint?
...please let me know?
View 10 Replies
View Related
Mar 22, 2007
Hello All,
I am doing some data extraction from a database table: custtable. The customers have zip codes of 9 digits however I just need the first five of them. I am not sure of the syntax. Can anyone suggest me on this one?
This is what my statement is
where ((len (ct.zipcode ) > 5 and ct.country = '001')or ct.country <> '001')
This one returns customers with zipcodes > 5 and I need to only truncate this, coz my database also contains cust with zip code of only 5 digits and I have extracted that data already. Now I need all the zipcodes that have a length of 9 to be truncted to 5.
An earliest possible response is highly appreciable.
Thanks,
Rashi
View 5 Replies
View Related
Dec 14, 2007
How to setup a sql 2005 maint plan for truncate log ?
Is there any other way to do this?
View 4 Replies
View Related
