SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Shrinking transaction log
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

userSQL
Starting Member

3 Posts

Posted - 07/05/2013 :  09:33:21  Show Profile  Reply with Quote
Hi,

I have a situation where the SQL Server 2008 database and transaction log is backed up by an external package. The transaction log file is very large.
I can only shrink the file to a certain point 214 GB from 220 GB though the use of the GUI utility.

I need to shrink the file down to 100 GB.

Currently, the database is in simple mode.
Per my research the database needs to be backed up in full mode
through SQL Server to commit the transactions in the log file.
Is this true? Or can the database be backed up in simple mode
and then shrink the transaction log?

If the database does not need to be backed up, how can the transactions in the log file be commited show I can shrink the file?

Also, there is not enough space on the server to back up the database. Can the database be backed up to a network drive?
If so, how can this be done? Is there an example?
Can the back up instructions be entered in the SQL Server Management Studio Query window?
I can map to the network drive from the server where the SQL database resides.
Will this still commit the records in the transaction log by backing up to a network drive?

Can the transaction log be shrinked at this point?

Does the transaction log need to be backed up so I can shrink the file?

I used the following SQL template to shrink the Transaction log and the log file did not shrink (I put in the correct database and transaction log names). The database was not backed up through SQL server.

USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
GO


Thank you for your help,

Mike

djj55
Constraint Violating Yak Guru

USA
329 Posts

Posted - 07/05/2013 :  10:14:47  Show Profile  Reply with Quote
Transactions are commited when the transaction is finished. The transaction log will still how a copy of the transaction. You can backup a database that is in simple mode, and you can back it up to a mapped drive. The mapped drive must be mapped on the sql server machine.

This is a quick and simple explination. There are many points that can be expanded on.

As to the size of the transaction log file, it will only shrink the the initial size.

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000