Hi All,
I noticed recently that the setting for "Write Cache Enable" for the disk drives is enabled. Can anybody help me out with what is this "Write Cache Enabled" Setting and what might be the consequences of keeping it enabled on a SQL Server production Machine. What performance gains can we have by turning the feature off. Can anybosy please suggest and article for the Same topic
thanks in Advance
Jacx
Hi,
While reading (I have come across what seems to be maybe a powerful and useful feature) available in MSAS and thus this related question:
Can someone briefly explain the use of this feature and how it can come in handy.
So far I have skimmed through BOL and it says that "Only parent-child dimensions support this dimension characteristic" i.e. Write-enabled feature.
Thanks.
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data
Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User
Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF"
providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €œDBCC FLUSHPROCINDB (@dbid)€??
Is there a workaround for €œdbo€? to be able to flush procedure and data cache without being elevated to €œsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans.
Thx.
Hi guys,
I am looking at the plan caches/cached pages from the perspective of
sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) )
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
a slight change from a query in
http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
Does anyone have any idea what is going on?
On Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL
Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is
consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two
hit ratios, and why would we have such a significant difference between the two?
Any help or advice would be much appreciated.
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
Can anyone tell me what this means and how to fix it? I created a stored procedure in VS2005 and did a build. When I went to SQL Server there was the stored procedure but when I run it I get the error....
View 24 Replies View RelatedHi All,
When i run the command,
use master
go
sp_configure 'awe enabled',1
go
reconfigure
go
I got the output,
name : awe enabled
minimum: 0
Maximum: 1
config_value: 0
run_value: 0
From the value of config_value ,it mean that the "awe" is not enabled.
But, when we see the System Configuration:
OS --- Win 2003 Standard sp1
Sql server 2005 sp1 ( I have only one instance )
System configuration = 3.83 GB of RAM
The total is 3.83GB of RAM, but When i right click the sql server
instance , select 'Properties' and then on the 'General' tab , i
see 'Memory' assigned to that instance as = 3927 (MB)
Does this mean that, someone already has enabled AWE option. That's
the reason its showing memory = 3927 MB. (or) i have to enable it
externally.
Can someone reply as soon as possible plz.
Thanks.
now i have enabled awe in sqlserver 2005.And i set the LOCK PAGE IN MEMORY in group policy.but while i see the sqlserver agent log ,i find that the log shows 4 processor(s) and 4096 MB RAM detected,in fact my server have 8GB RAM.so anyone can help me ?Thanks
i have see the sqlserver log ,and find awe enabled
I've looked in several books in addition to the online SQL Server documentation, and I can't find a way to determine whether or not a trigger is enabled or disabled. I know how to enable/disable, I just can't figure out the current status of the trigger. It's not returned in sp_help or sp_helptrigger, and there's no indication in Enterprise Manager of a trigger's status. Does anyone out there know how to do this?
View 1 Replies View RelatedI'm tryng to restore a single table (12000 records) from a database backup, but during the restore process I receive the message :
DBProcess is dead or not enabled.
If I try to restore a smaller table (9000 record) there is no problem.
Any idea?
Thanks!
I execute the following:
-- Turn on advanced options
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
-- turn on clr
sp_configure 'clr enabled', 1
GO
RECONFIGURE;
GO
and get this error:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
When I run surface area config, I select Configuration for Features, but under MSSQLSERVER, I do not see the entry for Database Engine that should have the CLR Integration under it.
Any suggestions? Thanks in advance.
Hi,
I'm using SQL Server 7.0. I'd like to know if there is anywhere in the information schema or system tables where I can tell that a constraint has been flagged as 'enable constraint for INSERT AND UPDATE' or not.
Thanks in advance,
Darrin
Hi,
I tried to run the following script from SQL2005 server A (publisher) from Database 'TestDB' to SQL2005 server B
(subscriber) database 'TestDB'
-------------------------------------------
exec sp_addsubscription @publication = N'publish_TestDB_FromCA', @subscriber = N'subscriberTest', @destination_db = N'TestDB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all',
@update_mode = N'read only', @subscriber_type = 0
with following error:
Msg 14013, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 252
This database is not enabled for publication.
Any idea?
Thanks
David
Hello,
Can anyone help me how to solve this problem ?
I had message "DBPROCESS is dead or not enabled" from my Server SQL v.4.2.1b.
I hope anyone want to help me.
Regards,
Susan
If TCP/IP is the only protocol enabled on the General tab of the Client Network Utility, does it mean that other protocols are disabled and will not be used in communicating to SQL Servers on other machines?
In this case, should the registry HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServer ClientSuperSocketNetLib only contain the Tcp entry?
If I have alias setup to use other protocol such as Named Pipes, does it mean that the alias cannot be connected since Named Pipes is not enabled?
Thank you for any help.
I have a VB6 application from which I want to create and schedule a jobs. The individual jobs should run once and be deleted when succesfully finished. Almost everything works fine. But enabling the jobschedule won't work. In the sp below I set the @enabled parameter of sp_add_jobschedule to 1 (is also default) which should indicate that the schedule is enabled. How to fix this, or what I am doing wrong? I tried to place an execute sp_update_jobschedule at the end of the sp, but no effect at all.
CREATE PROCEDURE mis_CreateCustomJob (
@strUserName varchar(50),
@strDateTimeStamp varchar(14),
@strJobType varchar(10),
@intDate int,
@intTime int
)
AS
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @JobName VARCHAR(67)
DECLARE @ReturnCode INT
SELECT @JobName = 'jb_' + @strUserName + @strDateTimeStamp
SELECT @ReturnCode = 0
BEGIN
-- Add the job
DECLARE @strDescription VARCHAR(100)
SELECT @strDescription = 'Job aangemaakt met MUC door ' + @strUserName
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID OUTPUT ,
@job_name = @JobName,
@owner_login_name = N'Admin',
@description = @strDescription,
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
DECLARE @strCommand varchar(100)
SELECT @strCommand = 'SET ANSI_NULLS ON' + char(13) + 'SET ANSI_WARNINGS ON' + char(13) + 'go ' + char(13) + char(13) + 'exec mis_JobTest'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 1,
@step_name = N'Upload Contracttabellen',
@command = @strCommand,
@database_name = N'mis',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = N'HandmatigeUpload',
@enabled = 1,
@freq_type = 1,
@active_start_date = @intDate,
@active_start_time = @intTime
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Just curious to hear if anyone else has experiences this or knows a reason
why it maybe should be this way...
I have some jobs that are disabled accourding to the Job Activity Monitor view in Mgmt Studio. However, if I open the schedules details, this view says Enabled. Can a schedule be enabled for a job, even if the job in itself is
disabled maybe?
But, I can't see any enable/disable flag in the sysjobschedules table,
only in sysjobs.
We are running application on Ms SQL Server connecting to PowerBuilder 7.0.3, using MSS Microsoft SQL Server 6.X driver.
We have recently migrated the SQL Server from 2000 to 2005 SP2, in which databases are still at compatability level 80.
The application runs perfectly for one week, but later we received some occassional reports from users that database connection breaks after a
certain idle period. Below are the errors, usually error 1 appears before error 2.
Error 1
A database error has occured.
Database error code: 10025
Database error message:
Select error: Possible network error: Write to SQL Server Failed. General network error. Check your documentation.
Error 2
A database error has occured.
Database error code: 10005
Database error message:
Select error: DBPROCESS is dead or not enabled.
We couldn't find any related errors in the SQL Server log. Does anyone has the idea of what's wrong or how to trace the connection brokerage?
Is it possible to check if xp_cmdshell is enabled on a server or not? (Using T-SQL*)
How would you perform this check?
I am trying to mirror a database and I keep getting the error Msg 1469 saying that "Database mirroring cannot be enabled because the database is an auto close database on one of the partners. Well.. the database properties is saying "false" under auto-close. What else could be wrong?
View 12 Replies View RelatedHello,
I've been trying for two days now to get SQL Cache Dependencies to work. So far, nothing has worked, and I have been around the block a few times now on this one. So now I'm going back to basics, as I think my code and queries are fine. My first question is how to confirm that I have a Service Broker that is up and running. I am using SSX as my database engine. Other posts mention how the "look at the service broker folder", and I don't see a folder anywhere. Can someone tell me what to look for? I've added various SP's based on tutorials on web sites, so I can't tell if those SP's are mine or theirs at this point (I've been at this too long). Where is the "folder" the other posts have mentioned? Do I need to do anything special to get it? Did I install all the right files?
Mike
I've Created a DLL Library and added it as an assembly in a database when I make a select statement from SQLCmd using my functions it runs fine but when I try to Create View from the VB 2005 Express Development Environment it Gives me the Error
SQL Execution Error
Error Message: Execution of user code in the .net Framework is disabled. Enable "clr Enabled" configurartion option
note that CLR Integration Option of the SQL server is ON and I've Tried sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGUREform SQLCMD in vainwhen I execute the select statement from the SQLcmd it runs fine but when I try to make a View from the Developmet Environment i gives me that error Please Help
Does annybody know how I can chance de User Instance to True.
I don't mean chanching it in web.config but by the SQL Server 2005 configuration.
I try to add a database to my project but i get the following message.
Generating user instance in SQL Server is disabled. Use sp_configure 'user instance enabled' to generate user instance.
Thanks for responding.
Erich.
Hi,
I'm using bcp and sqlcmd against SQL 2005 engine. They both work fine for long periods of time, but every few days they stop working with an error something like this:
A connection could not be establed - actively refused - this might be because remote connections are not allowed or the named pipes protocol is not enabled.
The thing is, remote connections ARE allowed, and named pipes ARE enabled. I have to recycle the SQL serverice to make it start working again.
Here are the commands that stop working.
sqlcmd -Ulowcostbuy -Plcb01 -S127.0.0.1 -id:homeWeBuysql runcate_bid_request.sql
bcp low_cost_buy..bid_request in d:homeWeBuyuploadunzipped
equest.dat -S127.0.0.1 -Ulowcostbuy -Plcb01 -fd:homeWeBuyformatsid_request_in.fmt -ed:homeWeBuylogid_request_in.err
Another note: sql is listening on port 1737, not the standard 1433 (or whatever it is).
Can anyone please help!? This is a very high profile application; having it die every few days is NOT an option!!
HiI'm having no end of problems with Visual Web Developer and SQL Server 2005 Express Edition.
View 1 Replies View RelatedI am getting a reference problem in APP_Code when I am creaing LinQ enabled website. When I am creating LinQ enabled website at that time it adds some dll like System.Query, System.Data.DLinq, System.Xml.XLinq and in web.config file it adds
<system.codedom> <compilers> <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CSharp.CSharp3CodeProvider, CSharp3CodeDomProvider"/> </compilers> </system.codedom>
automatically. Now the problem is when I add references like System.Query it does not show the Query namespace (It does not show any of the external dll added in the bin folder). And if we comment the
<system.codedom> <compilers> <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CSharp.CSharp3CodeProvider, CSharp3CodeDomProvider"/> </compilers> </system.codedom>
in web.config file it works properly that means it shows System.Query and other refrences.
Please if there is any solution do reply,
Thank You..
Hello
I am using
I created sqldata source, tables and also a gridview.
I am using sql server express with prof VS 05.
I CANNOT USE the executable aspnet_regiis.exe on SQL server express.( you need the full version)
I get the error"Debugging failed because integrated windows authentication is not enabled"
gino
I´m trying to enable full-text indexing in SQL Server Management Express 2005.
I can see the option is there (Database name - Properties - Files), but the options is blurred and I´m not able to select it.
How do I solve this problem?
Getting desperate trying now...
I am unable to access the default port 1433 on my SQL 2003 server. There is no firewall. I run telnet <ip> <port> and get "Connection failed" which explains my inability to connect to the server for weeks. I have being unable to connect to the server after I uninstalled sql 2000 and reinstalled it on my 2003 server. Some of the things I have checked is Network Configuration in EM. Named Pipes and TCP/IP is installed with port set to 1433. HKLMSoftwareMicrosoftMSSQLServerClientSuperSocketNetLibTcpDefaultPort port is set to 1433. Is there anything I need to configure on the server side to have the default sql server port enabled.
Thanks
We have bulk copy option enabled for our DB and we really use it. Will it be possible to set up a snapshot replication over the Internet of particular tables to a remote server from which the data will be only retrieved and never changed? Also, is it necessary to have PKs in all tables for this one-way snapshot replication? (for transactional replication it is needed, as I know)
Thanks a lot!
Andy.
Hi all,
I am trying to get a MS Access Database to work on my companies intranet. Everything works fine on my laptop with local file paths. After transferring the files to the web server and changing file paths, I get the 2 following errors.
"Data provider could not be initialized"
"Not a valid file name"
I assume that I am having a problem relinking the database, but unsure of how to do this.
All help is greatly appreciated.