January 30, 2013 at 2:39 am
one of my database's tran log got full on dev server . (recovery doesnt matter)
here are the details
total drive : 180 gb
log file on same drive : 28gb
data file on same drive : 68 gb
free space : 84gb
what 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 ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 30, 2013 at 2:46 am
If 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.
January 30, 2013 at 3:00 am
Wow, 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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2013 at 4:00 am
i 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.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 30, 2013 at 5:17 am
Did you investigate what the SPIDs where doing which blocked you?
Did you try setting the DB into single_user mode?
January 30, 2013 at 5:29 am
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;
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
January 30, 2013 at 5:45 am
andrew gothard (1/30/2013)
s it's a DEV box, try running this then retryingDECLARE @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;
Or to do it in 1 line
ALTER 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.
January 30, 2013 at 6:02 am
anthony.green (1/30/2013)
andrew gothard (1/30/2013)
s it's a DEV box, try running this then retryingDECLARE @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;
Or to do it in 1 line
ALTER 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.
Good point.
And with no cursor
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
January 30, 2013 at 6:42 am
If 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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2013 at 10:14 pm
anthony.green (1/30/2013)
Did you investigate what the SPIDs where doing which blocked you?
THe were some internal processes spid < 50
anthony.green (1/30/2013)
Did you try setting the DB into single_user mode?
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)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 31, 2013 at 1:36 am
stop the sql service cleanly, start it up the engine in single user mode, then try shrinking.
January 31, 2013 at 2:04 am
Bhuvnesh (1/30/2013)
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)
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 ?
February 1, 2013 at 4:08 am
i 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.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 1, 2013 at 4:09 am
Divine Flame (1/31/2013)
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 ?
no i missed that . actually blocking by internal processes puzzled me there (i kknow patience was the key there 🙂 )
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 1, 2013 at 4:10 am
Depends what the commands where going.
You should really debug what they are going using the sql text dmv.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply