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 have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
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 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?
recently i noticed that my trans Log size is not increasing(it is constant) , but the databse size is increasing at high speed. why is it so? earlier the reverse used to happen i.e databse size increased only after a certain period. and log size increased at const. rate.
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.
I am considering some disaster recovery scenarios.
Lets assume my MDF is gone - the disks are dead.
The LDF is on a different disk channel. Lets assume its fine.
Can I make a "final" TLog backup from the "good" LDF file?
Maybe copy some-earlier-MDF file into place, would that enable a TLog backup from the LDF file?
'Coz if I can then I could use that as a route to getting a zero-loss recovery - make a final TLog backup, and then restore the whole lot from last FULL + All TLog backups thereafter.
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've already shrunk the tlog from 350 GB to 313.My DB Server (2008 R2 Sp2) cannot be restarted and the db cannot go offline or detach due to company policy.My DB after changing from full to simple mode still has 313GB tlog file and when I run DBCC OPENTRAN I get Transaction information for database 'DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (2882:26:1) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Which means this DB is participating in a High Availability process like replication, mirroring or log shipping.
So I run EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1.
This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.
But I get an error:
Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
There are currently 9 connections and all are sleeping.What else can i try in order to shrink the tlog file?
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 am getting periodic failures of my Transaction log. SQL Server 7.0 Service Pack 3 on NT 4.0. IT is not occurring in a set pattern. I have attached the error. I am unable to find anything on the Internet or n TechNet can someone help me out.
Thanks;
Message is as follows:
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: HYT00)] Error 0: [Microsoft][ODBC SQL Server Driver]Timeout expired
My Transaction log has 0% Log Space available. I have performed a dump transaction with no_log but to no effect. I have also extended the log size from 100MB to 200MB and this has not made any inmpression either after several dump database statements also. The Data Device was 500MB and I extended it to 800MB and again performed a backup. Has anyone any idea how to recover from this situation ? I can make queries on the data fine but the several backups(and Truncate log) I have made are still not creating any space for my Transaction log.
Is there anyway to find out using TSQL for the used space VS what is available for TLOG file? Cause the GUI won't allow me [Database] > Report > Disk Usage. It said that Unable to display the report becuase the database has a compatibility level of 80 ?!?!
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?
