Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transactional log is full.. space recovery lead to BLocking Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 2:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1413532
Posted Wednesday, January 30, 2013 2:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1413535
Posted Wednesday, January 30, 2013 3:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 40,424, Visits: 36,873
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1413540
Posted Wednesday, January 30, 2013 4:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1413558
Posted Wednesday, January 30, 2013 5:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
Did you investigate what the SPIDs where doing which blocked you?

Did you try setting the DB into single_user mode?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1413574
Posted Wednesday, January 30, 2013 5:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 312, Visits: 3,530
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.
Post #1413577
Posted Wednesday, January 30, 2013 5:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
andrew gothard (1/30/2013)
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;





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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1413585
Posted Wednesday, January 30, 2013 6:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 312, Visits: 3,530
anthony.green (1/30/2013)
andrew gothard (1/30/2013)
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;





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.
Post #1413594
Posted Wednesday, January 30, 2013 6:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 40,424, Visits: 36,873
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1413610
Posted Wednesday, January 30, 2013 10:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1413843
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse