SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactional log is full.. space recovery lead to BLocking


Transactional log is full.. space recovery lead to BLocking

Author
Message
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5200 Visits: 4076
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;-)
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10056 Visits: 6324
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
When a question, really isn't a question - Jeff Smith
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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

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


Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5200 Visits: 4076
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;-)
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10056 Visits: 6324
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
When a question, really isn't a question - Jeff Smith
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


andrew gothard
andrew gothard
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1043 Visits: 5782
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.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10056 Visits: 6324
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
When a question, really isn't a question - Jeff Smith
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


andrew gothard
andrew gothard
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1043 Visits: 5782
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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

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


Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5200 Visits: 4076
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search