Hello everyone,
I have 45 GB db with
-Automatically grow file by 10 %
- full recovery
-log shipping every 5 minutes.
-full backup every 24 hrs
database grown from 33GB to 45 GB for 1 year period
4-5 times a year massive insert
done to database(no specific dates)
if I change autogrow to by 300MB or 4%
1.how would it affect insert process ?
2.how it affect daily performance ?
Thank you
Alex
Bear with me - My SQL Server 2005 Maintenance is as good as a Newbie..
I was running a Very Large Transaction over the weekend (Say 10Mill Inserts)..
And after waiting for 3/4 Hrs for the transaction to complete -- Checked the LDF File, I has grown to a 100 GB.
After that i discovered that i had the Recovery Model as FULL .. So Killed the Job and Changed the recovery mode from Full -> Simple.
Now i see that the LDF file is not growing in size even though there are many transactions that were complete successfully (Still Very slow though)...
What am i missing here - Iam clueless as to why my LDF is not growing in size?
Any Ideas??
What is the best option to set for File Growth?
Is it in megabytes or by percent?
Hello,
I'm running a long and heavy query. during the running the log file of the DB is growing more than 20 GB and i'm running out of disk space consequently. Is there a way to restrict the log file size without demaging my query?
Thanks.
My logfile has grow the disk full - the logfil is 25 gb and I have 4 gb free.
I can't shrink the log fil !
Can I set the log file to null ??
I have backup my datafil successfully!
Help!
I've been through the forum and read a number of threads on people's DBs not growing and the answer usually is they don't have auotmatically grow data file. Unfortunately I have this on, but when I look at the properties of the database it reports the space available is 0.00 MB? Up until about two weeks ago I was showing appx 48% space utilization. When I ran an SP to show growth, it tells me that it was expanded by 20% yesterday, but SQL Server is still telling me the space available is zero.
The log file is also set for auto growth. The DB is 14.5 GB in size and the drives still have around 92 GB of space.
Has anyone experienced this before? Any ideas? Does anyone know of an SPs that can give me detailed info on internal data file size compared to stated size (i.e. wasted space in data file)? Is SQL Server doing something funny in the way it is seeing the database or data files individually? Any help is appreciated.
My primary (and only) data file has reached the point where it is auto growing. I would like to grow this file in one big chunk at an off peak time. I can't seem to find the code I need to make the file grow when I want it to?
View 1 Replies View RelatedFor one of our database we have an issue where its log file got increased rapidly last week on Fri and Sat. The database is on SQL Â server 2008 R2 with compatibility level at 80. Please see below log grow events :
First, we thought Index maintenance like Re-index and update stats could have been the reason, but when check the schedule that job ran on 16th using below code:
USE ABC
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GOEXEC sp_updatestatsGO
I know above is OLD fashioned, but we believe that should not be the major cause here? How can i determine what happened on 14th and 15th which cause the event to trigger and log file bumps to 80 and 70 GB both days.
Greetings,The following shows how the Properties of a database look like:Data Files:File Name: student_datLocation: e:dataMSSQLDatastudent.mdfSpace allocated (MB): 62'Automatically grow file' checkedFile growth: 'By percent 10%' checkedMaximum file size: 'Unrestricted file growth' checkedTransaction Log:File Name: student_logLocation: e:dataMSSQLDatastudent.ldfSpace allocated (MB): 52'Automatically grow file' checkedFile growth: 'By percent 10%' checkedMaximum file size: 'Unrestricted file growth' checkedThe physical files look like this:Name Size Type Modified------------------------------------------------student.ldf 52,416 KB Database File 2/11/2004 10:34PMstudent.mdf 63,424 KB Database File 2/11/2004 10:34PMMy question is now that it has been specified 'Unrestricted file growth'for both the data and the log file, why both haven't increased any insize since 2/11/2004? Actually, the modified timestamps of some otherdatabases files are also '2/11/2004 10:34PM'. That's weird.I found the following message that's relevent to the above timestamp inthe SQL Server Logs:=====2004-02-11 22:34:10.12 server SQL Server terminating because ofsystem shutdown.=====I'm pretty sure there have been a lot updates taking place on thisdatabase. We don't hear any complaints from the customers that theyhave had any problems (such as, no space left) with the databases.Did the SQL server write the data and log somewhere else?Any insight on what's going on would be appreciated.Bing
View 7 Replies View RelatedIs it possible to dynamically grow a data file for TempDB?
I have some TSQL that I want to execute (this is raw tsql, not something I'd typically share) when I get an alert that TempDB is growing.
SET NOCOUNT ON;
DECLARE @TempDB TABLE (FileID tinyint, name varchar(50), size bigint)
DECLARE @TotalSize decimal(19,4), @TotalFiles int, @CurrentTempDBSize bigint, @SQL varchar(1000), @DBName varchar(50)
SET @SQL = ''
INSERT INTO @TempDB
[code]....
ENDThe code runs fine, but doesn't actually increase the size of the file.
My understanding is that the log file is not supposed to grow if the database is under simple recovery mode.I am in a situation where the log grows if do any inserts that involve millions of rows.How do i make sure that it does not grow?
View 11 Replies View RelatedI need to copy files from one machine to another machine. It should be done automatically. Could you please give me a suggestion. I have no idea about this. It should write a script or program. But I have no idea yet,
Thanks
The code in cmd looks like today.
sqlcmd -S PC03 -d db_test -E -o "testMyData.csv" ^
-Q "[test2]" ^
-W -w 999 -s","
I would like to change the name of the file into "20150512". The name of the file should be today's date. I do not know how to do it.
Hi,
I'm going to be getting several flat files that need to be imported into one of two tables. Although the text files will have different file names, they will have either "Header" or "Detail" in the file name, so I can tell which table they need to be imported into.
The problem is I don't know enough about SQL Server 2005 to set up an automated import of these files into the database. Does anyone have any suggestions on how to do this, or have any experience in setting this up? Are there any inexpensive programs to load data realtime or on a schedule? (I work for a young company with a very tight budget).
I had an idea of creating a windows vb or batch program to create the import commands for each of the flat files, but I can't find the line command to build the import command.
I'm just at a loss and need a solution soon...
Thanks,
Laura
I have a job on SQL server agent on SSMS that runs daily, however, where the results go as the query wont appear on the screen.I want to have it automatically save the results from the query to a .csv or an. xlsx file in a specified folder.
View 2 Replies View RelatedHi again all,
Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks.
ddave
Hi,
I've created a stored procedure that creates a script to create a number of objects within the database (based on what existing objects are in the database). From Management Studio, this works fine, and the output is exactly as I want it.
I'm now trying to create a job that will execute this stored procedure, and deposit the results into a file somewhere on the server. When the job runs, the script is created in the correct place and is essentially ok.
However, there are a couple of questions I'd like to ask.
Why does SQL Server Agent put a header at the top of the output file? I was hoping to be able to use that output file 'as is' and execute it automatically to recreate my objects when required. (Obviously, I can manually remove the header, but this is an inconvenience in this situation). How do I stop it?
Also, when executed from SSMS, the output is correctly line-spaced. But, the output from the scheduled job adds an extra line between each line of text, which is, again, inconvenient. Why does it do this, and how can I prevent this (again, without manually editting the output)?
Any thoughts and help greatly appreciated.
Cheers
Simon
Ok I am faced with working with XML on a regular basis, which is fine.
DECLARE @ViewSN INT
IF NOT EXISTS (select null from tblviews where viewcode = 'loadAtTerm') --<workflowEventType>loadAtTerminal</workflowEventType>
insert into tblviews (ViewName,Description,OutBoundForm,StoredProcSN,TriggersReply,ViewCode,DispXactLayer,DispXactViewType,DispXfcTag,Comments)
select 'QC:WF-LoadAtTerminal','This View Corresponds to the XML for loadAtTerminal in Omnitracs Workflow','0',NULL,'0', 'loadAtTerm','MCOM','MCOM',NULL,NULL
[code]...
What would be really useful is to be able to present any xml file and automatically parse the NODE names into a memory variable table and then the fields of each node in another.
I have a report that is scheduled to run a once a week. This works fine. But now I would like this report to be saved as an Excel file automatically when it runs. how / where do I do this?
View 0 Replies View RelatedThe problem that I am having is that with Visual Web Developer I am creating a webpage and having it directly put online, so for example when I start a new ASP.NET page, I select the location to be HTTP, with the location http://MYWEBSERVER/Website and for the language and Visual BasicI notice a couple of things, first that there is no longer a a link under the Main toolbar "Website" selection called the ASP.NET configuration. So how can I configure what I want to have users be able to do? It seems that this choice is only available if I am building the ASP.NET page on my "localhost". So that is the first problem. So I am able to get the pages to work, atleast the things such as the textboxes to show up etc, (Even things as advanced as the "Login" box). How ever when I try to get someone to try to login I am taken to a page that has an server error. The error is:
"The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. "
The Stack Trace Errors are at the bottom. I think this is happening because the automatically generated databases are not getting built online as they are on my computer. On my computer I have MSSQL express. So either the databases are not getting built for some reason, (and I think that is the case as I don't see any in the folder). So I think that somehow I have to create a database on my server, and then somehow configure the ASP.NET file, perhaps in the Web.Config file to look for that new database. Is this the correct methodology? Is there some simpler way that I can just somehow upload things as they are and have them work correctly on my server? The error says that either the Server did not find the database or that the trust level was insufficient. I don't think that is it as I just looked again and I don't see any .MDF files. So how would I go about getting this to work right? Is there a way to do this with MySQL also? So that I don't have to use MSSQL? My server only allows 1 DataBase for that.
Thanks and I hope my question makes sense. It is basically how can I get it to be able to create and check users etc. online?
Brian
[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.] System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2555237 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Hi,
I know that SQL 7 grows databases dynamically, but I'm wondering how it determines how much to grow it by? I have a couple of databases on our servers that are 3.4 GB but with 1.6 GB space available. So I'm wondering when it determines it needs to grow a database and what it does to determine how much to grow it by.
Thanks,
Mike Gagne
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedI need to load the latest csv files from file server , The files are placed in a folder called -
Posted 02022015- --> csv files .
I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location
I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .
Hi ,
I have a SQL 7 database in which I have set the autogrow on. I need some way to be notified when the database does an autogrow. The reason for this is that if it does an autogrow once then if I am notified then I can manually expand the DB size without having SQL Server do multiple autogrows. I was looking at setting an alert but cannot find any message in sysmessages that seem to be information types for auto grow. Has anyone done this kind of thing.
Thanks
Venkat
OK. Here's a good one.
I wrote a query that caused a HUGE amount of stuff to be written to the transaction log. Since I set the database up before I had enough coffee yesterday, I didn't turn on a "Restrict Filegrowth" on the log. So the transaction ran until it filled up the available space on the drive (my local workstation, so it grew to about 6 GB) and then it rolled back. (BTW: Microsoft finally figured out that rolling back a transaction shouldn't be a blocking operation. ISQLW tells you that the transaction failed as soon as it fails, and then releases the connection to you, so you can go on with your life while SQL Server cleans up. Good one!)
OK. So that done, I figured I'd just truncate the transaction log and do the nifty new "DBCC SHRINKFILE()" thing. So I truncate the log and do DBCC SHRINKFILE. Nothing happens. Enterprise Mangler (OOPS Manager. I really mean Manager) shows that only 43 MB of the 6 GB file is in use. DBCC SHRINKFILE reports that the minimum size is 128 pages, the current size is 697,256 pages, and 697,256 pages are used.
Great. So I can't shrink the file.
Step 2: I dump (OOPS, sorry, BACKUP) the database, delete the database, make sure all the files are gone, and then restore the database. It re-creates the 6 GB file, which, by the way takes a very long time. What's funny about that is the query timer in ISQLW reports that the query took 30 minutes, but the return from the restore command shows that it took about 300 and some seconds (about 5 minutes) because the restore command doesn't count the amount of time it took to build the files (I'm guessing). After I figure out that it rebuilt the 6 GB file, I screamed, and started downloading PostGreSQL for my Linux box, and got on to other projects.
This morning I came in and started reading Books Online to figure out what's going on. It says something about "Virtual Log Files" and how a log can't be shrunk past that point. Great. MS basically defines a virtual log file as "the point past which you can't shrink a log". So I have a 5 GB virutal log file, and I can't truncate it, shrink it, or make it go away.
So I have a stroke of genius and decide to build a new log file in the database, and then use the DBCC SHRINKFILE command with the EMPTYFILE option, and then use ALTER DATABASE to remove the file.
Then I get this really cool error that says:
Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
OK. Last time I checked, a log file doesn't belong to a primary filegroup, so there's something else going on here. Basically, it looks like the first file that gets created is the "Primary" file and can never be removed.
So, new policy, every "first" file in a database is going to be a 2 MB file, with a 2 MB growth limit, so we can remove it later. That's a load of....fertilizer.
It looks like the AutoShrink for logs is just a myth. Auto-Grow seems to work almost too well, though. I'm picturing one of those Access newbies using the Export function in Access to put data into SQL Server on one of our pre-production boxes, and having a 180 GB log that can't be shrunk. That'll be a good time.
The moral of the story: Always set growth restrictions, especially on log files.
The questions:
1. Anybody got any bright ideas on how I can get my disk space back WITHOUT using BCP (or DTS, or similar methods)?
2. Anybody know how a different file can be set as a "PRIMARY" file?
3. Anybody know why MS decided to fill the Transact-SQL help in ISQLW with "You can't get there from here" messages that reference Books Online?
Thankfully, this isn't anywhere in our production system, and if the quality continues this way, it won't ever be in our production system.
chris.
Hardware:
IBM Netfinity 8500
2 processors Xeon 700
1,5 Gb memory
Windows 2000 Server SP2 Build 2195
SQL Server 2000 Standard Edition 8.00.534 SP2
There is only on Database (DB) of 16 Gb in drive G.
Drive G has 32 Gb space free.
Yesterday we appended tables to the database and in SQL logs appears the next error:
2003-01-23 12:26:42.57 spid101 fcb::ZeroFile(): GetOverLappedResult() failed with error 2.
2003-01-23 12:26:42.61 spid101 Error: 1105, Severity: 17, State: 2
2003-01-23 12:26:42.61 spid101 Could not allocate space for object 'ttdssc030104' in database 'MYDATABASE' because the 'PRIMARY' filegroup is full..
2003-01-23 12:26:48.03 spid101 fcb::ZeroFile(): GetOverLappedResult() failed with error 2.
DB configured to grow automatically by 100 Mb and transaction log Automatically grow in 10% .
Unrestricted file grow selected on both.
I try to expand the DB manually by Enterprise Manager to 20 Gb but not work and in SQL log appears the error
"2003-01-23 12:26:48.03 spid101 fcb::ZeroFile(): GetOverLappedResult() failed with error 2."
In Enterprise Manager-Databases-Properties-General-Size of DB maintain 16Gb.
Windows explorer say MYDATABASE.MDB is 20480 MB.
I delete the tables inserted and the problem persist.
Thanks in advance,
Hi All
i am bit confused about how data and log files grow in databases. suppose i turn off the auto grow and restrict the maxsize upto some limit but size of data/log file is less then maxsize at some stage because what i understand is size of data/log file keep changing depends upon the activities going on the database. in future if data/log file need to grow can it grow upto the maxsize without turing on the auto grow.
regards
SQL 2000I thought I would throw this out there for some feedback from others.I'd like to know if you feel using MS auto-increment field is a goodsolution these days or should one grow their own ?Thanks,Me.
View 11 Replies View RelatedI made a database to hold recordings of calls made to our customers.When I made it I set the size of the primary datafile to 18GB. It'sbeen running flawlessly for over 10 months. A few days ago the userswere suddenly no longer able to save the recordings to the database.They got an error message to the effect that the timeout had expired.The failure occurred on the .Execute statement of the Command thatcalls the stored procedure.I noticed that the data had reached the size allocated for the file.The file was set to auto-grow (5%). However, since I couldn't findanything else wrong, and since the test version of the database (whichonly has 15GB of data in an 18GB-dimensioned file) did not exhibit thesame behavior, I decided to try increasing the size of the file withan ALTER DATABASE statement. I increased it to 21GB. Lo and behold,the problem disappeared.Here's what I think might be going on: The default timeout for theADO Command object is 30 seconds... this is probably not long enoughfor SQL Server to add 900 MB to the datafile, therefore the Commandtimeout expired. So from now on instead of relying on auto-grow, I'mgoing to just make sure the datafile always has plenty of headroom.FWIW.
View 1 Replies View RelatedI've got a little console app that basically pulls back a recordset from our SQL Server 2005, goes through each row in the dataset and may/may not insert a record into a different table in the database. We use sproc's for every transaction and I close every connection in the application. However, when the application ends, I still show connection pools open in the performance monitor. Same with websites that I know have no traffic or that have been stopped by me in IIS.Last night I showed a total of 6000+ "Current # pooled and nonpooled connections". Should I be worried about what seems to be unending growth in the connection pools? If so, how can I look to manage this better?
View 2 Replies View RelatedHi all,
I'm having a problem with one of our ddbb because we didn't run the maintenance plan from the beginning. The thing is that the hard drive is out of space and the log files are around 100GB. We only have 20MB free. Do you think that is space enought to run the maintenance plan or the shrink command??
Thanks very much!!!
We're using SQL2000 on Windows 2000 Server, but this is a problemwe've had on one particular database since SQL7 on NT4.The database in question is set to autogrow by 10% (currently sittingat 31Gb total size). However, last week users complained of aslowdown in performance. When we checked we found that only 14Mb wasfree on the database (we thought it would've grown automaticallybefore then), and when we added an additional 1Gb manually performancepicked up.Does SQLServer wait until all the space is used up (i.e. 0% free)before autogrowing? Even at that, we've never actually had thedatabase grow automatically - we've always had to add space manually.Settings on this database, and one that does grow automatically,appear to be the same (have also checked via sp_helpdb). So wheredoes the problem lie?Any help you can give would be greatly appreciated.
View 1 Replies View RelatedHello all!
I've a problem with my database. Till yesterday the option for Auto Grow of Database (10 %) was working very fine, but now it seems to be some problems with it. Finally I had to specify a restricted size for the database and then it again startd to give me some space in the database to write in. Ideally it should have worked automatically, isnt it ???
There is no problem with the space on the drive, I still have some 76 gb of free space there ...
Thanks in advance ...
Anjä