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?
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?
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
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.
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 RelatedHere 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.
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.
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
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???
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 RelatedHi,
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
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.
How can I find the size of a file/ database in kb/megs?
View 1 Replies View RelatedIs there a way to get a particular file/tables size??
View 1 Replies View RelatedHi
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
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.
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?
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 RelatedHi 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!
Hi my database .ldf (log file) size is very big. How can I decrease the size of it?
Thanks.
Yes my DB file is 10MB but my LOG file (LDF) is 300MBis there a way to reduce or compres that one?thanx
View 2 Replies View RelatedGreetings,
I want to increase the size of a transaction log file. Can I do this while the database is live? If not, what do I need to do?
Thanks!
Rob
How the size of transaction file is calculated. are there any standards.
I created my database with 2000 mb data filesize and 200 log file size.
now my datafile size has reach to 11000 mb but log file size is still 200.
Therefore when I take the online backup my logsize totally consumed and I have to cancel my backup.
Please reply ASAP.
regards
Sanjeev Lamba
My Database Log file Size Increased dramatically upto 5GB. My Data file size
is only around 600MB. Almost my Harddisk space occupied fully by log file. How I reduce the log file size. Anyone can give me some tips?.
I created a database and had its file size as automatic grow. Now the database file is of 17 MB and its transaction log file size is 230 MB. After checking transaction log file properties I came to that it is using 13 mb only and the rest of the 230 MB i.e 217 MB is free. I want that area in the transaction log to be freed and get the transaction file size to its actual size. Any help will be greatly appreciated.
Thank you in advance.
P.S: It is very urgent.
I am using SQL 7, SP1 / NT 4. The .LDF file has grown to 1.1GIG; I ran a DBCC SQLPerf(LogSpace), the used portion of the log is 2%. When I run a DBCC Shrinkdatabase and DBCC Shrinkfile, the log file does not reduce in size. How do I get the virtual log files that are not active released back to the system? Is there a way to tell if all the virtual log files are active, therefore, not reducing the size of the file? Any help is greatly appreciated.......
View 3 Replies View RelatedHi everybody,
I have a database in production server with 3,5 GB of data file size and 10 GB log file size. This is very strange isn't it?
The features of this database are:
SQL Server 2000
Recovery Model = Full
Auto Update Statistics = Yes
Torn page detection = Yes
Auto create statistics = Yes
Full database backup taken once daily.
No log backup is taken.
So, I would like to apply some statregy to avoid the log file increase out of control. Can you give me your suggestions???
My free disk space is very low.
Thank you all,
(Windows 2000 professional running SQL Server 2000)
How do you reduce the file size of the transaction log? Is it safe to delete the transaction log? I tried clicking on the ellipse (...) box in database properties, but even tho i'm the dba, it says i don't have permissions to do that! I see the "automatically grow transaction log" and "maximum file size for transaction log" dialogs, but our transaction log is already 5.5 GB, and we need for it to be WAY smaller! Is there a way to back it up, then start over with a new one or something?
Hi all,
How can I see what is exact size of SQL server backup file while backup is running. The process is running for more than 2 hours, it is chewing up disk space (it already took about 20G), but the size of backup file is still showing as 0.
Thanks in advance
SELECT size_in_mb,used_size_in_mb,size_in_mb-used_size_in_mb as free_in_mb FROM (
SELECT cntr_value/1024 size_in_mb ,
(SELECT cntr_value/1024 FROM master..sysperfinfo WHERE counter_name='Log File(s) Used Size (KB)' AND instance_name='mydb') used_size_in_mb
FROM master..sysperfinfO WHERE counter_name='Log File(s) Size (KB)' AND INSTANCE_NAME='mydb'
) a
I need to store totalsize,usedsize,freesize of the datafiles in a table to get an average of how much my datafile has increased over a week.
The above query i am using is for logfile size. Can any one help me with datafile size plz.
I've checked sp_helpfile, sysfiles but couldn't find what i am lookin for(used and free space). EM in taskpad view for a database shows the statistics for the datafile. I've tried a trace to find out a stored procedure but couldn't!!!
May be i am unaware of a simple stored-procedure that can do this for me.
Howdy!
Hello, relatively new to SQL Server.
I have a database with 1 .mdf data file and 1 .ldf t-log file. There are multiple inserts/deletes/transactions performed on the data daily, but the size of the two files remains constant (5,774,,336 and 153,480 respectively)??? I perform daily full backups and hourly T-log backups (during business hours of 9-6) and these backup files change size, but why aren't my physical DB files changing? I have them set to auto-grow at 10% unrestricted...
Thanks for your help/advice
Arthur
I have the following T-SQL which simply creates new database. However, in properties of this DB I see that LOG's max file size is restricted to 2 GB. Where's error?
CREATE DATABASE Bonus
ON PRIMARY
(
NAME = N'Bonus',
FILENAME = N'E:Bonus.mdf',
SIZE = 3072KB ,
[Code] ....
Dear all,
I wanna ask you how to decrease log file size..?
For example i have database file with data file size is 122,166 Kb
and the database log file is 6,330,176 KB
Thanks,
Oko Sakti Banget