Lock Manager Out Of Space
Aug 31, 2007
Hi,
I'm getting an error which I'm pretty sure I can avoid for now, but I'd like to understand the underlying issue.
I'm have SQL CE 3.1 on Win XP and am replicating a reasonably large set of data with SQL Server 2005 using merge replication. The initial replication works fine, and brings the SQL CE database to around 800MB. Subsequent delta syncs are also fine. However, if I re-initialise the subscription, it chugs away for a while, grows the local database to just over 1Gb then errors with the following message:
The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.
I can avoid this issue by either deleteing the .sdf file and recreating the subscription from scratch, or by splitting the publication into smaller sets, and re-intialising each one seperately. Obviously SQL CE uses some sort of temporary or "lock" space to manage the re-initialisation. Is, as the error message suggests, there no way to increase this? How much space is there - ie what is the threshold overwhich I need to split up the update operations into multiple steps.
I'm assuming this is a SQL CE limitation, hence posting this here rather than under the replication forum.
Cheers
View 4 Replies
ADVERTISEMENT
Jul 20, 2005
hi,guysi have query which given below output given belowmanager personlevel person name20851 Howard Wilson120852Howard Wilson220853Howard Wilson320854Howard Wilson420855Howard Wilson560861Andrew Saxon60862Andrew Saxon60863Ian Thompson60864Ian Thompson60865Phil Darganwhat i want is after a manager ends i want a null to be inserted foreach of there columnsso that i can distinguish that when a new manager startsso thatt output looks like thismanager personlevel person name20851 Howard Wilson120852Howard Wilson220853Howard Wilson320854Howard Wilson420855Howard Wilson5null null null60861Andrew Saxon60862Andrew Saxon60863Ian Thompson60864Ian Thompson60865Phil DarganBrlliant minds any solution for this..i know can i loop through the records and do itand check for a new managerbut i want a better solution ..give me your ideads folks..Regards,Navin Mahindroo
View 3 Replies
View Related
May 16, 2006
Hello
I have created a report that works how I want it in the visual studio 2003 designer. I have deployed it to our test box (sql server 2k, reporting services with sp2) and when we preview the report, there is a giant white space between the graph in the report and the matrix object below it. This white space was not in the designer. It only shows when previewing the report. If you print from the report manager, it does not print the white space.
Is there a way to remove it from the report manager when the report is previewed? I am sure users will complain when they see this.
Thanks.
Ben
View 2 Replies
View Related
Feb 2, 2007
I simply made my script task (or any other task) fail
In my package error handler i have a Exec SQL task - for Stored Proc
SP statement is set in following expression (works fine in design time):
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"
From progress:
Error: The Script returned a failure result.
Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
View 11 Replies
View Related
Apr 7, 2004
Hi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Please advice. Thanks a bunch.
Pankaj
View 1 Replies
View Related
May 22, 2006
Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
View 9 Replies
View Related
May 10, 2006
Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
View 1 Replies
View Related
Nov 14, 2007
Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
View 1 Replies
View Related
Feb 23, 2007
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
View 1 Replies
View Related
Nov 26, 2015
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
View 3 Replies
View Related
Mar 2, 2005
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
View 1 Replies
View Related
Jul 20, 2005
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies
View Related
May 23, 2001
Hi,
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
Thank you for any help
View 1 Replies
View Related
Dec 5, 2001
Hello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.
View 1 Replies
View Related
Nov 14, 2006
How do we suppress multiple spaces to a single space in T-SQL
E.G.
Field: FullName
e.g.
WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E
The o/p wanted is
HERSH STWEART P.
Thank you.
View 3 Replies
View Related
Nov 2, 2007
A user was created with a limited privilege under the USERS group. Once this user loged in the Report Manager he is acting like an Admin and Content Manager, though he is not given even a browser role.
What do u think that this guy is acting like a Super User evenif he is restricted to a browser role on the Report Manager ????????????
I did all my best, but no luck so far
View 5 Replies
View Related
Jul 20, 2005
On one of our machines, all of the SQL Server 2000components except for the main Server component (SQL Servercore) itself were installed (Management tools, etc) a while agoand everything was running fine. Now I go and add/install theServer component and then Service Pack 3a.It seems that Service Manager won't start up (I get an hourglass cursor)and now I find that Enterprise Manager won't run as well. No errormessages appeared and I don't think I saw anything unusual inthe log file.However, I can use Enterprise Manager on a differentmachine and connect to the database (so the databaseitself seems to be running).Any suggestions as to what the problem might be and how tofix it? I like to see if I can repair this without havingto do a reinstall.Thanks.PF
View 2 Replies
View Related
Dec 6, 2006
IF someone can assist me. Everytime I load up enterprise manager the service manager turns off. And the enterprise manager can't connect to the local database. But everytime i turn it back on and try to connect again it shuts it off and around and around we go. Help would be appreciated. Thanks.
View 2 Replies
View Related
Nov 24, 2000
I made some copy of table and I have this error but on my hard disk i have 4 gig of empty space.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Backup_Date_11_24_00_Time_9_08_34_AM' in database 'LogActiviteIntramedia' because the 'PRIMARY' filegroup is full.
/Intranet_API/Forms/videTableLog.asp, line 16
My question is how can I increase the space of primary filegroup?
Thanks and have a good friday
View 2 Replies
View Related
Mar 24, 2008
I am using SSIS 2005 on Windows 2003 server. Using Excel Source to dump the data for staging database.
I am getting following error while I execute it through BI studio's execute button.
Please help.
- Sachin
View 2 Replies
View Related
Mar 11, 2008
I have deployed my packages into Sql Server and I am using Configuration File. As my Data Source is Excel, I have changed the connection string during deployment with Server Path. But I am getting the following errors. Actually the File Exist in Path. May I know What is cause of the issue? Do I need to give any permission to execute the package.
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
component "Excel Source Service Contract Upload" (1) failed validation and returned error code 0xC020801C.
One or more component failed validation.
There were errors during task validation.
DTS_E_OLEDBERROR, Error Code: 0x80004005 Source: "MS JET DB Engine" Description : Path is not valid
View 27 Replies
View Related
Sep 18, 2006
Hello everyone,I have a web project where users access a aspx page to view information stored in an SQL database.My client want that one user can access a row of information and see it, all other users shouldn't be able to view or update the same row?it means whenever a row of data is displayed by some user, this row should be locked even for beeing viewed by all other users, when this user close this page, this row will be available. ?I should do this in code behind or something in sql...How can I do that???
View 6 Replies
View Related
Oct 6, 2003
I remember there's a command you can type into SP . so no one else can see your SP even the SA. can anyone tell me what's the command is thanks!!
View 3 Replies
View Related
Mar 27, 2001
Hello,
An application we are designing is behaving rather strangely. Basically, we
have a trigger on SQL 2000 on W2K Server watching for a record update. When
that happens a stored procedure gets executed which in turn spawns a VB that extracts that record, applies some rules to it, and writes a text file to disk. 9 out of 10 times, this program will cause a CPU spin, and it can't be killed from the Task Manager.
I did not write the VB app, I am writing the front end for the application in ColdFusion, but I need some ideas on what might be causing such behavior and how can the problem can be diagnosed. Thanks for any help!
Stas Newdel
View 1 Replies
View Related
Sep 3, 2007
Is it good practice to use WITH (NO LOCK) on SELECT statements, ie
SELECT * FROM MyTable WITH(NO LOCK)
Or does the SQL Server optimiser automatically use WITH (NO LOCK) ?
View 2 Replies
View Related
May 30, 2006
Hi,
i have a big problem , i work with sql 2000 on windows 2000.
When user do a select against my database sqlserver lock all the table and nonoe can work.
Haw can i change the isolation level for a ropw and for all the db.
Thanks.
View 3 Replies
View Related
Mar 29, 2007
Hello,
Wondering if anyone might have a guess about this.
I have a small 4 table DB. it's got several stored proc's and it's accessed through .NET to fill it in and get data from it.
It's been working just fine. But this morning, while it was doing its thing, I experimented with it by adding then deleting a View (through the View Wizard.)
Then later I started noticing that my .Net calls had slowed to a crawl (I hadn't made any code changes)and even making direct queries through Query Analyzer had slowed too.
My question is: being that there were no network issues, could the View create/delete have caused the DB to come to a halt or perhaps a table lock-up?
While I'm at it, is there anything that I can put in stored proc's or other places to prevent locking issues (if that's what happened here.)
I already use Begin/Commit Tran pairs.
And sorry if this post doesn't read like a SQL beginner, but believe me, I am.
Thanks for your advice!!
--PhB
View 2 Replies
View Related
Aug 20, 2007
Lets say i have a view.
vuTestingNoLocks
this view looks like this...
Create View vuTestingNoLocks as
SElect *
From dbo.Employees
inner Join dbo.EmployeeTerritories on EmployeeTerritories.EmployeeID = Employees.EmployeeID
If I select from this view using Select * From vuTestingNoLocks (NOLOCK)
Does the (nolock) command propegate down through the tables? Meaning will it scan tables that are locked still ignoring their locks?
View 5 Replies
View Related
Oct 13, 2006
I'm working with SQL Server Express, and I want to configure a named instance so that only the 'sa' user and a specified SQL Server user with a specified password have access. In particular, I'm trying to lock out BUILTINAdministrators. Furthermore, I need to be able to do this from a command-line, since I want to configure it in a script. Nothing I do seems to work.
I've attempted to use sqlcmd and the T-SQL call ALTER LOGIN [BUILTINAdministrators] DISABLE, but that returns the error "Cannot alter the login 'BUILTINAdministrators' because it does not exist or you do not have permission."
What I can (apparently) successfully do is run DENY CONTROL TO [BUILTINAdministrators]. This runs without reporting an error. However, after running it against the 'master' database and the specific database in my named instance I care about, I can still run the following:
sqlcmd -S (local)MyInstance -d MyDB -Q "select * from my_table"
and see the contents of my_table.
What do I need to do to restrict access exclusively to 'sa' and other SQL users I designate?
View 3 Replies
View Related
Dec 27, 2007
Hi,
I am working on SQL Server 2005 (x64) with Windows Server 2003 (x64) operating system. I am having a major issue in SSIS. Here is the detailed explanation of the issue :
I have an EXCEL file in 2003 / 2007 version. It contains some data. I want to import the data using SSIS into SQL Server 2005 (x64) database table. I have taken "EXCEL FILE SOURCE" and "SQL Server DESTINATION". It was failed on importing data. Surprisingly it works fine in SQL Server 2005 (x32). Can you please explain why it is NOT woking on (x64) ?
Here is the error code i am getting:
[Excel Source [1]] Error: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Appreciate your time and patience !!
Thanks
View 3 Replies
View Related
Jul 19, 2006
Hello.
I need to insert some records to an accounting table and calculate the balance after that. Thus, other users can be trying to do the same. How to lock the db and make the other users wait until the right moment? I'm using SqlDataSource to do that.
Thanks.
View 7 Replies
View Related
Sep 19, 2007
hi all iam working on a ticketing application i want to avoid two users to book the same ticket the requirement is as follows
1. the system should show all the available tickets which is not yet booked
2.when two users book the ticket at the same time time it should not allow the two persons to update at the same tme
the main aim is to avoid data concurency
how can i get this done
View 2 Replies
View Related
Jul 23, 2004
Hi,all:
This problem almost drives me crazy, hope I can get some hints from you guyz!!!
Ok, here is the situation:
I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating.
It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!!
Thanx in advance!
View 5 Replies
View Related
