Tag Info

Hot answers tagged

6

Sadly mirroring of the distributor isn't supported. I'm not sure if it would work as the server names wouldn't be correct when the agents would log in, not to mention you'd have to manually setup all the agents on the backup box. Currently your only HA option for the distributor is Windows Clustering.


6

The transaction log does not not contain statements, it contains the physical changes occurred in the database. If you see a log record that indicate a delete you cannot know if this was a DELETE statement, a MERGE statement or a wide (split) UPDATE statement. If you see an operation indicating an INSERT you cannot know if it was an INSERT (...) VALUES (...) ...


6

use dbx; select foo from db1.dbo.table join db2.dbo.table on condition where some_function(); This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is ...


6

Well, you could not grant execute on it explicitly to end users (deny it even), and only enable it in stored procedures that use EXECUTE AS with some login that does have execute permissions. Then grant execute on only that stored procedure to the user that needs to run the command. First, make sure xp_cmdshell is enabled for the instance: EXEC ...


6

You should be using the catalog views instead of the old system tables (e.g. sys.objects instead of sys.sysobjects, sys.columns instead of sys.syscolumns). The system tables still exist, but they are there for backward compatibility only. Some of them have been moved to the hidden system database, mssqlsystemresource, and/or are only visible when connecting ...


5

You can also setup a proxy account using sp_xp_cmdshell_proxy_account to allow non-administrators to use xp_cmdshell. This will allow you to setup a less privileged Windows account rather than xp_cmdshell always using the SQL Server service account. This is similar to setting up proxy accounts for SQL Agent jobs.


5

"Isn't it possible to scale database servers through virtualization, like forever?" Yes, you can make more and more database servers and you can also make a larger and larger database server (but the scale up limit can be reached very quickly), but in the scale out scenario are the servers you mention supposed to cooperate in any way or are they ...


5

I wrote a series on SQLServerCentral about baselines that might be of interest to you: http://www.sqlservercentral.com/Authors/Articles/Erin_Stellato/351331/ And as Shawn so kindly mentioned, I also have a Pluralsight course. If you have more questions, feel free to contact me (erin at sqlskills dot com). Erin


4

Assuming SQL Server getdate gives local server time getutcdate gives utc / gmt There is no timezone setting or changes possible inside SQL Server


4

The only option to think about is disk layout but you only have six of them. This isn't enough for "optimum performance" A RAID 1 volume with 2 disks will be around 135GB so if your database is 100GB+ you'd need RAID5/RAID 10 with 4 disks, but then you don't have enough for separate logs/OS/Tempdb volumes. As an aside, with 100GB+ DB why only 16GB RAM? ...


3

The no-beep option causes the mysql command line client not to beep when errors occur. If that's an option you want, it should be in the [mysql] section of your my.cnf. This error suggests that it's in the [client] section of your my.cnf, which causes mysql_upgrade to throw the error since the option isn't recognized. Check your my.cnf and either remove ...


3

It seems that postgresql doesn't like your postgresql.conf file. The log message clearly says that it wasn't expecting a " character on line 392, but it doesn't tell you which one (of two " on this line) is it. I would assume that there is an unclosed " earlier in the config file.


3

I one up'd the answer above because, technically, it's correct on how to accomplish it. However, I would make the opposite recommendation. Meaning, I would recommend using linked servers for what you are doing. This is because it seams like you're asking the question about architecting Federated Servers using Distributed Partitioned Views techniques. There ...


3

You can indeed do this, but I recommend against it. You can create a linked server on DB-SERVER-1, connecting it to DB-SERVER-2. You then could write a query using four part naming convention as follows: (assuming you are running this query on DB-SERVER-1) select * from DB1.dbo.YourTable a join [DB-SERVER-2].DB2.dbo.OtherTable b on b.ID = a.ID ...


3

So far (I'll update this as you provide more info). 2 disk RAID 1 for the OS, 2 disk RAID 1 for the data, 2 disk RAID 1 for the transaction logs. These are the kinds of questions you should be asking before you purchase hardware.


3

No there is no rollback. You will need to restore from the most recent backup that you have.


3

I don't think you can get the query from the transaction log itself - now I could be wrong but I have never seen it done - though I have never tried either. You can however get the data that was modified during a transaction. The article below presents a stored procedure that will read the modified data from the transaction log; I haven't used it myself but ...


3

Please keep in mind that prior to MySQL 5.1.38, InnoDB was not designed to access multiple CPUs. You have one of two options to get MySQL to take advantage of multiple CPUs. OPTION 1 : Install MySQL 5.5 OPTION 2 : Install MySQL 5.1 InnoDB Plugin There are new options available since MySQL 5.1.38 (InnoDB Plugin Only) that is now fully available in MySQL ...


3

Your statement seems to be correct. It could also be written like this: DELETE ii FROM inventoryitems AS ii WHERE ii.itemid = 2340000 AND EXISTS ( SELECT * FROM characters AS c WHERE c.characterid = ii.characterid AND EXISTS ( SELECT * FROM accounts AS a WHERE a.id = c.accountid ...


3

Please stop looking at syslogins. This is a deprecated system table that is now a backward compatibility view and is only there for that purpose (to prevent breaking old code). The user is dbo probably because they created the database. You should be able to find the login the user is associated with using: SELECT login_name = sp.name, user_name = dp.name ...


3

There are several reasons why this can happen: Make sure you are not using the query cache on your old server and not on the new one. Execute always benchmarks with SELECT SQL_NO_CACHE. Execute the same queries several times. If it is a new server -the buffer pool, filesystem cache and other buffer are probably not yet settled for best performance, ...


2

This is the one I would use, courtesy of Kimberly Tripp: SELECT objtype AS [CacheType] , count_big(*) AS [Total Plans] , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs] , avg(usecounts) AS [Avg Use Count] , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1] , sum(CASE ...


2

What you can do is restrict access to the SQL server and the Windows server that SQL is on. There are database server level roles with sysadmin giving you the highest privileges. Review this link to see what you will need to give your temp. The temp should never be in the sysadmin server level role, based on what I read from your requirements. Then there ...


2

SQL Server 2008 includes all sorts of auditing which can be used to monitor what is done in SQL Server. I've written about this in "Securing SQL Server" which may be of some use.


2

Note you say this is in the server log and there are no known problems with clients. Consequently, I don't think that it is libpq per se. Additionally on doing some additional searches, it looks like this is a standard Windows "connection refused" message, which suggests that PostgreSQL is trying to connect to something and being refused. If you are ...


2

For this scenario, the database server should not be publicly exposed, for obvious security reasons. You should expose a public interface (API) of your application, probably by using something like web services. The mobile application instances then only communicate directly with the API. This separation (decoupling) allows for all sorts of good things to ...


2

The easiest way I have used to see if any objects are using a particular command (e.g. those that use a local time zone, such as GETDATE() and CURRENT_TIMESTAMP) is syscomments. The text column of this table in SQL 2000 has the actual command that makes up the object. The caveat to this is if the procedure was created using WITH ENCRYPTION you will not be ...


2

Start by commenting out the offending line in your postgresql.conf and retry. It is possible that a change made some time ago caused this problem and you are only now seeing it because a reload operation or similar failed, and so PostgreSQL was running on old configuration. If that works address what you have on that line (maybe get rid of the quotes?) If ...


2

You cannot have an unique constraints backed by an aligned index (or plain unique non-clustered indexes) unless you add the partitioning column to the the unique expression. So if you have partitioned your table on column [datetime] then your unique constraint (or the unique index) must be ([datetime], [xyz]). Since more often than not this is not ...


2

No. The instance is configured to use cluster resources and as such if the cluster is removed the instance will not function correctly. If you like to play with fire in an experimental-not-in-production-learn-by-trial-and-error way, you can do the opposite of How to move SQL Server from Standalone to Cluster environment.



Only top voted, non community-wiki answers of a minimum length are eligible