Is There A Way To Find Growth In Log (ldf) And Database(mdf) ?
Sep 22, 2005
Hello,
I need to monitor every 15 minutes growth in data file and log file .
Since mdf and intial file sizes are set to high value,
measuring these values at 15 min interval will not provide the change
in size .
My intention is to measure the log file size growth which helps to
calculate the disk space and bandwidth required to setup log shipping .
We need to set up this infrastructure based on this calculation
I'm a beginner in SQL Server databases, my problem is this:
i'm making a database witch the frontend is an access project, the database has several stored procedures views and user functions (the normal..), but a few data, (only the experimental), last night i've noticed that the file grow from 22 MB to 89 MB, the objects are the same and also the data, the only diference was that i forgot to put in an event procedure code, the ADO method, "MoveNext", to update various records, and the loop was infinit. Is it possible that SQL statments generated by ADO make the file grow so rapidly!? If so how can i shrink it, because i've tried and and the results was 4%.
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor) exec (@l_sql_string)
fetch next from db_name_cursor into @l_db_name end close db_name_cursor deallocate db_name_cursor select * from DB_Growth with (nolock) if object_id('DB_Growth') is not null drop table DB_Growth set nocount off set ansi_warnings on return
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor) exec (@l_sql_string)
fetch next from db_name_cursor into @l_db_name end close db_name_cursor deallocate db_name_cursor select * from DB_Growth with (nolock) if object_id('DB_Growth') is not null drop table DB_Growth set nocount off set ansi_warnings on return
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor) exec (@l_sql_string)
fetch next from db_name_cursor into @l_db_name end close db_name_cursor deallocate db_name_cursor select * from DB_Growth with (nolock) if object_id('DB_Growth') is not null drop table DB_Growth set nocount off set ansi_warnings on return
I've got a question about the automatic database growth feature of V7. Here's an example:
I have a 1gb db that can grow to max size of 2gb. I set the auto grow option to 75% The first time the db grows it will grab 75% of the free space (1gb)
What happens if the database needs to grow again?
Will the db grow using the remaining free space (25%) or has the database reached its max size because it can't grow any further?
:eek: I am somewhat confused -- I have a database in production that I restored to a QA environment; upon restore, the size has grown by 200MB.
Both production and QA are running SQL2000 -- the only difference is that QA has the latest security hotfixes installed -- version 8.0.0.665 from KB article at the following link:
I am trying to find a way to calculate everyday my DB Growth, I did find a script on some site but it seems to give me the same information as the taskpad wich is not very specific. Basicaly i would like to know the size of a table in MB or in whatevever conversion possible, so that i will be able to do some forcasting.
My DB size was from 500MB to 10GB since 8/1998 to 12/2004. But now is 16GB (from 1/2005 - 5/2005), I don't why the data size growth too fast (as double) ?
I have a client running RMS, since moving to SQL express his database size has jumped 2 from 2G to 4G in 8 months. Previiuosly it took 2 years to reach the 2G size. has anyone else experienced this rapid growth of their database?
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.
Hopefully I'm posting in the right area. There is a database that has grown to about 41-42 GB in size in about a 2 month period. The previous database had grown to about 22 GB before it was purged out. I'm running this on SQL 2000, and I've tried running all the DBCC SHINKFILE and SHRINKDATABASE commands to no avail. In this case, the MDF file is the one that has grown out of control as opposed to the log file (LDF file).
Does anyone have any suggestions on what could be done to control the size?
I need to monitor my database growth, as few of databases are growing rapidly. My client wants the growth list of my databases. have report of database growth of specific databases, at least of one month.
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'm currently using SQL Server 2005. Before I have set my database on unrestricted auto growth. But today, I have noticed that the Log file has been set to limit its growth to 2,097,152 MB. I have 160GB space for my log files, I just want to maximize the space for logs in my hard drive.
When I try to change the settings back to auto growth it still keeps on returning to its previous setting it is still set on 2,097,152 MB. What I did was : Right Click on the Database - Properties - Files - Click the (...) - set the auto growth option to unrestricted - Click Ok But when I checked log file, it is still set on 2,097,152MB.
Can some one help me change the settings of my Database.
Can anyone tell me why my SQL2000 database has grown aprox 15 % and my Log file 20,000 % when I attach it to SQL2005 .I've Thousands of Databases to Upgrade, but with the log file increasing to more than the size of the Database Its going to be a struggle !
It also takes a fair ammount of time to attach,
I suspect there is some reindexing going on , as when I try to reattach to SQL 2000 I get index errors ?
Is the re anything I can do in advance to reduce the database growth ?
I know I can truncate the log afterward but the peak diskspace consumed during my Migration may be an issue !
I am only DBA in my company and client want to know the growth rate of his SQL server DataBase which is in production. How can I get the growth rate per day?
I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>
I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!
Is there any automated script available to - "Monitor Database Growth and if any DB is grown by 20%, sending mail alerts"? If not, what is the approach to write the T SQL script ?
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 took some database from mproduction, updated and then restored to acceptance. I was trying to make my update as quick as possible so I dropped my originals and renamed my new ones...
I am very new to VC# and SQL Express. I made a databse and a table in SQL Express, but I can not find it when I open up VC# Express and search through the Database Explorer. Can anyone provide me with information on how to get them working together? I have searched online for a while, but so far no good answers. Thank you!
Hi, I installed already vs 2005. The installation installed automaticaly SQL Server 2005 express. Now , I want to see which databases I have there and tables,views,store procedure etc. I tried the server explorer in the vs but I don't see any database on my pc. I tried also to open somthing like enterprise manager like I have in sql server 2000 but I don't found such a tool. I tried to find it in START=>Programs=>SQL Server 2005 but their I see only the Configuration tool. I want to know if the installation of the vs 2005 does not install Databases for the sql server Express ? Should I have to download this database samples? What about a tool to view those database like the enterprise manager in sql server 2000 ? Thanks, David
Why can I not connect to the database created under app_data folder with sql express and view the tables. I am trying to understand the structure of how it works, I have gone through the My personal web site starter kit's files to try and understand how it writes to the database.Why is the databse not visible in SQL express and why are the databases attached database files and not directly in SQL2005.
I am trying to attach a database that was created on another server. I believe the database was created using SQL Server Management Studio Express 2005. Now, I have successfully attached the database in my development environment, which is using SQL Server Management Studio Express 2005. However, when I tried to attach the database onto our production server, which is SQL Server 2000 I received the error: Error 602:Could not find row in sysindexes for database ID 18, object ID 1, index ID 1. Run DBCC CheckTable on sysindexes. In my development environment, I ran DBCC CheckDB on the source database and no errors are returned. I aslo checked the compatibility level under the database properties and it shows: Sql Server 2000 (80) So, this should not be a version incompatibility issue. What is causing the attach to fail on SQL Server 2000? Thanks for any help.
