For some reason, my stored procedure which is kicked off by VB.NET is growing the log file for the database in question by gigs, very fast. Why is it doing this?
The code:
Sub Main()
objConn.Open()
Dim cmdSql As New SqlClient.SqlCommand("IT_sss_Collector_DotNet", objConn)
cmdSql.CommandType = CommandType.StoredProcedure
cmdSql.CommandTimeout = 1000
Dim reader As SqlDataReader = cmdSql.ExecuteReader()
Dim ppa_rowcount As Integer
ppa_rowcount = 0
If reader.HasRows() Then ' test to see if there is any data in the reader
reader.Read()
ppa_rowcount = Convert.ToInt16(reader("pcount"))
End If
reader.Close()
'Dim ppa_rowcount As Integer = Convert.ToInt16(cmdSql.ExecuteScalar())
MsgBox(ppa_rowcount)
If ppa_rowcount > 0 Then
MsgBox("got here")
RunsssReport()
End If
the log is 29 gigs! this is a copy of a production database running on my test server. The production db log is only 15 gigs. How do I manage this and why it is growing at such a crazy porportion just from running this simple stored proc with a cursor???????
CREATE PROCEDURE pop_notes ( @contnum as bigint,@cusnotes varchar(1000) OUTPUT) as
begin
declare @noteid as int select @noteid=max(noteid) from cusaddnotes where contractnum=@contnum and rtrim(popup)='yes' if @noteid > 0 begin select @cusnotes=(notes + ' [' + convert(varchar(100),dateadded) + ']' ) from cusaddnotes where noteid =@noteid
end else begin set @cusnotes='none' end print @cusnotes end GO
the srored proc is returning the @cusnotes with a size 0 and its throwing out errors in my asp.net page
'@cusnotes' of type: String, the property Size has an invalid size: 0
i tried to run the stored proc frm the QA
declare @note as varchar(1000) exec @note=pop_notes 3430,'y' print @note
I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction. Here is how i wnated to do ( in sequence )
C# ===== Open a connection Begin the transaction Execute the command
In the Stored Proc =========== do multiple operations one by one if error stop processing further Throw the error C# ======== if exception rollback the transaction else commit the transaction
I have tried using raise error in stored proc but never thrown exception
Can any one let me know how to achieve this scenario??
We have a high volume database with 1000's of users and 1000's of procs. Our application enforces a 20 second timeout on all connections.
We can't adjust the 20 seconds - this is a business rule.
It sometimes happens that a proc does not complete within 20 seconds and then times out halfway though. This causes data inconsistency where 50% of the code was saved to the DB and 50% was not - seeing that a stored proc is not transactional and therefor does not roll back the code.
We can't put the code in a TRANSACTION in order to roll back when a time out occurs, because this causes exclusive locks on the tables.
So I guess my question is: Is it possible to undo/rollback all the code in a proc when a timeout occurs - without using a TRANSACTION? And if a TRANSACTION is the only way - how do I avoid the exclusive lock and blocks?
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Hello, If I'm running an insert statement in a stored procedure, and it fails, an I have code to do: if ( @@error <> 0) begin rollback transaction return -1 end This will still not stop an error from returning to the program right? I'm coding my DAL, and wanted to make sure. Thanks.
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END. Any help on this would be appreciated.
Hello! We have unusual situation. We increased the size of transaction log up to 100MB. After we run the transaction log backup the physical size of transaction log file getting smaller and smaller from 100 to 88 and then to 76 and so on. Do you now the reason? Thank you, Natalia
I have a very serious problem, if somebody can help me quickly. my transaction log file is getting bigger and bigger even after truncating. today morning when i checked, it was 1.5 GB, by evening it has gone upto 3GB, the total size of the database is 3.4 GB, out of which 3GB is Transaction log. why is it growing heavily!!!!!!!!. there is not much transaction happening. even after truncating the transaction log it is still showing 3gb.
I currently have an database that is 110 mb and grows on an average of 5mb a week. THe enviornment is IIS 5.0 /ASP /SQL 6.5. However I find myself continously increasing the log file since it frequently fills up. At this point it is 300mb and can be filled in less than an hour with an average of 35 users. I realize that this file has alot of overhead and is affected directly by the application making the updates. Last week on a busy day (time card app) I witnessed the log increase from below 75% to 100% in a minute, forcing me to truncate the log. This application is an administrative nightmare! Any ideas what could cause this type of activity in the trans log.
We have here a small database (around 400 MB) with simple recovery. Also autogrowth is active.
But why does the transactionlog also sized up, when it is not in use? Our TL is around 100 MB and filled with 5 MB. Everytime the DB gets bigger, the TL also....
This isin continuation with my previous query. If i cannot reduce th T Log size , how can i stop it from increasing further. Can i make secondary log files which can be deleted later on?
I need to create a script that would return the size of transaction logs for all databases. I ran a select statement against the sysfiles table but you can only run it against one db at a time. Any suggestions?
Hello dear friends, In my database i am not able to take the backup of transaction log. Even if i took the back up and then shrunk the file it doesnt make any change to my transaction log size. Still the size is same. So in every two weeks i am restarting my server. Once it is restarted then it is ok for another 2 weeks. After 2 weeks my transaction log size will be more than the size of my datafile. Can you suggest your openion. No replication or log shipping exist.
I need to create a script that would return the size of transaction logs for all databases. I ran a select statement against the sysfiles table but you can only run it against one db at a time. Any suggestions?
I have written an application which runs as a windows service. The application constantly requires to write data into the database.. pretty much every seond. Some of the tables have more than 800,000 rows.
the application works fine. Hoever I am noticing the transaction log grows very fast. All information added is handled through stored procedures. In one week the transaction log grew to 2GB. I assume a part of the reason was because I ran out of disk spae once. But generally this transaction log is growing fast. What could cause the transaction logs to grow fast, What could I do to keep the log file size down....I cant have the database down as the application needs to be running constantly.
I have a database whose recovery mode is FULL. I setup one maintenace plan do a backup for the database and another backup plan which backup the transaction log. The two maintenace plans runs daily.
However, the log file is still growing to a very large size. Should the log file be able to reuse after each backup of the transaction log ?
If I'd like to keep the database recovery mode as "FULL", what do I have to do to keep the transaction log within a reasonable size.
If I have a transaction log in a database of size 1GB ( space allocated is during creation of database) currently only 300 mb of its space is used i.e. nearly 700 mb is free. If I want to reduce physical file size of transaction log by 200 mb and release it for operating system then How can I do it???
I'm sure this question has been asked before but I need clarification on a couple of points.
I have a database (500 Mb) which is having a full backup every night at 2AM. I am doing a transaction log backup every 2 hours between 7AM and 7PM. I have noticed that the transaction log keeps growing bigger and bigger so I do a manual truncate.
- Will my transaction log keep growing bigger and bigger ? - How can I automate a task to reduce the log size ? - Does a full backup not truncate the log ?
I created a database and had its file size as automatic grow. Now the database file is of 17 MB and its transaction log file size is 230 MB. After checking transaction log file properties I came to that it is using 13 mb only and the rest of the 230 MB i.e 217 MB is free. I want that area in the transaction log to be freed and get the transaction file size to its actual size. Any help will be greatly appreciated.
My database's transaction log has become 1.7 GB. Can I reduce it's size? I have tried to shrink database and also set truncate on checkpoint option and also taken the backup after that. but nothing helps. Please advice.
A very quick (and probably simple to answer) question.
I have a database for which I used Enterprise Manager to set up a complete backup at 2AM every day and a transaction log backup every 2 hours between 8AM and 8PM.
I have noticed that the transaction log is growing huge but I thought that it is supposed to get truncated every complete database backup !!
What can I do to "manage" the log file size ? (preferably automatic task)
