SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
Via t-sql, how can I query for a file's initial size?
I want the same thing one finds by : Start SQL Server Management Studio, view, object explorer, right click [dbname], properties, files, Database files: Initial Size (MB).
Many thanks.
Anything will give up its secrets if you love it enough. -- George Washington Carver
I have a database I need to copy from a Prod server to a Dev server. There is not enough space on the Dev server. In looking at the size of the files on the Prod server, the Initial Size property for the transaction log on the Prod server is set to 100,000 MB though the log is using nowhere near that.
This is a mirrored database so the recovery model is "full". I know that to change the initial log size, I have to put the database in 'simple" recovery model. Is this possible? Can I just:
1. Pause the mirror 2. Switch recovery model to simple 3. Change the initial size property to something smaller. 4. Shrink the transaction log 5. Change the recovery model back to full and resume the mirror?
I honestly don't know if the transaction log is needed on the Dev server. Meaning I may just be able to restore the transaction log to a different location on the server and delete it so that new one is created.
We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file V->Tempdblog having templog.ldf file
I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.
I have a log file that is approximately 50 GIG. I backed up just the log and the file size of the .bak is 192 GIG . Why is this? Shouldn't it be closer to the 50 GIG.
Normally I wouldn't let log grow this much. But we are in process of getting new server up and running and don't have backups going yet. They are working on getting that up and running this week.
So I did a log backup to give me back some log space for now but was concerned when I saw the size of the .bak file.
When I view media contents of the backup device it shows one tranaction log back up and size of 192 GIG.
What is up with this. I know in SQL 2000 the log backup files where never this big. they were about the size of the log itself.
We had a runaway query which built the size of tempdb to 24000mb. Then someone changed the unrestricted file growth property to restricted growth while the size was 24000mb. Now I can not reduce the initial size. I have set the property back to unrestricted file growth. I have shrunk the tempdb and available space is almost 24000mb. I have stopped sqlserver. I even deleted the existing tempdb.mdf & tempdb.ldf files. But when SQL server is restarted, the initial size is set to 24000mb. It will not let me reduce the size. Is there anything short of manipulating the system tables to reduce the size back to 500mb?
I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
Is there a way to decrease the initial size of a database/log file? I've noticed you can increase it, but if you decrease it, after you confirm the change and go checking again, you will see nothing happened.
Currently my db size is only 6 GB but the transaction log file initial size was set to 20 GB and has grown much way beyond the db size with the autogrowth feature turn on. The database was originally a test/development DB and was migrated to a production server including the log file. This probably caused the accumulation of transactions on the log.
We run backup everyday and tried to shrinkfile and file size did not change.
Can I change the "initial size" setting of the transaction log without causing any problems? Do I need to stop the service before I made the change assuming I made the change after the backup run? Or can I change it on the fly?
I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
Hi folks,Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString)); dataSource .CommandType = CommandType.StoredProcedure; dataSource .Parameters.AddWithValue("id", poid); dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output; etc, etc... And the SPROC:------------------------------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[retrieveData] @id int, @title varchar(50) OUTPUT, @creator varchar(50) OUTPUT, @assignee varchar(50) OUTPUT, @contact varchar(50) OUTPUT, @deliveryCost numeric(18,2) OUTPUT, @totalCost numeric(18,2) OUTPUT, @status tinyint OUTPUT, @project smallint OUTPUT, @supplier smallint OUTPUT, @creationDateTime datetime OUTPUT, @amendedDateTime datetime OUTPUT, @locked bit OUTPUT AS /**SET NOCOUNT ON; **/ SELECT [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee], [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost], [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked] FROM purchase_orders WHERE [id] = @id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:
Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 63: retrievePODetails.Connection.Open();Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.] System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally
Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR. I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0. par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar); par.Direction = ParameterDirection.Output; cmd.Parameters.Add(par); cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas
I've worked with informix for a very long time and this is my first aproach to sql server. I have an extremely simple design for a "small" database and at this moment I'm creating the tables, in informix I can assign a first extent and next extent size to the creation of the table so if your volume and growth analisys is good you can basically be sure that you will allways have contigous space on disk for your table. I'm readin BOL to see if I have that feature here but can't seem to find anything similar. Does that mean that my table data will be "fragmented" all over the primary and secondary files every time I load into them? Would it be a good practice to simulate the extents by creating a secondary file for each table with the size I require?
We currently have a fairly new SQL server 2000 db (currently about 18mb is size) as a backend to an application (Navision). Performance seems to be below what it should be.
The db is increasing quite rapidly in size, with a lot of data scheduled to be loaded onto the db and also more and more shops and users coming onto the system with alot more transactions going onto the db.
The initial setup of the db has the database File properties set to "Automatically grow file" by "30%" and has an unrestricted file growth.
The server that the db sits on is high spec and very large disk space.
Because the database will be expanding alot and thus reaching its maximum space allocation and then performing a 30% increase in size (which I guess affects performance quite a bit??) quite regularly.
Is it best to set the intitial size of the db to a alot bigger size in the first place as we have large disk space availiable and also set the % increase bigger also.
any advice on best performance would be much appreicated.
I am getting error to run stored procedure using executenonquery method. The Stored Procedure is having OUTPUT parameter. ExecuteNonQuery statement is called using SqlHelper. Error : String[18]: the Size property has an invalid size of 0
Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com
From BOL, I see these remarks with respect to the MODIFY FILE subcommand (my underline added):
Initializing Files By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:
Create a database
Add files to an existing database
Increase the size of an existing file
Restore a database or filegroup
Which leads me to believe that expanding the size of a datafile will also wipe out (my definition of 'initialize') any existing data within that file.
I may be misunderstanding 'initialize', because when I tested it out, I found this wasn't the case - my table data written to the file was still there after a resize.
Need to clarify to what degree I'd be taking a risk by increasing the file size on a datafile which already has data in it.
hi all, I am using SQL server 2005.The Log file size increased to 40GB,so I detach the DB and delete the log & created new log file.Can I restrict the growth.I mean if the file size become 1000MB,I need to clear the log file.Previously it was Enabled the autogrowth 10% & unrestricted File size,Actually I modified to restricted file size 1000MB.is it work? can I know restricted file size 1000MB what will happen?I mean , is it clear the log after reaching 1000MB?please advice.
Hi, i use this script that show me the size of each table and do the sum of all the table size.
SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
I want to store a Zip file as a BLOB, but I get an error: "File 'C:<path of mdf file> ' appears to have been truncated by the operating system. Expected size is 2560KB but actual size is 1536KB " whenever the BLOB exceeds 1MB.
Any suggestions? How can I store larger .ZIP files to the Database? I am using MS SQL 2005 Express and the data type that I gave for the column to store Blob is "varbinary(MAX)".
I am inserting byte array in to this field. It works fine for a zip file less than 1MB but as soon as the zipped file size increases beyond 1MB, lots of error pop-up and then the database is not readable. Its says that, the data in the data base may have been corrupted...
