August 19, 2012 at 12:26 pm
Hi All,
Could you please assist on tlog space issues. I am trying to execute the query have update statement like
''update tblGatewayPersons SET ImportDataXML_NT = ImportDataXML_NT''
tlog space getting full in a few minutes and the query is through the follwing error
''The transaction log for database 'Perspective' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases''.
Please advise me.
Thanks
Ashwin
August 19, 2012 at 12:29 pm
Post your actual T-SQL statement.. some one will be able to show you how to run that for a reasonable number of rows, then take a log file backup, and then continue with the updatting.
August 19, 2012 at 12:57 pm
Hi,
Thaks for you prompt Reply. Please check the below script. I execute the script by batches but during the below single statement tlog is getting full.
''update tblGatewayPersons SET ImportDataXML_NT = ImportDataXML_NT''.
Please find the atatched script in .txt format and I execute the script untill the above statement. I make a gap between the script to easy Identify.
Error I am getting
''The transaction log for database 'Perspective' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases''
Thanks
Aswin
August 19, 2012 at 6:54 pm
Hi All,
Can anybody help me out from the above issue.
August 20, 2012 at 3:09 am
--begin tran
use master
declare @DB varchar(50)
select @DB = 'DB_Name'
CREATE TABLE #TMPFIXEDDRIVES (
DRIVE CHAR(1),
MBFREE INT)
INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES
CREATE TABLE #TMPSPACEUSED (
DBNAME VARCHAR(50),
FILENME VARCHAR(50),
SPACEUSED FLOAT)
INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')
SELECT C.DRIVE,
CASE
WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS DISKSPACEFREE,
A.NAME AS DATABASENAME,
B.NAME AS FILENAME,
CASE B.TYPE
WHEN 0 THEN 'DATA'
ELSE TYPE_DESC
END AS FILETYPE,
CASE
WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS FILESIZE,
CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,
B.PHYSICAL_NAME
FROM SYS.DATABASES A
JOIN SYS.MASTER_FILES B
ON A.DATABASE_ID = B.DATABASE_ID
JOIN #TMPFIXEDDRIVES C
ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
JOIN #TMPSPACEUSED D
ON A.NAME = D.DBNAME
AND B.NAME = D.FILENME
and A.NAME = @DB
ORDER BY DATABASENAME asc
DROP TABLE #TMPFIXEDDRIVES
DROP TABLE #TMPSPACEUSED
--rollback tran
share the output of the above query for your DB and what is the backup and log growth details.
Regards
Durai Nagarajan
August 20, 2012 at 3:55 am
If your database is in full recovery you need to take log backups between each batch. Otherwise make the log larger.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply