I've been looking over quite a few of the sql server installations at my new job and I have been noticing that most of them are set up to autogrow the data-files by 1MB unlimited and the log-files by 10% unlimited which is the default settings when creating a new database in SSMS. This is regardless of the database size which in some cases are 400+ GB. I've usually set autogrow to a fixed MB value of around 20% of the current size, but autogrowing a 400GB database by 20% will take a considerable amount of time and could be a killer during peak hours. What would you guys recomend...?
I am having a problem where I have a process that seems to fill up the database but the database does not seem to grow like it should. The database is set to autogrow by 10%... the database is around 15-16GB and it is right over 16GB when it fails. This happens once a week lately. nothing on the server has changed.
the strange thing is when you look at the database properties through EM it shows as no space available but when you do a shrink file and look at the log and data files there are over almost 2GB free on the data file and 1GB free on the log... it does no make sense...
in order for EM to show there is free space I have to add over 5GB to the data file and 1.5 GB to the log file. then processes continue...
I have a database that was autogrowing fine and now has stopped. I'm using MS SQL 7 and the database in question just reaced 32 gigabytes in size. That is the old db size limit but now it's 32 terabytes I thought. Has anybody heard of this ? Any ideas?
Is there a way that I can get SQL Server to tell me (Alert?) when it has grabbed another chunk of space for a database that has the "Automatically grow file" option set?
Hello Folks,Last evening, as a result of our autogrow settings, our productiondatabase grew from 102 GB to 107 GB. What puzzled me is that, prior tothe growth, we still had a good 10% free space left in the data file.My understanding is that SQL Server will automatically grow a databasewhen worker thread decides that more space is necessary to fulfill arequest. We don't suspect the app of sending over a request whichwould require that much more space.Any ideas on why the file grew unexpectedly?Thanks,Joe
i had a problem with a server instance where an app processing a file and putting the data into the database using a stored procedure was timing out. the app is a service and in the case of errors, will try to process the file until it has been succesfully added data to the database. in the event viewer i keep seeing this message during this timeout period:
"Autogrow of file 'mydatabase_log' in database 'mydatabase' was cancelled by user or timed out after 15687 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."
is there a possibility that this process was causing my SP to timeout? are there any other log or debug files that SQL Express creates that might be helpful in discovering what caused the timeout? currently the log is at... ouch 10gigs restricted... maybe trying to autogrow from this while restricted is hogging resources?
I'm currently running the command below and getting this error message. I am getting it every few seconds for a couple hours now. Seems to have started when I started this SPROC below. I'm just over 2 hours into running.
Not sure what this means yet, any help is much appreciated. Desperately trying to resolve this urgent matter!
thanks once again! mike123
"Autogrow of file 'templog' in database 'tempdb' was cancelled by user or timed out after 2813 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."
All queries on our Production database are timing out. Viewing the error log file the following show up over and over again:
Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 3937 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
Autogrow of file 'Prod' in database 'Production' was cancelled by user or timed out after 33156 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
Our production database is about 1 gig in size with 3.5 million records. I tried setting the autogrow from 30% which it was before to 100MB, but no luck, still timing out and getting the errors above. Permission should be all good, nothing has changed.
There is about 50gigs of available disk space as well, so that's not the problem. Thanks for the help.
Autogrow of file 'FORMS' in database 'FORMS' cancelled or timed out after 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.
FORMS.LDF file is 7613952 KBand the growth is 512MB .
By how much should I set the filegrowth? The users are complaining that the application is freezing on them.
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
I have some tasks that I need to accomplish within T-SQL but cannot find a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using the BULK INSERT command which works fine but it does not allow the following..
I am tying to call BCP to output the contents of a table to a text file from with in a stored procedure. The procedure will be called from an ASP page ... My question is were does the file get created ??? I want to create the text file on server ONE and SQL server is running on server TWO and IIS is running on server THREE... do i have to have a drive letter mapped to server ONE and if so is it mapped on the SQl server or the IIS server ....
ie: exec master..xp_cmdshell bcp db..table out h:est.out -Uxx -Pxx -Sx
I have some tasks that I need to accomplish within T-SQL but cannot find a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using the BULK INSERT command which works fine but it does not allow the following..
I have two questions, 1) Could anyone please point me in the right direction concerning information pertaining to NT Server Enterprise Edition verses NT Server Workstations. We are having problems running SQL Server 7.0 and the Enterprise Edition together on the same machine and was wanting to find information about compatability issues, if there are any, 2) I, on a SQL Server 6.5 database shrunk it by 2 Gb. When I looked to see if SQL Server released those 2 Gb back to the hard drive, I was amazed that it didn't! Did I miss something or will SQL Server 6.5 not release the space because of the initial set-up. And why did SQL Server 6.5 automatically take the space from the Transaction logs when neither of the devices were specified? Is the Transaction Log the default area for shrinkage?
Thanks in Advance! Daimon Russell daimon_r@hotmail.com
1. When we create DTS in SQL Server through DTS designer, where are they stored physically? 2. What would be the best way to modify a DTS without using DTS designer? 3. Is there any other way to create DTS apart from DTS designer and Visual Basic? 4. Is there any website which has detailed information for DTS? (which has more FAQs like above?)
In our production environment, we keep changing the servers frequently, and everytime that happens, I have to change the connection properties in all the DTS going to them one by one.
I am not too familiar with SQL Server, but my supervisor gave me the task of finding out the difference between SQL Enterprise and SQL Standard. He also asked me to research the difference between processor licences and client access licences. I will use the Internet as a resource, but I would also like to hear the opinions of someone who uses these programs or is knowledgable about them. So please any suggestions or any useful links would be very helpful.
I have two questions. 1) If a database is suspect we can have that trace from sysdatabases.There is a column named status.My question is in case of suspect datatbase what will be value in the field status of sysdatabases? 2) The password of an user login(created by using sp_addlogin stored procedure or any other way) is stored in the table sysxlogins of master database.The password is stored in a varbinary format.How can I get the actual password(means in a char format)?I mean how can I convert the varbinary value to a readable format?
Hi All, I am new in SQL SERVER 2000.I have few questions - 1) WHAT WILL I DO TO TRUNCATE THE SIZE OF A TRANSACTION LOG? 2) WHAT WILL BE THE STEPS OF BUILDING THE MASTER DATABASE? 3) WHAT WOULD BE THE PLAN OF ACTION WHEN SQL DOES NOT STARTS UP? 4) WHAT WOULD BE MY PLAN OF ACTION WHEN SQL DB GETS CORUPTED OR STARTS IN A SUSPECT MODE?
Could I do periodicity backups to another computer(mediaserver) using VDI??
I mean , Could I config a Virtual Device so that I can do backups like disk or tape, I can use 'backup database ...to virtual_device='...' ' to do backup to another computer(mediaserver)? suppose that I have finished the interface of mediaserver.
If this is impossible,how can I do periodicity backups to another computer??
I have finished a program using VDI that can do backup to another computer,and I know how to do periodicity backups to disk or tape. but I am puzzled about the periodicity backup using VDI.
I am putting together a proposal for my church, the current DB software (Access) has been outgrown. One proponent of a no name brand software insists that to implement SQL could take a year and a team of programmers, is this true??
Hi Could any one tell me the answers for these questions.
1. how do we troubleshoot a datbase if it is in suspect mode.What is the reason for a database to be in suspect mode. 2. how can we move a file from C drive to D drive, so that the file location in C drive is completely moved . 3.In a particular primary file group there are many objects. How can we move some of the objects from this primary file group in to another file group. 4. can we install a sql server on a remote server so that it will not ask any inputs like domain name, authentication modes. . 5.If we want to implement clustering , can we use the virtual ip address on clustering as the ip address of our system or we have to use another ip address for clustering 6. In 2000 we have DTs package.can we run the same DTS package in 2005.
Hi Im relatively new to ASP/SQL and have been thrown into the deep end by work. Ive got courses to go on, but not for another 3-6months.
Ive got an ASP file calling a database using SQL. Once you hit the "go" button, it puts this data into an Excel file, under a new window (still showing the asp file in the address bar).
It currently shows:
1) Item numbers that end in "0" i.e. 3.10, 12.20 appear as 3.1 and 12.2 respectively in the Bill of Materials … i.e. being treated as decimals … I need them to show-up as text. ************************************************** ******************* 2) When saving the spreadsheet … it would be good to get "X" to set the default file name to: <Quote ID>_<Customer>_<Platform>_<version>.[xls|pdf] ************************************************** ******************* 3) When a spreadsheet is displayed, it is in an editable Excel format. Is it possible to lock the file automatically when its opened or password protect it? ************************************************** ******************* 4) If a spreadsheet window is open and you try and open a new one, the old one pops-up/is still there. Need to be able to close the old one and re-open a new one automatically.
If necessary i will post the code. Any takers? Thanks
I need help with a few sql questions, but you need to look at picture containing the tables and the relationships between them. How do I post or upload the picture first?
Hi all,I'd like to know if it's possible to sort twice in a same SQL query.I use SQL for retrieving Data into an Excel Spreadsheet.(Excel 2000 or XP) and to paste the queries results into an Excelspreadsheet.1. I tried to sort by date and the only way was to use the serail numberinstead of the litteral nameI tried SELECT * FROM [Sheet1$] Where [Date] <= 07/20/2003"but this does not work.I then tried SELECT * FROM [Sheet1$] Where [Date] <= 37822" and itworks.Do you know a way to use 07/20/2003?2. I need to sort my data twice, first by name and second by date. Is itpossible to sort twice in one query?Something like:"SELECT * FROM [Sheet1$] Where [Date] <= 07/20/2003 ORDER BY [NAME] ASC,ORDER BY [DATE] DESC"3. Even if the first row of my Excel sheet includes the headers, I cannot perform a query other than SELECT * FROM, for exemple, SELECT [NAME]FROM, does not work. do you know how to do that?Thanks in advance for sharing your experience,Phil*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
1. If the TempDB Database is deleted from MS-SQL Server what willhappen..?2. How to insert a not null column in an existing table withrecords..?3. If a table is deleted, what will happen for the Stored proceduresand Views, which that table reffered..?
Hi All, I was asked couple of questions yesterday and I thought I had to still learn a lot. Questions sound very normal but as for as I am concerned, don't know the answers hence this forum. Please advise. Here the questions asked to me 1) A simple insert into the table (could be from .net application or query analyzer) takes 10 minutes and times out, does not do any thing. What could be the reason and how would you identify and resolve the problem.
2) A simple select from table taking 10 minutes( assuming there are only there columns in the table,those are, id, name, description) id being identity column and has got index as well. What could be the reason and how would you identify and resolve the problem?
3) How would you change the identity column value or can we change the identity value and how?
4) How would you use the shared, update, exclusive locks in the sql statement.?
i have started developing on DTS recently. and i have a few question about "How-To" issues.
Hope you guys can help
1) I noticed, whenever i want to "redirect row" whenever an error happen, i need to set my AccessMode to OpenRowSet... This mode as i noticed, cant be used when i am tranferring data into a sql table which has primary key set
e.g. Ms Access which has identity -> SQL table with PK set
Is there anyway, to capture the row error in my situation?
2) Another question is, in a data flow... can i have a source which has multiple output arrow.
The <Protect Sensitive Data Using Encryption in SQL Server 2005> said : If regenerate service master key with "FORCE" option, the keys protected by old service master key are lost forever along with the data encrypted by them.
I create a master key in database and create a certificate protected by it.Then i use the certificate to encrypt some data.
The master key also exists and the encrypted data can by decrypt after i regenertate the SMK with force option. So i'm puzzled,I want to know what are the keys which protected by SMK , are they database master key?
All of a sudden MS-VSA window does not come up after hitting the Design Script... button. Work around: Get out and get back.
What is the work around? Regarding SQLDMO:
Trying to add a reference to SQLDMO using the References node of a Script Task brings up a dialog with just one tab for the .NET. Is there a way to reference COM?
