Should The AutoClose Option Be Turned On For MSDE Databases?
Jan 20, 2004Should the AutoClose option be turned on for MSDE databases?
When use as the data source for a website.
Thanks
ADVERTISEMENT
AutoClose
Jun 15, 2006How do you set the AutoClose property of a SQLExpress db? Also, can the user instance timeout property be set at the database level too to make for easier application distribution.
Is there any downside to turning off AutoClose in order to speed up the initial startup times after a period of inactivity.
Connections Not Closed - Can SQL Server Autoclose
Feb 14, 2008Hi all
I have inherited an horrific load of mad commentless code.
It was working fine but I have just moved it from SQL Server 2000 to 2005.
No its has ground to a halt due to un-closed connections.
I therefore assume that SQL 2000 must have auto closed conncetions but 2005 doesn't (or something similar).
I am about to wade into the code and find the places where connections are opened but not closed.
This is going to take time
In the interests of getting my site back up and running does anyone know how to set 2005 to autoclose connections?
any help much appreciated.
ta
C
Using MSDE And Moving Databases
Jul 23, 2005Hi all,I am pretty new to dB programming and a have a few questions which I willput in separate threads. I have only coded as a hobby and some limited workprojects. I have recently been asked if I could help implement a simple helpdesk for work. This has only been running for a few weeks and the data iscurrently being stored in excel (I know...I know!! :-)I have installed a copy of MSDE on my development machine and plan on doingthe same on my office 'server'....in fact it may well already be on there. Ialso have another testbed to act as the client.My first question (in no particular order) is how to move the sql db fromthe development machine to the server? I don't have any sql/db tools otherthan anything that is included with MSDE (which I don't think is much).I will be posting other Q's for any experts out there!Thanks in advance.Phil
View 2 Replies View RelatedBackup And Restore Databases In My MSDE
Jul 13, 2004Hi all! How can i backup databases which are running in my MSDE 2000 & then how can i restore them with all the data, Yes i know that their is no visual tool for doing this in MSDE but can we do it with scripts, if Yes then how
View 1 Replies View RelatedMS SQL MSDE Quick Question About Databases
Sep 27, 2004Hey,
Im using MSSQL MSDE with the ASP.NET WebMatrix Project to build databases, but i cant find where the database is stored on my hard drive. I am about to format and reinstall Windows and would like to backup these databases so that i can continue to work with them after i reinstall. Can anyone help me?
Thanks in advance, all help is appreciated :)
E.
Can The Transaction Log Be Turned Off?
Mar 3, 2004I have a couple of databases that see a lot of updates (WEB user session state stuff) and I don't care at all if I lose any or all of the data. They don't get backed up and fresh copies can be created in seconds. Is there a way I can turn off the transaction logging to cut down on overhead?
Thanks
Can Logging Be Turned Off On An Inser Statement
Oct 10, 1998I`m using SQL Server 6.5 and my application does a weekly batch load of data
into a database. Since it`s a re-runnable batch process, there is no need for
logging, the log files just fill up, cause problems and slow the process down.
Is there a way to do an Insert into a database and have it NOT write to the
transaction log?
Alternately, if I just want to dump the transaction log, is there a stored
proc for doing that?
Thanks for your help.
Suspect Database Has Turned Into A Nightmare
Jan 22, 2008This morning one of our client's database appeared as Suspect. I checked the event log and saw the following error:
Could not redo log record (413:835:2), for transaction ID (0:156861), on page (1:1246), database 'CLP' (database ID 26). Page: LSN = (412:2642:4), type = 1. Log: OpCode = 4, context 2, PrevPageLSN: (412:12236:2). Restore from a backup of the database, or repair the database.
I then tried to run a DBCC CHECKDB on the database and got the following output:
Msg 7987, Level 16, State 1, Line 1
System table pre-checks: Object ID 13 has chain linkage mismatch. (1:339)->next = (1:771), but (1:771)->prev = (1:341). Check statement terminated due to unrepairable error.
DBCC results for 'CLP'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'CLP'.
After a bit of research I began to get very sad, for a number of reasons. First I can't find much information on this error and what there is implies that I'm totally up the creek. Secondly and worst of all, backups have not been working so we don't have a viable backup of the database.
I did go as far as to run repair_allow_data_loss and received the same Msg: 7897 error as above.
Is there any hope? Can this be repaired? Any help is greatly appreciated.
-Will
Loooonnngggg Delay In Loading Due To Pre-validation. Can It Be Turned OFF?
Mar 14, 2006During dev. I _know_ certain things are invalid. I don't need the validation, it just wastes a lot of time...
View 3 Replies View RelatedSQL Server Admin 2014 :: DB Growth Report With Compression Turned On?
Apr 14, 2015I got into confusion while working on DB Growth report for 2012 databases which has compression on.I am analyzing the DB Growth based on MSDB..dbo.backupset table which stores the backup information.
But here it gets tricky, In Previous versions we use "backup_size" column to get actual backup size and estimate the db growth based on the previous all backup file info. But now since compression is on in 2012 the "backup_size" colmuns gives a compressed file size(If i am right) so how do you know the actual backup size to estimate the db growth over a period of time??
In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing
Apr 27, 2008Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies View RelatedDB Engine :: DB Autogrowth Turned On In Secondary Data File Still 0% Internal Free Space
Oct 30, 2015Today 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.
Transferring Databases From MSDE 2000 To SQL 2000
Jan 28, 2007Trying to transfer databases from MSDE to SQL. When I use SQLEnterprise Manager - DTS - import or export, it works for the firstdatabase, but then fails for the others. I select the to and fromdatabases using DTS - Import - 'MS OLE DB Provider for SQL Server',then select 'Copy objects and data between SQL Servers'. It failswith the generic error message: 'Copy objects failed', nothingfurther. I tried to just copy data, and it doesn't work totally. Ontwo databases, it said that it copied everything, but when I go inunder Enterprise manager, some of the tables are incomplete, i.e. nodata, table shows up in the list, but if you try to look at it, itsays that it is missing or empty. Also, one table was not evencopied. In the other two cases, I get errors, does not copy alltables. I get errors, like insert fails, but the column it referencesfor the table does not even exist in that table. Any suggestionswould be appreciated.
View 2 Replies View RelatedSQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option
Feb 10, 2015So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
Can Logging Be Turned Off On Inserts To A Specific Temp Table From A Specific Sp?
Oct 10, 2007I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
MSDE Text Datatype - Unable To Store More Than 900 Characters. (msde + Visual Studio 2003)
Jun 6, 2005Hello,
I'm not sure if it's the setup I did wrong, but I can't seem to get my
text datatype in my database to store more than 900 characters.
I'm trying to setup a news database for my website, which will populate
the information into a datagrid. To test, I manually added a news
item in the database through the visual studio 2003 gui. I
immediately noticed a problem as the I was getting an error after a
long news item saying:
"The value you entered is not consistent with the data type or length of the column, or over grid buffer limit."
I couldn't find anthing to set the buffer limit and the datatype is
"text" filled with simple text in the column. As a further test,
I
simply entered 12334567890123... up to 900 characters and still
recevied the error.
I would appreciate someone leading me in the right direction on this one.
Thanks a lot.
How Do I Change The Name Of A Computer Running MSDE With Out Reinstalling MSDE
May 27, 2000I am trying to change the computer name of a machine running MSDE but I get an error when SQL Server starts. With regular SQL when I change the name of a computer I re-run setup and setup fixes this problem. MSDE can only be installed from unattended mode so I can’t rerun setup and fix the problem.
My question is "How do I change the name of a computer running MSDE with out reinstalling MSDE"
MSDE: Renaming Machine Without Reinstall Of MSDE
Mar 27, 2001We currently have the problem, that all our machines are produced with the
same name and afterwards the name is changed. So we have the problem that
the checksum key for the MSDE isn't valid anymore. As MSDE can only be installed
from unattended mode so I can’t rerun setup and fix the problem. Does anyone
know a solution for this problem ?? A program recalculating the cheksum ??
regards,
Manfred
MSDE Worth Using Or Should I Invest In MySQL? (was MSDE)
Mar 9, 2005I'm not sure if this is the correct forum for this this question but I'll give it a shot.
The only db development that I have ever done is in MS Access. I have a project at work that is being accomplished in VB and I need a db engine to use as the back end. Visual studio came with a copy of MSDE. Is this tool worth using or should I invest in mySQL? Are there any advantages to using MSDE over mySQL?
Linking Tables From Different Databases Or Querying From Multiple Databases
Dec 10, 2007Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
Service Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)
Jan 24, 2006I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
Which Would Be The Best Option?
Apr 28, 2008I have a table name “StringResources� which contains resources for different cultures. Right now, whenever admin adds any new resource, it immediately available to end user. Now the new requirement came up. We want admin to add resource first and when he is ready with all the resources for particular culture then only the resources should be available to end user.
Important: StringResource table has SQLCacheDependecy set. So any change for particular culture will invalidate the cache. This how the select statement looks.SELECT dbo.StringResources.resourceType, dbo.StringResources.cultureCode, dbo.StringResources.resourceKey, dbo.StringResources.resourceValue FROM dbo.StringResources
WHERE dbo.StringResources.cultureCode=@cultureCode
Which would be the best option below:
1> Add new Boolean column “Published� and show only resources which are published to end user.
Advantage: No need of extra table.
Disadvantage: This will invalidate the cache every time resource is added even if it’s not published.
Other option
2> Add new temporary table with same structure. When admin add new resource, add it to this temp table and when publish move resources to String Resources table.
Advantage: Admin will have separate working space. It will invalidate cache only when resources are published.
Disadvantage: Need extra table.
An ALL Option
Sep 9, 2006How can I get an All option into the cascading prompt?
I want to view data for all states in USA. I find I can't proceed to load report unless I have filled in a value for all prompts. Any help?
Thanks
AWE OPTION
May 19, 2008I have a small doubt.
If we enable AWE option we can have advantage of available physical memory.we can have more memory by using max server memory property.
then why this AWE option comes in to picture.
I read bol but iam not able to understand what exactly happens.
Could any one tell me why this AWE option if we have max server memory property.
Option (RECOMPILE)
Oct 25, 2007Hi,
What is equivalent to OPTION (RECOMPILE) in SQl Server 2000.
Create table #Employee
(
EmpId int IDENTITY,EmpName varchar(30)
)
insert into #Employee(EmpName )
select EmpName from AllEmployees
OPTION (RECOMPILE)
Best Option To Update Every Jan 1st
Feb 27, 2006I have a vacation request app I'm designing, and it has a VacationData Table with TotalVac, UsedVac, VacLeft, VacationCarriedOver, and VacCompleted.
I need to take the VacLeft and divide by 2 and place that data in the following two spots, one in the VCO and add it to the TotalVac, which is pulled from another table w/hire date and other info.
I only need to run this on Jan 1 of every year, any suggestions?
Lock Option
Jul 12, 2001I was running a query and it didn't execute stating you have run out of locks
How could I set the locks
thanks
Sejal
Not For Replication Option
Oct 14, 1999I am trying to alter a table that has an identity field to make the column have the not for replication option. I just can't seem to get the syntax down for the alter table command. PLEASE HELP. I know it can't be that hard!!!
View 2 Replies View RelatedDB Recovery Option
Jan 23, 2003Is there a Stored Procedure or something else that will tell me if a DB is set to Full or Simple recovery?
Thanks,
Ken Nicholson
Query Option In DTS
Apr 27, 2001Im trying to transfer data from SQL tbl to .xls file through a DTS package.
My problem is that DTS transfers all the data from the source tbl to .xls
file.
But i would like to have the selective data transfer based on some query like ;
select * from tbl where field ='val'
instead of complete data transfer.
Thanks in advance.
Regards
Siva
What Are Best Option Available In Market?
Oct 15, 200570-229 certification exam measures your ability to design and implement database solutions by using Microsoft SQL Server 2000 Enterprise Edition. Candidates for this exam work in a medium to enterprise computing environment that uses SQL Server 2000 Enterprise Edition. Candidates have at least one year of experience implementing relational databases. I don't have experience on database. Haven't experience on SQL server administration. Suggest me good one resources in low price. What are best option available in market?
View 2 Replies View RelatedIs SQL Server An Option?
Jun 6, 2006First off, I apologize for not knowing what I'm talking about and being long winded. I'm trying to determine if SQL Server Express is an option for a client of mine. Their needs are beginning to go beyond what I'm comfortable with in Access, so I'm looking into the option of upgrading to a SQL Server Express back-end with a VB front-end.
Access doesn't require any setup beyond "File, new", so I know nothing about the background work required to get a database running on a platform like SQL Server.
My first concern is what kind of network admin rights do I need to install & use SSE..
It's unlikely that her IT group will just hand us the keys to any of their servers, so all we really have available is what we can put on her network drives. Am I right in assuming that using a SQL Server database would involve more network privileges than just dropping a file on the network and pointing my front end app at it?
With that said, I think I remember seeing something on one of the MSDN pages about setting up a database to run off of a CD-Rom. Could I somehow use this capability to get what I need?