Tag Info

New answers tagged

0

You can use Data Pump for SQL Server which allows transfering tables from any ADO-compatible data source including Oracle to MS SQL Database. It is not free but can be setup quickly.


2

Individual accounts are going to be much harder to administer. They give you more granular control over access, but you can not keep out your Active Directory administrators. Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance. And, as a very good DBA told me once, if you can't trust your domain admins ...


5

If your log file is corrupt, my concern is that a backup/restore would retain the corruption. My approach (which would probably be faster to complete) would be: Run a full backup. Detach database. Delete/rename the log file. Attach the database and rebuild the log file. Take a second full backup. To attach the database and rebuild the log file, it's ...


4

The log vs. data separation is sound advice, but the devil is always in the details. A write intensive workload that commits frequently requires the log stream to flush to disk as fast as possible. This in turn requires a sequential write pattern, as the log does, undisturbed by any other operation. Hence, isolate the data writes (and reads!) from the log ...


0

DBCC CHECKDB makes extensive use of tempdb. It relies on a mechanism similar to snapshot isolation to make sure it always sees a transactionally consistent image of the database, independent what else might be going on. The error you receive is most likely due to the fact that there is not enough room on the drive that tempdb is on. However, the error also ...


6

There are couple of ways to do such forensic analysis : Using Default trace (provided that the default trace is running). The default trace create 5 *.TRC file that are recycled when they arrive to 20 MB size. : -- create a test procedure .. so that we can drop it use test_kin go create procedure usp_ABOUT_TO_DELETE as select name from sysobjects where ...


6

For the future you can use a database trigger to log the DDL events. SQL Server DDL Triggers to Track All Database Changes


4

In order to transfer logins, you will need to use the sp_help_revlogin script and procedure. This will allow you to transfer logins and retain the password from on instance to another.


0

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this. The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on ...


0

You may not use temporary table to achieve the result you need. Please try this query: with joined as ( select em.*, e.* from [Event] e inner join [EventEmployee] eem on (eem.EventID = e.EventID) inner join [Employee] em on (em.EmployeeID = eem.EmployeeID) ) select distinct EventID, EventDate from joined where EventDate = (select max(e.EventDate) ...


2

Here is the sample for DDL trigger that captures SQL query too: CREATE TRIGGER Audit_DDL ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS DECLARE @event xml; SET @event = EVENTDATA(); INSERT INTO Audit_DDL_Events VALUES ( REPLACE(CONVERT(varchar(50), @event.query('data(/EVENT_INSTANCE/PostTime)')), ...


0

Who knows! Just add something like and sja.start_execution_date >= DATEADD(dd, -1, GETDATE()) This is my current answer in my environment. This works for me as the greater script this is a part of runs on a daily basis. It does feel like a better answer or explanation should be available.


2

I've never heard of comments causing a problem. The code you captured from the running server and the posted source are for different SPs. It makes me think that maybe the ..SU01 version is still in your database and being executed (from a job perhaps?) whereas you are trying to debug the ..SU012_XML code. For testing you could add another INSERT to the ...


0

I don't think there's a general best option in this matter. It depends on many things; For instance, if you are dealing with high activity on the database, or even have memory issues (not you, but the database jk) you might want to grab the data and leave asap, that means, move most of the computations on SSRS; Leave the simple aggregations (like totals, ...


0

Note that you can use the following expression when setting the condition rule using the Expression grid by specifying: @Name LIKE ‘ACME_%’ in order to limit the condition to those databases whose names starts with ACME string


0

Are you using a named instance or a default one? Did you enabled a remote connection on that instance? Right click on the server and in the Server Properties dialog under the Connections tab check the Allow remote connections to this server option In the TCP/IP Properties -> IP Addresses -> IPAII: For a named instance you should set set the TCP Dynamic ...


2

A very fast shot: do you need the sorting? Especially in your getUpdatedAgentGroup function? This may can be a quickfix. Another issue are indexes, have you them in place?


0

the tracing was enabled for ODBC and it was reponsile for this file. I do not know why after Stoppin tracing from below screenshot all instances and agents was using this file and we have to stop them one by one until deleting was possible.


0

We had a problem with AMD chipset where they would drop into power saving mode, reducing their clock speed by 50%. Worth a look.


3

For any RDBMS, the bottleneck for performance is (usually) disk performance. Compared to all other parts of the computer, disk I/O is the most expensive in terms of time required. To this end, RDBMS engines focus on operating on as much data as possible in memory, as RAM is much faster to read and write to than physical disk. It also provides a safety ...


1

It depends on the commodity of the query. Being extra specific should never hurt in this instance, but for something that simple the query planner in SQL Server will be bright enough to imply either o.OrderID = @xorderid or od.OrderID = @xorderid from the other because of o.OrderID = od.OrderID so you should not need to specify both. Of course other ...


2

The best way to determine this is to simply look at the execution plans. Typically the optimizer will "condense" a lot of the operations in a query down. For example, if you include "WHERE 1=0" in your query it will see that, know it to be false, and do as little work as possible to return an empty set. In fact, in this case, it does 0 I/O operations as far ...


1

If your server has been up for a reliable amount of time (e.g. at least one business cycle), you can tell when there has been any read or write against any table in a database using DMVs like sys.dm_db_index_usage_stats. There isn't really a reliable way to tell who last "used" a database. SQL Server doesn't log this information unless they do something ...


2

Make sure his server login exists.(Meaning you dont delete this!!!) DENY VIEW ANY DATABASE TO user (I think this is where you are stuck right now as the user is unable to view any databases.) Then Delete his user logins from those 3 databases that he is a database owner on. Then go to each database > right click > properties > files > Choose his login as ...


0

MBR cannot handle partitions bigger that 2 TB, thus if your partition is bigger than 2 TB you have to use GPT. There is no performance gain as far as I know. It's all about the capacity!


1

Do you have Microsoft Dynamics installed? There's a clear reference to a Master80 account in the above link: Expand your instance of SQL Server. Expand Security. Double-click Logins. Right-click Master60Sp (Master80 in Dynamics SL 2011), and then click Delete.


1

No. You're referring to SQL Server Management Studio, presumably, and that behaviour doesn't exist there. There are other applications you can use that will leverage FKs in that way though.


3

Actually in order to be blocking a session only has to hold a lock to a resource something else wants. It does not actually have to be doing anything. For example if you run the following BEGIN TRANSACTION UPDATE MyTable SET Col1 = 'ABC' And don't run a COMMIT then you are going to leave a transaction open. The session is not doing anything and in ...


0

You can use a combination of tools. I'll give you an abridged list of the ones I use. First, I assume you have database mail configured. Second, you can trigger a lot of run-of-the-mill alerts using SQL Server Alerts. You can also run a stored procedure using a scheduled job (say every five minutes or some interval like that) and fire the user counters ...


2

Nobody can really tell you exactly why this person coded the WHERE clause this way. I can tell you that I can't think of a reason that form is better than: r.roomprovider_id <> br.roomprovider_id (And while I can interpret how the code is currently written, I am not sure of the expected/intended behavior - should a row be included or excluded when ...


4

Yes, there is, though you got to use RESTORE command anyway. Instead of RESTORE DATABASE, use RESTORE FILELISTONLY to get a detailed view of files in the backup. The Size column tells the file size in bytes. RESTORE FILELISTONLY FROM DISK = N'v:\MyBackup.bak'


1

While PowerShell is high on my list of things to learn I don't know much about it right now and I needed a csv quick, so I wrote a simple query in T-SQL to get the data I needed out of the XML in a tabular format and then just copied that into excel and saved it. -- get XML from file DECLARE @xml XML; SELECT @xml = CAST(A.[BulkColumn] AS XML) FROM ...


3

You can grant the Service Start/Stop right for the SQL Server and Agent service to the Developers or their Windows group. You can use Use Group Policy Security Templates or Use Subinacl.exe Above all are described in this KB article How to grant users rights to manage services As a side note, you can use sysinternals tool - accesschk.exe. It will ...


0

If your SSIS package runs with the appropriate file system permissions, you can create a template Excel file with the desired formatting and copy that file to a new one (using a file system or script task) where the data will be exported. If the sheet will contain a variable number of cells that must be formatted, you can use conditional formatting in the ...


1

How much I/O can a normal disk and a SAN disk can handle? You have to read the manual to find out or you can do a stress test using SQLIO or SQLIOSim. Brent has a good article on : SQLIO Tutorial: How to Test Disk Performance To see if underlying storage is a problem, you can use sys.dm_io_virtual_file_stats. It will show you where the hot-spots are ...


0

If you are trying to make foreign key and your child table already have some data that does not match with your parent table, you will get this error message. For Example: CREATE TABLE MyParent(Job_id int PRIMARY KEY,Job_Name Varchar(50)); INSERT INTO MyParent Values(1,'CEO'); INSERT INTO MyParent Values(2,'Business Manager'); INSERT INTO MyParent ...


3

You should be able to turn the decode into a case: UPDATE [SCHEMA].TLS_FAC_PRC_ONG SET FPO_RID_OWNER = CASE FPO_RID_OUTSTANDNG WHEN 'NONE' THEN FPO_PID_FACILITY WHEN NULL THEN FPO_PID_FACILITY ELSE FPO_RID_OUTSTANDNG FPO_CDE_OWNER = CASE FPO_RID_OUTSTANDNG WHEN 'NONE' THEN ...


0

UPDATE [SCHEMA].TLS_FAC_PRC_ONG SET FPO_RID_OWNER = CASE WHEN FPO_RID_OUTSTANDNG='NONE' THEN FPO_PID_FACILITY WHEN FPO_RID_OUTSTANDNG IS NULL THEN FPO_PID_FACILITY ELSE FPO_RID_OUTSTANDNG END, FPO_CDE_OWNER = CASE WHEN FPO_RID_OUTSTANDNG='NONE' THEN 'FAC' WHEN FPO_RID_OUTSTANDNG IS NULL THEN 'FAC' ELSE 'OST' END DECODE function is a shortcut? for CASE


1

Don't know why but it is kind of documented. sp_rename (Transact-SQL) Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. sys.sql_modules is a view that uses the base table sys.sysschobjs. sp_helptext uses the view ...


1

Stopping the server and disabling the sa account seems like overkill to drop a database. Next time this happens, the following should be sufficient: USE [master]; GO ALTER DATABASE [dbname] SET OFFLINE WITH ROLLBACK IMMEDIATE; GO DROP DATABASE [dbname]; The connection that could be sneaking in could be from anything - SQL Server Agent, a linked ...


0

YES!!! I DID IT!!! When I ran select * from master..sysprocesses where spid = <spid>, I realized that the connection was being made from the sa user account. I still don't understand what in the world was doing it, but I temporarily disabled the sa account and restarted the server. It wasn't able to make a connection again and I dropped the ...


0

For speed of your query as shown, the primary impact on performance would be whether or not you have an index (which should probably be either a primary key or else unique) on id. That index would allow a seek to the row that needs to be updated. With regard to column order city or first_name for the two updates in your sample, there is no difference. You ...


0

You are asking SQL Server to perform 19.5 Billion string manipulations (300,000,000 rows X 65 columns), all in one giant transaction. That is assuming vbep_Full is a regular table with simple column definitions (not a view with joins to other tables and/or with computed comlumns). If your CPU is not maxed out, you could try breaking the work into multiple ...


2

I can't think of any scenario where the order of columns in your UPDATE statement will have any impact on performance. Of course, this is something you could test. I also can't imagine any scenario where it's a good idea for a single table to have 2,000 columns.


7

This doesn't really seem all that crazy, but note that some of the UI dialogs might not have completely up-to-date information (this is why we have things like DBCC UPDATEUSAGE), and rounding can also be involved in some of those calculations. Finally, the dialogs show you total space for the whole database, but unallocated space is only calculated for the ...


3

You can't restore a 2008 backup on SQL Server 2005. Simply not possible. You'll need to either: (a) Restore it on 2008, then use Import/Export wizard, Generate Scripts Wizard, Copy Database Wizard, SSIS, 3rd party tools like SQL Compare, etc. to move the structure and data to the 2005 instance, or (b) Upgrade. Developer Edition is $50 or so.


8

You can create a view to hide the rest of the rows when they do exist: CREATE VIEW dbo.Top1000Rows AS SELECT TOP (1000) ...columns... FROM dbo.table_name ORDER BY DateCreatedUTC DESC; GO Then periodically run this in a background job (just play with the frequency until you're happy with how often the table itself is "off", and by how much): ...


2

Besides using explicit transactions, I typically write a SELECT statement first to see how many rows would be affected, and then copy the WHERE clause to my UPDATE statement. SELECT * FROM [SomeTable] WHERE [SomeDateColumn] > '3/1/2014' From this I see 100 rows would be updated. So I either copy the WHERE clause, or convert the SELECT into an UPDATE ...


3

Study the following: BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION Also read Paul Randall's A SQL Server DBA myth a day: (26/30) nested transactions are real Also read XACT_ABORT @@ROWCOUNT IF ...ELSE GOTO @@TRANCOUNT More advanced topics, optional until they're not: SET TRANSACTION ISOLATION LEVEL Gail Shaw's intro to deadlocks Then ...


2

My initial thought was a logon trigger but, sadly, those only fire after a successful login. The documentation for the Audit Login Failed Event Class says that failed login audits will include the application name, but I haven't observed that so far (I only tested failed logins from changing connections within Management Studio, so maybe it is exposed in ...



Top 50 recent answers are included