February 20, 2012 at 2:22 am
I found T-log was 99% filled up, not taking any backups and i tried to change unrestricted growth from restricted growth 2,097,152. But it is not allowing giving error lock period time out period exceeded (micorsoft sql server, Error: 1222. What should i do? please suggest any one....
February 20, 2012 at 2:31 am
How big is your TX log? 2TB? Or is the drive it is located on full?
run the following and return the results
select name, log_reuse_wait_desc, recovery_model_desc from sys.databases where name = 'NAME OF DB GOES HERE'
February 20, 2012 at 2:37 am
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
February 20, 2012 at 3:04 am
It is showing the results as below...
namelog_reuse_wait_descrecovery_model_desc
nod055LOG_BACKUPFULL
please give me the next step..
February 20, 2012 at 3:08 am
GilaMonster (2/20/2012)
Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
p.s. restricted growth of 2 TB (what you say it is), is the same as unrestricted, so focus on the cause of the problem (the lack of log backups), not the log's max size setting.
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
February 20, 2012 at 3:19 am
to add on from Gail's post, you haven't been doing regular TX log backups.
you now have a number of choices
backup the log to a new 2TB drive, shrink the log and then setup regular TX log backups
change the database to SIMPLE recovery and then shrink the log, then do a full backup --Only do this if you dont need full recovery
change the database to SIMPLE recovery, shrink the log, change recovery to FULL, do a full backup, impliment TX log backups
force a checkpoint, detach the database, delete the ldf, then use CREATE DATABASE.........FOR ATTACH
February 20, 2012 at 3:49 am
anthony.green (2/20/2012)
force a checkpoint, detach the database, delete the ldf, then use CREATE DATABASE.........FOR ATTACH
No!!
That's a really, really good way to lose or damage a database badly, especially if the log is full or near-full (checkpoint doesn't have space to log, so detach is not done with a clean shutdown and the attach fails).
The log file should never be deleted (yes, that's one of the very few nevers in SQL Server)
p.s. It would be CREATE DATABASE ... FOR ATTACH_REBUILD_LOG
I somehow doubt the log really is 2TB in size, it's probably just close to the size of the drive.
If log backups haven't been done this long, then there's no point-in-time recovery anyway, so it's probably reasonably safe to switch to simple, shrink log to a reasonable size, switch back to full recovery, take a full backup and then look at the appropriate log maintenance.
Worst case, create a second log file on another drive, then simple recovery, shrink, full recovery, backups and later remove that second log file.
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 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply