Whenever we restart the services on this one SQL server 2005 instance, the database autogrowth changes to grow by 2500%. We have to manually change the autogrowth of data file to some sane number. Has anyone faced this issue? We tried to put SP2 on but even that doesn't help. Any help would be appreciated
View 1 Repliesgive me the best autogrowth & initial size setting for database
View 7 Replies View Related
I am using data flow. source is ole db and target is flat file (csv). I run sql server stored procedure in source and mapped all columns to target file.
Value "-5.0000000000000003E-2" is giving me hard time. It's coming in target file how can remove exponential before writing to target file. In source table that value is coming from float type column. I would like to use some function in select sql if I can.
Thank you - Ashok
Exponential Moving avg is calculated using the formula.EMA = (Today's Price)* K + (EMA yesterday) * (1-K)where K = 2 / (N+1)The user is going to Input the K.It is something likeF(N) = Price * K + F(N-1) * (1-K)How can I reference, the previously calculated value in the Next rowcalculation. I need to implement this in SQL Server.I created a Stored procedure to do this and I used a Temp tbale withIdentity.Create Table #TempMovAvg(MID int identity(1,1) Primary key, tempDate DateTime, tValue float)I Populate the data for that temp table using the below query.Insert Into #TempMovAvg (tempDate, tValue)Select Date, Price From DataTableI tried Diff options to calculate the exponential Moving Avg using theabove formula, but none of them are giving the correct answers. I amnot able to reference the Prev Calculated value in the Next rowcalculation.Some queries I used.Select a.TempDate, a.tValue,0.9*A.tValue+0.1*(Select 0.9*t1.tValue+0.1*t2.tValue From #TempMovAvg t1, #TempMovAvgt2 Where t1.MID=A.MID and t2.MID=t1.MID-1)FROM #TempMovAvg AWhere A.MID>=2order by a.TempDateSELECT A.MId,SUM(CASE WHEN B.MID=A.MID THEN 0.9*B.tValueELSE 0.1*A.tValue END) exponential_averageFROM #TempMovAvg A, #TempMovAvg BWHERE A.MID>=2 AND A.MID BETWEEN B.MID AND B.MID+1GROUP BY A.MIDAny help will be greatly appreciated.thanksGanesh
View 1 Replies View RelatedI am testing a msde 2000 sp4 database. (would like to use it against sqlserver 2005 later)
I need to turn the property for autogrowth on and check that an application alterts the user that it is on
and then off and verify the user is altered that it was set off.
I am not sure if this will work for mydb and mydblog
--turn off autogrowth
USE master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = mydb,
FILEGROWTH = 0MB)
GO
--turn on autogrowth
USE master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = mydb,
FILEGROWTH = 10MB)
GO
USE master
GO
ALTER DATABASE mydblog
MODIFY FILE
(NAME = mydblog,
FILEGROWTH = 0MB)
GO
--turn on autogrowth
USE master
GO
ALTER DATABASE mydblog
MODIFY FILE
(NAME = mydblog,
FILEGROWTH = 10MB)
GO
Also I need to know the t-sql for setting unrestricted growth on and off but I can not seem to find that.
Any help would be great.
We can not use enterprise manager so I'm planning to use osql thru a cmd prompt.
thanks
ced
Hi,
I am values in scientific notation when i am rendering the report into excel.
I wanted the number to be displayed as it is without any scientific notation(exponential format)
Thanks in advance
Nalini
I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?
Thanks,
Carlos
Hi Guys,
Should I keep all my databases to have a standard growth size for the data and log files? What would be good practice?
Also should be be shrinking the log file every so often?
Regards,
Af
I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?
Thanks,
Carlos
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
Hi,
Is it possible to change the Autogrowth option of a database is none is set? I recieved an alert saying that one of the databases has 39.9% of freespace. Having checked the properties of the database, I noticed that the Autogrowth option had not been used.
The database is SQL 2005.
Thanks.
Af.
For whatever reason i'm unable to cast anything more thtan e-4 to a float which makes no sence. Am i missing something?
select cast( '1.550e-6' as float)
?????????
returns 1.55E-06
????????
select cast( '1.550e-5' as float)
?????????
returns 1.55E-05
????????
select cast( '1.550e-4' as float)
returns 0.000155
select cast( '1.550e10' as float)
returns 15500000000
So I need to write a script to look for available free space percentage in my databases, but I only want it to look at capped files. We consider a file with autogrowth off as capped for our purposes.
This is my problem: in sys.database_files and sys.master_files, if I have autogrowth off then max_size is -1, which is the same value as unlimited growth.
I cannot find another setting anywhere to determine how SQL Server recognizes that a particular file is set to not allow autogrowth. Any setting in a DMV anywhere where I can see whether autogrowth is disabled or not?
give me query or script to find autogrowth setting of all databases in instance
View 5 Replies View RelatedSQL2K5 SP1the autogrowth setting in one of my database's primary filegroup datafile keeps having the value of 12800%, which was originally set to100MB, everytime the service is restarted. the same occurs whenever irestore a backup of this database in our development environment.WTF? this issue does not happen to other filegroups. only on theprimary data file. whenever this happens the 4GB data file grows tomore than 70GB (even the math is incorrect) with about 95% of UNUSEDspace.has anyone else come accross this BS or anyone knows how to preventthis from happening? is MS aware of this not-so-funny joke?thanks
View 2 Replies View RelatedI want to save 999999999 as real data in sql.but it saved 1+E09.
how can I save 999999999?
I am using SSIS to populate a star schema.
The issue is in the data flow for loading and setting the Fact table dimension keys (the dimensions are all loaded fine). After 16 rather pedestrian Lookup Transformations, I have an escalating problem adding additional Lookup transforms to the Data Flow. The problem is not in execution; the problem is adding more transforms in design mode.
Lookup # Fields in Data Flow Time to validate that lookup
<17 47 Sub-second
17 48 2 sec
18 49 4 sec
19 50 8 sec
20 51 16 sec
21 52 32 sec
22 53 64 sec
While I€™m intrigued by the mathematical progression that is forming here, the issue is that I have at least 6 more Lookups to perform. I hope you can see my dilemma.
I have gone to where it takes a little over 4 minutes each to validate the lookup transform and its associated Derived Column transform and Union transform (Total 12 Minutes). Not only does this add up to many idle minutes to each design step, BUT it breaks the debugger as it pre-validates the ENTIRE data flow before it ever switches into debugging mode.
Some notes:
1. It doesn€™t matter what order the Lookup transforms occur in, the timings are exactly the same.
2. I tried many Data Flow execution optimizations, but they don€™t improve the validation times (or even get a chance to improve the execution times!)
I realize this may be somewhat of a unique problem.
Thanks for any help you are able to lend.
-Dave
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue
I am importing a text file with a column (serial numbers) with alphanumeric data, some mixed and some only numeric. The very large values that are all numeric are being converted to exponential when I run it thru an import package in SQL Server Integration Services (2005)
Ex. 4110041233214321 --> 4110040000000000 (displays as 4.11E+15)
In the past I dealt with this by importing the text file into Excel and changing the format of the column to number. This works even when many of the values contain alpha characters. I am not sure how to accomplish this same thing without going thru Excel. If you have any ideas on this I would be happy to hear from you.
I am importing the text file into a sql table.
Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below
name   fileid   filename   filegroup   size   maxsize   growth   usage
XX   1   I:DataMSSQL.1MSSQLDataNew XX.mdf   PRIMARY   68140032 KB   Unlimited   0 KB   data only
XX_log   2   I:DataMSSQL.1MSSQLDataNew XX_log.LDF   NULL   1050112 KB   2147483648 KB   102400 KB   log only
XX_2   3   I:DataMSSQL.1MSSQLDataNew XX_2.ndf   PRIMARY   15458304 KB   Unlimited   0 KB   data only
XX_3   4   I:DataMSSQL.1MSSQLDataNew XX_3.ndf   PRIMARY   13186048 KB   Unlimited   0 KB   data only
XX_4   5   I:DataMSSQL.1MSSQLDataNew XX_4.ndf   PRIMARY   19570688 KB   Unlimited   204800 KB   data only
XX_5   6   I:DataMSSQL.1MSSQLDataNew XX_5.ndf   PRIMARY   19591168 KB   Unlimited   204800 KB   data only
2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.
What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its  other files.
What more data i need to ensure i submit an analysis to this.
Hello,
I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.
Example: 1225000.00
When I select this value using SSMS I get the correct value:
1225000
Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))
1.225e+006
I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.
Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?
Thanks: Peter
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
I have database on localhost and i want to show this data on my website. I want to create a database online and want to sync with Local Host. Can it be possible syncing data automatically after some interval?
View 6 Replies View Relatedyes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
View 4 Replies View RelatedI want to create a duplicate database in sql 2000 using asp.net from a webform
I created a database using CREATE DATABASE .......
But how to copy tables, views, stored procedures to newly created
database from old using asp.net from webform
Is there any another method to create a duplicate database with another name
from existing database on same server ?
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
Hi all,
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.InsertCustomers
GO
CREATE PROCEDURE dbo.InsertCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.UpdateCustomers
GO
CREATE PROCEDURE dbo.UpdateCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24),
@Original_CustomerID nchar(5)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Thanks in advance,
Scott Chang
hii want to copy one database table to onther database table using script?my database is ms-sql server 2000
View 4 Replies View RelatedI recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies View RelatedWe have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies View RelatedI had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO