Shrink TL Below Set MIN Size

Jan 8, 2008

I need some advice for an issue with a production transaction log min size having been set to 50 GB by some rouge developer while I was on vacation. Now, as the only resident DBA, I'm responsible for going in and reducing it down so we can free up limited disk space. It’s in SQL 2000 and participates in replication.

Because the log file cannot be set below its current minimum size, I’m a little stumped. Can I somehow force the minimum size by updating the table database.dbo.sysfiles?

Otherwise, what do you-all think of this plan?

1.)Back up the database.
2.)Verify no open transactions with DBCC opentran(‘database’) and proceed accordingly.
3.)Change it to single user mode.
4.)Set it to Simply Recovery mode.
5.)Issue a checkpoint to clear the log.
6.)Script out push replication.
7.)Delete replication.
8.)Detach it.
9.)Delete the Transaction Log.
10.) Attach it and let SQL Server recreate a transaction log to the SLQ Server default size.
11.) Run the Replication Script.
12.) Start the Snapshot Agent.
13.) Restart the Transaction Log Reader Agent.
14.) Celebrate a Victory!

Thanks in advance for your help!


Jeffrey.Hoover@gmail.com
Minneapolis, MN

View 3 Replies


ADVERTISEMENT

Shrink Database Log Size ......

Jun 28, 2004

I want to shrink database:

1. backup log ABC with truncate_only
2. dbcc shrinkdatabase(ABC, 10)

Cannot shrink log file 2 (ABC_Log) because requested size (113792KB) is larger than the start of the last logical log file.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 14432 12800 14432 12800

there is no transaction in this ABC database. Is there way to shrink it?
Not quite sure about "because requested size (113792KB) is larger than the start of the last logical log file.". The current log size is 113 mb.
thanks
-D

View 2 Replies View Related

Shrink Ldf Will Reduce Mdf Size Too?

Jun 17, 2008

Hi all;

i m using SQL 2000, i have a database with 86G mdf and 56G ldf size. i shrink the ldf and it reduced to 32M, however, i did not do anything on my mdf file, but the size of mdf has been reduced to 28G. just would like to check, is this correct?why is mdf size reduced when i only shrink my ldf? hope can help. thanks

View 2 Replies View Related

Shrink Tempdb Below Original Size.

Feb 11, 2002

I have a tempdb that was created at 1Gig. I don't know why but I want to shrink it below the original creation size. Is there a way to shrink this file or create a new file and delete the old?

I have tried shinkfile and shrink database with no luck.

Thanks Jeff

View 6 Replies View Related

DB Engine :: 2008 R2 - Shrink Data File Size?

Nov 9, 2015

I have a sharepoint content database in sql 2008 R2 (WSS_Content) that is at 230Gb size, but has 40% of it is empty space.  This is because we have removed a large amount of old content from sharepoint.  The log file is fine.  I have 60GB left in my drive that host the database files.  I would like to shrink the datafile to get disk space back.  I found that under the files property, the WSS_Content data file's initial size is 228702 MB (220 Gb or so).  

When i try to do a shrink file (data file) from management studio, i see the 60 GB of drive space keep dropping.  So i have to kill the process. what i should do to reduce this data file.

why it keep using up all the free space in the drive when i try to shrink the data file?

View 10 Replies View Related

Transport Level Error Returned When Trying To Shrink A Data File Using DBCCSHRINKFILE ('DB', Size)

Nov 30, 2007

Greetings:

I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?

Regards,
Derek

View 1 Replies View Related

Reporting Services :: Determine Objects Inhibiting Horizontal Shrink Of Tablix And / Or SSRS Report Size?

Jun 10, 2015

I am working on complex invoice document that handles various types of invoicing, credit, and progress bill customer facing documents.  I am faced with same problem I see many SSRS devs posting on.  And that is extra blank pages, being generated because the report width exceeds the width of the page.

My problem is I cannot shrink the horizontal width of the tablix and therefore the report width either...

While I would have done things differently if developing from scratch, the tablix in question has only one column and objects contained in rectangles are placed in the row/column cell.....The link to the image below shows the row I am currently working on.  Essentially all the client wants a columnar report with column lines, closed footers, etc...  so rather redoing the whole tablix, which I still might do, I am using rectangles with the column then lining them up.  Anyway, that is a whole 'nuther post.  My specific questions are:  

Invoice in Report Builder

1. In the image I have selected the parent rectangle.  The children are other rectangles and the data is with grandchildren textboxes that exist within those rectangles.  I clearly have a bit of right side white space in the main parent rectangle, but there is no sizing tool on a hover... just move tool.  And if I change the rectangle size in the prop form it just snaps back to the cell width.  How do I size the parent rectangle and keep if from expanding.

Here I am guessing that to actually reduce the horizontal width of tablix and then the report, I have to first decrease the parent width of the rectangles that occur in each row?

View 2 Replies View Related

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.



Any help with this process?

View 1 Replies View Related

How To Shrink A Tranlog Which Won't Shrink

Jan 5, 2007

If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:

1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.

I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.

View 4 Replies View Related

Sql 2005 Install Error. MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Jun 15, 2006

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.


I'll try to post the full log in a new post.



View 11 Replies View Related

SPROC Problem - String[1]: The Size Property Has An Invalid Size Of 0.

Mar 16, 2007

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   

View 1 Replies View Related

Error - Output Param - SP - Size Property Has An Invalid Size Of 0

Nov 14, 2007

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 

View 6 Replies View Related

Reduce The File Size Less Then Original Size In SQL 2000

Jul 25, 2007

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

View 1 Replies View Related

Encrypted Data Size By Original Size, Algorithm ?

Jan 24, 2006

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

View 3 Replies View Related

String[18]: The Size Property Has An Invalid Size Of 0

Dec 11, 2007

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

View 1 Replies View Related

Best Practice For Data Size/Log Size Ratio

Jul 23, 2005

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

View 4 Replies View Related

Analysis :: Different Size In RAM Than Actual Size On Disk

Apr 20, 2015

The Tabular model is showing 19 GB on disk, but it is acquiring around 40 GB in memory.

View 3 Replies View Related

DB Size Not The Actual Physical Size

Feb 15, 2000

Hi,

An MSSQL DB running SAP indicates a smaller DB size (MMC & SAP) than the actual physical size. The difference is about 8 GB.

A lot of records were deleted before this. Did they remain in the DB as NULL values or something ?

Does anyone know what the reason for this could be ? And how to clean this up ?

Thanks in advance,
Paul

View 2 Replies View Related

Table Size And Database Size

Mar 2, 2008

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?

THX

View 5 Replies View Related

Shrink DB

Jun 6, 2002

I have a SQL 7.0 sp3 server and I am trying to shrink a production database for use on other servers. The database/log sizes are 9601mb / 138mb with 772mb / 128mb free. I truncate a table that contains binary data. After this the database/log sizes are 9601mb / 138mb with 6634mb / 111mb free. The next step is to use EM to issue a shrink db. After this the database/log sizes are 6807mb / 378mb with 9.56mb / 365mb free.

After the use of truncate the the data in the datafile is about 2967mb.
After the shrink db the data in the datafile is about 6797mb.

The size of the data grew after the shrink db was issued. I looked at the tables and the space is largely associated with two tables that have a binary field, but the number or rows are still the same. I ran a checkdb just to make sure that there was not a problem and no problems were reported. I have used this process many times and this is the first time I have come up with these results. Any suggestions?
Thanks,
Glen

View 2 Replies View Related

Shrink Db

Aug 17, 2001

if I shrink the db will it enable me to create another db with that space released by the first db?

View 1 Replies View Related

How Can I Shrink My Log?

Jul 19, 2000

How can i shrink my log file? It is like 900 megs and i
was it to be more like 400 megs.

Please Help

Melissa Sener
Project Manager
Square Yard

View 5 Replies View Related

Log Not Shrink

Sep 27, 2002

Hi, My name is Alexander and came´s to ask about Sql Server log.
Do you know why sometimes the log not truncate? All time
comes increasing!

I setup "Automatic Shrink" and it swims happens. If you can help me!
Thanks in advanced

View 3 Replies View Related

Shrink Db

Jun 30, 1999

Hi everybody:

Actually I have a 8.5 Gb database but the correct size that I need
to due disk space is 5 Gb. When I make a backup, the size of the dump is
4Gb, so I wonder if I create a 4- 4.5Gb my dump db will fit in it, but
not. Other way is create antoher db with 4 - 4.5 Gb and transfer via
sql-transfer or sql-bcp but I got a message that my device is too
short.

So I ask : What are the correct ways to reduce the size of a
database ?


Thanks in advance.

View 1 Replies View Related

Why Is SQL DB So Big? Can I Shrink?

Jun 16, 2004

Hello,

We just migrated our DB from one SQL Server to Another. Both DB's used to be the same size at 11GB. The new SQL Server is a whopping 33GB, and I can't seem to find out why. The new DM has fewer tables (the data is identical).

Does anyone know how I can compact or shrink my DB, know why or how it grew so much and didn't go back down to normal size, and maybe where I can search properties or change settings to find out what is going on and prevent it from happening again.

I know MS Access has a shrink utility, but don't know where it is in SQL Server.

Thanks, in advance for your help.

Rodney

View 12 Replies View Related

Shrink Db

Jul 26, 2004

Hello,
I have a 24go sqlserver 2000 database, but only 5go used, when i use the shrink command in em there is no change.

can i modify the property of the db (auto-shrink => on ) actually it's on OFF.

View 6 Replies View Related

To Shrink Or Not To Shrink ??

Nov 27, 2006

Newbie question about reclaiming some disk space on our SQL server.

We currently have a db that has the following stats:

space allocated: 34734 MB
space free: 843.66

of this the current size of .mdf is 33877 MB and the space used is 33873 MB

the drive that holds this database is running out of space, what is the best method for me to use to reclaim some disk space?

I have read books online and am a little bit confused as to whether or not using shrinkfile on the .mdf will free up space to the OS? Do I need to do anything else before or after such as reindex? Also what is the overhead as we only have about 10% free on the drive.

View 4 Replies View Related

Shrink An LDF

May 22, 2006

I am trying to shrink a log file (.LDF), but it will not work. I am typing:
BACKUP LOG "Database1" WITH TRUNCATE_ONLY
dbcc shrinkfile ("Database1_log", 50)
The microsoft site says this should work:
http://support.microsoft.com/kb/272318/
...but I can't get the file to shrink, at all? I'm sure the SQL services need to be running. I am logged in as my Administrator / sa. What could I be missing? Trying to shrink it too much? (THANKS).

View 3 Replies View Related

SQL 2012 :: Existing Replica MDF File Size Increased In Size Than New Replica Install

Apr 14, 2015

Here are my scenarios:

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.

View 0 Replies View Related

Why Is Database Still Big After Shrink?

Jul 26, 2000

A few of the databases I have are take up a lot of space, but when I look at the actual space that's being used, it's very small. I've tried shrinking them but they still remain at this bloated size. Is there any other way to reduce their size?

View 3 Replies View Related

Auto Shrink Db

Oct 24, 2001

what's this : "auto shrink " db option in properties ???

in which case may i use this option ?
please help

View 3 Replies View Related

10GB Log Won&#39;t Shrink

Sep 18, 2000

Txn log is nearly 10GB with only 60MB actually used. Disk has 150MB remaining. SQL 7.0 SP2.
I've truncated the log several times. I'm aware that the active portion of the log can be at the end and have added many records to cycle the active portion round to the start of the log file. How big are these log segments?

I've run dbcc shrinkfile with and without a target size.

I've checked DBCC OPENTRAN. No open txns exist.

What else can I try? I've looked through similar postings and haven't spotted anything.

Yours hopefully,

Simon

View 2 Replies View Related

Shrink File

Mar 27, 2000

I am looking for the correct procedure for shrinking a database file ,
please reply back if anybody knows the solution
Thanks
Ajay

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved