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.
View 3 RepliesWhat 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 ?
Thanks,
JK
Hello Everyone,
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.
Any advice would be great.
Thanks
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 .....
View 2 Replies View RelatedWe 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?
Thank you,
Greg
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?
Thank you
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?
Thanks in advance.
Bill Zimmer - zim@ibx.com
Can someone point me to examples of database & file growth monitoring.
I specificially want to monitor a number of separate SQL servers (2000, 7.0)
I want to end of with statistics of any size changes on any of these over time.
Help is greatly appericated..
thanks
We have a database called "ih".
On Saturday the mdf file was 41.8 GIG. Today it is currently 79.1 GIG.
I've restore the DB to a new db from Saturday to compare to see where the growth is happening. That db is called "ih_restore".
I've found the following script which reports the size of each table of the database -
SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
[Code] ....
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?
Hi everyone,
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.
thank you in advance
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?
Can I control the size the tempdb can grow to?
SQL 2005 (x64) sp1
W2K3 R2 SP1
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?
Thanks!
SQL Server 2000 | Transactional Replication
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.
Any thoughts?
Jeff
Guys,
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.
Any ideas/suggestions?
Thanks a lot
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?
Thanks in advance for the help.
Script to find DATEWISE MDF FILE GROWTH Like
30/6/2015 10%
1/7/2015 8%
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.
View 6 Replies View Related 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 ?
View 7 Replies View RelatedHi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
View 1 Replies View RelatedI 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?
View 5 Replies View RelatedHi friends,
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.
Cheers..
Thanking you
Sadik Ali
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)
Thanks in advance :)
Bob
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'm copying files to a folder with the naming convention as follows in the source folder:
CM_ABC_MY_TEST.txt
In the destination folder, this filename needs to appear as:
CM_XYZ_MY_TEST.txt
In my File System Task, I'm pretty sure I'm going to need an expression with a replace, substring, etc. But am having a hard time nailing down the exact syntax.
Does anyone know how to do this using variables? Everytime I try it, I get the
Error: Failed to lock variable for read access with error 0xc00100001.
I also tried it writing a script and still the same error. If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename. So the value of the variables will change everyday. Here is my expression:
@[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )
The result:
C:Documents and SettingsmroushDesktopOSU20060818
the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)
I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container €“ Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) €“ Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(€œUser::FileDestinationPath€?).Value = dts.Variables(€œUser::FileDestinationPath€?).Value & €œ€? Month & €œ_€? & Day & €œ_€? & Year & €œ€?) which gives me €œC:TestFiles 9_14_2006€?.
File System Task (within For Each Loop, second step) €“ This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day€™s date. Basically, how do I get this to work since I can€™t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the €œVariable €œFileNameAndLocation€? is used as a source or destination and is empty.€?
Let me know if I need to clarify further€¦...I may be missing something very simple. Any help would be greatly appreciated!
I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.
What I can do to solve this issue.
Bye!
Daniel
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
I have used the copy database wizard, but I realized I had forgotten to shrink the transaction log file. So I canceled the wizard. My database, detached by the wizard, has now disappeared. The mdf file is still there, but when I try to attach it manually I get the "create file encountered operating system error 5 while attempting to open the physical file..." error.
Any way I can recover it?
Thanks.