Initial size is not just 3MB, it is taken from the model database (if not specified during the creation of your user database.) So assuming you haven't specified a initial size during the creation of your user db and you haven't altered the model database file sizes after you have created your userdb you can do the following:
--Create testDB
CREATE DATABASE [TEST_100]
GO
--grow your database file size
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'Test', SIZE = 100MB )
GO
--switch context
USE [TEST_100]
GO
--Find size of modelDB mdf file, this is your initial file size used for the userdb
DECLARE @TargetFileSize int
SELECT @TargetFileSize = (size * 8 / 1024)
FROM sys.master_files
WHERE database_id = 3 --model database
AND file_id= 1 --first file is mdf, assuming you have a model with just one mdf. If you have multiple files, change for the one you need to find.
--shrink the the first file of your current database to the target size that you just found.
DBCC SHRINKFILE (1,@TargetFileSize)
EDIT
Okay, some extra info is needed after your edits and comments.
First of all. I feel that the "Initial size" label that you see when you look at the file properties in SSMS is a misnomer. Basically, your intial size is just a concept. It's the first size that is used during creation of the database. You can either explicitly specify this in the CREATE DATABASE
statement, or you can have SQL Server implicitly copy it from the model database by ommiting that information during creation.
However, once the database is created, from a DBA perpective there is no such thing as a "initial size" there is only one property visible for a DBA and that is: the actual size. Even the "Initial size" property in SSMS just shows actual size, not the initial size.
Well how come that DBCC SHRINKFILE
or DBCC SHRINKDATABASE
"know" the initial size then? Even after you have changed the size. Interesting question.
The first page of a dattabase file is the file header page. In there you have, amongst others, 2 properties: size and minsize.
At creation of the file, both file header properties get filled with the inital value:
DBCC TRACEON(3604)
--parameters for DBCC PAGE: (Dbname, fileID, pageID, outputTypeID)
DBCC PAGE('Test_100',1,0,3)with tableresults
Both sizes are in the amount of data pages. In this case. 288 data pages.
Now if I alter the file size:
ALTER DATABASE [test_100] MODIFY FILE ( NAME = N'test', SIZE = 50MB )
You can see that the "size" property is changed to reflect the new size. However, the "MinSize" property still contains the "Initial" size. It's the minimal size to which the shrink command will go.
However, having said all this. I still don't understand why you want to complicate things by first altering the initial size and then shrink to that initial size. Instead of just shrinking directly to a targetsize.
Anyway, to answer your question. The "initial" size is not exposed as a property to the user/dba.
The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be:
ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([logfilename], 1)
ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT
Changing the database to simple recovery may cause transactions to stop being replicated, but if scheduled during off hours it should shrink the log and return to full recovery with no side effects.
If that is unacceptable, you can use the sp_repldone stored procedure to manually mark transactions as distributed and reset the replication status of invalid transactions:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
Best Answer
You have a couple actual questions here, so let's address each one individually.
To answer the question of why the log_reuse_wait won't update, this field (along with other metadata within SQL Server) will only update after certain events occur. With this field, it will update after a database checkpoint occurs. To force this after taking a log backup, do the following:
The checkpoint will occur and the field in
sys.databases
will update.For why your log file won't shrink, this is a MUCH larger question. You should read this question/answer by Mike Walsh on why your log file is growing and how best to manage it. I also provide some more detail in this answer.
Regarding VLFs, I would strongly recommend you not worry about that right now. Yes, you can see a performance impact over having to many VLFs (and understanding what a VLF is and how it works is fundamental for understanding the log file), but managing that should only be addressed when you're regularly managing your log with backups and it's not growing out of control.
Finally, as for the size of your log, this is a very open ended question. It very much depends on how much activity your database sees along with the scope of that activity. Chances are that you will shrink your log file, only to see it grow again to accommodate your regular database activity. Before considering shrinking your log file, you need to understand what's making it grow and whether or not you're running regular scheduled log backups against it.