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

"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

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

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

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? ...


4

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


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

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

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

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


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

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, ...


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

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 ...


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

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

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 ...


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

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.


2

Xp_cmdshell would work fine - just access the file using the UNC path (\server1\share\file.exe). As the other person mentioned, make sure you have proper permissions for you sql agent service account to access the file. If, instead, you actually want to run the file on the remote server, you can use a tool called PSEXEC that will run am exe on another ...


2

The only way a login has rights to a server is if those rights have been granted. The question is likely: How did these rights get granted? If DOMAIN\USER_A is a member of some Windows group, then through the rights granted to the group the DOMAIN\USER_A login exists in the server. This is true even if there is no individual entry in ...


2

The URL you are currently using is for the report manager. Report RDLs are deployed to the report server URL, so change the path to http://localhost/reportserver. Alternatively, you can find the correct URL for your server configuration in the Reporting Services Configuration Manager > Web Service URL (generally located in the Start menu > All Programs > ...



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