shrink log file have problem

_{Wednesday, September 5, 2012 3:32 PM}

I ran DBCC SQLperf(logspace)

Databasename        Log size (MB)        Log Space used           Stastus

EmployeeInfo_Test 30970.38 **              0.844103** **                        0**

According above i have log file that has 30 GB and use only 0.25 GB 

So I took log file backup  and try to shrink log file using below command

DBCC shrinkfile(2,2000)-- make 2 GB

Cannot shrink log file 2 (EmployeeInfo_log) because total number of logical log files cannot be fewer than 2.

Can please help?

_{Thursday, September 6, 2012 11:18 AM ✅Answered}

Hi,
From: http://msdn.microsoft.com/en-us/library/ms189493.aspx

"Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used. The size of the virtual log file is chosen dynamically by the Database Engine when log files are created or extended"

Each of those VLF's look to be 15.1GB :0   (though your columns are scrunched up, I think I've picked the right bits out), and as you need 2 VLF's in your log file, you might struggle to shrink this.  I think the best you're going to be able to do is get it down to 15GB.  You could:

1) Add a new log file (then when you run dbcc loginfo for that database, you should see that a new file has been added with a new fileid)

2) Run dbcc shrinkfile(2, emptyfile)

3) Run dbcc shrinkfile(2, 1) - DBCC LOGINFO should return (probably) 5 rows.  One row for fileid 2, 4 rows for fileid 3

4) Grow out the primary log file by 1MB, i.e. ALTER DATABASE mydb MODIFY FILE(NAME = N'mydb_log', SIZE = 15486mb).

5) Run DBCC shrinkfile(3, emptyfile)

6) Run alter database mydb remove file mydb_log2

7) DBCC shrinfile(2,1)

That's about as small as I think you can get it (as far as my frazzled brain can currently think).  You could possibly (though wouldn't recommend) detaching then reattaching with rebuild log option, but that would scare me!

Thanks, Andrew

_{Wednesday, September 5, 2012 4:11 PM}

what's dbcc loginfo return for the database you're trying to shrink the log file for?  Each row returned represents a VLF.  Was the log initially sized at 30GB?  If so, you should have 16 VLF's, each VLF being a little under 2GB I guess.  That means, based on the error, you can probably only shrink the log file to somewhere like 4GB.  Give that a go and see what happens:

dbcc shrinkfile(2, 4096)

Thanks, Andrew

_{Wednesday, September 5, 2012 4:23 PM}

Thank you for help

I don't know what was initial size of log file?

DBCC Loginfo ('employeeinfo_test')

Fileid    filesize         Start offset     Fseqno status Parity CreateLSN

2 16237395968 8192         157         2 64  0
2 16237395968 16237404160 0       0 0  0

What if it was initially created at 30 gb 

got some error when I run

DBCC Shrinkfile( 2,4096)

_{Wednesday, September 5, 2012 9:07 PM}

What error you are getting

check this link

http://msdn.microsoft.com/en-us/library/ms189493.aspx

Sample script

USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1); --> provide the log file that you want to shrink and next size.
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GOThank you

http://sqlage.blogspot.com/

_{Wednesday, September 5, 2012 9:14 PM}

I did it but not work

_{Monday, September 10, 2012 6:59 AM}

check below things :-

1) any open transaction on database is exits or not

2) check sys.databases for log_reuse_desc column, to check the current state

This is the normall case when the active vlf is at the end(2gb) due to which your file is not shrinking beyound that.

Check for above 2 things & take one more t-log backup & try to shrink again.

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!