Reducing Log File Size

Feb 12, 2008

I have a database who is in full recovery mode. I have four maintenence plans setup: database backup, log backup, optimization and integrity checkup. The last two plans run weekly and the first two run daily. I found that the log size often increase to a dramatically size in a very short period, almost same size as the database file (4G). Further I found that the size seems increase a lot after the last two plan runs.

My question is that the optimization operation(reconstruct index page) will write any reocord to log file? Is this possible a reason?

Now the log file occupy too much disk space (90% of space can be free). What I should do? Shrink database weekly?

Thanks

View 1 Replies

Reducing Log File Size....!

Dec 7, 2007

I have a Log file grown to 28 G.

TASK: I want to claim the hard disk space.

I want to use

--backup Log DB-NAME with truncate_only
--dbcc shrinkfile(DB-NAME_Log,1)

Is there any risk involved in above steps, and OR would any experienced Folk like to share his or her idea to the Task as above.

Many Thanks,

View 4 Replies View Related

Reducing The Size Of The Transaction Log File

Oct 3, 2006

At this time I am only playing with the applications that generate the data and send it to the database. Without doing too much, and with deleting most data tables that were created, my transaction log file has grown over a gigabyte. I tried using the SQL server management studio (express) to shrink the database (tried shrinking files, too) but that did not make the file smaller. Right now there is hardly any data in the database (6 tables, a dozen columns and rows each) so it must be old transactions that are kept in the log. How do I get rid of the old data and make the file size smaller? Thanks.
Kamen

View 3 Replies View Related

Reducing Size Of The Database

Apr 29, 2002

hi
i have a database that becomes to big after a few days. is there a chance to say if the table reaches a number of lines or a special memorysize, delete (or better archive) the oldest entries?

thanks

View 1 Replies View Related

Reducing Size Of Transaction Log

May 25, 2001

If I have a transaction log in a database of size 1GB ( space allocated is during creation of database) currently only 300 mb of its space is used i.e. nearly 700 mb is free. If I want to reduce physical file size of transaction log by 200 mb and release it for operating system then How can I do it???

View 4 Replies View Related

Reducing Transaction Log Size

Jul 17, 2003

i am new to sql server. i recently found the transaction log size of my database has reached 109 MB. how can i reduce it. a transaction log backup was sceduled daily at 12.00 noon nad full backup monthly.

View 3 Replies View Related

Reducing Database Size

Dec 14, 2001

I have a production database of a size of 70 GB. Half of the data was archived and deleted from the current database. What is the best way to
reduce the size of the database, as we cannot shrink an entire database to be smaller than its original size? Thanks a lot!

View 1 Replies View Related

Archiving Is Not Reducing DB Size

Jan 10, 2005

I created few jobs that would archive the production DB and delete the archived data...
but it looks like the DB size is not reducing!!! Some times it looks like the size has increased!!

I think this is because of the log file size has increaded by the DELETE operations....But what can I do for this???

Please Help!!

View 1 Replies View Related

Reducing Transaction Log Size - SQL2000

Jan 3, 2001

Can anyone help me reduce a transaction log. It is currently at 2.5GB because it was set to autogrow with no backup !?

I need to drastically reduce it and have backed it up and tried dbcc shrinkfile...but...it now says space used is 120MB but current size is still 2.5GB.

How can i reduce this down please ??

thanks

View 3 Replies View Related

Reducing Database Size (contains Images)

Jun 8, 2004

Hello. I am wondering how to effectively reduce the size of my database. After viewing the individual table sizes, I have come to realize that nearly 99% of the database's size is due to images. I am told that too much binary data is not good. How can I go about reducing the size of my database (possibly the images themselves)? I'd appreciate any help.

View 11 Replies View Related

Reducing Database Size After Dropping Text Column

Jul 20, 2005

Hello,A while back I dropped a text column from a SQL Server 7 databaseroughly 3GB in size. I expected the size of the database to decreaseby around 1GB, but no change occurred. After searching usenet, Idiscovered that SQL Server 7 has no way of reclaiming that space, butthat there is some command that can be run in SQL Server 2000 thatwill reclaim it.I have since migrated this database to SQL Server 2000, and am nowtrying to figure out what that command is, but cannot locate anyusenet posts about it... also tried searching books online, but can'tfind anything that way either.Does anyone know what I should run?Thanks,Tom

View 6 Replies View Related

Reporting Services :: SSRS Report Body Size Is Not Reducing

Nov 10, 2011

In one of my SSRS report under body properties default size (width = 34.54712cm , height = 20.92853cm ). I've tried to reduce the size to width = 26cm ,height = 18 . once i changed. the width its going to the same orginal size again.

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

Log File Not Reducing

Jan 2, 2001

i used "dbcc shrink file" to reduce the log file of a database.the query analyzer says "successfully executed" but the log file doesn't seem to reduce..am i missing something?

View 2 Replies View Related

Log Backup File Size Not Matching Log File Size

Apr 15, 2008

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.

Any ideas?

Stacy

View 8 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

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

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

SQL Server 2012 :: Write A Process To Get File Size In Kb And Record Count In A File?

Jul 31, 2014

I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?

I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.

View 1 Replies View Related

DB Engine :: Recommended Size And File Growth For A Database And Log File?

Sep 22, 2015

What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:

CREATE DATABASE Dummy
ON
PRIMARY
( NAME = Dummy_data,
FILENAME = 'D:....DATADummy.mdf',
SIZE = 250MB,
FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
FILENAME = 'D:....DATADummy_log.ldf',
SIZE = 50MB,
FILEGROWTH = 5MB ) ;
GO

View 3 Replies View Related

Log File Size (IDF) Is 4 Time Greater Than Data File (MDF)

Feb 19, 2013

I have a database whose log file size is 4 time greater then data file size, and its continuously growing day by day. Recently face limited disk related issue.

Is there any way to truncate log file???

What is impact on db if i truncate log file???

Is there any way to prevent this file continuously growing???

View 13 Replies View Related

SQL 2012 :: Max Size Of File Which Store To DB By File Stream

Dec 5, 2014

is there limitation for size of file to store in db by filestream in sql server 2008?or it accept all sizes?

View 1 Replies View Related

Check Db Size Data File And Log File

Apr 27, 2008

Hi,

i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns
dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB.
i try to write this a got stuck.
select sysdb.database_id,sysdb.name,sysdb.compatibility_level,
sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster
where sysdb.database_id = sysmaster.database_id

can anyone help me with this script?
THX

View 13 Replies View Related

Restoring BAK File Increases Log File Size

Sep 5, 2007

We have 2 SQL Server 2k5 servers running the same build - 9.0.2047 . When I backup any database from one server and attempt to restore it to the other, the log file generally increases by 100 fold. It errors out after I try to restore a 100MB db and it tries to create a 9.8GB log file. This happens both when I use the GUI to restore and when I restore from a T-SQL script. What am I doing wrong?

Thanks in advance.

View 1 Replies View Related

File Size

Apr 3, 2001

How can I find the size of a file/ database in kb/megs?

View 1 Replies View Related

File Size

Apr 4, 2001

Is there a way to get a particular file/tables size??

View 1 Replies View Related

Size Of Log File In SQL 7

Dec 13, 2000

Hi Everybody

First of all i would like to thank everyone for there time and efforts in this web page
I am new to the feild of DBA and i have some uncleared points that i would like any one to make them clear for me
Why the transactions log file size is not decreasing after the truncation of log?
is there any thing i have to do or is it normal way?

View 1 Replies View Related

Getting A File Size

Dec 6, 2004

Hi

I am currently trying to get file sizes and insert them into a table. The table already has the path to the actual file, so its just a matter of using that path and getting the size.

Any help is appreciated

View 6 Replies View Related

Size Of Mdf File

May 22, 2007

Hi,

I've an application which saved logs of internet access.. the file size can reach to >150 GB per month.

I don't know till what size I should keep the mdf file before creating a secondary.

what is best size for mdf should be.

Thanks in advanced.

View 2 Replies View Related

Bcp File Size

Jan 30, 2004

I use bcp to copy a file and the bak it generated is 966637 KB. However, when I run sp_spaceused, it shows me the following size :

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
550752 KB 395272 KB 155464 KB 16 KB

The difference doesn't make sense to me. Can anyone help pls?

View 5 Replies View Related

Size Of LDF File

Feb 5, 2004

I have one database in which MDF file size is 120 MB while LDF file has expanded to 10GB. How can i reduce the size of LDF file.

View 4 Replies View Related

XML Log File Size

Aug 13, 2007

Hi everyone,

I'm asked to continue an existing project in SSIS, which currently uses XML for logging. I noticed that we now have log files up to 200MB in size. Is there a way to limit those log files in size?
Please do keep in mind that I'm still learning SSIS, so keep your answer as simple as possible :-)

Many thanks for any help!

View 5 Replies View Related

How Can I Reduce Size Of ..log.ldf File?

Jun 7, 2008

Hi my database .ldf (log file) size is very big. How can I decrease the size of it?
Thanks.

View 2 Replies View Related