August 11, 2009 at 7:56 am
Hi All,
I'm new to the forum and new to SQL Server. We lost our DBA and it's now fallen to me to pick up the slack. Though training is in my future there are some tasks that I need to complete before then.
We have a few SQL servers working with several other products but the one that I'm working on now is part of our DAM (Digital Asset Management) solution, Artesia.
Over this last weekend our transaction log inexplicably quadrupled in size (approx 18GB's - 68GB's) and has filled the partition it sits on.
I'm trying to perform a backup of the db and log to an external drive I have attached to the box so that I can then perform a "shrink" on the db and try to get this log slimmed down so we can get back up and running.
I'm attempting to perform the backup by right-clicking on the db instance and selecting the backup...pointing to the ext drive that has 800+GB of free space. However I immediately get an error message stating that the backup failed. Additional information states that "an exception occurred while executing a transact-SQL statement or batch. The transaction log for database 'Artesia_Prod' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.
I've tried using "Full" backup and "Transactional Log" but get the same failure.
Bearing in mind that my short term goal here is to have a backup of the db and log should the shrink command cause problems...does anyone have a suggestion for getting around this problem? Or another way I can get this transaction log slimmed down so that the drive it sits on is not full? Note that the db itself did not seem to grow...only the log.
Any help would be GREATLY appreciated and though I'm a fairly seasoned IT guy I'm pretty new to SQL. Error screenshot attached.
Thanks
August 11, 2009 at 8:56 am
Can you run this code and paste the output here:
select log_reuse_wait_desc from sys.databases where name='test'
Replace the word test with actual database name which will tell what is preventing the log to be truncated.
--------------------------
As per BOL, you can try out few things:
1 Backing up the log.
2 Freeing disk space so that the log can automatically grow.
3 Moving the log file to a disk drive with sufficient space.
4 Increasing the size of a log file.
5 Adding a log file on a different disk.
6 Completing or killing a long-running transaction.
August 11, 2009 at 9:17 am
There is no reason to backup the log if you want to do a full. I would recommend dumping the log, shrink it and run a full. This will free up some needed space on your drives.
August 11, 2009 at 9:22 am
ps (8/11/2009)
Can you run this code and paste the output here:
select log_reuse_wait_desc from sys.databases where name='test'Replace the word test with actual database name which will tell what is preventing the log to be truncated.
--------------------------
As per BOL, you can try out few things:
1 Backing up the log.
2 Freeing disk space so that the log can automatically grow.
3 Moving the log file to a disk drive with sufficient space.
4 Increasing the size of a log file.
5 Adding a log file on a different disk.
6 Completing or killing a long-running transaction.
I ran: select log_reuse_wait_desc from sys.databases where name='test' exactly as shown but got not results...0 rows affected.
1. I'm trying to run the following but my syntax is off. Could you help me fill in the blanks?
This is what I entered and I got the below syntax errors. Can you help edit this?
BACKUP LOG Artesia_Prod TO WITH TRUNCATE_ONLY DBCC SHRINKFILE('Artesia_Prod_log.ldf',
The errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
2. Don't have an option to free space. There are 2 partitions on that drive. C: which is NOT where the db and log currently live and there's only 7 GB of space remaining. E: is the larger 96 gb partition where the DB & Log currently reside and it is completely full...again because this log for some reason mushroomed from 18gb-68gb over a weekend with no one accessing it in any way. The only other volume options I have is our SAN but that may not be an option as I don't know how much space I have...and the 800+gb removeable drive attached to the box which I'm trying to use for the backup.
3-5. Again, not sure how to do this or where I could do it under above circumstances.
6. I've never done this but for what it's worth I investigated the following:
I went into sys.database catalog view (I think-Please see attached screenshots) and looked at the columns...log_reuse_wait and log_reuse_wait_desc and both contained 0 items. Not exactly sure what that means but could it mean that nothing is currently preventing the log from being truncated?
August 11, 2009 at 9:28 am
You didnot run the code i mentioned. YOu just took the snapshot of the properties of that column
I wanted u to paste the code in a query window and then run the code by selecting it and pressing F5.
BACKUP LOG Artesia_Prod TO WITH TRUNCATE_ONLY
and
DBCC SHRINKFILE('Artesia_Prod_log.ldf',
are two different statements altogether.
The first one will truncate the log (mark the enteries in the log file to be reused but wont physically reduce it's size)
The second one will physically reduce the size of the log file.
Run the statements one by one.
there is syntax error in the dbcc line. correct syntax is
DBCC SHRINKFILE('Artesia_Prod_log.ldf',2048)
August 11, 2009 at 9:41 am
ps (8/11/2009)
You didnot run the code i mentioned. YOu just took the snapshot of the properties of that columnI wanted u to paste the code in a query window and then run the code by selecting it and pressing F5.
BACKUP LOG Artesia_Prod TO WITH TRUNCATE_ONLY
and
DBCC SHRINKFILE('Artesia_Prod_log.ldf',
are two different statements altogether.
The first one will truncate the log (mark the enteries in the log file to be reused but wont physically reduce it's size)
The second one will physically reduce the size of the log file.
Run the statements one by one.
there is syntax error in the dbcc line. correct syntax is
DBCC SHRINKFILE('Artesia_Prod_log.ldf',2048)
Ran the code you provided and edited my post with results...see above.
Ran: BACKUP LOG Artesia_Prod TO WITH TRUNCATE_ONLY
and got:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
I'm sure what I'm missing is obvious but please bear with me as I'm new to this.
August 11, 2009 at 9:54 am
OK.
Ran: BACKUP LOG Artesia_Prod TO DISK='F:\\backup' WITH TRUNCATE_ONLY
And got a command completed successfully.
But I don't see a directory being created yet on the F: drive...how do I know this is running and when it is complete so that I may complete the second command?
Also is it possible this command is trying to run on the db's previous location which WAS on the C: drive? The db and log had since been moved to the larger partition because it was filling up the 30gb of the C partition. While I removed the older "inactive" log file from C to clear space for paging I left the "inactive" copy of the db on C:.
August 11, 2009 at 9:56 am
my apologies. I oversaw the error in the backup log statement.
correct code is
backup log Artesia_Prod with truncate_only
August 11, 2009 at 10:00 am
backup log Artesia_Prod with truncate_only will not create any file on the drive.
After running this, try shrinking the log file usind dbcc shrinkfile and see if the physical size of the log gets reduced.
August 11, 2009 at 10:07 am
Ok, couple things.
By truncating the log you have broken the log chain, will not be able to take log backups and will not be able to restore to a point in time until you take another full backup. This means that should something break, you'll potentially be losing hours of data. If that's a concern, please take a full backup ASAP.
Please, please, please read through this - Managing Transaction Logs[/url] to get an idea how to properly manage transaction logs so that, should it be necessary, you can do point in time restores and the log won't fill the drive in future.
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
August 11, 2009 at 10:12 am
GilaMonster (8/11/2009)
Ok, couple things.By truncating the log you have broken the log chain, will not be able to take log backups and will not be able to restore to a point in time until you take another full backup. This means that should something break, you'll potentially be losing hours of data. If that's a concern, please take a full backup ASAP.
Please, please, please read through this - Managing Transaction Logs[/url] to get an idea how to properly manage transaction logs so that, should it be necessary, you can do point in time restores and the log won't fill the drive in future.
If not full backup, take differential backup, this will save considerable amount of time, resources and backup file also will be small. Once differential backup is done, transaction log backups can be taken.
August 11, 2009 at 11:00 am
ps (8/11/2009)
backup log Artesia_Prod with truncate_only will not create any file on the drive.After running this, try shrinking the log file usind dbcc shrinkfile and see if the physical size of the log gets reduced.
Gotcha. I ran the dbcc command as:
DBCC SHRINKFILE('F:\\backup\\Artesia_Prod_log.ldf',2048)
but got the error:
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'F:\\backup\\Artesia_Prod_log.ldf' for database 'Artesia_Prod'.
Tried creating the directory "backup" on the F: drive but still got that error.
Am I missing something in the command? Also, I go back to my previous statement that I wonder if for some reason this command is trying to locate the log file from it's original default location on C:\SQL\log which now stands empty. Our DBA (Before his departure) moved the db & log to the E: partition because space on C was running low and there was more space on E. After the move to the E partition I moved the original (presumably inactive) log on C: to our SAN to free up space on C but left the previous (also presumably inactive db) there just in case.
August 11, 2009 at 11:33 am
shopping (8/11/2009)
Gotcha. I ran the dbcc command as:DBCC SHRINKFILE('F:\\backup\\Artesia_Prod_log.ldf',2048)
but got the error:
You need the logical name of the file, not the physical name of the file. Run this in the database in question to see the logical name of the file for that DB.
select name from sys.database_files where type_desc = 'LOG'
That's the name you need to pass to ShrinkFile. There should be several examples and lots of details in SQL's Books Online (press F1 when in management studio to open BoL)
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
August 12, 2009 at 7:10 pm
murthykalyani -- Posted 08/11/09 @ 11:12 AM
If not full backup, take differential backup, this will save considerable amount of time, resources and backup file also will be small. Once differential backup is done, transaction log backups can be taken.
According to Microsoft a FULL backup must be done before you will be able to create anymore log backups
EDIT: referenced who quote was from
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply