Shrinking transaction log - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Shrinking transaction log

Author

Topic

userSQL
Starting Member

3 Posts

Posted - 2013-07-05 : 09:33:21

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

352 Posts

Posted - 2013-07-05 : 10:14:47

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources