February 22, 2011 at 5:33 pm
SQL 2000
Transaction Log File is 54 GB, More than 10 times the size of the database.
We are using Backup exec to backup databases. It is not truncating the trans log file.
Can somebody please help me with a command line syntax to do a backup to local disk. I am looking for the command which backups the database as well as truncates the inactive logs.
I know this is a very basic question but our inhouse SQL exp is less than zero.
Appreciate the help.
PC
February 22, 2011 at 6:00 pm
What is your recovery method on the database? If it's not simple, you need to also backup the transaction log.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 7:32 pm
Suggested reading: www.sqlbackuprestore.com.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 22, 2011 at 9:58 pm
The restore method is FULL.
PC
February 22, 2011 at 10:18 pm
It's not the restore method, it's the recovery model. Run this:
SELECT DATABASEPROPERTYEX('db1', 'recovery')
If it's simple, then just
Backup database mydb to disk = 'c:\mybackup.bak' with INIT
Replace mydb with your database name, and change the path/file.
If it's in full, then you also need a transaction log backup.
Backup log mydb to disk = 'c:\mybackup.trn'
You need to schedule both of these to clear the log and save the database. If you need to recover to more than the full backup, make log backups periodically during the day.
You can also easily set this up with a maintenance plan.
February 23, 2011 at 6:17 am
You might want to take a look at this blog entry[/url]. It addresses exactly what you're talking about.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2011 at 5:20 am
Any web search will turn up umpteen blogs, forum posts, articles, etc about FULL RECOVERY mode and having to do some form of transaction log backup to keep the tlog from growing indefinitely.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 24, 2011 at 8:12 am
I read the blogs posts here etc. I did setup a Maint Plan to backup DB and Transaction log but the log file is still huge.
The commands that were suggested here, do you run them from a command prompt windows or right within SQL.
If from SQL which screen.
If there is a way to truncate log file from maint plan that would work out I think.
Still have my HUGE 54GB trans log file.
PC
February 24, 2011 at 8:22 am
The log file grew that large while you weren't backing it up. Just because you're now backing it up doesn't mean it will shrink. Instead, you'll have to do something that I strongly advise against under most circumstances, shrink the file.
If anyone suggested TSQL, then you run that from a query window or include it in a SQL Agent Job. Usually, the Agent Job is how you would schedule these processes to run for you automatically.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2011 at 9:21 am
You can right click the database, then select Tasks, then Shrink, then files. Shrink just the log file.
Look at the largest log backup you have, add a 10-20% pad, and shrink to that size.
February 24, 2011 at 10:53 am
Thank you for 'shrink file' suggestion. Did that and found out it only cut down the log file size to about 50Gb eventhough I specified filesize to be 10Gb.
Looking at the prop of the DB itself I notice the allocated space for the log file is 50GB. Not my doing.
Cannot change it down.
Any suggestions on how I can change the allocated space?
SQL 2000.
PC
February 24, 2011 at 10:57 am
Also maybe take a read through this: http://www.sqlservercentral.com/articles/64582/
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 24, 2011 at 11:47 pm
Awesome article Gail; very clear.
Anirev - in short, you MUST backup the log in order for SS to mark the huge amount of log entries as 'available to be overwritten'. 50G of entries are all currently marked as Active, so SS won't let you shrink the file until those entries are marked as no longer Active, which is part of that the BACKUP LOG does. After a good log backup, THEN do a shrink and you'll find more success.
See the noted articles to get a clear understanding of how this all works. See BOL for syntax and options of the tlog backup if you don't really want to back it up, but just mark everything as Available.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply