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...
i have a very big database and number of people are working on it.. it's log file size is increaseing very day too much.. i am taking log back every 30mint...
i dont' know that wethare i need to truncate the log file after taking the log file backup or not.. i am taking differentail backup every day and full backup every week...
Please tell me do i need to trucate the log file to reduce the file size or i hv to leave as it....
I am using SQL Server 2000 with replication object for two location. Log size on publisher go upto 25 times of data file size, I mean 80 MB Data files has maintains 2 GB log file and it is same for all five co's working on same windows 2000 advanced server board.
Since last week server randamly get disconnected from user applications and at that time few tables are not openable at server.
Can any one give a reason ? Why this type misbehaviou done by SQL Server 2000?
Can I know somedetail about why the Sqlservr.exe app increasing in size drastically. Even I check all parameter of the server and I check the process running on server.
I feel server is not releasing the queues and It is occupying the memory. I any one suggest what could be the cause ?
I am having trouble increasing the size of the log device on a SQL 6.5 database. When I use SQL Enterprise Manager I get an error saying that the device has 0 MBs available. When I use the ALTER DB statement I get an error saying that there is not enough space on the disk, but I know this not to be the case. Has anyone any suggestions? Thanks in advance, Michael lawlor
Is there a way to increase the size of the procedure cache. Or is it only a auto configuring option. I have 2gb of memory, and when I check the size of the procedure cache it is just 10mb. I would like to increase this to around 50mb. Not sure if there is an setting to do this. Had a look on BOL could not find anything.
Just wondering if you could help me on this one. I'm not sure if mytransaction logs are behaving oddly or what. I've successfuly managedto shrink my transaction logs from 7GB down to 1MB and now I find itstrange that the log file doesn't seem to increase its size. Thetimestamp of the logfile is updating as well. But the size of the fileis constant. I haven't configured my database to do auto-shrink so Imreally confused why it hasn't changed its size for more than a monthnow.Hope I'm not losing any data here.Kindly advise.
I have a database which is centered around two date tables (approx. 5.5 million records each). We are finally making the big leap from SQL Server 2005 to 2012. The data is currently stored as datetime, and we are hoping to take advantage of the new date datatype, since the time component is not needed.
The first table has 13 different date columns. In testing on the 2012 server I have changed 3 columns so far, and have seen that changing the datatype to date is actually increasing the Index size and not affecting the data size. Only 3 of the columns are associated with indexes, and modifying a non-indexed column still increased the index size. I am running the Disk Usage by Table report to view the sizes.
data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa; Max pool size = 200;
And now strange thing is happening ,, I am receiving error :
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
The SqlServer Activity Manager is telling that only 100 connections are pooled, and I guess that the Max pool size is 100, It is not being changed by my Connection string. As I am trying to change the default 100 pool size value to 200.
Huh , So stucked up , how to increase the Max pool size.. Is there any 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.
Hi, i'm using SQL Server Express (9.0.2047). My database's *.ldf file's size increasing everyday... How can i decrease it? How can i make ldf file to small ?
I have faced a network problem during some days, what forced one of our replications to be stopped. The Publisher database is a high volume database. After I re-started the replication, the Subscirber database has its transacting log size increased quickly, because of the high volume of information to be inserted.
My concern is the way it is working, there will be no enough space for the log or for its backup files.
So, I have created a TSQL job within the following commands:
BACKUP LOG database_name WITH TRUNCATE_ONLY DBCC SHRINKDATABASE (database_name,TRUNCATEONLY)
It's running every 20 minutes, however the transaction log remains increasing.
I have also changed the db_option "SELECT INTO/BULKCOPY" to TRUE, in order to avoid logging bulk copies, but I believe, it didn't work because it didn't apply to replication process.
Does Anybody know if I can disable the transaction log or avoid this incresing of size during the replication?
Thanks a lot! Regards, Felicia Schimidt felicia.schimidt@br.flextronics.com
SELECT session_id, SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage where internal_objects_alloc_page_count >10 and session_id> 50 GROUP BY session_id;
[Code] ....
Database MDF is 27806 MB and I tried to shrink but unable to shrink. It is production server.I do not want Restart sql server.There is no open transaction.
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.
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 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 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???
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
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?
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 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.
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 :-)
