I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:
BACKUP DATABASE [DSS] TO DISK =
N'G:MSSQLBackupDSSbackup.BAK'
WITH INIT , NOUNLOAD , NAME = N'DSS backup',
SKIP , STATS = 10, NOFORMAT
Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?
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
We have a large 'History' database that is currently about 4.5TB, with most of that in a datafile that is 4.2TB. We wanted to stop growth on the one large data file and have SQL Server allocate new data to the other data files, but this throws an error when we attempt to change the MAXSIZE settings:
ALTER failed for Database 'History' MODIFY FILE failed. Specified size is less than or equal to current size.
The SQL Server is saying we can have a max size of 2TB, and anything over that is blocked. Since this is being blocked, the file continues to grow.
Is there any way to cap the growth of the 4.2TB file and not allow any more data to be written to it?
We have a SQL 2000 database here. As of 3 days ago, it was about 75 megs ( 73 Data / 2 log ). As of today, it is 73 Meg Data / 15 GIG log. The log file went from 2 MEG to 15 GIG in a matter of days, yet the data file size has not changed. also, there are NO users to this database - it's new and has not been placed into production yet. I currently don't have any idea as to what happened - I'm looking into Lumigent's log explorer software now. Does anyone have an explanation as to why the log file would have increased 6000X in size, yet the data is not any larger ?
I appear to be doing something wrong. When I set my Table for Unrestricted File Growth and save it, When I go back and look at the settings it is set back to restricted file growth.
It seems to me if you set any settings and apply and/or save it. Should it not keep that setting.
i have a database which has a log file size of 10 Mb. it goes into single user mode automatically . i tried to increase the size of file size of log file from 10 mb to 50 mb... but i want to make it only 20 mb ... i am unable to change since it gives a message .cannot decrease the size of the file .. is there another way to decrease the size of log file .....
We currently have a 10GB database that is functioning properly mirrored. The only issue we have is that the log files grow very quickly during the early morning hours when a large number of transactions hit our DB from scheduled jobs. We have transaction log backup and shrink job that runs every 3 hours to backup the log and shrink the logical file to 10GB. In most cases this will shrink the log back down to it's desired size of 10GB. However, on some mornings it takes more repetitions of the log backup/shrink job to return the log size back to "normal". During this these times when it does not shrink the DB effectively, I get a report of the job process by email that states "Cannot shrink log file 2 (e_Log) because all logical log files are in use." I run a DBCC OPENTRAN command, and there are no open transactions. Eventually, the log file will return to it's normal size of 10GB through the log file backup job. I have a few questions though.
1. Is this normal behavior during moderate database use? 2. Does mirroring have any effect on the SIZE of the log file? 3. Is it normal for the size of the data file to be smaller than the size of the log file on a heavily used database? 4. Does anyone have any suggestions to better maintain the log file size?
I am getting a bit concerned with the size of my log file and my understanding of backups and how the log file should be getting reduced in size. I have a production database that is 12 GB and the log file is 275 GB. The database file is set to autogrow at 1 MB and unrestricted file growth. The log file is set to 10% file growth and restricted to 2,097,152 MB file growth. I perform a full database backup each night. I had thought that all transactions in the log file would be rolled into the database file and then the log file auto-truncated in size during the backup process. I have never seen a log file stay larger than the database file. Please advise how I may keep the log file size (growth) down. Thanks!
SQL Server 2005 We need to set up a database with minimal log file growth or none at all. This database is used as an intermediate step in the data extraction process, i.e. ( daily inserts and truncations) - data recovery is not an issue, but the size of the log file is. How should I set this database options? Can I set log file growth to 0 ( none)? Will it affect inserts?
We are running SQL Server 7.0 SP2, and are experiencing the following out-of- space error message:
"Could not allocate new page for database 'FooBar'. There are no more pages available in filegroup SECONDARY. Space can be created by dropping objects, adding additional files, or allowing file growth."
Needless to say, but the the database is set for 10% unlimited autogrowth and there IS available space in the partition where the filegroup resides.
Any ideas as to why this is happening? What is SQL Server's algorithm for allocating space when growing a database? Must it satisfy the request in one 'extent' and the cause of our problem is that our disk is fragmented?
I've run it for ih and ih_restore and can see that the "reserved" and "data" fields are growing but no extra rows - so no inserts are happening in the database?? What or why will this be happening.
Example of csv file of a table that I've exported -
From ih - name,rows,reserved,data,index_size,unused em_comm_costing,384191,1011704 KB,512424 KB,498648 KB,632 KB From ih_restore name,rows,reserved,data,index_size,unused em_comm_costing,384191,119808 KB,62960 KB,56088 KB,760 KB
So the em_comm_costing rows are 384191 in both but the data field has increased to 512424 from 62960.
The database is being mirrored as well, but not sure if that would be effecting the size?
Once our company encoutered database disk full issue, so we cannot insert any more data. So is there way I can monitor the data file size? or is there a monitor tool to sending out alert?
for example, It can show me how many percentage of data file has been used e.g. 90%. so I know it's critical and I need to increase more space for database or disk drive.
I have 50GB datafile (.mdf) and have 650 mb lift on the hard drive. I have another drive (on the same box) which has about 30 GB left. My question is can i create a .ndf file in that 30GB drive and continue the database growth on the new .ndf file with out any furthur growth on the .mdf file? please help!
Thanks in advance!
THE LADDERS (The Most $100k+ Jobs.) www.TheLadders.com
We have a problem with the size of the tempdb.mdf file. The tempdb had grown to 25Gb and consumed all the available disk space. SQL server was restarted and the tempdb was reset back to the default size. The following day the tempdb suddenly increased in size from 200mb to 25GB within a very short space of time. There were a couple of event log entries from sqlservger regarding the lack of disk. Since then the server is running without any problems but the level of free space is virtually zero on the drive with tempdb.mdf file.
What would cause the tempdb to grow suddenly and to this size?
Suspected Problem: Distribution Database Transaction Log Not Checkpointing
I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.
I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.
SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.
Does anyone know where I can find the "retricted file growth" value (for the data file) from the system tables? We setup a limit for the file growth for all the databases. We would like to write a script to alert us when the database size approachs the limit.
I am a bit puzzled. Our database backup grew from the usual size of ~27GB to ~40GB, all of a sudden. Nothing special happened in the last few days - nothing major to cause such increase.
I found out about this, because we suddenly had the backups failing, and when I explored, I saw that this was due to the lack of space on the hard-disk.
I do know that we need additional hard disk space. In the meantime, however, I'd like to be able to identify what exactly could cause such growth.
As far as I understand, for the backup to grow, the database needs to grow in a similar proportion. My only theory is that when the backup failed a few times, each time, somehow, it resulted in the database growth. Does this make any sense?
Another clue is that the backup job, which usually runs ~ 30 minutes has been running for 6 hours already, the file has grown to 40GB, and the backup job is still running ...
What is the best way for me to explore what exactly happened? Are there some system tables containing history of table counts or something - so that I can see who grew when and by how much?
I ran a query to see which objects were added in the past few days - that did not give me any clues - all looks normal.
I have a SQL Server 2005 SP2 instance and I detach/attach a database to the system and changed the file growth parameter from 10% to 100MB. Now when I look at the file growth parameter, it is showing 12800% instead of 100MB. I did some research and I found out this is an issue with SQL Server since MSSQL 2000. Is there a way to fix this issue using ALTER DATABASE or some other SQL command?
A log file size of a production database has been increase from 4gb to 150 gb initial size.Now i want to find when it will grow & how much it grow & which transaction is responsible for this.
I currently have a DB that is growing at a rate of 10gb per month. It is set to 1mb unrestricted growth and the log file is set to 400mb restricted growth. I take regular transaction log backups so the log file is well under under without any issue. This DB's recovery model is set to FULL as it has to be mirrored to a backup site. Any recommendations on how to control the growth. - Is it advisable to take create a new DB with data older than 2 years and transfer that file to an external drive and if i do this, can i "attach" it back to the main server if and when required ?
I am monitoring the data file growth of the databases in a table. Every week I review to see how much space is left on each database. I am thinking of writing a query where the current free space left is less than 20% of the file size, it sends out an alert to me, so that I can manually resize the file . Is it a good practice to resize the data file manually? If so I believe this need to be done at the time when the server is least busy since it can slow down the database. Also do I need to re-index the tables once the data file is resized?
I am working on a application in which,at once i have to make a huge amount of data to insert and delete in my data base.At this time of some transaction my data base log file,shows data base error like the log file of this data base has been full.plese see the sys.databsesd view...like..
Please help me.If you need the exception then i will send you later.
Hi at all, first post....first help :P . I hope to help another one (basing on my knowledge) in the next future :)
I'm a beginner dbadmin and I'm looking for help regarding a strange fact relative SQL Server 2005 :)
I migrated a medium(3,5GB) database from sql server 2000 to sql server 2005. I made a backup in sql server 2000 (it has generated a 3,5GB BAK FILE) and I restored it in sql server 2005 on another server. (I didn't checked data file size).
I configured (with wizard) a maintenance plan on new sql server 2005 with these steps (in this order): (tellme if the order of the steps is wrong :))
1) check db 2) rebuild index 3) reorganize indexes 4) update statistics (all table and all views)
I planned the maintenance plan on 3a.m. and I went home.
Next day I found that data file (MDF) on sql server 2005 was 22GB large!! I made a shrink DB but there wasn't free space to erase. It seems there are 22GB of data. (the data inside is the same of the sql 2000 server..same records same table...identical, non change of data in the meanwhile)
How is it possible? What am I doing wrong? I don't understand what can caused the growth of the file, the maintenance plan or the restore? (unfortunately....I didn't checked size after restore...I checked it only the next day). May be the statistics? (there wasn'int update statistics job on old maintenance plan on sql server 2000)
I was looking to change the file growth setting in our AlwaysOn environment databases.We have a single availability group, one primary and one secondary replica. I learned that when changing the file growth setting on the primary databases (data file), the change flows though to the database on the secondary replica.However after doing the same with the log files, the file growth setting changed on the primary but the change did NOT propagate to the secondary.
Is the solution to apply the change directly to the secondary?here's the T-SQL code I used:
ALTER DATABASE myDB MODIFY FILE ( NAME = N'myDB_log', FILEGROWTH = 512MB ); GO SQL Server 2012 (11.0.5532)
I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.
I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
3. Should a truncate log and shrink log file be performed after these maintenance commands?
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
I run two different types of backups on the same database.
A monthly full and nightly diffs appending to the same full file - file 'A'
A weekly full with 10 min trans log backups appending to same file - file 'B'- during working hours.
2 strategies, 2 backup files.
These are new strats that have gone live this week - tuesday in fact. The monthly and weekly both ran fine on tuesday as did the nightly diff and all the TS backups througout the day. last night - Weds - the nightly diff failed with the following error:
"
Executed as user: <USERNAME>. Cannot perform a differential backup for database "objectstore", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
"
Whilst the error is perfectly legible I dont understand what its implying - cant I add multiple Diff backups to the same full backup OR is my weekly / 10 mins on the same database (but to a diff backup file) ballsing things up for me somehow? If this is the case how come everything ran fine on tuesday night?
Any help gets beers.
THanks
Alastair Jones.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
I'm working on a restore procedure for the case where all MDF filesare missing, but the LDF files are all intact. A full backup is doneevery 24 hours, and a log backup is done every 3 hours. Afterrestoring the last full + log backups, is it at all possible to usethe LDF files to recover data from that point up to a newer point intime?I've found a post which explains how to do this on SQL Server 2000<http://groups.google.com/group/comp...s.ms-sqlserver/browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4fails with the following error message:BACKUP LOG cannot be performed because there is no current databasebackup.
I'm trying to test out a restore scenario for a large database. It contains several files, each one is backed up when we are finished inserting data into it. No changes are made to the data after insert. All changes happen in tables in the primary file group.
So, I backup the primary file, the log, the first "partitioned" file, the log, the next "partitioned" file, the log, etc, etc, etc. Now, I delete one of the tables residing on one of the partitioned files and want to restore it. For arguments sake, let's say I have a primary file, and 5 partitioned files. I delete the table which was on file 3.
I'm having trouble wrapping my head around the steps to restore under these circumstances. I thought I understood it but, apparently not. When I try to restore, I'm told I must first backup the tail of the log. Having done that, I restore file 3, and all subsequent log files. Now, I'm right back where I was (missing the deleted table). When I try to roll back to a point in time before the delete...it claims to be successful but, nothing perceptable is different...??
Is it alright to move the .bak and .trn backups which are automatically created in a File Share Witness when a DB is added to an Availability Group? I did that and there were error IDs 1069 occurring, and the AG was unable to be brought up whenever I run a load intensive batch job ...
We have a set of databases some are fully read-only others have read-only file groups, is there any way to restore backups of these taken on an MSDE 2000 to an SQL Express 2005 instance?
When doing the inplace upgrade we change these to read-write before the upgrade and set them back after the upgrade.
These databases are used in the field by customers althought the controlled upgrade requires a backup before (and blocks if it fails) and tries a backup after if the post upgrade backup fails (due to disk space) we might need to recover from this odd situation.
The only solution I have is install MSDE some place restore to this then do the controlled upgrade again, any other ideas?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
