InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 19:12:17 GMT20http://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxDepends what the commands where going.You should really debug what they are going using the sql text dmv.Fri, 01 Feb 2013 04:10:21 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspx[quote][b]Divine Flame (1/31/2013)[/b][hr]Did you see the command run by internal process (DBCC INPUTBUFFER) ? Or in case DBCC INPUTBUFFER was not providing any result atleast the Command column's value in sp_who2 result ?[/quote] no i missed that . actually blocking by internal processes puzzled me there (i kknow patience was the key there :-) )Fri, 01 Feb 2013 04:09:12 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxi still have one confusion, Why i was getting "blocking by internal processes" for all above mentioned commands , is it normal (though i havent experienced in past) behaviuor ? i know the best apporach on that moments was to wait for command to get completed.Fri, 01 Feb 2013 04:08:00 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspx[quote][b]Bhuvnesh (1/30/2013)[/b][hr]but i was not sure why everything got blocked by internal processes.Should i have been waited for sometime ? (for commmand which i executed to change the db to single user mode) ? can a alter database statement take around 15 minutes to execute ? (though i stopped it after 15 minutes)[/quote]Did you see the command run by internal process (DBCC INPUTBUFFER) ? Or in case DBCC INPUTBUFFER was not providing any result atleast the Command column's value in sp_who2 result ?Thu, 31 Jan 2013 02:04:47 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxstop the sql service cleanly, start it up the engine in single user mode, then try shrinking.Thu, 31 Jan 2013 01:36:41 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspx[quote][b]anthony.green (1/30/2013)[/b][hr]Did you investigate what the SPIDs where doing which blocked you?[/quote] THe were some internal processes spid < 50 [quote][b]anthony.green (1/30/2013)[/b][hr]Did you try setting the DB into single_user mode?[/quote] YEs i tried that too but again that got blocked but i was not sure why everything got blocked by internal processes.Should i have been waited for sometime ? (for commmand which i executed to change the db to single user mode) ? can a alter database statement take around 15 minutes to execute ? (though i stopped it after 15 minutes)Wed, 30 Jan 2013 22:14:33 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxIf it's a system process (which are not necessarily under session id 50), then wait or restart SQL. Probably something like the checkpoint that can just be left to finish.Wed, 30 Jan 2013 06:42:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspx[quote][b]anthony.green (1/30/2013)[/b][hr][quote][b]andrew gothard (1/30/2013)[/b][hr]s it's a DEV box, try running this then retrying DECLARE @DBName NVARCHAR(200); SET @DBName = '<Enter your db name here>'; DECLARE @spid INT; DECLARE IHateCursors CURSOR FOR SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID(@DBName) and spid > 50 UNION SELECT DISTINCT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID(@DBName) AND request_session_id > 50; OPEN IHateCursors; FETCH NEXT FROM IHateCursors INTO @spid; WHILE @@fetch_status = 0 BEGIN EXEC ('kill ' + @spid); FETCH NEXT FROM IHateCursors INTO @spid; END CLOSE IHateCursors; DEALLOCATE IHateCursors;[/quote]Or to do it in 1 lineALTER DATABSE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE;Will kill all SPIDS, roll them back and if yur in that database context, leave you with access.[/quote]Good point.And with no cursorWed, 30 Jan 2013 06:02:51 GMTandrew gothardhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspx[quote][b]andrew gothard (1/30/2013)[/b][hr]s it's a DEV box, try running this then retrying DECLARE @DBName NVARCHAR(200); SET @DBName = '<Enter your db name here>'; DECLARE @spid INT; DECLARE IHateCursors CURSOR FOR SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID(@DBName) and spid > 50 UNION SELECT DISTINCT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID(@DBName) AND request_session_id > 50; OPEN IHateCursors; FETCH NEXT FROM IHateCursors INTO @spid; WHILE @@fetch_status = 0 BEGIN EXEC ('kill ' + @spid); FETCH NEXT FROM IHateCursors INTO @spid; END CLOSE IHateCursors; DEALLOCATE IHateCursors;[/quote]Or to do it in 1 lineALTER DATABSE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE;Will kill all SPIDS, roll them back and if yur in that database context, leave you with access.Wed, 30 Jan 2013 05:45:16 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxs it's a DEV box, try running this then retrying DECLARE @DBName NVARCHAR(200); SET @DBName = '<Enter your db name here>'; DECLARE @spid INT; DECLARE IHateCursors CURSOR FOR SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID(@DBName) and spid > 50 UNION SELECT DISTINCT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID(@DBName) AND request_session_id > 50; OPEN IHateCursors; FETCH NEXT FROM IHateCursors INTO @spid; WHILE @@fetch_status = 0 BEGIN EXEC ('kill ' + @spid); FETCH NEXT FROM IHateCursors INTO @spid; END CLOSE IHateCursors; DEALLOCATE IHateCursors;Wed, 30 Jan 2013 05:29:21 GMTandrew gothardhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxDid you investigate what the SPIDs where doing which blocked you?Did you try setting the DB into single_user mode?Wed, 30 Jan 2013 05:17:00 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxi missed to post here but i also tried to change the recovery model to simple but again this also cause blocking (seen by sp_who2) so i stopped in after ~20 min.Wed, 30 Jan 2013 04:00:54 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxWow, two ways to mismanage the log. If you don't care about recovering to a point in time then put the DB in simple recovery model and leave it alone.Deleting the log is one of those things that should never be done. The log is not an optional file (regardless of recovery model), deleting it can leave the database transactionally inconsistent, structurally inconsistent or completely unusable.Wed, 30 Jan 2013 03:00:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxIf recovery dont matter then set recovery to simple and leave it in simple.Once in simple issue a checkpoint in the DB, then shrink the file.Taking offline detach attach can cause you a lot more problems as it may not always be possible to recreate the log file.Wed, 30 Jan 2013 02:46:13 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1413532-2893-1.aspxone of my database's tran log got full on dev server . (recovery doesnt matter)here are the detailstotal drive : 180 gblog file on same drive : 28gbdata file on same drive : 68 gb free space : 84gbwhat i did : 1) try to take log backup with backup log testdb to disk = 'nul' .. ran to 30 minutes . i stopped it when i see it was getting blocked by some internal prcesses (spid < 50)2) then tried Dbcc shrinkdatabase ('testdb') .. same blocking happened .. i stopped it 3) tried to take database offline (so that log file can be removed by attach .detach and remove log file)btu again query got blocked by internale porceess . Any help ? Wed, 30 Jan 2013 02:39:18 GMTBhuvnesh