USE DatabaseName
GO
use these queries to remove the lock
--dbcc opentran
--kill 56
--SELECT * FROM sys.dm_os_waiting_tasks;
--SELECT * FROM sys.dm_tran_locks
DBCC SHRINKdatabase(DatabaseName
BACKUP LOG DatabaseName
DBCC SHRINKdatabase(DatabaseName
Monday, April 27, 2009
Transaction Log is full
If you the getting the error like your transaction log is full when updating then put this query
USE DatabaseName
GO
use these queries to remove the lock
--dbcc opentran
--kill 56
--SELECT * FROM sys.dm_os_waiting_tasks;
--SELECT * FROM sys.dm_tran_locks
DBCC SHRINKdatabase(DatabaseName, 1)
BACKUP LOG DatabaseNameWITH TRUNCATE_ONLY
DBCC SHRINKdatabase(DatabaseName, 1)
USE DatabaseName
GO
use these queries to remove the lock
--dbcc opentran
--kill 56
--SELECT * FROM sys.dm_os_waiting_tasks;
--SELECT * FROM sys.dm_tran_locks
DBCC SHRINKdatabase(DatabaseName
BACKUP LOG DatabaseName
DBCC SHRINKdatabase(DatabaseName
Subscribe to:
Post Comments (Atom)
3 comments:
very useful query
HEY.... TO CLEAR THE DATABASE LOG BETTER USE THIS QUERY.
BACKUO LOG DBNAME WITH NO_LOG
DBCC SHRINKFILE(LOGFILE,1)
********
SOME ADDITIONAL INFORMATION
TO GET THE LOG FILE NAME USE THE COMMAND
USE DBNAME
SP_HELPFILE
***********
TO GET THE LOG FILE AND LOG % STATUS OF ALL THE DATABASES
DBCC SQLPERF(LOGSPACE)
****
HOPW THIS INFO WILL BE HELPFUL
CHEERS
VARUN BHAGAT
HI The actual query is
USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO
Post a Comment