July 19, 2012 at 3:08 pm
Not sure why it would happen. I have enough disk space on the folder where the data files and log files.
I am getting Transaction Log for Database is full message.
Database is in Simple Recovery Model.
When I righ click on the daabase properties, space avialbale is 0.
DB size is 20 GB
Can you please guide to the steps to correct this?
Thanks in adavce.
July 19, 2012 at 3:26 pm
And the DB and log files are set to 'Unrestricted growth'?
July 19, 2012 at 3:30 pm
Yes. And sanpshot isolaiton level on, auto update statiscits on as well...not sure if that contributes to fill the transaction log.
July 19, 2012 at 3:39 pm
Do you see any blocking on the server? Are you running Express edition? if you issue a checkpoint manually does it come back with an error?
July 19, 2012 at 3:41 pm
No blocking. Enterprise Edition.
July 19, 2012 at 3:51 pm
What if you right click on the database and go tasks>shrink>files and select 'log' from file type drop down. What does it say in currently allocated space and available free space?
July 19, 2012 at 3:55 pm
Currently allocated space: 20038.75 MB
Available free space 101.13 MB (0%)
No error on checkpoint and no active open transactions and no blocking.
July 19, 2012 at 4:03 pm
Run the DBCC OPENTRAN('dbname') to see if there is any long running open transaction preventing the log file to be truncated.
also run CHECKPOINT and then try to shrink the log this might help.
Pooyan
July 19, 2012 at 4:20 pm
Is there an OS paging file on this drive?
July 19, 2012 at 4:23 pm
OK I din't read your previous response carefully:-D.
try
select * from sys.databases
the log_reuse_wait_desc shows the reason.
Pooyan
July 19, 2012 at 4:50 pm
reason says NOTHING.
July 19, 2012 at 5:06 pm
Create two backups of the database. Then Right-click the database and select Task\Shrink\Files and set File type to Log, then OK.
Dan
July 19, 2012 at 5:16 pm
Dan, tried that. But that didn't help. The screen closes in fraction of second as if nothing happened.
July 19, 2012 at 5:27 pm
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Run a checkpoint first, the value of log_reuse in sys.databases doesn't always immediately update.
If nothing is holding the log active (log reuse of NOTHING) and shrink won't do anything it means that the active portion of the log is at the end of the file. Run some transactions (any dummy ones will do) to wrap the log around (it won't grow because it's mostly empty) and then try again to shrink. Once the active portion is at the beginning of the beginning of the file, you'll be able to shrink it.
Finding out what filled it may be harder...
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
July 19, 2012 at 7:26 pm
One issue...The initial log size was set to 1...Could this be issue? I increased this to 25% of the database size and ran SQLPerfLogs and now it is showing space used is 0.05%....But when i right click on database and go to properties the space available is 100 MB now....Could this have fixed the issue?
Can i run some command/query to see if I get transaction log full message or not?
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply