Hi,
Issuing 'dbcc shrinkfile (log ,truncateonly)'
I get the error message below. Books online doesn't say any more.
Can anyone explain? I am the only user connected to the db at the time, no jobs are executing.
Cannot shrink log file 2 (log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
Hello...
Is it normal in SQL Server 6.5 the user who only running the query blocking the other user who try to update/add the records?
note: The query is a complex SQL.
Many Thanks!
We are using SQL server 6.5 and currently have about 100 users connections at a given
point in time. The application is Visual Basic 5.0 based and it allows users to create
MS Word documents from the application. These documents names are stored in a table which basically
acts as a reference table. Every time a document needs to be attached to a record
this table is called with an insert/update query. This results in an exclusive page/table
lock and ends up into a blocked process. This finally results into a major halt for
all the system users.
Manual killing of these blocked processes frees up the resources and brings things back to normal
although disruptive to the users.
Any clue as to why the blocked processes are not able to free themsevles up ?
Are we missing something in our SQL configuration that will help us with unblocking
these processes ?
What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.
Thanks,
jim craddock
I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
I thought the locking hint would prevent from blocking other processes?
I am getting the following error message when I tried installing the 2005 Developer Edition from the MSDN kit. I previously installed the 2005 Express Edition, and it worked just fine, so I thought that the install failed as it was at a lower version than the Express Edition. I uninstalled the SQLExpress Edition, however, when I try to install SQL Server 2005 again, I get the following message:
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
I have also installed Visual Studio NET, and I wonder if some component in the Studio app is holding back the SQL Server install. I have uninstalled all SQL components several times already, and I keep on getting this message.
Gregory
Gregory
Hi all,
I have an SQL Server 2005 mirroring config with "High availability". Twice over the past four weeks, the principal server started logging to the event log:
"All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT" I checked the database activity monitor and indeed there were lots of processes in DBMIRROR_DBM_EVENT.
The database was totally unresponsive to queries (all got timeouts), and no failover was done.
This are the log entries for all day:
05/04/2008 08:40:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:39:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:38:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:37:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:36:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:35:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:33:49,Server,Unknown,The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
05/04/2008 03:24:02,Backup,Unknown,Log was backed up. Database: TeleCable<c/> creation date(time): 2007/08/24(13:38:00)<c/> first LSN: 205089:18926:1<c/> last LSN: 209688:18463:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'\face01f$logbackup'}). This is an informational message only. No user action is required.
05/04/2008 00:00:14,spid24s,Unknown,This instance of SQL Server has been using a process ID of 1700 since 4/9/2008 3:18:54 AM (local) 4/9/2008 1:18:54 AM (UTC). This is an informational message only; no user action is required.
After that, the same entry once per minute.
I restarted the database and everything came to normal.
As you can imagine, the problem is really serious.
Should I forget about mirroring? Anyone has experienced something similar?
Thanks in advance and best regards.
Hi guys,
I have a stored procedure which generates the next sequence number...
it uses SERIALIZABLE Option. procs look something like below..
begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
Sequence generating statement...
COMMIT TRANSACTION
set @NextSequenceValue = @NextSequenceValue
Return @NextSequenceValue
end
For some reason when i call the proc with below parameters to get next sequence number.. its hungs up..
declare @NextSequenceValue int
set @NextSequenceValue = 0
exec spGetNextSequence 19, 'LotSequence', @NextSequenceValue output, Null
select @NextSequenceValue as NextSequenceValue
When i queried sp_who2 it shows that my processid is blocked by some other processid.. and when i do DBCC INPUTBUFFER (blockingprocessid), the query of blocking processid and my nextsequence generation stored proc is not realted at all..
Can you help shed some light on why my nextsequence generating proc is getting hunged...?
help is appericated..
If I kill a blocked process, why does the current activity window still show the process? Both processes, blocking and blocked, are scheduled tasks. Also, the blocked process is still listed as a running task in the manage scheduled task window.
View 1 Replies View RelatedThe following is an extract from sp_who2
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO
----- ---------- ------- -------- ----- ------- ------- ------- ------
162 sleeping om18682 . -1 STIPROD SELECT 236 120
Has anybody experienced a process being blocked by SPID -1.
Hi,
I have a fairly complex application running which has numerous connections on various threads accessing my sql 2005 database.
Every now and then I find that one of my connections is being blocked by process with a pid of -2.
I assume this is some system process.
The only way I can get my application going again is to restart the sql server.
Can someone tell me what process -2 is?
cheers,
Robert
HiShortly, I keep invoices in a table.Occasionally, someone will fire the execution of a stored procedure(SP) that performs several UPDATEs against (potentially) all invoicesOLDER than a date that is supplied to the SP as a parameter.The SP is usually a lengthy process (it takes at least 30 mins).The problem is that SQL server 2000 Dev Edition doesn't allow me toinsert new invoices that are "younger", while the SP is executing.How should I specify to SQL Server that new invoices are "harmless"?Thanks.
View 8 Replies View RelatedI have written a stored procedure to list out all tables in whichrows or the table itself is locked. The only information I amnot able to get is the time when the lock occurred. The way Iwant is that if I run the procedure it should show all lockson a table which are at least 5 or x seconds old. This way I canavoid momentary locks on a table which go away after few seconds.Which table and column of master database has that information?Thanks.--email id is bogus
View 3 Replies View RelatedHello,
I am trying to execute next query, but when doing it, TABLE1 locks and it does not finish.
SERVER2 is a linked server.
BEGIN TRAN
INSERT INTO TABLE1
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2 WHERE TAB_F1 IS NULL
COMMIT TRAN
I have same configuration in other 2 computers and it works ok.
What is the problem?
Thank you!!
Hi guys,
we have a database here and something happened which causes a database block. We tried to run the 'sp_who' 'active' command to see the spid which locked the database, and we found out that some transaction is blocking another transaction. The following is the sample data results from the sp_who 'active'
spid ecid status loginame hostname blk dbname cmd
52 0 sleeping HOSTINGSQLMonitor BLUE2 185 tempdb INSERT
53 0 sleeping sa 10.10.10.106 185 mfgq_live SELECT
56 0 sleeping sa 10.10.10.106 175 mfgq_live UPDATE
57 0 sleeping sa 10.10.10.143 185 mfgq_live SELECT
We killed all spid which casuse the blocking, but they are keep on coming.
Does anybody have any idea on what casuses this problem or a teporary solution for this? Please help.
Thx
Hi
On one of my SQL servers (SQL 2000 SP4) i have a problem with spid blocked by itself (spid 54 (Blocked by 54)).
On the server it's running a verry simple UPDATE statement (from the Query Analizer)'. The query needs very long time to finalize (on SP3 it was very fast). I searched some forums regarding this issue and i found that maybe the SP4 can cause this problem.
Do you have any idea or advice?
Thanks
I'm getting the following error when I try to run SQL05 SP2 on my server. When I get to the Authentication screen, I select Windows Authentication and check the box to use for all services. I am logged in using my personal admin account. I click the 'Test' button. Connection to Database Services, instance name 'MSSQLSERVER' is successful. However, connection to Reporting Services, instance name 'myserverSSRS' fails. The error message is: "Login failed: HResult 0x2, Level 16, State 1, Named pipes provider: Could not open a connection to SQL Server[2]. Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server."
I've had problems getting Reporting Services to work. Is this message a result of SSRS not being set up properly? How can I correct this?
I have an SSIS package that is basic in nature. It imports a flat file and makes some inserts to tables via OLE destination and also updates some tables via stored procedure calls using params. Sometimes the process "locks" and never finishes. When it locks it gets locked by process "-2" or a value that is not an actual process. What does this mean? What is -2?
View 3 Replies View RelatedMessage: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
I am using VB2005 Express. I had, evidently, a simpler version of SQL but deleted it, as was recommended, and then downloaded SQL 2005 Express. Now when trying to open an SQL database in a project I get that Information Message.
FURTHERMORE after loading the new SQL it wiped out all of my projects in VB 2005. The back up files were wiped out also.
That was naughty!
Thank you in advance for a reply.
gascitydan@yahoo.com
we have tables that load overnight. Sometimes a user will try to run a query up against that table while the table is loading and a deadlock occurs. I do not notice this until I get into the office. By this time many tables have not loaded. Is there a way to have SQL6.5 automatically Kill deadlock processes.
View 1 Replies View RelatedHi
We just installed SQL service pack 4. I am now finding that when doing a sp_who2 active, there are a lot of connections that are blocked by itself. The common factor is they all have a status of 'sleeping'. The strange thing is that even though it shows the connection is blocked, it is in fact not and will still return results. Below is a snapshot of a portion of what the sp_who2 active returns:
SPID Status Login HostName BlkBy DBName
53 sleeping sa TRACKER 53 dbABC
58 sleeping sa TRACKER 58 dbCDE
64 sleeping sa TRACKER 64 dbSTA
66 RUNNABLE User12 PC24 . master
70 sleeping User5 ANALYSIS 70 dbBML
74 sleeping sa TRACKER 74 dbCDE
76 sleeping sa TRACKER 76 dbPTS
83 sleeping User5 ANALYSIS 83 dbANA
86 DORMANT User11 CPTDB . NULL
Has anyone seen this? Is it related to the installation of service pack 4? (We have installed the services pack on many other SQL servers, but have not come accross this before.
Tx,
TessZA
I have changed SQL Server from default port to another port number. All appears fine. If SQL Server is restarted it is all fine, however, when the server is restarted SQL Server fails to start. If you then log into the server and manually start the services they start just fine. So, it appears that something blocks the port or uses the port upon server startup for a few seconds. The AV folks say that they are not blocking ports and that it AV service actually starts 15 seconds after the SQL start failure.So, the question becomes is how can I track down what is blocking or using that port?
This is Win2012/SQL2014 CU3
Server failed to listen on xxxxx::xxxxxxxxxx <ipv6> xxxxx. Error: 0x2741. To proceed, notify your system administrator.
Server failed to listen on fe80::5efe:10.255.196.25%18 <ipv6> 14001. Error: 0x2741. To proceed, notify your system administrator.
TDSSNIClient initialization failed with error 0x2741, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The requested address is not valid in its context.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log. SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Hi
I'm using VS'08 and develop in VB.
I'm using SQL CE 3.5 as a local cache for SQL'05 tables.
The table that's being updated uses an interger autonumber for the PK.
When the program starts up and inserts records into the table, it works, as long as no on else is insering recoreds into the same table.
Once a duplicate PK is created by another WS, the records no longer update the SQL'05 table.
If the blocked program is restarted, it'll insert records with a PK that's past the one found at the initial sync. until blocked again.
PK ---- ProgID ----- MSG
1
0
4
B
hello
2/6/2008 9:33:55 PM
2/6/2008 9:33:55 PM
2
0
4
B
hello
2/11/2008 7:54:38 PM
2/11/2008 7:54:38 PM
3
1
1
B
hello
2/11/2008 8:32:41 PM
2/11/2008 8:32:41 PM
4
0
4
T
just something
2/11/2008 8:34:18 PM
2/11/2008 8:34:18 PM
5
1
1
B
one
2/11/2008 9:13:41 PM
2/11/2008 9:13:41 PM
6
1
1
B
two
2/11/2008 9:14:06 PM
2/11/2008 9:14:06 PM
7
1
1
B
three
2/11/2008 9:14:35 PM
2/11/2008 9:14:35 PM
8
1
1
B
four
2/11/2008 9:15:04 PM
2/11/2008 9:15:04 PM
9
1
1
B
five
2/11/2008 9:15:59 PM
2/11/2008 9:15:59 PM
10
0
4
B
cp 1
2/11/2008 9:17:44 PM
2/11/2008 9:17:44 PM
11
0
4
B
cp 2
2/11/2008 9:18:13 PM
2/11/2008 9:18:13 PM
12
1
1
B
eight
2/11/2008 9:21:31 PM
2/11/2008 9:21:31 PM
13
0
4
B
cp 3
2/11/2008 9:21:52 PM
2/11/2008 9:21:52 PM
NULL
NULL
NULL
NULL
NULL
NULL
NULL
David L.
After installing IE7, I tried to open SQL Server Reporting Services 2005,
I'm getting a login prompt. I am admin on my laptop. My admin login
doesn't work here.
Am I missing a special security setting for IE7 that I have to change to
allow access to SSRS? I can open the SSRS configuration tool,
but not SSRS itself.
Thanks!!
I have a question about blocked processes. My manager wants to know how you can know the following when a blocked process is encountered.
The job, program and the statement that is causing the blocking. I have figured out how to determine the database involved but not a lot else. Such as in the blocked process report there is a waitresource field. How to you decipher it?
The report also tells you the client application, but in this case it just states the Micro focus Net Express as the client, not the job or program that is running.
I have an inputbuf listed but it just shows the database id, and the object id. And I am not sure how to locate the object id in this case, I am guessing sys.objects, but I do not seem to find that one.
I have read many things on MSN about the blocked process report, but it does not seem to go into great detail.
I seem to have all the pieces, but do not exactly know how to quickly tie them all together.
Any help would be appreciated.
Thanks,
Kathy
I am upgrading a sql server 2000 with sp4 (I have double and triple checked the sp) on a windows 2003 sp1 server and I keep getting the following error:
Name: Microsoft SQL Server 2000
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Service pack requirement check:
Your upgrade is blocked because of service pack requirements. To proceed, apply the required service pack and then rerun SQL Server Setup. For more information about upgrade support, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
I cannot find anything saying that I need any other service pack other than sp4 for sql serer 2000. Any one ever run into this or have a clue what might be causing the installer to think I have the wrong service pack?
Since couple of days, I am getting a very weird problem on my production environment. Basically, any index operation [rebuild, drop] etc. against an index on a table is getting blocked by [Sleeping,Dormant] connections.
Scenario: I have a Table TableX in database DBX on Server X. This table has a Non-unique, non-clustered index on a NVARCHAR column. This table is accessed by SQL written in a store procedures from Server A via a link server. Both the Servers are SQL 2008 R2 edition.
If I try to run any operation on the index, it get blocked by existing session and keeps on getting blocked by different sessions. Yesterday it was blocked for about 13 hours before I killed the session.
Executing sp_WhoisActive shows few sessions with Host Server A without any sql_text. These sessions actually blocked the indesx operation on the ServerX. These sessions always exist. Sessions details are
Status= Sleeping/Dormant
Open_Tran_Count=1
Host=ServerA
Read/Writes=NULL
CPU=NULL
WaitInfo=NULL
I am not sure how to find the sql causing this issue.
Is there anyone know any feature in SQL server which can detect/log the transaction which has been blocked unusually long? My idea is:
If a transaction has run for over 30 minutes, then log the following information in SQL server log:
1. State of the transaction : Blocked, Running, Sleeping
2. Time happeing
3. Duration
4. Last SQL it run
5. Blocked by which thread and info of the thread : the last SQL it run, the state of the thread.
Possibly it would be run in SQL server agent or SQL profiler has such feature, I am not sure about it. Can anyone suggest? Thanks.
My SQL Server is a shared account at MaximumASP.com a client just deployed my .NET application on GoDaddy.com and they have all there ports blocked and my app cannot connect to the SQL Server. Using "Network Library =dbmssocn" in the connection string did not help and GoDaddy will not help. MY QUESTION IS: how can I get my .NET app to connect to the SQL Server? web service? This is the first time I have run into this problem. There seems like there has to be some way. THANKS IN ADVANCE!
View 2 Replies View RelatedPerhaps someone here can give me a hand with this...
I have three servers running SQL Server 2000. Two are running Windows Server 2000 SP4 with SQL 2000/SP4 and one Windows Server 2003 with SQL 2000/SP?(probably not 4). I copied the installation DVD to a network drive and successfully performed an in-place upgrade of the Windows Server 2003 machine. However, when I try to upgrade either of the other servers, I get an error indicating that I have a service pack problem.
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Service pack requirement check:
Your upgrade is blocked because of service pack requirements. To proceed, apply the required service pack and then rerun SQL Server Setup. For more information about upgrade support, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Of course, the error doesn't indicate what the service pack problem actually is. My SQL 2005 media was probably released before SP4 was released because the Setup Help indicates that you can directly upgrade from SP3, but makes no mention of SP4. I can't imagine there are any migration problems with it since I can't find any reference to it as a problem on any SQL Server sites... So... I'm left wondering what my problem really is... :confused:
Any help would be appreciated.
How do I find what is keeing SSMS Object explorer from giving a list of tables or stored proc's? Even when you filter on what you are looking for? This will clear up in time, so I am guessing that some process is placing a lock .. but when I run the blocking query, below, I get no result.
SELECT DTL.[resource_type] AS [resource type]
, CASE WHEN DTL.[resource_type] IN ('DATABASE','FILE','METADATA')
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
[Code] .....
In master.dbo.sysprocesses I can filter for blocked users (Blocked >0)and I can create a self join(ON SPID = Blocked) to see what userLoginame is causing the block. In the column [cmd] I can see thecommand that the offending blocker is running to cause the block, butit only says "SELECT" etc. with no details about the stored procedurethat is causing the block.If I am running a Trace, I can see the exact stored proceduresincluding the parameters that every cmd is running.Is there a way to see that same Trace information when looking forblocked users in master.dbo.sysprocesses, or in some other place?Ideally what I want is a list of blocked users, who is causing theblocks and the stored procedure name (or other mischief) causing theblock.Any help is appreciated.lq
View 17 Replies View Related