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)
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?
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?
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 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.
We have an SQL2K SP3 Decision Support Server which will be receiving a nightly data dump from the offsite production system. We’ve decided on a backup/restore process to move the data. We’re currently testing the process and have come across a problem. The test backup file is only 500MB, the real DB will be approx. 20GB, and when it is restore the DB is fine but the log file has ballooned to 8 GB.
When the DB is restored the properties of the production DB are forced over our settings. These include no truncation of log on checkpoint and a full recovery model (the destination DB will have simple recovery). We’ve been through the books online and search online forums, but have not found information on this particular problem. We can, of course, shrink the log file after restoring but are concerned about the amount of dead space needed when the real DB is delivered.
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
DOES ANYONE KNOW HOW I CAN RESTORE A SQL7 DATABASE WITH JUST THE DATA FILE? THERE IS NO GOOD BACKUP OF THE DATABASE AND THE LOG FILE IS MISSING. THE CLIENT THOUGHT THEY WHERE BACKING UP THE DATABASE BACKUP FILE TO TAPE, BUT THEY WHERE NOT. THE BACKUP FILE WAS BEING OVERWRITTEN EVERY NIGHT. THE LAST NIGHTS BACKUP DID NOT GET TO FINISH TO COMPLETION. PLEASE, ANY INPUT WOULD BE APPRECIATED. THANKS, TIM
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?
Hi i have an web app demo that allows users to add info and change attributes within a SQL 2005 Express DB. I'd like to restore a clean copy of this database every couple of hours from a .bak file using a Windows scheduled task on the server. Has anyone got a .sql script for database restoration that i could use and call using a .cmd script file? Thanks.
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.
A full database backup file was created and placed in my C:Program filesMicrosoft SQL ServerMSSQL.1MSSQLBackup folder. In attempting to restore the file using "Restore Database", I get the following error: System.Data.SqlClient.SqlError: Directory lookup for the file "d:Microsoft SQL ServerMSSQLdataworkspace.mdf" failed with the operating system error 3 (The system could not find the file path specified.).
Our SQL database log file got physically deleted. Now the database is in suspect mode. Is it possible to recover / restore the database just with the data file.
i have sql server 2000 db with two data file... primary data file has extension mdf and secondary file has extension ndf (as per microsoft recommendation)..
when i try to backup the db and restore thru the enterprise manager .. in the restore -> options window ... i see both the files has the same extension mdf.. and when the restore completed, the new database still has extension mdf for both the file..
why this behaviour?
* i even try to create a new test db with two files, still its the same behaviour.
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?
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?
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?
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.
I am trying to create sql code that restores a backup of a master database to a new database on the same server. It “seems” to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with “MODTRNMaster”
The databases which are created from the restores start with “M1_” and “M2_”. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster to disk = 'f:kupMODTRNMaster.bak'
backup database MODTRNMaster_IMG to disk = 'f:kupMODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC to disk = 'f:kupMODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM to disk = 'f:kupMODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. I’m hoping that there is just something simple that I’m overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master go
drop database M1_MSLH go
restore database M1_MSLH from disk = 'f:kupMODTRNMaster.bak' with move 'DEV5_Data' to 'f:mssqldataM1_MLSH.mdf', move 'MM' to 'f:mssqldataM1_MLSH_1.mdf', move 'AMB' to 'f:mssqldataM1_MLSH_2.mdf', move 'DM' to 'f:mssqldataM1_MLSH_3.mdf', move 'IMM' to 'f:mssqldataM1_MLSH_4.mdf', move 'ED' to 'f:mssqldataM1_MLSH_5.mdf', move 'DEV5_Log' to 'f:mssqllogM1_MLSH_log.ldf', recovery
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.
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.
