Solved

Trunicate Transaction Login in SQL 2008

Posted on 2014-02-09
6
245 Views
Last Modified: 2014-02-25
My SQL 2008 doesn't backup for a long time and the Log file partition is full.

After backing up the transaction log, the size is still remain the same. What else can I do to free up the space in the transaction log file ?

Tks
0
Comment
Question by:AXISHK
  • 3
  • 3
6 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 39846640
I'd shrink the Database,
Then check the log file size.

To stop this happening again,
I'd do a full backup of the DB, then institute an hourly log backup.

Generally, a daily full backup and hourly log backups will suffice.

T
0
 

Author Comment

by:AXISHK
ID: 39846697
The log will be automatically expanded. How to truncate it to a smaller size ?
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39846705
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:AXISHK
ID: 39848944
Is there a sample script that I can reference to backup the database and Log onto a portable HDD.

- I need to backup the database and transaction log onto a portable device.
- truncate the transaction log
- shrink the log file

To test my understanding, backup transaction log with truncate will only remove the committed transaction but it can't resize the physical size of the log. I need to issue DBCC to shrink the transaction log (eg. resize it to 10Mb in the following case).

DBCC SHRINKFILE (<LogName>, 10240) WITH NO_INFOMSGS

What's the difference if I only issue DBCC SHRINKFILE((<LogName>) ?

Can we shrink the database ? If yes, under what situation ? Tks again.
0
 
LVL 12

Accepted Solution

by:
Tony303 earned 500 total points
ID: 39849135
Hi,

I may run out of time on all these questions in 1 post...
But here goes...

I need to backup the database and transaction log onto a portable device.

I'd use the SQL Server Management Studio Maintenance Plans, create a new plan using the wizard and choose the Backup Database Task.
Once you have made the selections, there is a "View T-SQL" button.
This will show all the SQL needed to execute what you have created in the Wizard.

For each DB selected you'd see something like this....
BACKUP DATABASE [YourDB] TO  DISK = N'\\yourbakackupPath\YourDBName_backup_2014_02_11_161719_3854038.bak' WITH NOFORMAT, NOINIT,  NAME = N'YourDBName_backup_2014_02_11_161719_3854038', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

Open in new window


Ditto process for a .trn file backup, routine. Look at doing a new Maintenance Task wizard and go from there.

portable device

OK, Yes, I have the exact situation on a legacy machine and no internal disk space that I have to keep running for a few more months yet. So, the external drive is connected to the usb port on the machine that is hosting SQL. This drive has been made visible to the computer, I am not 100% sure how the engineers rigged it, but I am referencing it via a UNC path using an ip number.

So my path looks more like this

DISK = N'\\192.168.10.10\
Rather than this... I wrote above...
DISK = N'\\yourbakackupPath

Be aware backup up over USB is slow, real slow.

T
0
 

Author Closing Comment

by:AXISHK
ID: 39887693
Tks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question